Recordset Use

[gknierim]

I want to use multiple recordsets at the same time to gather my data but I do not want to create x number of recordsets. I just want to create one and switch between recordsets. I think you can do this with .ActiveConnection but I'm confused reading the MSDN on this. Can anyone help me out?

Thanks,
Greg

[Johnny101]

If you call a command that returns multiple recordsets you can them back a couple of ways. Some things to note though -

1) the initial recordset object can only move forward through the collection of recordsets. Meaning that once you call the .NextRecordset method you can not get back to the first one.

2) you can set the same object variable equal to the NextRecordset method - there by only using one recordset object variable

3) you can set seperate object variables equal the next recordset method and keep both recordsets open at the same time - however there is sort of "hiearchy" about this. Lets say you have a stored proc that returns 3 recordsets, one for titles, one for authors, and one for publishers and the first recordset is the titles recordset. to access the publishers recordset, in a seperate variable, you have to go through a seperate recordset variable:

Dim cn as ADODB.Connection
Dim rs as ADODB.Recordset
Dim sql as string
Dim rs2 as ADODB.Recordset
Dim rs3 as ADODB.Recordset

set cn = new ADODB.Connection
cn.ConnectionString = "Provider=SQLOLEDB;Server=;User ID=;Password=;"
cn.CursorLocation = adUseClient
cn.Open

sql = "select * from titles (nolock)"
sql = sql & "select * from authors (nolock)"
sql = sql & "select * from publishers (nolock)"

set rs = new ADODB.Recordset
set rs2 = new ADODB.Recordset
set rs3 = new ADODB.Recordset

rs.CursorLocation = adUseClient
rs.LockType = adLockOptimistic
rs.CursorType = adOpenStatic

rs2.CursorLocation = adUseClient
rs2.LockType = adLockOptimistic
rs2.CursorType = adOpenStatic

rs3.CursorLocation = adUseClient
rs3.LockType = adLockOptimistic
rs3.CursorType = adOpenStatic

set rs = cn.Execute(sql) 'returns the titles rs

set rs2 = rs.NextRecordset 'returns the authors rs
set rs3 = rs2.NextRecordset 'returns the publishers rs



at this point - you have three recordset objects - each one is active and ready for your use - simultaneously.

You have to use a client side cursor in order to keep them alive after calling the NextRecordset method - otherwise they are set to nothing.

You can do this, however:

'open your db connection call the same query as above
set rs = cn.Execute(sql) 'returns the titles rs

'do your work the titles rs

set rs = rs.NextRecordset 'returns the authors rs into the same variable

'do your work with the authors

set rs = rs.NextRecordset 'returns the publishers rs into the same variable

'do some more work




you have now iterated through all the recordsets, but with only variable - easier on the memory, but less functional.

hope this helps,

john

John Pirkey
MCSD
http://www.ShallowWaterSystems.com
http://www.stlvbug.org