ASP.NET - How to edit 'bit' data type?

Question

I am coding in Visual Basic. I am using a checkbox control. Now depending on its checked property I need to set/unset a bit column in a SQL Server database. Here's the code:

    Try
        conSQL.Open()

        Dim cmd As New SqlCommand("update Student set send_mail = " + _
            sendemailCheckBox.Checked.ToString + " where student_id = '" _
            + sidnolabel.Text + "'", conSQL)

        cmd.ExecuteNonQuery()
    Finally
        conSQL.Close()
    End Try

The send_mail attribute is of bit datatype. This code is not working. How do I go about it?

Answer 1

Try:

Convert.ToInt16(sendemailCheckBox.Checked)

In general, I use SqlParameters for this, and you can specify a SqlDbType.TinyInt.

Here is an example of using sqlparameter which is better because it avoids SQL injection.

EDIT: I just noticed you were using bit, not tinyint. not sure if this will work.

Answer 2

Well bit data type is equivalent to boolean in C# or Visual Basic, so you can simply assign true or false values to these types and can then update the record in your database.

Answer 3

Hi, my son is enrolled in your school. His name is:

Robert'); DROP TABLE STUDENT; --

We call him little Bobby Tables.

Answer 4

First, in the name of everything sacred, at least PARAMETERIZE your SQL code. Otherwise, you're asking for a SQL injection attack.

Second, the "bit" datatype uses 1 for True and 0 for False. That's what SQL wants to see when you're assigning values.

Answer 5

To answer your question, just assign a Boolean value (i.e. sendemailCheckBox.Checked) to the bit column in the database.

To help you out with your SQL injection issues - do not directly write user input into a SQL string. You must use parameters to ensure that users cannot mess with your database. Your code should be written like this:

Using conSQL As New SqlConnection("SomeConnectionString")
   conSQL.Open()
   Using cm as SqlCommand = conSQL.CreateCommand()
      cm.CommandType = CommandType.Text
      cm.CommandText = 'UDPATE Student SET send_mail = @send_mail WHERE student_id = @student_id'
      cm.Parameters.AddWithValue("@send_mail", sendemailCheckBox.Checked)
      cm.Parameters.AddWithValue("@student_id", sidnolabel.Text)

      cm.ExecuteNonQuery()
   End Using
End Using