0 or 1 vs True or False

[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")

[Mythran]

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
"Wayne Wengert" <wa************ ***@wengert.com > wrote in message
news:OL******** *****@TK2MSFTNG P10.phx.gbl...

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")

[Wayne Wengert]

Thanks for that suggestion.

Wayne

"Mythran" <ki********@hot mail.com> wrote in message
news:uU******** ******@tk2msftn gp13.phx.gbl...

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
"Wayne Wengert" <wa************ ***@wengert.com > wrote in message
news:OL******** *****@TK2MSFTNG P10.phx.gbl...

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")

[Stephany Young]

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("Upd ate myTable SET EQ=@EQ", _sqlcon)
_com.Parameters .Add(New SqlParameter("@ EQ", SqlDBType.Bit)) .Value =
dr("EQ")
_com.ExecuteNon Query

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(d r("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.
"Wayne Wengert" <wa************ ***@wengert.com > wrote in message
news:OL******** *****@TK2MSFTNG P10.phx.gbl...

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")

[Wayne Wengert]

Thanks for the very informative response.

Wayne

"Stephany Young" <noone@localhos t> wrote in message
news:Op******** ******@tk2msftn gp13.phx.gbl...

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("Upd ate myTable SET EQ=@EQ", _sqlcon)
_com.Parameters .Add(New SqlParameter("@ EQ", SqlDBType.Bit)) .Value =
dr("EQ")
_com.ExecuteNon Query

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(d r("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.
"Wayne Wengert" <wa************ ***@wengert.com > wrote in message
news:OL******** *****@TK2MSFTNG P10.phx.gbl...

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")