CODEWITHSUNDEEP
sqlasp.netsql-server
Sixthsense
Sixthsense

Reputation: 1975

Modifying SQL code to pick the Dropdown list instead of constant text

In the below query I would like to redirect ''MyDivision_1'' (MyDivision_1 is a constant text) to a dropdown list selection.

<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 where sDivision = ''MyDivision_1''
                                    )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)">
</asp:SqlDataSource>

The above SQL query is in an aspx page (design mode).

Any suggestion will be very much helpful.

Upvotes: 1

Views: 97

Answers (1)

Tetsuya Yamamoto
Tetsuya Yamamoto

Reputation: 24957

You can use a parameter inside SELECT query like this:

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

Then use parameter name with ControlParameter inside SelectParameters option in SqlDataSource, by setting PropertyName="SelectedValue" from dropdown list:

<%-- Dropdownlist example --%>
<asp:DropDownList ID="DropDownList1" runat="server" ... />
    <asp:ListItem Text="SampleText" Value="SampleValue" />
    ...
</asp:DropDownList>

<%-- SqlDataSource --%>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
 ConnectionString="<%$ ConnectionStrings:dbFOConnectionString %>" 
 SelectCommand="..." ...>
     <SelectParameters>
         <asp:ControlParameter ControlID="DropDownList1"
              PropertyName="SelectedValue"
              Name="MyDivision_1"
              Type="String" />
     </SelectParameters>
</asp:SqlDataSource>

Note: The passed type in ControlParameter must match with type defined in SelectedValue attribute of dropdown list.

Related issue:

How to assign DropDownList selected value to SqlDataSource?

Upvotes: 1

Related Questions