0

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:

    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();
        }
    }
}
2
  • You don't read back the values for the other checkbox. How do you suppose to set the other checkboxes ? Commented Apr 25, 2013 at 17:09
  • Well, for saving on Text in this example, I just did the first CheckBox. I assume that, the code(reader) on the next checkBoxes is going to be the same. Commented Apr 25, 2013 at 17:17

2 Answers 2

1

Booleans are stored as 1 or 0 in Sql Database, but the datareader do the conversion for you. Instead use:

var myBool = reader.GetBoolean(i);

Then just assign the value to the control's value property.

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

7 Comments

Thanks, I changed to GetBoolean(i)... but I didn't get which control value property or where to assign it. Could you elaborate more, please.
var myBool = reader.GetBoolean(i); mycheckBox.IsChecked = myBool;
I did that, and now I get the following error: "Object reference not set to an instance of an object." Please help!
Well, I'm just writing partial code. I suppose you know how to obtain a reference to your checkbox and how to manage null values in a datareader. Try with: var myBool = reader.GetBoolean(i) != null ? reader.GetBoolean(i) : false; Another choice is to declare myBool as nullable. bool? myBool;
Thanks, I did the reference before the reader like: "if (CheckBox1.Checked == true)" however, when I run the app the checkbox is empty, and no value is returned.
|
1

Finally got the reader they way it should work to get the checkmark values. I edited my question with the working code; however, below is what I added or changed for the reader:

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")));
} 

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.