Clone Recordset to new Recordset with Append.Fields
From: Chad (ChadBeckner_at_ProspectiveLink.com)
Date: 03/25/05
- Next message: Jerommeke: "Re: Adding relationships between tables using ADODB and ADOX"
- Previous message: Ross McKay: "Re: ADO and Win32 Services"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 25 Mar 2005 00:59:28 -0500
Hey everyone,
I am "trying" to do the following:
1. Query Database for recordset
2. Create a duplicate recordset with additional fields (using Append.Fields)
3. Get the data from the original recordset into the new recordset (the one
with the appended fields)
I have tried using .Clone(), but then I can't seem to append fields to the
"cloned" recordset.
I am using the following method:
Set RS1 = Server.CreateObject("ADODB.RecordSet")
RS1.CursorLocation = adUseClient
RS1.CacheSize = 25000 'leave room for expanding
RS1.Open SQLStatement, <dbconnection>, adOpenStatic, adLockReadOnly,
adCmdText
'
...................................................................^....................^..........
---> I have tried different variations of this...
RS1.ActiveConnection = Nothing
With RS2.Fields
For Each Field in RS1.Fields
'Response.Write "Field Name: " & Field.Name & vbTab & "Field
Type: " & Field.Type & vbTab & "Field Size: " & Field.DefinedSize & "<br>"
.Append Field.Name, Field.Type, Field.DefinedSize,
adFldIsNullable
Next
.Append "New_Field_1", 200, 50, adFldIsNullable
.Append "New_Field_2", 200, 50, adFldIsNullable
.Append "New_Field_3", 200, 50, adFldIsNullable
.Append "New_Field_4", 200, 50, adFldIsNullable
End With
RS2.Open
While NOT RS1.EOF
With RS2
.AddNew
For Each Field in RS1.Fields
.Fields(Field.Name).Value = RS1.Fields(Field.Name).Value
Next
.Fields("New_Field_1").Value = "NewValue"
.Fields("New_Field_2").Value = "NewValue"
.Fields("New_Field_3").Value = "NewValue"
.Fields("New_Field_4").Value = "NewValue"
.Update
End With
RS1.MoveNext
Wend
The original recordset contains around 4500 records (and is very fast to
get from the SQL server), but this method is EXTREMELY slow (takes 6
seconds), and this should happen all in memory, so it should be much
quicker, right? (I know the ADO can be slow, but this is not THAT many
records, considering). Since this is in vbscript and a web app, as we all
know, time is money! Does anyone have a better method or idea? I've tried
changing the cursor types, lock types so that I could just append the fields
to the original recordset, but that is not working (always gives me an
error!!!).
Any help would be very much appreciated.
Chad
- Next message: Jerommeke: "Re: Adding relationships between tables using ADODB and ADOX"
- Previous message: Ross McKay: "Re: ADO and Win32 Services"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
