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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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.