Write bit value to database

Question

I'm trying to write a bit value (true or false) into my database in a field called "processed". i'm currently trying to do this by passing in bool values, but I get an error saying can't convert from type varchar to bit. Can anybody see what is going on in my logic?

       protected void CheckBoxProcess_CheckedChanged(object sender, EventArgs e)
    {
        bool update;
        bool trueBool = true;
        bool falseBool = false;
        string checkedString = "UPDATE SecureOrders SET processed = '%" + trueBool + "%' WHERE fName LIKE '%" + DefaultGrid.SelectedRow.Cells[2].Text + "%' AND lName LIKE '% " + DefaultGrid.SelectedRow.Cells[3].Text + "%'";
        string uncheckedString = "UPDATE SecureOrders SET processed = '%" + falseBool + "%' WHERE fName LIKE '%" + DefaultGrid.SelectedRow.Cells[2].Text + "%' AND lName LIKE '% " + DefaultGrid.SelectedRow.Cells[3].Text + "%'";
        CheckBox cb = (CheckBox)sender;
        GridViewRow gvr = (GridViewRow)cb.Parent.Parent;
        DefaultGrid.SelectedIndex = gvr.RowIndex;
        update = Convert.ToBoolean(DefaultGrid.SelectedValue);

        orderByString = orderByList.SelectedItem.Value;
        fieldString = searchTextBox.Text;


        System.Configuration.ConnectionStringSettings connectionString;

        connectionString = rootWebConfig.ConnectionStrings.ConnectionStrings["secureodb"];



        // Create an SqlConnection to the database.
        using (SqlConnection connection = new SqlConnection(connectionString.ToString()))
        {
            connection.Open();
            SqlCommand checkedCmd = new SqlCommand(checkedString, connection);
            SqlCommand uncheckedCmd = new SqlCommand(uncheckedString, connection);
            dataAdapter = new SqlDataAdapter("SELECT * FROM SecureOrders", connection);

            // create the DataSet
            dataSet = new DataSet();
            // fill the DataSet using our DataAdapter               
            dataAdapter.Fill(dataSet, "SecureOrders");

            DataView source = new DataView(dataSet.Tables[0]);
            DefaultGrid.DataSource = source;


            if (cb.Checked == true)
            {
                checkedCmd.ExecuteNonQuery();

            }
            else
            {
                uncheckedCmd.ExecuteNonQuery();
            }

            connection.Close();
        }






    }

Answer 1

You need to set the bit fields to 1 or 0 depending on whether it is true or false.

So:

string checkedString = "UPDATE SecureOrders SET processed = 1 WHERE fName LIKE '%" + DefaultGrid.SelectedRow.Cells[2].Text + "%' AND lName LIKE '% " + DefaultGrid.SelectedRow.Cells[3].Text + "%'";
string uncheckedString = "UPDATE SecureOrders SET processed = 0 WHERE fName LIKE '%" + DefaultGrid.SelectedRow.Cells[2].Text + "%' AND lName LIKE '% " + DefaultGrid.SelectedRow.Cells[3].Text + "%'";

Also, as has been mentioned in the comments, constructing your SQL statements directly from user input is the easiest way to fall victim to SQL Injection attacks. It's always best to use paramaterized queries (or even Stored Procs) in those cases.

....
string checkedString = "UPDATE SecureOrders SET processed = 1 WHERE fName LIKE @p1 AND lName LIKE @p2";
string uncheckedString = "UPDATE SecureOrders SET processed = 0 WHERE fName LIKE @p1 AND lName LIKE @p2";

You can then create parameters to pass to your ExecuteNonQuery call

SqlParameter p1 = new SqlParameter("@p1",SqlDbType.Varchar) { Value = string.Format("%{0}%",DefaultGrid.SelectedRow.Cells[2].Text) };
SqlParameter p2 = new SqlParameter("@p2",SqlDbType.Varchar) { Value = string.Format("%{0}%",DefaultGrid.SelectedRow.Cells[3].Text) };
if (cb.Checked == true)
{
    checkedCmd.Parameters.Add(p1);
    checkedCmd.Parameters.Add(p2);
    checkedCmd.ExecuteNonQuery();

}
else
{
    uncheckedCmd.Parameters.Add(p1);
    uncheckedCmd.Parameters.Add(p2);
    uncheckedCmd.ExecuteNonQuery();
}

Answer 2

While it is true that you will solve your problem if you inject into your SQL statement string "1" for True and "0" for False, the "proper" way to solve this problem is to parameterize your SQL statement and add parameters to the command object. Type conversion from a VB Boolean to SqlDbType.Bit is then done by the framework.

Try:

string sqlString = "UPDATE SecureOrders SET processed = @Processed WHERE fName LIKE '%' + @FirstName + '%' AND lName LIKE '%' + @LastName + '%'";

And:

        SqlCommand objCmd = new SqlCommand(sqlString, connection);
        objCmd.Parameters.AddWithValue("Processed", cb.Checked);
        objCmd.Parameters.AddWithValue("FirstName", DefaultGrid.SelectedRow.Cells[2].Text);
        objCmd.Parameters.AddWithValue("LastName", DefaultGrid.SelectedRow.Cells[3].Text);

Finally:

objCmd.ExecuteNonQuery();

If you are going to be writing data-driven web applications it's very important you understand how to avoid the SQL injection security vulnerability. For more information: MSDN How To: Protect From SQL Injection in ASP.NET