• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• Best Practise of fetching recordset with or without parameters

Best Practise of fetching recordset with or without parameters

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



Rate Best Practise of fetching recordset with or without parameters

• 1
• 2
• 3
• 4
• 5

(2(2 Vote))

'---------------

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