using a bound CheckBoxList for sql search query

Question

I have a checkboxlist on my asp webpage which lists a number of skills required for a project. What i wish to do is to check multiple items from this checkboxlist and then create a search function to list all users who have the relevant skill to the ones checked. I can kinda get this to work, but instead of showing all users, it only shows one user.

For example, if i check "Planning" and "Designing" from my checkboxlist it should list users "Peter" and "Vicki" as they have the required skills but my code only shows Peter. My code is below:

StringBuilder sb2 = new StringBuilder();

foreach (ListItem item in chkGeneralSkills.Items)
        {
            if (item.Selected)
            {
                sb2.AppendFormat("{0}  ", item.Text);


                    using (SqlConnection oConn = new SqlConnection(ConfigurationManager.ConnectionStrings["connString"].ToString()))
                    {
                        string sql = "SELECT us.FName + ' ' + us.SName As 'Name', " +
                                 "sk.SkillsID, sl.SkillTitle " +
                                 "FROM Users us " +
                                 "LEFT JOIN Skills sk ON sk.UserID = us.UserID " +
                                 "LEFT JOIN SkillsListing sl ON sl.SkillsListingID = sk.SkillsListingID " +
                                 "WHERE sl.SkillTitle LIKE '" + item.Text + "'";
                        try
                        {
                            oConn.Open();
                            SqlCommand cmd = new SqlCommand(sql, oConn);
                            SqlDataReader reader = cmd.ExecuteReader();
                            chkMatchedUsers.DataTextField = "Name";
                            chkMatchedUsers.DataValueField = "Name";
                            chkMatchedUsers.DataSource = reader;

                            chkMatchedUsers.DataBind();

                            oConn.Close();
                        }
                        catch (Exception)
                        {
                            throw;
                        }
                    }
            }
        }

Can you try to put % sign to your like query and see if it works? — DevelopmentIsMyPassion
– DevelopmentIsMyPassion, Commented Feb 16, 2013 at 13:38

DevelopmentIsMyPassion · Accepted Answer · 2013-02-16 13:50:32Z

1

In your loop where you are looping through items just add variable and assign that value to variable. Then use that variable in where clause. Also end that foreach loop before you start initiating sql connection. Hope its clear to you.

edited Feb 16, 2013 at 13:50

answered Feb 16, 2013 at 13:43

DevelopmentIsMyPassion

3,6014 gold badges37 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Peter Over a year ago

Hi AshReva, I'm a bit confused on what you mean?

DevelopmentIsMyPassion Over a year ago

I think in your where clause you use like item.text but you should use s1.skilltitle in and just use that variable in the "IN" clause. Did you understood?

Peter Over a year ago

Hi AshReva, I'm a little confused could you provide sample code?

csharpnumpty · Accepted Answer · 2013-02-16 14:21:03Z

0

It's hitting the database for each selected item, but if my assumption is correct that Peter has one of the to skills and Vicky has the other, you're ignoring the first response and overriding with the second.

Using what you have already, you should build up a where clause looping through the items and then Call the database just once, rather than per selected item. This will cause your database round trips to decrease too and should return both Vicky and Peter in the results, rather than one or the other.

Untested code - to give you the idea how to accomplish.

var sb = new StringBuilder();
    foreach (ListItem item in chkGeneralSkills.Items)
    {
        if (item.selected)
        {       
           sb.Append("'" + item.text + "',");
        }
    }

    using (SqlConnection oConn = new SqlConnection(ConfigurationManager.ConnectionStrings["connString"].ToString()))
    {
        string sql = "SELECT us.FName + ' ' + us.SName As 'Name', " +
                    "sk.SkillsID, sl.SkillTitle " +
                    "FROM Users us " +
                    "LEFT JOIN Skills sk ON sk.UserID = us.UserID " +
                    "LEFT JOIN SkillsListing sl ON sl.SkillsListingID = sk.SkillsListingID " + 
                    " WHERE sl.SkillTitle IN (" + sb.ToString() + ")";
        try
        {
            oConn.Open();
            SqlCommand cmd = new SqlCommand(sql, oConn);
            SqlDataReader reader = cmd.ExecuteReader();
            chkMatchedUsers.DataTextField = "Name";
            chkMatchedUsers.DataValueField = "Name";
            chkMatchedUsers.DataSource = reader;

            chkMatchedUsers.DataBind();

            oConn.Close();
        }
        catch (Exception)
        {
            throw;
        }
    }

edited Feb 16, 2013 at 14:21

answered Feb 16, 2013 at 13:35

csharpnumpty

3,7231 gold badge33 silver badges46 bronze badges

6 Comments

Peter Over a year ago

Thanks for your response, I'm a newbie at programming, are there any code examples that I could reference for the while clause loop and how to call the database once rather than querying each time?

DevelopmentIsMyPassion Over a year ago

@spacebison cant we just use in clause and add items to variable? Why we need to build where clause?

csharpnumpty Over a year ago

@AshReva Yes, that's an alternative and probably less code. Updated answer as it's an easier/simpler solution.

Peter Over a year ago

many thanks for the example code, i now have this working. Thank you

csharpnumpty Over a year ago

Excellent - good stuff - @AshReva also deserves thanks (and a vote) as I adapted my suggestion with his simpler, more effective solution. Glad it helped :)

|

Collectives™ on Stack Overflow

using a bound CheckBoxList for sql search query

2 Answers 2

3 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related