RecordSet in ASP

Thunder chicken, It doesn't work because in a loop the recordset will not allow a moveprevious.

This will work as long as you don't have adOpenForwardOnly set with your cursor.  Try adOpenDynamic

great minds think alike.

rs.cursorlocation = 2 'adUseServer
rs.cursortype = 2 'adOpenDynamic

or use "adOpenStatic" I read somewhere that it's faster to go through than "adOpenStatic"

just my 2?

Another option is to put the recordset into an array, which is usually best for this type of operation.  Although, ADO has the capabilty of allowing you to disconnect the recordset and move through locally.  I assume you need to use a client cursor (adUseClient) and adOpenStatic.  adOpenDynamic is for manipulating records while staying connected so that your recordset always is updated with any server changes.  Use an array:
Dim iRow
Dim ary()
iRow = 0
With rs
  While not .EOF
    iRow=iRow+1
    ary("Field1",iRow)=rs("Field1"}
    ary("Field2",iRow)=rs("Field2")
    .Movenext
  Wend
End With

' now play with it

If ary(2,"Field1")=ary(1,"Field1") Then
  MSGBOX "See I was Right"
End If
       

Jon_Raymond,
 putting the same information already available into another object is usually a bad practice.

If you close and destroy the recordset after filling the array, then you are only using one object at a time.  The recordset is useful for retriveing the data. The array is useful for comparing rows of data.  But, neither one is good for both operations.  Overall, this would use the least memory overhead,especially if the recordset is large.  A dynamic cursor is especially resource intensive and with multiple users will create a potential for record lockups, and system lockups due to memory overrun.  With ASP you have to think in terms of multiple users, hundreds or thousands, hitting the server.  Any resource intensiveoperation is multiplied by the number of users involved.

Jon_Raymond

You are basically duplicating the functionality of ADO's GetRows method.

Anthony

You're missing my main point.  There is no need to stay connected and use a dynamic recordset to compare rows of data.  When you do that you use the overhead associated with maintaining a live link to the database.  It is best to disconnect after retrieving the data and then do the comparison operation.  If you do this with an array you also don't need the overhead with maintaing an ADO recordset.  So, you use ADO to get the data, and that's it.  That's what ADO is good for.  There's no need for GETROWS to get a few rows at a time.  But there is a need to compare rows.  The recordet object is not the best one to use to compare records.  But the array is.  It is also posible to disconnect the recordset and manipulate the recordset as a stand alone object.  That might be a second preference.  But, I doubt you can get better performance for this operation than by using an array.

You may want to take a look at the GetRows method: Using the default parameters will populate an array with the whole recordset.  So yes, if you chose to do so, you can:
1 Open the recordset
2 Fill an array with a call to GetRows
3 Close the recordset
4 Use the array.

On the other hand if you prefer to write code to loop through the recordset and in so doing, get better performance, more power to you.

Anthony

Yes. getrows is great for populating an array.  But, I'd like to see some code that loops through a recordset and compares one row with a previous row - getting better performance.

Jon_Raymond,
 I agree with you that in some cases it would be a great alternative, though it is not recommended. And I have to admit at some times I am doing the exact same thing. Except that that is because I need to pass data in a specified format to a component. In this case it might also work pretty well.

I wouldn't grab the array right away but would first like to know what the exact scenario is. Why do you need to compare with the next recordset? This can probably also be done with a well written SQL statement or other mechanism.

Madhu_28,
can you explain exactly what kind of data you have and why the need exists to compare the recordsets?

ZRegards,
CJ

Still if you ONLY need to compare the current row with the next row (I had to do this for a report - and put a break inbetween each department and give totals), then just do the following. (This way you can still use your existing forward only cursor.

do until rs.eof
load the current record department in a variable
Print current Record

rs.movenext
Compare departments, produce sub-totals if the next record is in a different department

loop

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Accept Answer by Ruperts
Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
GaryC123
EE Cleanup Volunteer