Cursor Location RecordSet

[mibeach7]

Hello,
The problem is I have two recordsets. I need to leave one open while I run another against it. I get an error due to the cursor location. I am writing out 1 header record with recordset rsHeader using Q1, and writing out multiple detail records using recordset rsDetail using Q2. Then looping until all Header records are read.
Here is my psuedo code.


Dim MyCon as new adodb.Connection
Dim rsHeader as new adodb.Recordset
Dim rsDetail as new adodb.Recordset
Dim Q1 as string
Dim Q2 as string

MyCon.Connectionstring = "dsn=database ,uid=me , pwd=me;"
MyCon.CommandTimeout = 0
MyCon.Open

rsHeader.cursorlocation = aduseclient
rsHeader.open (Q1), MyCon, adOpenKeyset, adLockOptimistic

Do Until rsHeader.EOF
rsHeader.MoveFirst

rsDetail.cursorlocation = aduseclient
rsDetail.open (Q1), MyCon, adOpenKeyset, adLockOptimistic

Do while rsHeader!KeyValue = rsDetail!KeyValue 'join
outfile.write 'write out header record
rsDetail.MoveNext
Loop

outfile.write 'write out detail records
rsHeader.MoveNext
Loop

rsHeader.Close
rsDetail.Close
MyCon.Close
rsHeader = Nothing
rsDetail = Nothing
MyCon = Nothing

End Sub

The problem seems to be with having two recordsets open at the same time and trying to use .CursorLocation for both.
Is there a better way to loop without having both recordsets open at the same time?? Or change the setting of the rs.open command??
I read my VB 6 advanced book and it explained DAO and recordsets very well, but did not show an example with two recordsets.
Any help would be greatly appreciated.

 

[fredericofonseca]

You can have several recordsets opened at the same time, and the cursor location does not depend on the other recordsets. It only applies individually.

I did however noticed a problem with your code.

Look at the following code modified from your original

  rsDetail.cursorlocation = aduseclient

Do Until rsHeader.EOF
rsHeader.MoveFirst

'  rsDetail.cursorlocation = aduseclient ' move this bit to outside loop.
'Here you open the recordset within a loop
' I also assumed you had a typo on the sql variable
  rsDetail.open (Q2), MyCon, adOpenKeyset, adLockOptimistic
'  rsDetail.open (Q1), MyCon, adOpenKeyset, adLockOptimistic

' what if there are no details? May not be needed.
if not rsDetail.EOF then 'new code
  Do while rsHeader!KeyValue = rsDetail!KeyValue  'join
  outfile.write       'write out header record
  rsDetail.MoveNext
  Loop
' so here you need to close the recordset
  rsDetail.close 'new code
end if 'new code

outfile.write          'write out detail records
rsHeader.MoveNext
Loop

Now the above code does not seem very good.
If you are looping through a headers query it would seem logical that the detail SQL is related directly to that header, but I don't see you changing the Q2 query. Otherwise and assuming the records are on the same order (e.g. invoice number for example) then the rsDetail recordset should be opened OUTSIDE the loop.

rsDetail.cursorlocation = aduseclient
rsDetail.open (Q2), MyCon, adOpenKeyset, adLockOptimistic

Do Until rsHeader.EOF
rsHeader.MoveFirst

if not rsDetail.EOF then 'new code
  Do while rsHeader!KeyValue = rsDetail!KeyValue  'join
  outfile.write       'write out header record
  rsDetail.MoveNext
  Loop
end if 'new code

outfile.write          'write out detail records
rsHeader.MoveNext
Loop

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[pkailas]

I have one question.

Do Until rsHeader.EOF
rsHeader.MoveFirst


Won't that start each loop at the same record? How will it ever get to the second record? Should it be:

rsHeader.MoveFirst
Do Unitl rsHeader.Eof