How to retrieve asp:CheckBox value from SQL db?

Question

0.00/5 (No votes)

See more:

I have a table of CheckBoxes that are inserted into a SQL db as '1' and '0'. However, I would like to retrieve those values again with a load event, but I'm not able to get them. This is my code:

C#

    private void getAuditChecklist()
    {
        SqlCommand cmd = null;
        string conn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        string queryString = @"SELECT Mount, Braker, Access, Conn_Net, Log_Book, Pictures, Floor, Cb_Lenght, Channel FROM AUDITOR_CHECKLIST " +
            "WHERE SITE_ID = @SiteID";        

        using (SqlConnection connection =
                   new SqlConnection(conn))
        {
            SqlCommand command =
                new SqlCommand(queryString, connection);
            connection.Open();
            cmd = new SqlCommand(queryString);
            cmd.Connection = connection;

            cmd.Parameters.Add(new SqlParameter("@SiteID", //the name of the parameter to map
                  System.Data.SqlDbType.NVarChar, //SqlDbType value
                  20, //The width of the parameter
                  "SITE_ID")); //The name of the column source
            //Fill the parameter with the value retrieved
            //from the text field
            cmd.Parameters["@SiteID"].Value = foo.Site_ID;

            SqlDataReader reader = cmd.ExecuteReader();

            
while (reader.Read())
       {                    
       CheckBox1.Checked = (reader.GetBoolean(reader.GetOrdinal("Mount")));
       CheckBox2.Checked = (reader.GetBoolean(reader.GetOrdinal("Braker")));
       CheckBox3.Checked = (reader.GetBoolean(reader.GetOrdinal("Access")));
       CheckBox4.Checked = (reader.GetBoolean(reader.GetOrdinal("Conn_Net")));
       CheckBox5.Checked = (reader.GetBoolean(reader.GetOrdinal("Log_Book")));
       CheckBox6.Checked = (reader.GetBoolean(reader.GetOrdinal("Pictures")));
       CheckBox8.Checked = (reader.GetBoolean(reader.GetOrdinal("Floor")));
       CheckBox9.Checked = (reader.GetBoolean(reader.GetOrdinal("Cb_lenght")));
       CheckBox10.Checked = (reader.GetBoolean(reader.GetOrdinal("Channel")));
       } 
       reader.Close();
        }        
    }

What am I missing to get the checkmark from the sql db? Below is how insert into sql:

private void SaveAuditChecklist()
{
    if (auditChecklist != null)
    {
        SqlCommand cmd = null;
        string conn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

        string queryString = @"INSERT INTO AUDITOR_CHECKLIST VALUES(" +
            "@SiteID, @Mount, @Braker, @Access, @ConnNet, @LogBook, @Pictures, @Floor, @CbLenght, @Channel) ";

        using (SqlConnection connection =
                   new SqlConnection(conn))
        {
            SqlCommand command =
                new SqlCommand(queryString, connection);
            connection.Open();
            cmd = new SqlCommand(queryString);
            cmd.Connection = connection;
            cmd.Parameters.Add(new SqlParameter(
                "@SiteID",                        //the name of the parameter to map
                System.Data.SqlDbType.NVarChar,   //SqlDbType value
                20,                               //The width of the parameter
                "Site_ID"));                      //The name of the column source

            //Fill the parameter with the value retrieved
            //from the text field
            cmd.Parameters["@SiteID"].Value = foo.Site_ID;
            cmd.Parameters.Add(new SqlParameter("@Mount", SqlDbType.Bit));
            cmd.Parameters["@Mount"].Value = CheckBox1.Checked;
            cmd.Parameters.Add(new SqlParameter("@Braker", SqlDbType.Bit));
            cmd.Parameters["@Braker"].Value = CheckBox2.Checked;
            cmd.Parameters.Add(new SqlParameter("@Access", SqlDbType.Bit));
            cmd.Parameters["@Access"].Value = CheckBox3.Checked;
            cmd.Parameters.Add(new SqlParameter("@ConnNet", SqlDbType.Bit));
            cmd.Parameters["@ConnNet"].Value = CheckBox4.Checked;
            cmd.Parameters.Add(new SqlParameter("@LogBook", SqlDbType.Bit));
            cmd.Parameters["@LogBook"].Value = CheckBox5.Checked;
            cmd.Parameters.Add(new SqlParameter("@Pictures", SqlDbType.Bit));
            cmd.Parameters["@Pictures"].Value = CheckBox6.Checked;
            cmd.Parameters.Add(new SqlParameter("@Floor", SqlDbType.Bit));
            cmd.Parameters["@Floor"].Value = CheckBox8.Checked;
            cmd.Parameters.Add(new SqlParameter("@CbLenght", SqlDbType.Bit));
            cmd.Parameters["@CbLenght"].Value = CheckBox9.Checked;
            cmd.Parameters.Add(new SqlParameter("@Channel", SqlDbType.Bit));
            cmd.Parameters["@Channel"].Value = CheckBox10.Checked;
            cmd.ExecuteReader();
        }
    }
}

