StarQuest Technical Documents

Sample Visual Basic 6.0 ADO Code to use RecordCount property for an ADO Resultset

Last Update: 26 June 2008
Product: StarSQL
Version: 5.x
Article ID: SQV00SQ038

Abstract

Below is sample code written in Visual Basic 6.0 which demonstrates how to successfully use the RecordCount property for an ADO Resultset. With a ForwardOnly cursor, you must set the CursorLocation property of the ADO Connection to client.

Solution

To use this code, create a standard exe in VB 6.0 and perform the following steps:

1. Create a form named Form1.
2. Add command button named Command1.
3. Add a listbox named List1.
4. Add "Microsoft ActiveX Data Objects 2.0 Library" to Project | References.
5. Add the code below to the command button's click event.
6. Modify the connect string to match your datasource name and uid and pwd.

7. If you do not create a sample table on your host using the SQL data definition language statement below, you will need to modify the SELECT statement to query the chosen table on your host. Sample SQL DDL to create table:
   
   CREATE TABLE MYLIB.MYTABLE (COL_ONE CHAR(10))
   
   which you may populate with the following INSERT statements:
   
   INSERT INTO MYLIB.MYTABLE VALUES ('TEST1')
   INSERT INTO MYLIB.MYTABLE VALUES ('TEST2')
   INSERT INTO MYLIB.MYTABLE VALUES ('TEST1')
   INSERT INTO MYLIB.MYTABLE VALUES ('TEST3')

Sample Code

Private Sub Command1_Click()

'Initialize objects
On Error GoTo Command3_ErrorProc:
Dim adoConn As New ADODB.Connection
Dim adoCmd As New ADODB.Command
Dim objErr As ADODB.Error
Dim adoRs As ADODB.Recordset
Dim RecordsAffected As Long
Dim SqlSent As String
Dim i As Integer
List1.Clear

'Set cursor location to client, so that record count does not return -1
'See Microsoft Knowledge Base article Q194973 for discussion
adoConn.CursorLocation = adUseClient
Me.MousePointer = 11

'Select statement with WHERE criteria on first column
'Modify this statement if you are not using the provided sample table
SqlSent = "SELECT * FROM MYLIB.MYTABLE WHERE COL_ONE = 'TEST1'"

'Connection string using StarSQL DSN
adoConn.Open "DSN=MY_DB2;UID=MY_UID;PWD=MY_PASS"

'Command properties set
adoCmd.Name = "TestParm"
Set adoCmd.ActiveConnection = adoConn
adoCmd.CommandText = SqlSent
adoCmd.CommandType = adCmdText
adoCmd.CommandTimeout = 15

'result set defined; execute SQL statement
Set adoRs = adoCmd.Execute(RecordsAffected)

'Return first column of each record in result set to List box
For i = 1 To adoRs.RecordCount
List1.AddItem adoRs(0)
adoRs.MoveNext
Next

'Clear connection
Set adoCmd = Nothing
Set adoConn = Nothing
Me.MousePointer = 0
Exit Sub

'Error handling
Command3_ErrorProc:
Me.MousePointer = 0
If Not adoCmd Is Nothing Then
Set adoCmd = Nothing
End If
If Not adoConn Is Nothing Then
Set adoConn = Nothing
End If
MsgBox Err.Description

Exit Sub

End Sub