Boolean string to Bit field

[VBAHole22]

I have a checkbox list that I am using against a SQL server db. I developed this code initially on an access database and it would assess the values of the checks in the checkbox list and build a SQL string that I submitted.

 

Access would allow :

UPDATE table SET MyField=True

 

MyField was yes/no in access and this worked

 

But now I shift over to SQL Server. MyField is now bit and this technique above no longer works.

How can I convert the CheckBox.Checked boolean value into a bit so I can submit it to the db?

[akiaz]

A field of type bit can only be 1, 0 or NULL. The equivalent SQL statement would then be something like:

 

UPDATE table SET MyField=1 WHERE ...

 

So depending on the value of your checkbox, set the appropriate SQL statement like:

 

Dim sqlstring As String

If CheckBox.Checked Then
  sqlstring = "UPDATE table SET MyField=1 WHERE ..."
Else
  sqlstring = "UPDATE table SET MyField=0 WHERE ..."
End If

 

Don't forget your criteria to specify which record to update. HTH

[VBAHole22]

The reason I didn't go that route is because I was building a dynamic SQL string and I have 6 of these check boxes and the string would have been huge or I would have needed a lot of pre-code before the actual sql.

 

I did figure out a much better way.

 

Ctype(checkbox.Checked, Integer)

 

And a bit field will also accept -1. The above cast will convert Tru into -1 and false into zero and SQL Server seems to be cool with this.

[akiaz]

Wow, I like your method of using Ctype better. I looked in the SQL Server Books Online documentation and it doesn't mention any other valid values other than 0, 1 or NULL but it does say it is an integer data type. I experimented with SQL Query Analyzer and found that any non-zero integer assigned to a bit field will be saved as a 1. I guess that's good that it allows this but worry if that could disappear in future SQL Server versions.

 

Using -Ctype(checkbox.Checked, Integer) could prevent this as it would change a true response to positive 1.

[Nerseus]

If you want a more type-safe way of doing it, use the SqlParameter objects which take actuall bool values for bool datatypes. Meaning, you can use your check1.Checked property (a bool) to fill a SqlParameter object which will take care of conversion to SQL Server or Access. In other words, let the data adapter take care of the adaptations.

 

If you're really going to use dynamic SQL I'd strongly suggest NOT using the CType to an Integer for at least 2 reasons: CType itself may not be supported in future versions of .NET (it's a carryover from VB6 and under), it's not as clear that a bool is being converted to an int the way you want (not to mention you want a bit in the database).

 

When in doubt, be clear in code. If you must use dynamic SQL, why not use String.Format?

Dim sql As String

sql = String.Format("UPDATE table SET MyField={0}...", BoolToSQLString(checkbox.Checked))

Public Sub  BoolToSQLString(ByVal b As Boolean)
   If b Then
       Return "1"
   Else
       Return "0"
   End If
End Sub

 

-Nerseus

[VBAHole22]

I like your suggestions, and thanks for the tip. Now with the SQlParameter would I need to be using stored procs or can you give me acode snippet of using it with dynamic SQL.

Also, I though about the function that you wrote as well. It just seemed to me, as I wrote it, that anything that small and usefull must surely be in there already, right?

Guess I was wrong.