locked
Modifying SQL Code to pick the Dropdown List Instead of Constant Text RRS feed

  • Question

  • User1250602945 posted

    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 aspx page (Design Mode).

    Any suggestion will be very much helpful.

    Thursday, December 13, 2018 11:09 AM

All replies

  • User-1174608757 posted

    Hi Sixthsense6

    According to your description ,I have made a sample here.To set the parameter in sqldatasource control,you can set the dropdownlist as the parameter control of sqldatasource. Then you can set the selected value of dropdownlist as the paramter in your sqlcommand.Here is the code ,I hope it will help you.

    dropdownlist.aspx:

    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
            <div>
                <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1" Height="251px" Width="327px">
                    <Columns>
                        <asp:BoundField DataField="fruit" HeaderText="fruit" SortExpression="fruit" />
                        <asp:BoundField DataField="color" HeaderText="color" SortExpression="color" />
                    </Columns>
                    
                </asp:GridView>
                <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="true">
                    <asp:ListItem>Apple</asp:ListItem> 
                    <asp:ListItem>Banana </asp:ListItem>
                    <asp:ListItem>Pine</asp:ListItem>
                    
                </asp:DropDownList>
                
                <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:mssqlserver %>" SelectCommand="SELECT * FROM [db] WHERE FRUIT=@FRUIT"  CancelSelectOnNullParameter="true" >
                    <SelectParameters>
                        <asp:ControlParameter ControlID="DropDownList1" Name="FRUIT" PropertyName="SelectedValue" ConvertEmptyStringToNull="true" />
                    </SelectParameters>
                </asp:SqlDataSource>
            </div>
        </form>
    </body>

    My database:

    You can see:

    Best Regards

    Wei Zhang

    Friday, December 14, 2018 10:22 AM