How to save CheckBox value in SQL Server database in C#

Question

I'm working on a Windows forms app, I created a room form which contains room name, size, and LAB.

The table in the database contains columns RoomName (varchar), Size (int), and LAB (bit).

This is the ADD_ROOM stored procedure:

CREATE PROCEDURE [dbo].[ADD_ROOM]
    @NAME_ROOM VARCHAR(50),
    @SIZE INT,
    @LAB BIT
AS
    INSERT INTO [dbo].[ROOM] ([NAME_ROOM], [SIZE], [LAB])
    VALUES (@NAME_ROOM, @SIZE, @LAB)

Here is ADD_ROOM.cs:

class CLS_ADD_ROOM
{
        public void ADD_ROOM(string NAME_ROOM,int SIZE,Boolean LAB)
        {
            DAL.DataAccessLayer DAL = new DAL.DataAccessLayer();
            DAL.Open();
            SqlParameter[] param = new SqlParameter[3];

            param[0] = new SqlParameter("@NAME_ROOM", SqlDbType.VarChar, 50);
            param[0].Value = NAME_ROOM;

            param[1] = new SqlParameter("@SIZE", SqlDbType.Int);
            param[1].Value = SIZE;

            param[2] = new SqlParameter("@LAB", SqlDbType.Bit);
            param[2].Value = LAB;

            DAL.ExecuteCommand("ADD_ROOM", param);
            DAL.Close();
        }

        public void UPDATE_ROOM(string ID_ROOM, string NAME_ROOM, int SIZE, bool LAB)
        {
            DAL.DataAccessLayer DAL = new DAL.DataAccessLayer();
            DAL.Open();

            SqlParameter[] param = new SqlParameter[4]; 
            param[0] = new SqlParameter("@ID_ROOM", SqlDbType.Int);
            param[0].Value = ID_ROOM; 

            param[1] = new SqlParameter("@NAME_ROOM", SqlDbType.VarChar, 50); 
            param[1].Value = NAME_ROOM; 

            param[2] = new SqlParameter("@SIZE", SqlDbType.Int); 
            param[2].Value = SIZE; 

            param[3] = new SqlParameter("@LAB", SqlDbType.Bit); 
            param[3].Value = LAB; 

            DAL.ExecuteCommand("UPDATE_ROOM", param); 
            DAL.Close();
        }

And the code behind button ADD Room in ADD ROOM FORM

private void button1_Click(object sender, EventArgs e)
{
    ROOM.ADD_ROOM(TXT_ROOM_NAME.Text, Convert.ToInt32(TXT_ROOM_SIZE.Text), Convert.ToBoolean(CHECK_LAB.CheckState));

    MessageBox.Show(" The Room has been added successfully", "ADD PROCEDURE", MessageBoxButtons.OK, MessageBoxIcon.Information);
    this.dataGridView1.DataSource = ROOM_VIEW.GET_ALL_ROOMS();
}

And the error is

Input string was not in a correct format

Is TXT_ROOM_NAME.Text no longer than 50 symbols? Is TXT_ROOM_SIZE.Text numeric? — 26071986
– 26071986, Commented Oct 9, 2014 at 9:27
TXT_ROOM_NAME is a textbox receive a string from user & TXT_ROOM_SIZE is a tectbox too which receive an integer from user. @26071986 — Anas
– Anas, Commented Oct 9, 2014 at 9:37

marc_s · Accepted Answer · 2014-10-09 09:17:20Z

2

In your C# code, you should replace Convert.ToBoolean(CHECK_LAB.CheckState) with CHECK_LAB.Checked - that's the boolean with defines whether or not the checkbox is checked:

private void button1_Click(object sender, EventArgs e)
{
    ROOM.ADD_ROOM(TXT_ROOM_NAME.Text, 
                  Convert.ToInt32(TXT_ROOM_SIZE.Text), 
                  CHECK_LAB.Checked);

    MessageBox.Show(" The Room has been added successfully", "ADD PROCEDURE", MessageBoxButtons.OK, MessageBoxIcon.Information);
    this.dataGridView1.DataSource = ROOM_VIEW.GET_ALL_ROOMS();
}

The CheckState property is not a boolean property - it's one of three possible values:

CheckState.Checked
CheckState.Unchecked
CheckState.Indeterminate

answered Oct 9, 2014 at 9:17

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

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

Comments

JoriO · Accepted Answer · 2014-10-09 09:05:41Z

1

Not sure, but this could be worth trying. In ADD_ROOM.cls:

param[2] = new SqlParameter("@LAB", SqlDbType.Bit);
param[2].Value = LAB ? 1 : 0;    // <---- CHANGE THIS LINE

Value of Boolean as parameter may be converted to something other than 0 or 1, which are expected by sql server.

answered Oct 9, 2014 at 9:05

JoriO

1,0466 silver badges13 bronze badges

8 Comments

Anas Over a year ago

I Filled the "Room" table using sql mangment studio and try The update function and it's worked ...the problem is how to save the initial value.. here is the code behind "update button" private void button4_Click(object sender, EventArgs e) { ROOM.UPDATE_ROOM(TXT_ROOM_ID.Text,TXT_ROOM_NAME.Text,Convert.ToInt32(TXT_ROOM_SIZE.Text),CHECK_LAB.Checked); } @JoriO

JoriO Over a year ago

There is no method UPDATE_ROOM in the code you gave us. @Anas Al-shami

Anas Over a year ago

Yeap..it's in ADD_ROOM.cls too:public void UPDATE_ROOM(string ID_ROOM,string NAME_ROOM,int SIZE,bool LAB) { DAL.DataAccessLayer DAL=new DAL.DataAccessLayer(); DAL.Open(); SqlParameter[] param = new SqlParameter[4]; param[0] =new SqlParameter("@ID_ROOM", SqlDbType.Int); param[0].Value =ID_ROOM; param[0] = new SqlParameter("@NAME_ROOM",SqlDbType.VarChar, 50); param[0].Value =NAME_ROOM; param[1] = new SqlParameter("@SIZE",SqlDbType.Int); param[1].Value =SIZE; param[2] = new SqlParameter("@LAB",SqlDbType.Bit); param[2].Value =LAB; DAL.ExecuteCommand("UPDATE_ROOM", param); DAL.Close();}

26071986 Over a year ago

@AnasAl-shami Room ID and Room Name go with the same index in params array, so you declare 4 values, but give only 3.

JoriO Over a year ago

My answer is still the same. In UPDATE_ROOM.cls: param[2].Value = LAB ? 1 : 0;

|

Anas · Accepted Answer · 2014-10-09 11:20:37Z

Thank you so much Guys .. the problem is solved :)

