Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute:

i am using sql datasource and a checkboxlist to filter a datalistview. i want to make a parameter for sqldatasource from code behind and use SQL IN to filter datalistview rows. when i check 1 checkbox in checkboxlist everything work great... but when i check more than 1 checkbox datalist view takes no data. this is my onselecting code for sql datasource:

protected void DS_subgroupID_changed_Selecting(object sender,SqlDataSourceSelectingEventArgs e)
{
    List<String> selected = new List<String>();
    foreach (ListItem item in CheckBoxList_subgroup.Items)
        if (item.Selected)
        {
            selected.Add(item.Value);
        }

    String commaselect = String.Join(",", selected.ToArray());
    e.Command.Parameters["@product_subgroup"].Value = commaselect;


}

and here is my sqldatasource control:

<asp:SqlDataSource ID="DS_subgroupID_changed" runat="server"
   ConnectionString="<%$ ConnectionStrings:nimrokhDBConnectionString %>" 
   SelectCommand="SELECT * FROM [tbl_product] WHERE (([product_group] = @product_group) AND ([product_subgroup] IN (@product_subgroup)))"
   OnSelecting="DS_subgroupID_changed_Selecting">
        <SelectParameters>
            <asp:ControlParameter ControlID="radio_list_group" Name="product_group" PropertyName="SelectedValue" Type="String" />
            <asp:Parameter Name="product_subgroup" />
        </SelectParameters>
        </asp:SqlDataSource>

i have 1 table subgroup that contains subgroupID as int and subgroupname, and i have a table product that contan subgroup to store subgroup ID but this is in nvarchar. i wonder if this cause the problem. because my checkboxlist value is set to subgroupID form subgroup table.

i appretiate any help. thank you guys.

share|improve this question
1  
Probably the problem is the SQL IN. You need to use a split function on your comma separated string and join to the results. – Tab Alleman May 8 at 12:23
    
but i have read this here:4guysfromrolla.com/articles/012010-1.aspx can you give me a link that describe that manner? – Ashkan Hafezi May 8 at 12:37
    
Yes, this source points you to the correct direction. The problem is you are using IN keyword in conjunction with a parameter, which, as the above source explains, does not work. – Giorgos Betsos May 8 at 12:44
    
Here's an example: itdeveloperzone.com/2013/03/… – Tab Alleman May 8 at 12:51
1  
In a particular way, yes. This is not the way you are using it. You have to use some sort of dbo.Split UDF, as the article clearly states. – Giorgos Betsos May 8 at 13:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.