View Code Snippet

Author: Ulysses R. Gotera
Date Submitted: 7/25/2005
Category: Database
Compatibility: VB 6,VB 5
This code has been accessed 6810 times.

Task: This is a procedure (Sub) that will help you create a parameter object easily. Saves you a lot of keystrokes in building your command parameters. If you are the type of Visual Basic programmer that uses stored procedures and ADO objects then this is for you otherwise keep this code for future use or reference in using ADO command parameter object. Note : You can view my profile at friendster.

Declarations

Code

Public Sub BuildParameter(ByRef p_oCmd As ADODB.Command, _ 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