Using CheckedListBox Values in SQL Select Statements

Question

I have two CheckedListBoxes, Standard Codes and Standard Details. Upon initialization, Standard Codes is populated from a query to the database.

        InitializeComponent();
        SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=Project;Integrated Security=True");
        conn.Open();
        DataSet ds = new DataSet();
        SqlDataAdapter adapter = new SqlDataAdapter            
        ("SELECT [StandardCode] FROM [dbo].[StandardCodesAndDetails]", conn);
        adapter.Fill(ds);
        this.lstBoxStandardCodes.DataSource = ds.Tables[0];
        this.lstBoxStandardCodes.DisplayMember = "StandardCode";
        conn.Close();

From this CheckedListBox, the user is able to select multiple Standard Code values. As these values are checked or unchecked, I want to run a query that will populate the Standard Details CheckedListBox with the related Standard Details from the database, with some Standard Codes having more than one Standard Detail. This is the part I'm not sure how to write. I'm not sure how to include checked CheckedListBox values in a SQL statement like this.

Any help at all would be appreciated. Thank you.

One solution would be if you write a `store proc` that accepts comma separated values instead of using `dynamic sql` calls. Then you can just pass multiple values either using `for loops` or `foreach`. — smr5, May 05 '15 at 16:43

Sun Maung Oo · Answer 1 · 2015-05-05T16:55:17.520

0

You have to create your sql statement dynamically instead of hard coding it.For example I will write a method which provide a sql statement based on user input like this.

public string CreateSQL(string userName,string password)
{
  string sql="SELECT * FROM Table WHERE";

  if(!string.IsNullOrWhiteSpace(userName) && !string.IsNullOrWhiteSpace(password))
{
   sql+=" UserName='"+userName+"' AND Password='"+password+"'";
}
else if(!string.IsNullOrWhiteSpace(userName))
{
  sql+=" UserName='"+userName+"'";
}
else if(!string.IsNullOrWhiteSpace(password))
{
  sql+=" Password='"+password+"'";
}
else
{
 sql=null;
}

return sql;
}

edited May 05 '15 at 16:55

answered May 05 '15 at 16:49

Sun Maung Oo

183
2
11

If all the conndition are false then `sql` is "SELECT * FROM Table WHERE" which is invalid. Also should always use parameters to avoid sql injection. – juharr May 05 '15 at 16:53
The code above is only to be taken as example of how you can dynamically create sql.The best way is to use parameters and store proc.Also look [here](http://stackoverflow.com/questions/306668/are-parameters-really-enough-to-prevent-sql-injections) – Sun Maung Oo May 05 '15 at 17:03

Using CheckedListBox Values in SQL Select Statements

1 Answers1