How To Run Singleton SELECT Queries in a Visual Basic Client (271689)

SUMMARY

This article demonstrates how to retrieve a single record from SQL Server by using the IRow interface with a singleton SELECT. The main purpose for this technique is to avoid the overhead of creating a recordset when you are fetching a single record. Because no recordset is actually created, only one read-only ADODB.Record is returned. This is true even if the specified SELECT results in multiple records being returned if a normal ADODB.Recordset is used.

MORE INFORMATION

The following are detailed steps to create and test the sample.

Create a Visual Basic Standard EXE project. Form1 is created by default.
On the Project menu, choose References, and then set a reference to Microsoft ActiveX Data Objects 2.x.
Add a command button to Form1 and name it Command1.

Paste the following code into the code window for the Command1 click event:

Note You must change User ID=<username> and Password=<strong password> to the correct values before you run this code. Makesure that the User ID has the appropriate permissions to perform this operation on the database.

Private Sub Command1_Click()

    Dim adoConn As ADODB.Connection
    Set adoConn = New ADODB.Connection
                           
    Dim sConn As String
    sConn = "Provider=SQLOLEDB; Data Source=<your data source>;" & _
            "Initial Catalog=Northwind;User ID=<user name>;Password=<strong password>;"
    adoConn.ConnectionString = sConn
    adoConn.CursorLocation = adUseServer
    adoConn.Open
    
    Dim sQuery As String
    sQuery = "SELECT * FROM CUSTOMERS WHERE CUSTOMERID='ALFKI'"
    
    Dim adoRec As ADODB.Record
    Set adoRec = New ADODB.Record
    'Get one Row of data only
    On Error GoTo RecError
    adoRec.Open sQuery, adoConn, adModeReadWrite, , adOpenExecuteCommand

    Dim col As ADODB.Field
    For Each col In adoRec.Fields
        Debug.Print col.Name & ": " & col.Value
    Next col
    
    GoTo Bye
    
RecError:
    Debug.Print Err.Number & ": " & Err.Description
    If adoRec.State = adStateOpen Then
        For Each col In adoRec.Fields
            Debug.Print col.Name & ": " & col.Status
        Next col
    End If
    
Bye:
    If adoRec.State = adStateOpen Then
        adoRec.Close
    End If
    If adoConn.State = adStateOpen Then
        adoConn.Close
    End If
    Set adoRec = Nothing
    Set adoConn = Nothing

End Sub

Run the program, and note that the results are displayed in the debug window of Visual Basic. If the window is not displayed, you can view it by pressing the CTRL-G keys.