ADO Connection, Command, Recordset, and Parameter Object Example

Category:

Database

Type:

Snippets

Difficulty:

Beginning

Author: PB

Version Compatibility: Visual Basic 6, Visual Basic 5

More information:

This is a small example on how to extract the recordset by executing a stored procedure using command object of ADO. It demonstrates the major objects of ADO (connection, command, parameter, and recordset) and is therefore good for beginners to use as a starting point.

The example is based on a stored procedure in a SQL Server database. The procedure is as follows:

Procedure GetRecords

@rowC int output,
@statement varchar(50) output

As

select * from exe /*You can use any table which is present in the database*/

set @rowC=@@rowcount
set @statement='hey good work..buddy'

return

For more, information visit: http://www.geocities.com/priyadarshu

Instructions: Copy the declarations and code below and paste directly into your VB project.

Declarations:
' (None)

Code:

Private Sub Command1_Click()

Dim objCon As ADODB.Connection
    Dim objCom As ADODB.Command
    Dim objPara As ADODB.Parameter
    Dim objpara2 As ADODB.Parameter
    Dim objRS As ADODB.Recordset
    Dim k As Integer
    
    Set objCon = New ADODB.Connection
    Set objCom = New ADODB.Command
    
    'Creating the DB connection string
    'Please change the below connection string as per your server and

database being used.
    objCon.ConnectionString = "PROVIDER=SQLOLEDB.1;PASSWORD=;PERSIST SECURITY INFO=TRUE;USER ID=sa;INITIAL CATALOG=TestSQL;DATA SOURCE=Rockets"
    
    'Opening the connection
    objCon.Open objCon.ConnectionString
    
    MsgBox "Connection opened"
    
    'assigning the command object parameters
    With objCom
        .CommandText = "GetRecords"     'Name of the stored procedure
        .CommandType = adCmdStoredProc  'Type : stored procedure
        .ActiveConnection = objCon.ConnectionString
    End With
    
    'Create 2 output parameters
    Set objPara = objCom.CreateParameter("rows", adInteger, adParamOutput)
    Set objpara2 = objCom.CreateParameter("Status", adVarChar, 
adParamOutput, 50)
    
    'Append the output parameters to command object
    objCom.Parameters.Append objPara
    objCom.Parameters.Append objpara2
    
    'Store the result in a recordset
    Set objRS = objCom.Execute
    
    
    'Open the recordset
    Do While Not objRS.EOF
        For k = 0 To objRS.Fields.Count - 1
            Debug.Print objRS(k).Name & ": " & objRS(k).Value  
        Next
        Debug.Print "_____" 
        objRS.MoveNext
    Loop
    
    'Close the recordset
    objRS.Close
    
    'retrieve the output parameters values
    MsgBox "Total records returned: " & objPara.Value
    MsgBox objpara2.Value

'close connection
    objCon.Close

'cleaning up
    Set objCom = Nothing
    Set objCon = Nothing
    Set objPara = Nothing
    Set objpara2 = Nothing
    Set objRS = Nothing
    
End Sub