-1

The search criteria comes from a CheckBox-List populated by database values [1,2,3,4,5,6...].

I want to be able to retrieve the selected checkbox values and include it into the WHERE criteria of my SQL Command before executing it.

Click to see image

    protected void Button1_Click(object sender, EventArgs e)
    {
        string checkedSkills = "";

        //Store The checked values from chkBoxSkillset into a List.
        List<ListItem> selected = new List<ListItem>();
        foreach (ListItem item in chkBoxSkillset.Items)
            if (item.Selected)
                selected.Add(item);

        string checkedSkills = "SELECT DISTINCT Student.*FROM Student" +
            " INNER JOIN StudentSkillSet ON Student.StudentID = 
           StudentSkillSet.StudentID WHERE StudentSkillSet.SkillSetID IN (" 
           + checkedSkills; 

        foreach (ListItem skill in selected)
        {   
            checkedSkills+=Convert.ToString
            (selected[Convert.ToInt16(skill)] + ",");
        }

        checkedSkills += ")";

    }

Do i use cmd.Parameters or QueryString? How should i format it so it works?

habib
  • 2,366
  • 5
  • 25
  • 41
Jonathan Tok
  • 1
  • 2
  • Although [this](https://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause) is for SQL server, it's conceptually similar and might help. – ProgrammingLlama Jun 17 '18 at 06:39
  • Possible duplicate of [How do you use the MySql IN clause](https://stackoverflow.com/questions/6804657/how-do-you-use-the-mysql-in-clause) – mjwills Jun 17 '18 at 09:50

1 Answers1

0

I would suggest that you use String.Format() and String.Join() for example you could write down your query in this manner 

string query = SELECT DISTINCT Student.* FROM Student 
INNER JOIN StudentSkillSet ON Student.StudentID = 
StudentSkillSet.StudentID WHERE StudentSkillSet.SkillSetID IN ({0})

Then since you are getting a List<ListItem> you could use LINQ to get the desired values, in your case

String.Join(", ", select.Select(item => item).Where(x => x.Selected == true).ToList()) which will concatenate all the values in your list in this manner "item1, item2, item3..."

Then you can use the String.Format(query, String.Join(", ", select.Select(item => item).Where(x =>x.Selected == true).ToList()) which will automatically create your query, it is more concise and less steps than creating several foreach loops.

jonnhynick
  • 3
  • 3