Retrieve multiple values from CheckboxList using SQL WHERE IN

Question

I have a problem with the retrieval of multiple checkbox values in my program. So as you can see below, I'm trying to retrieve from my database based on a set of values from the checkboxlist.

If I select ONE check box value, the database would be able to retrieve the item, however if I select MORE THAN ONE checkbox value, nothing will appear. Can someone help me please? I've spent quite some time on it.

I've seen other solutions but it doesn't seem to have helped me. Thank you.

protected void chkBoxCategories_SelectedIndexChanged(object sender, EventArgs e)
{
    // Create the list to store.
    List<String> YrStrList = new List<string>();

    // Loop through each item.
    foreach (ListItem item in chkBoxCategories.Items)
    {
        if (item.Selected)
        {
            // If the item is selected, add the value to the list.
            YrStrList.Add(item.Value);
        }
        else
        {
            // Item is not selected, do something else.
        }
    }

    // Join the string together using the ; delimiter.
    Session["UPCategories"] = String.Join(", ", YrStrList.ToArray());
}

The following code is on the other page:

SqlDataSource1.SelectCommand = "SELECT sf.*, sfc.* FROM SkillsFuture sf INNER JOIN SkillsFutureCategory sfc ON sfc.SFCatID = sf.fkSFCatID WHERE sfc.SFCatName IN (@catnames) AND sf.SFPrice BETWEEN @min AND @max AND sf.SFCertStatus='Verified'";

SqlDataSource1.SelectParameters.Add("catnames", Session["UPCategories"].ToString());
SqlDataSource1.SelectParameters.Add("min", Convert.ToDecimal(Session["UPBudgetMin"]).ToString());
SqlDataSource1.SelectParameters.Add("max", Convert.ToDecimal(Session["UPBudgetMax"]).ToString());

SqlDataSource1.DataBind();

A string containing `'x,y,z'` is not the same as `x,y,z`. The first is a single value. The second is three values. — Martin Smith, Jan 01 '17 at 15:23

score -1 · Answer 1 · answered Jan 01 '17 at 12:38

-1

What you should be looking for in the @catnames is 'catname1','catname2', etc. You are using , as delimiter, but I don't see you adding the '' , so I expect that in the @catnames variables you get catname1, catname2, etc.., which would not work. Try:

 YrStrList.Add("'"+item.Value+"'");

answered Jan 01 '17 at 12:38

FBO

24
2

Hi, thanks for commenting. But there was still nothing displayed. – Elijah Jan 01 '17 at 13:22

score -1 · Answer 2 · answered Jan 01 '17 at 13:50

I've also run in similar trouble when filling the list of an IN(…) substatement dynamically. At least in PostgreSQL I had to replace the IN(…) with an ANY branch in the WHERE substatement and passing an array of the desired values to the statement, like this:

SELECT sf.*, sfc.* FROM SkillsFuture sf INNER JOIN SkillsFutureCategory
sfc ON sfc.SFCatID = sf.fkSFCatID WHERE sfc.SFCatName = ANY (@catnames) AND
sf.SFPrice BETWEEN @min AND @max AND sf.SFCertStatus='Verified'

@catnames would then be an array of the datatype of sfc.SFCatName.

Retrieve multiple values from CheckboxList using SQL WHERE IN

2 Answers2