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


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.

↑ 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
        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
    					
  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 : 218