How can I copy one recorset into other recordset?

[SunStorm]

I have a recorset with 30 fields, but I wan't to copy field by field. Can I copy recordset by recordset?

Thanks Thanks.
SunStorm

 

[chiph]

You can do this via SQL:
[tt]
INSERT INTO tbl_Apple (PriKey, Description, Weight)
[tab]SELECT PriKey, Description, Weight
[tab]FROM tbl_Fruit
[tab]WHERE Description LIKE 'RED DELICIOUS%'
[/tt]
Chip H.

 

[SunStorm]

But, I need to do it in VBS and ASP.

Thanks for all Thanks.
SunStorm

 

[Ovatvvon]

SunStorm,
Chiph headed you in the right direction. You use SQL statements with the web via ASP. You can use a similar SQL statement to the one (S)He gave in an .asp page like so...

---------copying.asp----------------------
<%
Dim sql, sql2
sql = &quot;SELECT tbl_original.prikey, tbl_original.anotherfield, tbl_original.lastField FROM tbl_original;&quot;
Set conn = Server.CreateObject(&quot;ADODB.Connection&quot
DSNtemp = &quot;Driver={Microsoft Access Driver (*.mdb)};&quot;
DSNtemp = DSNtemp & &quot;DBQ=C:\inetpub\

http://wwwroot\db\mydatabase.mdb;&quot;

conn.Open DSNtemp
Set rs = Server.CreateObject(&quot;ADODB.Recordset&quot
rs.Open sql, conn, 3, 3

Do while not rs.eof
sql2 = &quot;INSERT INTO tbl_newTable (prikey, anotherfield, lastField) VALUES ('&quot; & rs(&quot;prikey&quot & &quot;', '&quot; & rs(&quot;anotherfield&quot & &quot;', '&quot; & rs(&quot;lastField&quot & &quot;');&quot;
Set rs2 = Server.CreateObject(&quot;ADODB.Recordset&quot
rs.Open sql2, conn, 3, 3
rs.movenext
loop


conn.Close
Set conn = nothing

%>

-----------------------------------------
Hope this helps!
-Ovatvvon

 

[jonscott8]

Sorry, but I disagree with Chip and Ovatton. Why would you persist data in a table of the database when you only need it temporarily?

SunStorm,
refer to the recordset's clone method in the ADO documentation.

SET oConn = CreateObject(&quot;ADODB.Connection&quot
SET oRS = CreateObject(&quot;ADODB.Recordset&quot
SET oNewRS = CreateObject(&quot;ADODB.Recordset&quot

oConn.Open &quot;dsn=MyDSN&quot;
Set oRS.ActiveConnection = oConn
oRS.CursorType = 1 && Keyset cursor
oRS.LockType = 3 && Lock Optimistic
oRS.Source = &quot;tbl_Fruit&quot;
oRS.Open

Set oNewRS = oRS.Clone

Do Until oNewRS.EOF
oNewRS.MoveNext
Loop Jon Hawkins

 

[chiph]

jonscott8 -

I assumed that SunStorm wanted to make a permanent copy of the data. If, like you say, he/she wanted to make a local in-memory copy, the Clone method would be a good way to do it. For one thing, it's really fast compared with duplicating a recordset row by row, field by field.

Chip H.

 

[johnaregan]

To all
With this clone recordset,using ADO, would you be able to change a fields value and insert the record back into the database as a completely new record? Would you also be able to change the connection and insert this new recordset into a completely different database on a different server.

Basiclly what I would like is a 'create a new user' system which inserts records into the 4 databases we have using ASP and ADO. The databases exist on 4 different servers but have the same schema. At the moment the process to create a new user takes a long time!
There are 3 tables something like USER, GROUP, PERMISSIONS. It would be great if I could copy an existing user from these tables into an ASO recordset, change the user name and password, connect this recordset to each database in turn and insert the new record.

Many thx in advance
John

 

[SunStorm]

Thanks for all

But I need this:

I need to save 1 row of the database table in other database table but not all fields are the same, the 80 percent of the fields are the same that the original but the others are modified for the ASP.

It's very bored, make field by field the copy at the new table, for example:
Recorset1.addnew
Recordset1(&quot;field1&quot=Recorset2(&quot;fieldoriginal1&quot
Recordset1(&quot;field2&quot=Recorset2(&quot;fieldoriginal2&quot
......
Recordset1(&quot;fieldN&quot=value modified
Recordset1(&quot;fieldN+1&quot=value modified2
.......
Recorset1.update

I have a recorset with 70 fields!!!.

If I use the clone method, I can't make update later.
and I can't use the sql direct because I can't modified the data.

Do you know any solution for my problem???

Thanks for all.






Thanks.
SunStorm

 

[vasah20]

Have you tried iterating through the .Fields Collection, then doing the update that way? I haven't gotten a chance to test this, but in theory it should work.

ex: (assuming that the RS has been created, and that RS and RS2 have the same field names)

dim item
RS.AddNew
for each item in RS.Fields
if item = &quot;abc&quot; then
RS(item) = changedValue
else
RS(item) = RS2(item)
end if
next
RS.Update

It should be something along these lines...
hopefully this helps out your predicament

leo

 

[SunStorm]

Thanks vasah20, I test it in the first time that I need to copy the recorset.

Have a nice day. Thanks.
SunStorm

 

[SunStorm]

Your code not running because the Field object requires the property name for run correctly.

This code runs. Otherwise, thanks for your help.

dim item
RS1.AddNew
for each Item in RS2.Fields
If Item.Name=&quot;value&quot; then
RS1(Item.Name)=&quot;changed value&quot;
else
RS1(Item.Name) = RS2(Item.Name)
end if
next
RS1.Update

Thanks for all.
Thanks.
SunStorm