VB6 MySQL Connection Problem

[hesbourne]

Hello , I have successfully connected my VB6 application with MySQL database and its working perfectly only to find that I cant use the Rs.Recordcount facility. How can I get round this problem?

Regards

[ridder]

did you declare that record set or not

ie., Dim rs As ADODB.Recordset

[dee-u]

Welcome to the forums!

What specific problem are you having? Does it return the wrong count? Does it raise an error?

AFAIK, recordsets with cursor type set to adOpenKeyset is the only one that returns the Recordcount.

[hesbourne]

Thanks ridder, I had done that.

Dee-U, this is my code which is not working. It does not give any error message nor does it give the correct figure of the number of records selected from the database. Suprsingly, this works with Ms Access and the SQL Server.

dim Rs as New ADODB.Recordset
dim X as Integer
dim StudentsArray() as Variant

set Rs=Nothing
Rs.Open("SELECT F_Name FROM Students"),DbConn,adOpenKeySet, adLockOptimistic

if Rs.EOF =False and Rs.BOF =False then
for X=0 to Rs.RecordCount-1
StudentsArray(X)=Rs!F_Name
Next X
End if

At this point, there are 5 records. It enters the if statement but does not enter the For loop.

Regards

[dee-u]

Try this.

Private Sub Test()
    Dim Rs              As ADODB.Recordset
    Dim X               As Integer
    Dim StudentsArray() As String
    
    ReDim StudentsArray(0)
    Set Rs = New ADODB.Recordset
    With Rs
        .Open ("SELECT F_Name FROM Students"), DbConn, adOpenKeyset, adLockOptimistic
        Do While Not .EOF
            ReDim Preserve StudentsArray(UBound(StudentsArray) + 1)
            StudentsArray(UBound(StudentsArray) - 1) = .Fields(0)
            Rs.MoveNext
        Loop
        .Close
    End With
    Set Rs = Nothing
    If UBound(StudentsArray) > 0 Then
        ReDim Preserve StudentsArray(UBound(StudentsArray) - 1)
    End If
End Sub

[hesbourne]

Thanks Dee, think that'll work. Let me try. If it doesnt, I'll get back to U

Regards

[hesbourne]

That works! But I guess I opted for the For ... Next loop because am populating a two dimensional array. Any idea

Regards

[dee-u]

You mean retrieve two or more fields from the table? If yes then have a search for .GetRows.

[hesbourne]

Thanks dee . Rs.Getrows demands that I specify inadvance the number of rows to fetch. How can I work with it to get the number of records without necessarily specfyinng how many they are.

Regards

[si_the_geek]

Thread moved to Database Development forum (the "VB6" forum is meant for questions which don't fit in more specific forums)

The parameters for GetRows are all optional - you do not need to specify them (the default amount of rows is "the rest of them").

If for some reason you do actually need to find out how many records there are, run a separate query first (eg: "SELECT Count(*) FROM Students").