I changed this instruction in ADD_ROOM.cls :

 class CLS_ADD_ROOM
    {
        public void ADD_ROOM(string NAME_ROOM,int SIZE,int LAB)// <=== Change bool to int 
   {
            DAL.DataAccessLayer DAL = new DAL.DataAccessLayer();
            DAL.Open();
            SqlParameter[] param = new SqlParameter[3];

            param[0] = new SqlParameter("@NAME_ROOM", SqlDbType.VarChar, 50);
            param[0].Value = NAME_ROOM;

            param[1] = new SqlParameter("@SIZE", SqlDbType.Int);
            param[1].Value = SIZE;

            param[2] = new SqlParameter("@LAB", SqlDbType.Bit); //<=== keep it .bit
            param[2].Value = LAB ;


            DAL.ExecuteCommand("ADD_ROOM", param);
            DAL.Close();
            }

And add this condition in ADD_ROOM Form :

private void button1_Click(object sender, EventArgs e)
        {               

if (CHECK_LAB.Checked)
{

ROOM.ADD_ROOM(TXT_ROOM_NAME.Text,int.Parse(TXT_ROOM_SIZE.Text),Convert.ToInt32(1));

}
    else
{

   ROOM.ADD_ROOM(TXT_ROOM_NAME.Text, Convert.ToInt32(TXT_ROOM_SIZE.Text), Convert.ToInt32(0));              }

Collectives™ on Stack Overflow

How to save CheckBox value in SQL Server database in C#

3 Answers 3

Comments

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related