Posted 24-Apr-13 8:35am

Castellon

Updated 28-Apr-13 9:21am

v9

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2013-04-24T23:19:00

Your problems start with

C#

string queryString = @"SELECT @SiteID, @Mount FROM AUDITOR_CHECKLIST " +
            "WHERE SITE_ID = @SiteID";

I think this should be

C#

string queryString = @"SELECT SITE_ID, MOUNT FROM AUDITOR_CHECKLIST " +
            "WHERE SITE_ID = @SiteID";

i.e. return the columns from the database!

You haven't indicated how your "table of checkboxes" are related to this table - I'm guessing that SITE_ID would indicate which checkbox to update and that MOUNT is the 0/1 indicator

So your problems continue with ...

C#

if (CheckBox1.Checked == true)
            {
                while (reader.Read())
                {
                    cmd.Parameters.Add(new SqlParameter("@Mount", SqlDbType.Bit));
                    cmd.Parameters["@Mount"].Value = 1;
                }
            }
            else
            {
                cmd.Parameters.Add(new SqlParameter("@Mount", SqlDbType.Bit));
                cmd.Parameters["@Mount"].Value = 0;
            }

Firstly, you're trying to add parameters to the sql after you have run it. and you're only running the query if CheckBox1 is already checked. And you are not attempting to set the value of any checkbox.

I suspect that you really wanted something like (note I haven't tested this nor have I put in all of the proper try-catch, tests for null etc etc)

while (reader.Read()) { int i = reader.GetOrdinal("SITE_ID"); int cbNum = reader.GetInt32(i); // Find the checkbox we want to update - I'm using winform so you'll need to change this bit to suit asp CheckBox c = (CheckBox)this.Controls.Find("CheckBox" + cbNum.ToString(), True); i = reader.GetOrdinal("MOUNT"); if(reader.GetByte(i) == 1) c.Checked = reader.GetByte(i) == 1 ? true : false; }

[EDIT - update in response to OP update of question]

Try replacing your sql with

C#

SELECT Mount, Braker, Access, ConnNet, LogBook, Pictures, Floor, CbLenght, Channel FROM AUDITOR_CHECKLIST WHERE SITE_ID = @SiteID;

Note the only "@" symbol is for the SiteID parameter
Then you can read the data using

while (reader.Read())
{
     CheckBox1.Checked = Convert.ToBoolean(reader.GetByte(reader.GetOrdinal("Mount")))
     CheckBox2.Checked = Convert.ToBoolean(reader.GetByte(reader.GetOrdinal("Braker")));
     CheckBox3.Checked = Convert.ToBoolean(reader.GetByte(reader.GetOrdinal("Access")));
     CheckBox4.Checked = Convert.ToBoolean(reader.GetByte(reader.GetOrdinal("ConnNet")));
     CheckBox5.Checked = Convert.ToBoolean(reader.GetByte(reader.GetOrdinal("LogBook")));
     CheckBox6.Checked = Convert.ToBoolean(reader.GetByte(reader.GetOrdinal("Pictures")));
     CheckBox8.Checked = Convert.ToBoolean(reader.GetByte(reader.GetOrdinal("Floor")));
     CheckBox9.Checked = Convert.ToBoolean(reader.GetByte(reader.GetOrdinal("Cblenght")));
     CheckBox10.Checked = Convert.ToBoolean(reader.GetByte(reader.GetOrdinal("Channel")));
}

NB I've used the same spelling for Cblenght as you have - it might be Cblength on the database.

How to retrieve asp:CheckBox value from SQL db?

1 solution

Solution 1

Add your solution here

Preview 0

How to retrieve asp:CheckBox value from SQL db?

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us