locked
how to bind in query in sqldatasource based on listbox multiple selected item RRS feed

  • Question

  • User286910929 posted

    Hi..

    I need to bind sql IN query in sqldatasource the IN query will bind on dropdownlist and dropdownlist will show databased on Listbox multiple selection how to do this 

    I need to method 

    1) normal sql query

    2) another one is storeprocedure


    Tuesday, December 14, 2010 2:14 AM

Answers

  • User-1460196090 posted

    Hi there.

    I think the string is not created correctly and that's why this happens.

    The clean way I would go here would be by creating Stored Procedure and use the stored procedure.

    I have tested this and it works for me using Stored Procedure.

    Here it is:

    -- procedure
    CREATE PROCEDURE sp_ProcedureName (@filterString nvarchar(200))
    as
    declare @selectQuery as nvarchar(1000)
    set @selectQuery = 'SELECT ItemName FROM [ItemMaster] a WHERE a.ID in ('''+@filterString+''')'
    exec (@selectQuery)
    --print(@selectQuery)
    
    
    --testing the procedure
    -- decoment print(@selectQuery) to see how its going to create the query
    declare @filterString as nvarchar(100)
    set @filterString = 'C00112'',''C00111'
    exec sp_ProcedureName @filterString


    The textbox values are the same as in your example

    <asp:TextBox ID="TextBox1" runat="server" Width="150px">C00111','C00110</asp:TextBox>


    and now SelectCommand for the SqlDataSource is the Stored Procedure name, in other words, here is the SqlDataSource code

        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
        SelectCommand="sp_ProcedureName" SelectCommandType="StoredProcedure">

    It should work.


    Regards,
    Hajan

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 14, 2010 2:00 PM

All replies

  • User-1161841047 posted

    For the normal SQL query you can use hidden file or invisible Control like Label or TextBox you set the DropDownList values in this format :

    ddl1.SelectedValue + ',' + ddl2.SelectedValue + ',' + ddl3SelectedValue

    then output will be ex: 1,4,6

    So you can use this in your Query :

    ..... where YourField in (?)

    Tuesday, December 14, 2010 3:50 AM
  • User286910929 posted



        <form id="form1" runat="server">
        <div>
        <asp:GridView ID="Gridview1" AutoGenerateColumns="false" CellSpacing="2" runat="server" 
        GridLines="None" OnRowDataBound="Gridview1_RowDataBound" ShowFooter="true"><Columns>  
    <asp:BoundField DataField="RowNumber" HeaderText="SI.No">  
    </asp:BoundField>  
    <asp:TemplateField HeaderText="Field1"><ItemTemplate>   <asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="SqlDataSource1"
            DataTextField="Item" DataValueField="Item">
        </asp:DropDownList><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
            SelectCommand="SELECT a.ItemName FROM  dbo.ItemMaster a WHERE a.ID in(@P)">
            <SelectParameters>
                <asp:ControlParameter ControlID="TextBox1" Name="P" PropertyName="Text" />
            </SelectParameters>
        </asp:SqlDataSource>
    </ItemTemplate>   
     
    </asp:TemplateField>   
    <asp:TemplateField HeaderText="Qty"><FooterTemplate>  
                            <asp:Button ID="AddNewRow" runat="server"   
    OnClick="AddNewRow_Click" Text="Add New Row" Width="94px"  />                      
    </FooterTemplate>  
    <ItemTemplate>  
                            <asp:TextBox ID="Text1" runat="server"></asp:TextBox>  
                          
    </ItemTemplate>  
    </asp:TemplateField>  
    <asp:TemplateField><ItemTemplate>  
                            <asp:LinkButton ID="LinkButton1" runat="server"   
    OnClick="LinkButton1_Click" Visible='<%# Eval("DeleteFlag") %>'>Remove</asp:LinkButton>   
    </ItemTemplate>  
    </asp:TemplateField>  
    </Columns>  
    </asp:GridView>  
            <asp:TextBox ID="TextBox1" runat="server" Width="150px">C00108','C00112</asp:TextBox></div>
        </form>
    


    I have a gridview item template drop down list2. And i have a textbox outside of the gridview.

    Am showing the Item in drop down list 2 values based on outside textbox value MS SQL IN Query.

    When the textbox have only value say C00108 then it showing the Item in drop down list2 belongs to C00108

    But, if it has mutiple values say C00108','C00112 then the Drop down list2 not showing any Item belongs to C00108','C00112

    why whats the problem in My Msql IN Query

    Tuesday, December 14, 2010 3:57 AM
  • User-1161841047 posted

    you need to check if the Textbox value in this syntax:

    'C00108','C00112'

    Tuesday, December 14, 2010 4:12 AM
  • User286910929 posted

    so how to do in controlparameter so that it can understand

    Tuesday, December 14, 2010 4:22 AM
  • User-1161841047 posted

    You don't need to change anything in ControlParameter, just send this formate to the query. Try it and give us the feedback!


    Regards.

    Tuesday, December 14, 2010 4:42 AM
  • User286910929 posted

    just send this formate to the query.,,

    how to do so any one help

    Tuesday, December 14, 2010 4:49 AM
  • User-1460196090 posted

    Hi there.

    I think the string is not created correctly and that's why this happens.

    The clean way I would go here would be by creating Stored Procedure and use the stored procedure.

    I have tested this and it works for me using Stored Procedure.

    Here it is:

    -- procedure
    CREATE PROCEDURE sp_ProcedureName (@filterString nvarchar(200))
    as
    declare @selectQuery as nvarchar(1000)
    set @selectQuery = 'SELECT ItemName FROM [ItemMaster] a WHERE a.ID in ('''+@filterString+''')'
    exec (@selectQuery)
    --print(@selectQuery)
    
    
    --testing the procedure
    -- decoment print(@selectQuery) to see how its going to create the query
    declare @filterString as nvarchar(100)
    set @filterString = 'C00112'',''C00111'
    exec sp_ProcedureName @filterString


    The textbox values are the same as in your example

    <asp:TextBox ID="TextBox1" runat="server" Width="150px">C00111','C00110</asp:TextBox>


    and now SelectCommand for the SqlDataSource is the Stored Procedure name, in other words, here is the SqlDataSource code

        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
        SelectCommand="sp_ProcedureName" SelectCommandType="StoredProcedure">

    It should work.


    Regards,
    Hajan

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 14, 2010 2:00 PM