0

I am busy experimenting with ADODB as a means to write prepared scripts on customers' databases as it is infinitely better than batch scripting with sqlcmd.

Early on I noticed that sometimes ADODB results would differ from the results given by osql or sqlcmd and on further investigation I found that it always seemed to be in columns that hold a boolean value. If I run the following code I get -1:

declare @test bit
set @test=1
select top 1 @test

When logically it should return 1 as according to the MSDN and w3schools documentation the bit data type on SQL server holds either a 1, 0 or null.

Could anyone explain what is causing this and how to prevent it?

Will exporting information from my database and then importing the -1 rather than a 1 cause issues if the importing is also done with ADODB?

Dennis Kriechel
  • 3,719
  • 14
  • 40
  • 62
KaoSDlanor
  • 65
  • 7
  • As a note when searching online for an answer to this the best I got was suggestions to run a fix on the database however as my example above does not use a database that did not assist me – KaoSDlanor Dec 07 '15 at 11:32
  • @DennisKriechel being a new user I am not certain why the links I provided to the sources of my information were removed. Is there a policy against links on Stack Overflow? – KaoSDlanor Dec 07 '15 at 12:34
  • don't worry I didn't removed the link. I just put them behind the MSDN and w3schools label to make the text better readable. – Dennis Kriechel Dec 07 '15 at 12:36
  • @DennisKriechel ahhh, I apologize for my blind moment. Thanks – KaoSDlanor Dec 07 '15 at 12:45

1 Answers1

1

Demonstration Code 

ConnectionString = "Provider=SQLNCLI11;"
ConnectionString = ConnectionString & "Server=localhost;"
ConnectionString = ConnectionString & "Database=master;"
ConnectionString = ConnectionString & "Trusted_Connection=yes;"

Dim Conn As Object
Set Conn = CreateObject("ADODB.Connection")
Conn.Open ConnectionString
Set Recordset = Conn.Execute("SELECT convert(bit, 1) as bit")

MsgBox Recordset.Fields("bit").Type, 0, "FieldType"
MsgBox VarType(Recordset.Fields("bit").Value),0,"VarType"

This shows that the SQL Server data type bit maps to 11 which is the enumerated values ADODB.DataTypeEnum.adBoolean https://msdn.microsoft.com/en-us/library/ms675318(v=vs.85).aspx

Which in turn corresponds to a varian of Vartype 11 (VariantType.vbBoolean) https://msdn.microsoft.com/en-us/library/32bbtt2s(v=vs.90).aspx So the bit Values 0 and 1 aren't getting directly translated to the numbers 0 and -1 by ADODB.

They're actually getting translated to False & True which in turn correspond to the integers 0 and -1 when you implicitly convert them to integers.

You can demonstrate this by issuing the following commands in the immediate window. 

Debug.Print CInt(False)
Debug.Print CInt(True)

Quite where this leaves you in terms of practical advice I'm not sure but you should probably be treating these values as boolean variables in your code as opposed to numbers.

Luckily SQL Server will implictly convert -1 back to 1 on the database side so you don't need to worry about that

 DECLARE @table table (b bit)
 INSERT @table  (b) VALUES(-1)
 SELECT b from @table

Gives you 

(1 row(s) affected)
b
-----
1

(1 row(s) affected)

Hopefully this resolved more confusion than it caused

Tom Page
  • 1,211
  • 1
  • 7
  • 8
  • Well you successfully explained why it is happening and whether it will be an issue on re-insertion so all-in-all a great answer. Thanks for the help – KaoSDlanor Dec 07 '15 at 12:27