Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Problems With Recordsets

Hello everyone!

I was wondering if someone would be kind enough to explain to me why does this statement returns -1:

recordset.recordcount

The query used in the recordset is:

Select * From TakenExams Where CourseId = '1' and StudentNumber = '841010321'

I checked the table and there are at least 10 records that match the aforementioned query, so I do not know what is causing this error. So, in order to get the total of records that match this query I have to run a loop:

recordset.CursorType = adOpenDynamic
recordset.MoveFirst

While recordset.EOF = False Then
if recordset("CourseId").Value = "1" _
And recordset("StudentNumber").Value = "841010321" Then
totalRecs = totalRecs + 1
end if
recordset.MoveNext
Wend

I had to store a specific value from the Exams table in a dynamic array, so after I run this loop I had to run another one:

ReDim examGrade(totalRecs) as Single

recordset.MoveFirst
For i = 1 to totalRecs
examGrade(i) = recordset("Value").value
recordset.MoveNext
Next i

I would really like some help to optimize this code, because even though it is working, it has to be done in some other way.




Comments

  • jlegjleg Member Posts: 237
    : Hello everyone!
    :
    : I was wondering if someone would be kind enough to explain to me why does this statement returns -1:
    :
    : recordset.recordcount
    :
    : The query used in the recordset is:
    :
    : Select * From TakenExams Where CourseId = '1' and StudentNumber = '841010321'
    :
    : I checked the table and there are at least 10 records that match the aforementioned query, so I do not know what is causing this error. So, in order to get the total of records that match this query I have to run a loop:
    :
    : recordset.CursorType = adOpenDynamic
    : recordset.MoveFirst
    :
    : While recordset.EOF = False Then
    : if recordset("CourseId").Value = "1" _
    : And recordset("StudentNumber").Value = "841010321" Then
    : totalRecs = totalRecs + 1
    : end if
    : recordset.MoveNext
    : Wend
    :
    : I had to store a specific value from the Exams table in a dynamic array, so after I run this loop I had to run another one:
    :
    : ReDim examGrade(totalRecs) as Single
    :
    : recordset.MoveFirst
    : For i = 1 to totalRecs
    : examGrade(i) = recordset("Value").value
    : recordset.MoveNext
    : Next i
    :
    : I would really like some help to optimize this code, because even though it is working, it has to be done in some other way.
    :
    :
    :
    :
    :
    You should move to the end of the recordset before you can rely on the record count. Try MoveLast and then do a count. Or if its just a count your after you could create a count in your sql statement instead of returning a recordset and counting it.

    SELECT COUNT(*) As MyCount FROM CourseId = '1' and StudentNumber = '841010321'

    The field MyCount should return the record count. I would also create your recordsets with another name other than recordset. Something like rs or some other meaningful name.

    eg: rs.recordcount

    rs.Open "SELECT COUNT(*) As MyCount FROM CourseId = '1' and StudentNumber = '841010321'"

    MsgBox rs!MyCount

    Anyway try movelast first and see if it returns the correct value.


    Hope this helps

    John ;-)

  • Bren829Bren829 Member Posts: 14
    Hi John!

    I really appreciate your help. I tried what you suggested:

    rs.MoveLast
    rs.Recordcount

    And it still returns -1. So, I tried the Select Count(*) as MyCount.... query, and it works really fine; the only thing is that, because I also need the values of all the fields in the table, not just the count, I have to do two queries, but I no longer need to run the cycles.

  • archibaldarchibald Member Posts: 9
    : Hi John!
    :
    : I really appreciate your help. I tried what you suggested:
    :
    : rs.MoveLast
    : rs.Recordcount
    :
    : And it still returns -1. So, I tried the Select Count(*) as MyCount.... query, and it works really fine; the only thing is that, because I also need the values of all the fields in the table, not just the count, I have to do two queries, but I no longer need to run the cycles.
    :
    :

    The suggestion above will work if you open the recordset adOpenStatic instead of adOpenDynamic.




Sign In or Register to comment.