How can I extract data from AdoRecset?

[ShawnCoutts]

I have a program where I need to connect to a data base and pull a number of records out based on a certain criteria. I have managed to successfully connect to, and open the database, and return the required data into my recordset. This is where I begin to have problems. How can I extract the data out of the recordset? I have tried using the GetRows() function, to put the data into an array, and then used recordcount property of recordset to find the number of records. For some reason, recordcount keeps returning -1. Here is my code:

Private Sub TruckNum_Click()
    Dim adoCon As New ADODB.Connection
    Dim adoCmd As New ADODB.Command
    Dim adoRec As New ADODB.Recordset
    Dim rec As Variant
    Dim i As Integer
    Dim count As Integer
    
    i = 0
    Dim strRec As String
    
    
    adoCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Programmer\Desktop\Klassen_Timeslip\klassen.mdb;" 'UserID=admin;Password=;
    
    adoCon.Open
    adoRec.ActiveConnection = adoCon
    adoCmd.ActiveConnection = adoCon
    
    adoCmd.CommandText = "SELECT * FROM equipment WHERE equipment_type = 1 OR equipment_type = 7 ORDER BY unit_number"
    Set adoRec = adoCmd.Execute
    count = adoRec.RecordCount
    rec = adoRec.GetRows()
    Do While i < count
        strRec = rec(i, 0)
        i = i + 1
    Loop
           
End Sub

the eventual goal is to take strRec and add it into a listbox control, so that the end user can select it.

 

[Andrzejek]

Try

Set adoRec = New ADODB.Recordset
adoRec.CursorType = adOpenStatic
adoRec.CursorLocation = adUseClient
adoRec.LockType = adLockReadOnly
adoRec.Open "SELECT FieldName FROM equipment WHERE equipment_type = 1 OR equipment_type = 7 ORDER BY unit_number", adoCon

lstlistbox1.Clear
If adoRec.RecorCount > 0 Then
    For i = 1 to adoRec.RecorCount
        lstlistbox1.AddItem adoRec!FieldName
    Next i
End If

adoRec.Close

HTH

---- Andy

 

[ShawnCoutts]

Thanks, for that. WOrks great!

 

[Andrzejek]

Almost, I forgot:

Set adoRec = New ADODB.Recordset
adoRec.CursorType = adOpenStatic
adoRec.CursorLocation = adUseClient
adoRec.LockType = adLockReadOnly
adoRec.Open "SELECT FieldName FROM equipment WHERE equipment_type [b]IN (1, 7)[/b] ORDER BY unit_number", adoCon

lstlistbox1.Clear
If adoRec.RecorCount > 0 Then
    For i = 1 to adoRec.RecorCount
        lstlistbox1.AddItem adoRec!FieldName
        [b]adoRec.MoveNext[/b]
    Next i
End If

adoRec.Close

The IN is just a suggestion, a lot easier to modify if you end up adding other numbers.

Glad to help

---- Andy

 

[BobRodes]

For background information (to understand why the recordcount value sometimes says -1, for example), check faq222-3670.

 

[SBerthold]

ShawnCoutts,

For your setup, you could have simply change the cursor location on the connection (one line), prior to opening it, to use a client side cursor as default.
Then the cursor location and cursor type for the recordset would have defaulted to Client-Static.

adoCon.CursorLocation = adUseClient

This will also change the default cursor type, for recordsets returned from the connection's or command's Execute method, to use static.

 

[JoeAtWork]

I find using the EOF property the most reliable method of looping through a recordset:

Do While Not(rs.EOF)
   'Do some stuff with the recordset
   '.....

   rs.MoveNext
Loop

The above method works regardless of what type of cursor I'm using, or any other property setting.

 

[ShawnCoutts]

thanks for all your ideas guys. I came across one of my own as well while trying to figure this one out.

    Do While (Not adoRec.EOF)
        If (Len(adoRec!unit_number) > 0) Then
            ListForm.List.AddItem (adoRec!unit_number)
        End If
        adoRec.MoveNext
    Loop

by using the ! operator I can set my text field to equal the value within that particular record.

Thanks again

 

[CaptainD]

Instead of checking for a count, I like to use this

If (rs.BOF And rs.eof) = True Then
    Set rs = Nothing
    Exit Sub 'Or what ever else you might need to do
Else
    Do work with the recordset, do while loop etc.
End If

 

[BobRodes]

Shawn, the way I do what you say you want to do is just pull all the records into a recordset, and use it to directly populate the listbox. To store the key, I use the ItemData property. If the key isn't an integer or long integer, I use the itemdata property to store offsets of an array containing the keys. Over time, I've found this to be the most efficient method of using listboxes that are tied to database data.

So:

1. Start with empty listbox (clear if necessary)
2. Pull data into (forwardonly or static) recordset
3. Loop through recordset, populating listbox's list and itemdata properties

HTH

Bob