Passing a Null parameter to SQL Server using ADO parameter object

I am using the ADO command object to execute a stored procedure on Sybase (a DB like SQL Server).  The stored proc has parameters, so I create ADO parameter objects and append them.  Normally this works fine.  The problem arises when I want to pass the Null value to a parameter.  There is no adNull parameter type, and passing a variant containing Null to a parameter defined as adVariant gives a "unsupported type" error.  Anyone know how to create a null parameter?
pmwoodAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
mdouganConnect With a Mentor Commented:
Here's a complete example:

Private Function COMMISSION_UPDATE_Q_CHGCODE_SAVE(sMode As String, lRow As Long) As Boolean
Dim CMD As ADODB.Command
Dim i As Long
Dim lCount As Long
Dim sSQL As String
Dim sKey As String

Dim sErrors As String
Dim lError As Variant

    On Error GoTo ErrorRtn
   
    COMMISSION_UPDATE_Q_CHGCODE_SAVE = False
   
    ReDim Parms(1 To 3) As ADODB.Parameter
       
    For i = 1 To 3
        Set Parms(i) = New ADODB.Parameter
        Parms(i).Direction = adParamInput
    Next i
       
    'CHGCODE_SYSID
    Parms(1).Type = adVarChar
    Parms(1).Size = 15
   
    'CHGCODE_SHORTDESC
    Parms(2).Type = adVarChar
    Parms(2).Size = 15
   
    'CHGCODE_LONGDESC
    Parms(3).Type = adVarChar
    Parms(3).Size = 100
               
    If sMode = "INSERT" Then
        sKey = NewKey("COMMISSION_UPDATE_Q_CHGCODE", "CHGCODE_SYSID")
        If sKey = "" Then
            sKey = 1
        End If
        sSQL = "INSERT INTO COMMISSION_UPDATE_Q_CHGCODE ("
        sSQL = sSQL & " CHGCODE_SYSID,"
        sSQL = sSQL & " CHGCODE_SHORTDESC,"
        sSQL = sSQL & " CHGCODE_LONGDESC)"
        sSQL = sSQL & " VALUES (TO_NUMBER(?),?,?)"
       
        Set CMD = New ADODB.Command
        With CMD
            .CommandText = sSQL
            .CommandType = adCmdText
            .ActiveConnection = CN
            For i = 1 To 3
                .Parameters.Append Parms(i)
            Next i
        End With
       
        CN.CursorLocation = adUseClient
                           
        Parms(1).Value = sKey
       
        If tblTable.TextMatrix(lRow, 1) <> "" Then
            Parms(2).Value = UCase(tblTable.TextMatrix(lRow, 1))
        Else
            Parms(2).Value = Null
        End If
   
        If tblTable.TextMatrix(lRow, 2) <> "" Then
            Parms(3).Value = UCase(tblTable.TextMatrix(lRow, 2))
        Else
            tblTable.Row = lRow
            tblTable.Col = 2
            Screen.MousePointer = 0
            frmMessage.MessageBox "A Long Description is required.", vbExclamation, "Save Changes"
            GoTo ExitRtn
        End If
       
    ElseIf sMode = "UPDATE" Then
        sSQL = "UPDATE COMMISSION_UPDATE_Q_CHGCODE SET"
        sSQL = sSQL & " CHGCODE_SHORTDESC = ?,"
        sSQL = sSQL & " CHGCODE_LONGDESC = ?"
        sSQL = sSQL & " WHERE CHGCODE_SYSID = TO_NUMBER(?)"
   
        Set CMD = New ADODB.Command
        With CMD
            .CommandText = sSQL
            .CommandType = adCmdText
            .ActiveConnection = CN
            .Parameters.Append Parms(2)
            .Parameters.Append Parms(3)
            .Parameters.Append Parms(1)
        End With
   
        CN.CursorLocation = adUseClient
           
        Parms(1).Value = tblTable.TextMatrix(lRow, 0)
       
        If tblTable.TextMatrix(lRow, 1) <> "" Then
            Parms(2).Value = UCase(tblTable.TextMatrix(lRow, 1))
        Else
            Parms(2).Value = Null
        End If
   
        If tblTable.TextMatrix(lRow, 2) <> "" Then
            Parms(3).Value = UCase(tblTable.TextMatrix(lRow, 2))
        Else
            tblTable.Row = lRow
            tblTable.Col = 2
            Screen.MousePointer = 0
            frmMessage.MessageBox "A Long Description is required.", vbExclamation, "Save Changes"
            GoTo ExitRtn
        End If

    Else
        sSQL = "DELETE FROM COMMISSION_UPDATE_Q_CHGCODE"
        sSQL = sSQL & " WHERE CHGCODE_SYSID = TO_NUMBER(?)"
   
        Set CMD = New ADODB.Command
        With CMD
            .CommandText = sSQL
            .CommandType = adCmdText
            .ActiveConnection = CN
            .Parameters.Append Parms(1)
        End With
   
        CN.CursorLocation = adUseClient
           
        Parms(1).Value = tblTable.TextMatrix(lRow, 0)
    End If
               
    CMD.Execute

    COMMISSION_UPDATE_Q_CHGCODE_SAVE = True
   
ExitRtn:
    For i = 1 To 3
        Set Parms(i) = Nothing
    Next i
    Set CMD = Nothing
   
    Screen.MousePointer = 0
    Exit Function
ErrorRtn:
    Screen.MousePointer = 0
    sErrors = "COMMISSION_UPDATE_Q_CHGCODE_SAVE " & Err.Description
    For Each lError In CN.Errors
        sErrors = sErrors & vbCrLf & lError.Description
    Next
    frmMessage.MessageBox sErrors, vbCritical, Err & ""
    COMMISSION_UPDATE_Q_CHGCODE_SAVE = False
    Resume ExitRtn

End Function
0
 
mdouganCommented:
For Oracle, I'm able to do this:

Dim x as New ADODB.Parameter

........ other parm code here


x.Value = Null

If you don't put quotes around it, or move it into a variable first it seems to work
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Simply don't use adVariant in the createparameter function, but the effective data type given in the stored procedure. This should work.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
To explain this a little bit, Null is not the type of the parameter, but the value. adVariant is not supported by almost any DB, but only included in ADO for future use and/or some specific Providers...

0
 
pmwoodAuthor Commented:
Thanks, Guys.  Seems obvious, don't know why I was fiddling with adVariant types.  The full code example did the trick.
0
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.