0 or 1 vs True or False

  • Thread starter Thread starter Wayne Wengert
  • Start date Start date
W

Wayne Wengert

I am using VB in a VSNET 2003 Windows application. I've run into a situation
where, when trying to set a bit value in a SQL Server 2000 database I get
errors because the values extracted from a datarow return True or False. In
the snippet below, the SQL becomes "Update myTable SET EQ = True" which
fails with an error that "True" is not a valid column name? I gather that
the datarow object returns True or False for bit fields? I can always recode
this to get around the problem but I am trying to understand what the rules
are here?

========== Sample Code =============
Dim dr As DataRow
If chkEqp.Checked Then dr("EQ") = 1 Else dr("EQ") = 0

' If I need to manually do the update I use this code

"Update myTable SET EQ = " & dr("EQ")
 
If CInt doesn't work (nor CDbl) try doing:

"Update myTable SET EQ = " & IIf(dr("EQ"), 1, 0)

Can't remember if SQL Server's True is -1 or 1, try -1 if 1 doesn't seem to work,
although I think both will work.

Hope it helps :)

Mythran
 
Thanks for that suggestion.

Wayne

Mythran said:
If CInt doesn't work (nor CDbl) try doing:

"Update myTable SET EQ = " & IIf(dr("EQ"), 1, 0)

Can't remember if SQL Server's True is -1 or 1, try -1 if 1 doesn't seem to work,
although I think both will work.

Hope it helps :)

Mythran
Click to expand...
 
This raises a number of matters that need to be understood to arrive at the
best solution for you.

In SQL Server, the bit data type should not be considered to be boolean.
Whereas a boolean can be True or False (and nothing else), the bit data type
can be 0, 1 or NULL. To digress, the fact that it can be NULL, in my
experience, causes headaches, and I always declare a bit as NOT NULL with
default of 0.

In VB, the boolean datatype can be True oe False. Although a boolean can be
represented numerically as -1 for True and 0 for False, on should always
think of a boolean in terms of True or False and forget about the underlying
numerical values. The difference between VB and other languages that have 1
as their 'True' value is due to the historical fact that in BASIC, True is
actually calculated as Not False. If you do a bitwise NOT on 0 the result
is -1. Over the years the language has evolved so that any non 0 value will
resolve to True.

In a DotNet datatable, a SqlDBType.Bit will be interpreted as a
System.Boolean and the translation of 1 and 0 to True and False respectively
(and the reverse) is handled by the Framework.

If you do things the DotNet way, thus:

Dim _com As New SqlCommand("Update myTable SET EQ=@EQ", _sqlcon)
_com.Parameters.Add(New SqlParameter("@EQ", SqlDBType.Bit)).Value =
dr("EQ")
_com.ExecuteNonQuery

then you find that you have little or no difficulity.

If you wish to persist with a dynamic SQL string then using Math.Abs() will
help you out. There is a gotch in that Math.Abs() cannot be performed on a
boolean, so it needs to be converted to something else first, thus:

"Update myTable SET EQ=" & Math.Abs(CInt(dr("EQ"))

The result is either the absoulute value of -1 (1) or the absoulute value of
0 (0).

When updating your datarow, simply using dr("EQ") = chkEqp.Checked will
suffice, because the datatype for EQ in the datatable is System.Boolean.
 
Thanks for the very informative response.

Wayne

Stephany Young said:
This raises a number of matters that need to be understood to arrive at the
best solution for you.

In SQL Server, the bit data type should not be considered to be boolean.
Whereas a boolean can be True or False (and nothing else), the bit data type
can be 0, 1 or NULL. To digress, the fact that it can be NULL, in my
experience, causes headaches, and I always declare a bit as NOT NULL with
default of 0.

In VB, the boolean datatype can be True oe False. Although a boolean can be
represented numerically as -1 for True and 0 for False, on should always
think of a boolean in terms of True or False and forget about the underlying
numerical values. The difference between VB and other languages that have 1
as their 'True' value is due to the historical fact that in BASIC, True is
actually calculated as Not False. If you do a bitwise NOT on 0 the result
is -1. Over the years the language has evolved so that any non 0 value will
resolve to True.

In a DotNet datatable, a SqlDBType.Bit will be interpreted as a
System.Boolean and the translation of 1 and 0 to True and False respectively
(and the reverse) is handled by the Framework.

If you do things the DotNet way, thus:

Dim _com As New SqlCommand("Update myTable SET EQ=@EQ", _sqlcon)
_com.Parameters.Add(New SqlParameter("@EQ", SqlDBType.Bit)).Value =
dr("EQ")
_com.ExecuteNonQuery

then you find that you have little or no difficulity.

If you wish to persist with a dynamic SQL string then using Math.Abs() will
help you out. There is a gotch in that Math.Abs() cannot be performed on a
boolean, so it needs to be converted to something else first, thus:

"Update myTable SET EQ=" & Math.Abs(CInt(dr("EQ"))

The result is either the absoulute value of -1 (1) or the absoulute value of
0 (0).

When updating your datarow, simply using dr("EQ") = chkEqp.Checked will
suffice, because the datatype for EQ in the datatable is System.Boolean.
Click to expand...
 
Back
Top