Microsoft KB Archive/271689

= How To Run Singleton SELECT Queries in a Visual Basic Client =

Article ID: 271689

Article Last Modified on 7/13/2004

-

APPLIES TO


 * Microsoft Data Access Components 2.6
 * Microsoft Data Access Components 2.7
 * Microsoft Visual Basic 6.0 Learning Edition
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition
 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7

-



This article was previously published under Q271689



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= and 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 = &quot;Provider=SQLOLEDB; Data Source= ;&quot; & _ &quot;Initial Catalog=Northwind;User ID= ;Password= ;&quot; adoConn.ConnectionString = sConn adoConn.CursorLocation = adUseServer adoConn.Open Dim sQuery As String sQuery = &quot;SELECT * FROM CUSTOMERS WHERE CUSTOMERID='ALFKI'&quot; 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 & &quot;: &quot; & col.Value Next col GoTo Bye RecError: Debug.Print Err.Number & &quot;: &quot; & Err.Description If adoRec.State = adStateOpen Then For Each col In adoRec.Fields Debug.Print col.Name & &quot;: &quot; & 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.

Keywords: kbhowto KB271689

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.