• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• This is a procedure (Sub) that will help you create a parameter object easily. Saves you a lot of k

This is a procedure (Sub) that will help you create a parameter object easily. Saves you a lot of k

This is a procedure (Sub) that will help you create a parameter object easily. Saves you a lot of keystrokes in building your command

API Declarations


' ActiveX Data Objects Library 2.1 or higher.
' Press Ctrl+G and type in the Immediate window
' SampleUsage and then press enter.




Rate This is a procedure (Sub) that will help you create a parameter object easily. Saves you a lot of k

• 1
• 2
• 3
• 4
• 5

(1(1 Vote))

    ByVal p_strStoredProcParamName As String, _
    Optional ByVal p_DataType As ADODB.DataTypeEnum = ADODB.adCmdUnknown, _
    Optional ByVal p_ParamDirection As ADODB.ParameterDirectionEnum = ADODB.adParamUnknown, _
    Optional ByVal p_lngStringSize As Long, _
    Optional ByVal p_varValue As Variant)
' **************************************************
' Author      : Ulysses R. Gotera
' Description : Creates a parameter object.
' **************************************************
On Error Goto ErrHandler

    With p_oCmd
        ' Gets the return value of the stored procedure.
        If p_ParamDirection = adParamReturnValue Then
            .Parameters.Append .CreateParameter(p_strStoredProcParamName, _
            p_DataType, adParamReturnValue)
        ElseIf p_lngStringSize = 0 Then
            ' This applies for all data types exept for VarChar or Char types
            .Parameters.Append .CreateParameter(p_strStoredProcParamName, _
                p_DataType, p_ParamDirection, , p_varValue)
        Else
            .Parameters.Append .CreateParameter(p_strStoredProcParamName, _
                p_DataType, p_ParamDirection, p_lngStringSize, p_varValue)
        End If
    End With

ErrHandler:
   If Err.Number <> 0 Then
      MsgBox "Error #: " Err. Number & _
      vbCrLF & _
      "Description: " & Err.Description, vbOkOnly, "BuildParameter"
   End If
End Sub


Public Sub SampleUsage()
' Suppose that in the Northwind database you have a stored procedure
' named uspGetEmployees that has an input parameter @i_EmployeeId.
' You do not want the entire recordset to be returned because you
' would only want the first name and last name of a single 
' employee record. Your stored procedure have two ouput 
' parameters (@c_FirstName and @c_LastName) where you 
' will get their first and last names.  
Dim objConn As New ADODB.Connection, objCmd As New ADODB.Command, _
    strFirstName As String, strLastName As String, _
    bytReturn As Byte, intEmpNo As Integer

    objConn.Connection = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;" & _
        "Initial Catalog=Northwind;Data Source=TEST"   ' The Data Source is the
                                                       ' name of your machine.
    objConn.Open
    intEmpNo = 9    ' Sample employee number
    With objCmd
        Set .ActiveConnection = objConn
        BuildParameter objCmd, "@rc", adInteger, adParamReturnValue
        BuildParameter objCmd, "@i_EmployeeId", adSmallInt, adParamInput, p_varValue:=intEmpNo
        BuildParameter objCmd, "@c_FirstName", adChar, adParamOutput, 10
        BuildParameter objCmd, "@c_LastName", adChar, adParamOutput, 20
        .CommandText = "uspGetEmployees"
        .CommandType = adCmdStoredProc
        .Execute Options:=adExecuteNoRecords  ' Tells ADO not to return a recordset.
        bytReturn = .Parameters("@rc").Value
        strFirstName = .Parameters("@c_FirstName").Value & ""  ' The double quotes (empty string)
        strLastName = .Parameters("@c_LastName").Value & ""    ' are for the output parameters
                                                               ' returning a NULL value to prevent
                                                               ' a VB error.  You can only use this
                                                               ' technique for MS SQL Char and
                                                               ' VarChar data types.
    End With

    Set objCmd = Nothing
    Set objConn.Close: Set objConn = Nothing

    Debug.Print "Stored Procedure Return Value: " & bytReturn
    Debug.Print "First Name: " & strFirstName
    Debug.Print "Last  Name: " & strLastName

' ********** Stored Procedure **********
' If you want to run the SampleUsage
' then copy and paste this stored procedure
' to your Query Analyzer and then press F5.
' Just do not forget to remove the single quoutes.
'
' USE Northwind
' GO
'
' IF EXISTS (SELECT name
'     FROM   sysobjects
'     WHERE  name = N'uspGetEmployees'
'        AND type = 'P')
'     DROP PROCEDURE dbo.uspGetEmployees
' GO
'
' CREATE PROCEDURE uspGetEmployees
'     @i_EmployeeID Smallint,
'     @c_FirstName  Char(10) OUTPUT,
'     @c_LastName   Char(20) OUTPUT
' AS
'     /*
'         Author     : Ulysses R. Gotera
'         Description: Retrieves the first and last name of an employee.
'         Note       : I do not have an actual MS SQL installed at home so
'             my basis is the MS Access 2000 Northwind database.
'             I am only assuming that the data structure is the same.
'     */
'     DECLARE @i_error Int
'
'     SET @i_error = 6        -- Unexpected Error
'
'     DECLARE emp_cursor CURSOR LOCAL FAST_FORWARD FOR
'     SELECT  FirstName, LastName
'     FROM    Employees
'     WHERE   EmployeeID = @i_EmployeeID
'     OPEN            emp_cursor
'     FETCH NEXT FROM emp_cursor
'     INTO @c_FirstName, @c_LastName
'     CLOSE           emp_cursor
'     DEALOCATE       emp_cursor
'
'     IF @@ERROR = O
'         SET @i_error = 0     -- Success
' RETURN (@i_error)
' GO
' ********** Stored Procedure **********
End Sub