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.
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.
|
Help and Support
Back to the top
