0

I'm working on a windows forms app in Visual Studio. I have a form with a checkbox. I'd like to save the checked state in a database. And later I'd like to load the state of the checkbox from that database.

I'm using this querystring to save checkbox state into the database:

string insertQuery = "INSERT INTO database.database( checkbox_Voorschrift) VALUES('" + checkBox_VS.CheckState + "')";

In my database, I'm using a boolean.

To load the state of the checkbox, I'm using this:

checkBox_VS.Text = dataGridView1.CurrentRow.Cells[8].Value.ToString();
  • I've tried this with textbox.Text, textbox.Checked, checkbox.CheckStatus
  • I've changed the database fieldtype to Bit, Varchar
  • I've changed the querystring to .Value

But I haven't found a working solution where for example the checked checkbox is saved to the database and later on, on loading the checkbox gets checked whem loading the form with the data from the database.

What am I doing wrong? wrong database fieldtype? Some error in the querystring,.....

thanks for sharing!!!

Christophe
  • 39
  • 12
  • `dataGridView1` is not *the database* it will just have whatever you load into it. You need to requery the database and/or change your DGV's underlying datasource to the correct value. **And don't concatenate SQL queries, use parameters instead. see https://stackoverflow.com/questions/17552829/c-sharp-data-connections-best-practice** – Charlieface Jan 22 '21 at 14:39
  • I changed my code and now I'm not using a dataGridView to load the data from the database into the form fields. ` MySqlCommand cmd = new MySqlCommand("Select WL_Wachtlijst_Naam, WL_Voorschrift, from wachtlijsten where WL_Wachtlijsten_ID =@ID", connection); cmd.Parameters.AddWithValue("@ID", int.Parse(textBox_ID.Text)); MySqlDataReader da = cmd.ExecuteReader(); while (da.Read()) { Wachtlijst_lijstnaam.Text = da.GetValue(0).ToString(); checkBox_VS.Text = da.GetValue(7).ToString(); }` @Linker – Christophe Jan 27 '21 at 10:38

2 Answers2

0

There is a solution for your problem. CheckBox's CheckState is an enum type of System.Windows.Forms.CheckState so, most popular way to save an enum to database is to declare your column as SMALLINT type. You should change Boolean to SMALLINT in database. So, when you get the value from database, you have to set the CheckState like 

short demoDatabaseValue = 1;
this.checkBox1.CheckState = (CheckState)demoDatabaseValue;

and your insert query should be modified to

string insertQuery = "INSERT INTO database.database( checkbox_Voorschrift) VALUES('" + (short)checkBox_VS.CheckState + "')";

However, you showed a code checkBox_VS.Text = dataGridView1.CurrentRow.Cells[8].Value.ToString(); . This code will not work because you can not set CheckState like this. Finally, if your checkstate is in a dataGridView1 as a string then you can load state from that string like

Enum.TryParse("Checked", out CheckState myStatus); //tryparse will work for only 3 data Unchecked,Checked and Indeterminate
this.checkBox1.CheckState = myStatus;

Note: TryParse will not work for other text. And I did not checked your sql query properly. Please check the c# code and try it and let me know it works or not.

Srijon Chakraborty
  • 2,007
  • 2
  • 7
  • 20
  • **You should not concatenate queries like that: [C# Data Connections Best Practice?](https://stackoverflow.com/questions/17552829/c-sharp-data-connections-best-practice)** – Charlieface Jan 24 '21 at 02:49
  • I know that but it wasn't my query. The query was in the question. Parameterized query is the best one however, thank your for suggestion @Charlieface. – Srijon Chakraborty Jan 24 '21 at 04:11
  • I changed my column from Boolean to SMALLINT. I also changed my insertquery. Now the checked state is saved as '1' and the unchecked state is saved as '0' in the database. Now I'm still struggling to load the state from the database into the checkbox. I've stopped using the dataGridView to load the data. Now I'm using this type of command to load the checkbox state: _checkBox_VS.Text = da.GetValue(7).ToString();_ But I'm not sure how to modifie this so the checkbox in the form shows the (un)checked state? Many thanks! – Christophe Jan 27 '21 at 10:47
0

I've found this working solution:

  1. First of all, I'm storing the state of the checkbox as a SMALLINT type

  2. this is the querystring to save checkbox state into the database:

    string insertQuery = "INSERT INTO database.database( checkbox_Voorschrift) VALUES('" + (short)checkBox_VS.CheckState + "')";'"

  3. Now I'm using this code to load the checkstate of the checkbox into the winform (as you can see, I'v' stopped using the datagridview as source):

    checkBox_VS.Checked = Convert.ToBoolean(da.GetValue(4));

Now I can load the state of the checkbox out of the database and have the boxes (un)checked in my form!

Thanks for getting me on the right track :-)

Christophe
  • 39
  • 12