Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

How To Run Singleton SELECT Queries in a Visual Basic Client

View products that this article applies to.

This article was previously published under Q271689

↑ Back to the top


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.

↑ Back to the top

More information

The following are detailed steps to create and test the sample.
  1. Create a Visual Basic Standard EXE project. Form1 is created by default.
  2. On the Project menu, choose References, and then set a reference to Microsoft ActiveX Data Objects 2.x.
  3. Add a command button to Form1 and name it Command1.
  4. 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
        Dim sQuery As String
        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
        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
        If adoRec.State = adStateOpen Then
        End If
        If adoConn.State = adStateOpen Then
        End If
        Set adoRec = Nothing
        Set adoConn = Nothing
    End Sub
  5. 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.

↑ Back to the top

Keywords: KB271689, kbhowto

↑ Back to the top

Article Info
Article ID : 271689
Revision : 6
Created on : 7/13/2004
Published on : 7/13/2004
Exists online : False
Views : 451