必须将标量变量"@ MyDivision_1"声明为

问题描述

这很好:

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
     ConnectionString="<%$ ConnectionStrings:dbFOConnectionString %>"
     SelectCommand="SELECT sProductName,sDivision,sStockistName,Qty
                    FROM TblPresentStock
                    WHERE sDivision = @MyDivision_1 " >
     <SelectParameters>
         <asp:ControlParameter ControlID="ddlDivision"
              PropertyName="SelectedValue"
              Name="MyDivision_1"
              Type="String" />
     </SelectParameters>
</asp:SqlDataSource>

这也可以正常工作:

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
     ConnectionString="<%$ ConnectionStrings:dbFOConnectionString %>"
     SelectCommand="/* COLUMNS HEADERS */
                DECLARE @columnHeaders NVARCHAR (MAX)

                SELECT @columnHeaders  = COALESCE (@columnHeaders
                       + ',[' + sStockistName + ']', '[' + sStockistName + ']')
                FROM   TblPresentStock
                GROUP BY sStockistName
                ORDER BY sStockistName

                /* GRAND TOTAL COLUMN */
                DECLARE @GrandTotalCol  NVARCHAR (MAX)
                SELECT @GrandTotalCol = COALESCE (@GrandTotalCol + 'ISNULL ([' +
                       CAST (sStockistName AS VARCHAR) +'],0) + ', 'ISNULL([' + CAST(sStockistName AS VARCHAR)+ '],0) + ')
                FROM TblPresentStock
                GROUP BY sStockistName
                ORDER BY sStockistName

                SET @GrandTotalCol = LEFT (@GrandTotalCol, LEN (@GrandTotalCol)-1)

                /* GRAND TOTAL ROW */
                DECLARE @GrandTotalRow  NVARCHAR(MAX)
                SELECT @GrandTotalRow = COALESCE(@GrandTotalRow + ',ISNULL(SUM([' +
                CAST(sStockistName AS VARCHAR)+']),0)', 'ISNULL(SUM([' + CAST(sStockistName AS VARCHAR)+']),0)')
                FROM     TblPresentStock
                    GROUP BY sStockistName
                  ORDER BY  sStockistName
                ----------------------------------------------

                 -- DROP TABLE  temp_MatchesTotal

                /* MAIN QUERY */
                DECLARE @FinalQuery NVARCHAR (MAX)
                SET @FinalQuery =   'SELECT *, (' + @GrandTotalCol + ')
                AS [Grand Total] INTO  #temp_MatchesTotal
                            FROM
                                (SELECT sProductName,sDivision,sStockistName,Qty
                                    FROM  TblPresentStock
                                )A
                            PIVOT
                                (
                                 sum (Qty)
                                 FOR sStockistName
                                 IN ('  +@columnHeaders +  ')
                                ) B

                ORDER BY sProductName,sDivision
                SELECT * FROM  #temp_MatchesTotal
                UNION ALL
                SELECT ''Grand Total'','''','+@GrandTotalRow +',
                ISNULL (SUM([Grand Total]),0) FROM  #temp_MatchesTotal
                  DROP TABLE  #temp_MatchesTotal'
                 -- PRINT 'Pivot Query '+@FinalQuery
                 -- SELECT @FinalQuery
                 EXECUTE(@FinalQuery)">
    <SelectParameters>
        <asp:ControlParameter ControlID="ddlDivision"
             PropertyName="SelectedValue"
             Name="MyDivision_1"
             Type="String" />
    </SelectParameters>
</asp:SqlDataSource>

在上面的查询中，当我替换此行时:

In the above query when I replace this line:

SELECT ProductName, sDivision, sStockistName, Qty
FROM TblPresentStock

与此:

SELECT sProductName, sDivision, sStockistName, Qty
FROM TblPresentStock
WHERE sDivision = @MyDivision_1

发生错误:

唯一的补充是

WHERE sDivision = @MyDivision_1

如果我像下面特别提到部门名称，那么它工作正常，并且可以正确获取该特定部门的数据.

If I mention the division name specifically like the below then it's working fine and getting the data of that particular division without any error.

WHERE sDivision = ''Div1''

推荐答案

我只需要替换

WHERE sDivision = @MyDivision_1

使用

WHERE sDivision = ''' + @MyDivision_1 + '''

这篇关于必须将标量变量"@ MyDivision_1"声明为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！