?
Solved

Passing a Null parameter to SQL Server using ADO parameter object

Posted on 2000-05-16
6
Medium Priority
?
585 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

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

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…
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…
Suggested Courses
Course of the Month12 days, 17 hours left to enroll

777 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