?
Solved

Passing a Null parameter to SQL Server using ADO parameter object

Posted on 2000-05-16
6
Medium Priority
?
587 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 400 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
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…
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…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Suggested Courses

649 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