VBAHole22 Posted April 26, 2004 Posted April 26, 2004 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? Quote Wanna-Be C# Superstar
akiaz Posted April 26, 2004 Posted April 26, 2004 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 Quote
VBAHole22 Posted April 26, 2004 Author Posted April 26, 2004 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. Quote Wanna-Be C# Superstar
akiaz Posted April 26, 2004 Posted April 26, 2004 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. Quote
*Experts* Nerseus Posted April 27, 2004 *Experts* Posted April 27, 2004 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 Quote "I want to stand as close to the edge as I can without going over. Out on the edge you see all the kinds of things you can't see from the center." - Kurt Vonnegut
VBAHole22 Posted April 27, 2004 Author Posted April 27, 2004 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. Quote Wanna-Be C# Superstar
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.