by Nilesh Shamnani (1 Submission)
Category: Databases/Data Access/DAO/ADO
Compatability: VB 6.0
Difficulty: Unknown Difficulty
Originally Published: Sat 5th February 2005
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
Best Practise of fetching recordset with or without parameters
API Declarations
Private CMD As ADODB.Command
Private PRM As ADODB.Parameter
Private RS As ADODB.Recordset
VBcoders
'---------------
Public Function functionName(ByVal intID as Integer, ByVal strSSN As String) as ADODB.Recordset
Set CN = New ADODB.Connection
CN.CursorLocation = adUseClient
CN.ConnectionString = "File Name=" & App.Path & "\NRUA.udl"
CN.ConnectionTimeout = 0
CN.Open
Set CMD = New ADODB.Command
Set CMD.ActiveConnection = CN
CMD.CommandTimeout = 0
CMD.CommandType = adCmdStoredProc
CMD.CommandText = "USP_YourStoredProcedure"
Set PRM = New ADODB.Parameter
Set PRM = CMD.CreateParameter("ID", adInteger, adParamInput, , strIn)
CMD.Parameters.Append PRM
Set PRM = CMD.CreateParameter("SSN", adVarChar, adParamInput, 11, strSSN)
CMD.Parameters.Append PRM
Set RS = New ADODB.Recordset
Set RS.Source = CMD
RS.CursorType = adOpenStatic
RS.LockType = adLockOptimistic
RS.Open
Set functionName = RS
CN.Close
Set PRM = Nothing
Set CMD = Nothing
Set CN = Nothing
End Function
'Without Parameter
'-----------------
Public Function functionName() as ADODB.Recordset
Set CN = New ADODB.Connection
CN.CursorLocation = adUseClient
CN.ConnectionString = "File Name=" & App.Path & "\NRUA.udl"
CN.ConnectionTimeout = 0
CN.Open
Set CMD = New ADODB.Command
Set CMD.ActiveConnection = CN
CMD.CommandTimeout = 0
CMD.CommandType = adCmdStoredProc
CMD.CommandText = "USP_YourStoredProcedure"
Set RS = New ADODB.Recordset
Set RS.Source = CMD
RS.CursorType = adOpenStatic
RS.LockType = adLockOptimistic
RS.Open
Set functionName = RS
CN.Close
Set CMD = Nothing
Set CN = Nothing
End Function
