Solved

Passing a Null parameter to SQL Server using ADO parameter object

Posted on 2000-05-16
6
577 Views
Last Modified: 2013-11-23
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?
0
Comment
Question by:pmwood
6 Comments
 
LVL 18

Expert Comment

by:mdougan
Comment Utility
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
 
LVL 18

Accepted Solution

by:
mdougan earned 100 total points
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
Simply don't use adVariant in the createparameter function, but the effective data type given in the stored procedure. This should work.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
 
LVL 2

Expert Comment

by:Simonac
Comment Utility
0
 

Author Comment

by:pmwood
Comment Utility
Thanks, Guys.  Seems obvious, don't know why I was fiddling with adVariant types.  The full code example did the trick.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now