by Ulysses R. Gotera (9 Submissions)
Category: Databases/Data Access/DAO/ADO
Compatability: Visual Basic 5.0
Difficulty: Unknown Difficulty
Originally Published: Mon 25th July 2005
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
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.
VBcoders
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
No comments have been posted about This is a procedure (Sub) that will help you create a parameter object easily. Saves you a lot of k. Why not be the first to post a comment about This is a procedure (Sub) that will help you create a parameter object easily. Saves you a lot of k.
