VBA SQL (MariaDB) - Query SELECT * duplicates first row, and miss last one-CodePudding

I have an issue while trying to get every line from a table in my database. In VBA when requesting the table with 'SELECT * FROM companies;', the results duplicates the first row, and remove the last one. As a result, I have 3 records, which corresponds to the real number of records in my DB, but instead of having 1, 2 and 3, I have 1, 1 and 2.

Any idea?

You can see here Database records for table 'companies', when requesting 'SELECT * FROM companies': DB Records

You can see here the result of the same request in Excel/VBA using the following code:

sqlQuery = "SELECT ALL * FROM companies;"
rsDB.Open sqlQuery, conDB, adOpenDynamic

Do While Not rsDB.EOF
    For Each col In rsDB.GetRows
        Debug.Print col
    Next
Loop

Results: VBA request

Would love to get any piece of advice on this issue! The fun fact is that if I try to select only one column of the table, such as 'idCompany', then I have the result '1, 2, 3' with VBA, which is fine. The real issue only appears when using '*'.

Thanks a lot for your time,

CodePudding user response：

Difficult to test, but I suspect you need this instead:

rsDB.MoveFirst
Do While Not rsDB.EOF
    For Each fld In rsDB.Fields
        Debug.Print fld.Name & ": " & fld.Value
    Next
    rsDB.MoveNext
Loop

When you iterate an ADO recordset, the object itself represents a current row. So you refer to the Fields of the current row to get the columns. And the properties of each field to get descriptive information about that cell (name of column, value in cell).

CodePudding user response：

If you are missing the last one then I would change the check to happen at the end of the loop. I will say that none of my code does this so it doesn't seem correct to me. Maybe your object is different than mine. You don't show us how you are getting the object.

sqlQuery = "SELECT ALL * FROM companies;"
With rsDB.Open(sqlQuery, conDB, adOpenDynamic)
    If Not (.BOF And .EOF) Then
        .MoveFirst
        Do 
            .MoveNext

            For Each fld In .Fields
                Debug.Print fld.Name & ": " & fld.Value
            Next
        Loop Until .EOF
    End If

    .Close
End With