Solved

Passing a Null parameter to SQL Server using ADO parameter object

Posted on 2000-05-16
6
584 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 18

Expert Comment

by:mdougan
ID: 2814314
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
ID: 2814330
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 2814338
Simply don't use adVariant in the createparameter function, but the effective data type given in the stored procedure. This should work.
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 2814350
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
 

Author Comment

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

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

707 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