Updating MS SQL Server 'bit' field through stored procedure from VB

Why doesn't this work?

Using an unchanged and well tested VB routine for 'poking' an SQL Server stored procedure, I have run a new stored procedure that has to update a single 'bit' type field in my database.

VB does not have the 'bit' field type so I pass the 1 or 0 value in a smallint field and convert it within the stored procedure.

The routine and stored procedure run successfully (i.e. without returning any error code) but the field is not updated.

I have checked all of the obvious things (correct database/correct table/ I even ran the stored procedure (with hard coded parameter values) directly through Query Analyzer and it worked fine).

Has anyone come across something similar? Is there a better way of setting a 'bit' value through VB?

Here is my VB routine

The parameters passed are the name of the stored procedure
and a scripting dictionary.

Each entry to the dictionary is another dictionary containing details of the parameter to add (data type, size and value) i.e. A dictionary of dictionaries if you will.

NB it might be easier to copy this code into notepad/vb as it doesn't 'wrap' well in this little window!
Public Function RunSP(SPName As String, Optional ParmDic) As Long

    Dim var As Variant, lngTemp As Long, Success As Boolean
    Set m_cmd = New Command
    With m_cmd
        .Parameters.Append .CreateParameter("RV", adInteger, adParamReturnValue)
        .CommandType = adCmdStoredProc
    End With
    'If m_cn Is Nothing Then
        Success = pOpenConnection
    'End If
    If Not Success Then Exit Function
    With m_cmd
        Set .ActiveConnection = m_cn
        .CommandText = SPName
        If Not IsMissing(ParmDic) Then
            For Each var In ParmDic
                If var = "COMMANDTIMEOUT" Then
                    m_cmd.CommandTimeout = ParmDic.Item(var).Item("Value")
'                    MsgBox SPName & vbCrLf & var & vbCrLf & ParmDic.Item(var).Item("Value") & vbCrLf & ParmDic.Item(var).Item("DataType") & vbCrLf & ParmDic.Item(var).Item("Size")
                    .Parameters.Append .CreateParameter(var, _
                                                        ParmDic.Item(var).Item("DataType"), _
                                                        adParamInput, _
                                                        ParmDic.Item(var).Item("Size"), _
                End If
            Next var
        End If
        .Execute lngTemp
    End With
    RunSP = m_cmd.Parameters("RV")

End Function

Here is the stored procedure...

CREATE  PROCEDURE [dbo].[fl_Sys_Update_HasSubs_In_Documents_Record]      
            @P_HASSUBS smallint,
     @P_ID int

     IF @P_HASSUBS = 1
          SET @HASSUBS = 1
          SET @HASSUBS = 0

     UPDATE dbo.FLSYS_Documents
     SET HasSubs = @HASSUBS
     WHERE ID = @P_ID



Who is Participating?
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
>>VB does not have the 'bit' field type so I pass the 1 or 0 value in a smallint field and convert it within the stored procedure.

Use VB's boolean type, using true and false. I found this working fine...

Nitin SontakkeDeveloperCommented:
angelIII, correct me if i am wrong, but you will probably be using it with MS-Access. To the best of my current knowledge, VB true/false won't work with SQL Server as it represent values -1 and 0 respectively, whereas SQL Server will expect 1 and 0.

To test it correctly, i will suggest that PLEASE deviate from the custom procedure which you are calling and code specifically for the this stored proc. JUST FOR TESTING PURPOSE. I am sure it will work. Several times, i have used a bit as parameter for a stored proc and said so while building parameters object, this works fine...

Guy Hengel [angelIII / a3]Billing EngineerCommented:
Nitin, I used SQL-bit + VB-boolean with ADO, where ADO did the mapping between boolean and bit...
Anthony PerkinsCommented:
As angelIII has stated use the adBoolean datatype.

For this Stored Procedure:
CREATE Procedure sp_test3 @ID int, @BitCol bit


Update Table2
Set     BitCol = @BitCol
Where ID = @ID

This VB code worked for me:
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command

' Create connection and command objects
Set cn = New ADODB.Connection
Set cmd = New ADODB.Command

' Set connection properties and open
cn.ConnectionString = "Provider=SQLOLEDB.1;Password="""";Persist Security Info=True;User ID=sa;Initial Catalog=TestDB;Data Source=HOME-SERVER"

' Set command properties
With cmd
   Set .ActiveConnection = cn
   .CommandText = "sp_test3"
   .CommandType = adCmdStoredProc
   ' Define stored procedure params and append to command.
   .Parameters.Append .CreateParameter("@ID", adInteger, adParamInput, 2, 4000)
   .Parameters.Append .CreateParameter("@BitCol", adBoolean, adParamInput, 1, 1)
End With

' Execute the command
cmd.Execute , , adExecuteNoRecords
Set cmd = Nothing

Set cn = Nothing

G_EvansAuthor Commented:
Thank you - you were first to suggest the correct answer so the points have to be yours!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.