Sometimes you have more than one recordset and you want all
of them to be merged to single recordset which will hold all the
fields and all the records from all the original recordsets.
This allow you to control the data in more ways than with pure
SQL and also give you free movement within the records as you
get Disconnected recordset which is not Forward Only.
The only limitation of the merging is that if you have fields with
the same name in more than one of the recordsets, all of those
fields must have the same data type and same length.
Here is the function itself:
Code:
Function MergeRecordSets(arrRecordsets)
Dim x, y, objCurrentRS
Dim objMergedRecordSet, objField, blnExists
Set objMergedRecordSet = Server.CreateObject("ADODB.Recordset")
For x=0 To UBound(arrRecordsets)
Set objCurrentRS = arrRecordsets(x)
For Each objField In objCurrentRS.Fields
blnExists = False
For y=0 To objMergedRecordSet.Fields.Count-1
If LCase(objMergedRecordSet.Fields(y).Name) = Lcase(objField.Name) Then
blnExists = True : Exit For
End If
Next
If Not(blnExists) Then
objMergedRecordSet.Fields.Append objField.Name, objField.Type, objField.DefinedSize
'objMergedRecordSet.Fields(objMergedRecordset.Fiel ds.Count-1).Attributes = 32 'adFldIsNullable
End If
Next
Next
objMergedRecordSet.Open
For x=0 To UBound(arrRecordsets)
Set objCurrentRS = arrRecordsets(x)
Do Until objCurrentRS.EOF
objMergedRecordSet.AddNew
For Each objField In objCurrentRS.Fields
If Not(IsNull(objField.Value)) Then
objMergedRecordSet.Fields(objField.Name).Value = objField.Value
End If
Next
objCurrentRS.MoveNext
Loop
Next
objMergedRecordSet.MoveFirst
Set MergeRecordSets = objMergedRecordSet
End Function
as you can see, you have to pass array of recordsets to the
function, and you get back one recordset as the result.
Example of usage:
Code:
Dim rs1, rs2, objBigRS
Dim x
Set rs1 = objConn.Execute("Select * From Table1")
Set rs2 = objConn.Execute("Select * From Table2")
Set objBigRS = MergeRecordSets(Array(rs1, rs2))
rs1.Close
rs2.Close
Response.Write("<table>")
Response.Write("<tr>")
For x=0 To objBigRS.Fields.Count-1
Response.Write("<th>" & objBigRS.Fields(x).Name & "</th>")
Next
Response.Write("</tr>")
Do Until objBigRS.EOF
Response.Write("<tr>")
For x=0 To objBigRS.Fields.Count-1
Response.Write("<td>" & objBigRS.Fields(x).Value & "</td>")
Next
Response.Write("<tr>")
objBigRS.MoveNext
Loop
Response.Write("</table>")
objBigRS.Close
as you can see, you can (and better) close the original
recordsets right after merging them - all their data will
be inside the merged recordset.
Any questions and/or comments are welcome, Happy Programming!
ASP Free Forums Sponsor:
May 26th, 2007, 04:14 AM
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
