0

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.

A ツ
  • 1,267
  • 2
  • 9
  • 14
Ashkan Hafezi
  • 49
  • 9
  • 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 08 '15 at 12:23
  • but i have read this here:http://www.4guysfromrolla.com/articles/012010-1.aspx can you give me a link that describe that manner? – Ashkan Hafezi May 08 '15 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 08 '15 at 12:44
  • Here's an example: http://www.itdeveloperzone.com/2013/03/using-variable-in-in-clause-in-sql.html – Tab Alleman May 08 '15 at 12:51
  • possible duplicate of [Parameterize a SQL IN clause](http://stackoverflow.com/questions/337704/parameterize-a-sql-in-clause) –  May 08 '15 at 13:20
  • @GiorgosBetsos above source describe that u can use IN keyword with a parameter but in a particular way. my code works fine when i click only 1 checkbox, and also 1 checkbox goes to sql server with parameter. – Ashkan Hafezi May 08 '15 at 13:21
  • 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 08 '15 at 13:23
  • @GiorgosBetsos please read this section of the article:Programmatically Converting a CheckBoxList's Selected Items Into a Comma-Delimited String.... i have used exactly that aproach ... – Ashkan Hafezi May 08 '15 at 13:43
  • Yes, but you *also* have to change the query used by `SelectCommand` to sth like `AND [product_subgroup] IN (SELECT Value FROM dbo.Split(@product_subgroup,','))` as the article states. You haven't done this part. – Giorgos Betsos May 08 '15 at 13:50
  • @GiorgosBetsos ... oops that was stupid ... thank you man ... i will do it. :D – Ashkan Hafezi May 08 '15 at 13:58

0 Answers0