Returning records to dataGridView using multiple checkboxes.

Question

I am trying to pull records from a SQLite database based on checkboxes selected by the user. Although the code shows no errors, both methods I have tried return an exception when run. The code I have tried so far:

private void btnMedium_Click(object sender, EventArgs e)
   {
     readDataMedium();
   }

private void readDataMedium()
        {
            try
            {
                var datatable = new DataTable();
                myConnection.Open();
                string commandText = "Select * From Albums where Medium = '" + chk_CD.Checked.ToString() + "','" + chk_cassette.Checked.ToString() + "','" +
                chk_vinyl.Checked.ToString() + "','" + chk_vinyl.Checked.ToString() + "','" + chk_R2R.Checked.ToString() + "','" + chk_8Track.Checked.ToString() + "')";
                SQLiteDataAdapter mySqliteDataAdapter = new SQLiteDataAdapter(commandText, myConnection);
                mySqliteDataAdapter.Fill(datatable);
                myConnection.Close();
                dataGridView.DataSource = datatable;
            }

            catch (Exception)
            {
                throw;
            }
        }

I have also tried:

string commandText = "Select * From Albums where Medium = '" + chk_CD.Checked.Equals(true) + "','" + chk_cassette.Checked.Equals(true) + "','" + chk_vinyl.Checked.Equals(true) + "','" + chk_vinyl.Checked.Equals(true) + "','" + chk_R2R.Checked.Equals(true) + "','" + chk_8Track.Checked.Equals(true) + "')";

I would appreciate any help with this. Thanks in advance.

Stan Sypek · Answer

Hi theLizard, thanks for the quick response. What you're saying makes sense, I'll have to chew on it when I get time. Values are being stored Boolean, it's a radio button that you use to save the data. Each selection cd, vinyl, r2r, 8track do have their own field. I see that Medium doesn't make sense. I'm not sure where I got that, but most likely from something I found on Google and didn't understand. I'll go over this tomorrow and mark post accordingly. Thanks a lot for the help.

theLizard · Answer

How are you storing these values in Sqlite, Boolean or TinyInt? The Checked property of a CheckBox is bool, chk_CD.Checked.ToString() returns a "True" or "False" so your string commandText = "Select * From Albums where Medium = '" + chk_CD.Checked.ToString() ends up looking like string commandText = "Select * From Albums where Medium = 'True' (if checked) What your Select statement should look like is this; SELECT * FROM Albums WHERE Medium = 0 [False] or 1[True] So you should build youe string like this. string commandText = "SELECT * FROM Albums WHERE Medium = " + (chk_CD.Checked? "1" : "0"); The resulting string is; "SELECT * FROM Albums WHERE Medium = 1" Another problem you have is that you are asking for records from the DB based on Medium where this can only hold 1 value but you are asking the db engine to return records based on a number of values. You cannot do this (as you are doing) for 1 value; "SELECT * FROM Albums WHERE Medium = 1, 0, 1,1,0,1,1" Any wonder you are getting an Exception, if you want to be able to select records this way, you need build your string using an enum and storing the enum value in the Medium column of your data table eg. enum MediumTypes {cd, vinyle, r2r, 8track}; "SELECT * FROM Albums WHERE Medium = 0 or Medium = 1 or Medium =2 or Medium = 3"

Returning records to dataGridView using multiple checkboxes.

Answers (2)