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

  • : 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 ;-)

  • 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.

  • : 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.

Howdy, Stranger!

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

Categories