Expert: Jeff Laatsch - 9/18/2009

Question

Hi Jeff,

I have three tables, say X,Y,Z. Each of the three tables
is linked by a unique ID field.How can create a recordset using SQL in
VB6 so that the corrseponding records in each of the tables  
can modified or deleted at once??  

Answer
Charles:

Reference Website(s):

http://www.4guysfromrolla.com/webtech/083101-1.shtml

Please follow:

Multiple Recordsets - the ability to stuff multiple SQL query results into one Recordset - is an ADO feature that's few developers

When developing ASP pages, there are times when our ASP programming logic requires us to retrieve multiple recordsets that originate from multiple tables that have no direct relationship between them. (Retrieving multiple recordsets means that we use more than one SQL SELECT statement. ) But that is not too difficult a task for an intermediate or experienced ASP programmer. Usually the common solution would be to:


Get the first recordset...

Doing some processing with it...

Close it and get the second recordset...

Process it, close it, get the third, ... and so on...


First, create an instance of ADO Recordset and populate the first Recordset with the appropriate data from the xxxxx table, and do some processing with that Recordset:

example:
'adjust this to your own
sConnectString = "DRIVER={sql server};SERVER=localhost;" & _
                "DATABASE=northwind;UID=sa;PWD="

'Populate the Recordset
set oRS = Server.CreateObject("ADODB.Recordset")
oRS.Open "SELECT categoryId, categoryName FROM Categories " & _
        "WHERE categoryId > 3", sConnectString

'Now we've got the recordset, and do some processing with its records
Do While Not oRs.EOF
  '......some processing here      
  oRs.MoveNext
Loop

oRS.Close

Once we've finished processing the Categories table Recordset, we're ready to populate the second Recordset from Region table, and also do some processing with it.

oRS.Open "SELECT RegionId, RegionDescription FROM Region", sConnectString

'Now we've got the recordset, and do some processing with its records

Do While Not oRs.EOF
  '......some processing here      
  oRs.MoveNext
Loop

oRS.Close


Okay, at this point, we're 100% done with our Recordset objects, so we need to do clean up.

Set oRS = Nothing  


Yes, we've accomplished what we want
In order to make our code more readable and easy to maintain, it's better we declare our SQL SELECT statements like the one below (don't forget to put semicolon between SQL SELECT statements).

sSQL = "SELECT categoryId, categoryName FROM Categories WHERE categoryId > 3"
sSQL = sSQL & ";SELECT RegionId , RegionDescription FROM Region"
sSQL = sSQL & ";SELECT ShipperID, CompanyName, Phone FROM Shippers"

create our multiple Recordset:

sConnectString = "DRIVER={sql server};SERVER=localhost;" & _
                "DATABASE=northwind;UID=sa;PWD="

set oRS = Server.CreateObject("ADODB.Recordset")
oRs.Open sSQL, sConnectString

If you like to use SQL stored procedures, you can utilize multiple Recordsets by creating a stored procedure that returns multiple SQL queries, like so:

Create Procedure MyResults
AS

SELECT categoryId, categoryName FROM Categories WHERE categoryId > 3

SELECT RegionId, RegionDescription FROM Region

SELECT ShipperID, CompanyName, Phone FROM Shippers




And then create the multiple Recordset as follows:

sConnectString = "DRIVER={sql server};SERVER=localhost;" & _
                "DATABASE=northwind;UID=sa;PWD="

set oRS = Server.CreateObject("ADODB.Recordset")
oRs.Open "MyResults", sConnectString



'Create the SQL string
sSQL = "SELECT categoryId, categoryName FROM Categories WHERE categoryId > 3"
sSQL = sSQL & ";SELECT RegionId , RegionDescription FROM Region"
sSQL = sSQL & ";SELECT ShipperID, CompanyName, Phone FROM Shippers"

'Define our connection string
sConnectString = "DRIVER={sql server};SERVER=localhost;" & _
                "DATABASE=northwind;UID=sa;PWD="

'Retrieve the multiple Recordsets
set oRS = Server.CreateObject("ADODB.Recordset")
oRs.Open SQL, sConnectString

'Work with the first Recordset (the Categories table)
Do While Not oRs.EOF
  '......some processing here      
  oRs.MoveNext
Loop

'Move to the next Recordset
Set oRS = oRS.NextRecordset()

'Work with the second Recordset (the Region table)
Do While Not oRs.EOF
  '......some processing here      
  oRs.MoveNext
Loop

'Clean up...
oRS.Close
Set oRS = Nothing

There are some things to be wary of when using Multiple Recordsets. Unfortunately, you can't use the NextRecordset method with an ADO disconnected Recordset. Also not all data providers support multiple Recordsets

Good luck

Kind Regards,
Jeff