By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,365 Members | 2,946 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,365 IT Pros & Developers. It's quick & easy.

easy way to (almost)copy a recordset.

P: n/a
I want to open up 2 record sets. Then, copy everything from one record
set to another (excluding a few fields). Is this possible?

I need to exclude my primary key, and fields starting with "TMP_".

I'm trying this, but it's saying ADODB.Recordset error '800a0c93'
Operation is not allowed in this context.

Public Function saveChanges()

Dim strSQL : strSQL = "SELECT * FROM imf WHERE imf_id = " & Form_id
Dim strSQLTMP : strSQLTMP = "SELECT * FROM imf WHERE TMP_imf_id
= " & Form_id
Dim rs : Set rs = Server.CreateObject("adodb.recordset")
Dim rsTMP : Set rsTMP = Server.CreateObject("adodb.recordset")
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
rs.CursorLocation = adUseServer
rs.open strSQL,DBHandle.DBConn
rsTMP.open strSQL,DBHandle.DBConn

Dim i, strF
rs.movefirst
For i = 0 To rs.Fields.Count -1
echo i & "- " & rs.Fields(i).Name & "<br>"
strF = rs.Fields(i).Name
If strF = "imf_id" Then
' do nothing
echo "skipping " & strF & "<br>"
ElseIf Left(strF,3) = "TMP" Then
' do nothing
echo "tmpskipping " & strF & "<br>"
Else
rs.Fields.Item(i) = rsTMP.Fields.Item(i)
End If
Next

rs.close : rsTMP.close
Response.End

End Function
Jun 9 '06 #1
Share this Question
Share on Google+
7 Replies


P: n/a
Eric wrote:
I want to open up 2 record sets. Then, copy everything from one record
set to another (excluding a few fields). Is this possible?

I need to exclude my primary key, and fields starting with "TMP_".

I'm trying this, but it's saying ADODB.Recordset error '800a0c93'
Operation is not allowed in this context.

Public Function saveChanges()

Dim strSQL : strSQL = "SELECT * FROM imf WHERE imf_id = " & Form_id
Dim strSQLTMP : strSQLTMP = "SELECT * FROM imf WHERE TMP_imf_id
= " & Form_id
Dim rs : Set rs = Server.CreateObject("adodb.recordset")
Dim rsTMP : Set rsTMP = Server.CreateObject("adodb.recordset")
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
rs.CursorLocation = adUseServer
rs.open strSQL,DBHandle.DBConn
rsTMP.open strSQL,DBHandle.DBConn

Dim i, strF
rs.movefirst
For i = 0 To rs.Fields.Count -1
echo i & "- " & rs.Fields(i).Name & "<br>"
strF = rs.Fields(i).Name
If strF = "imf_id" Then
' do nothing
echo "skipping " & strF & "<br>"
ElseIf Left(strF,3) = "TMP" Then
' do nothing
echo "tmpskipping " & strF & "<br>"
Else
rs.Fields.Item(i) = rsTMP.Fields.Item(i)
End If
Next

rs.close : rsTMP.close
Response.End

End Function


My guess is the Cursor Location but who could know when you don't tell
use the line where the error occurs.
My other guess is that you are doing this for some reason, probably to
display the values of some of the fields in HTML, but I'm not familiar
with the "echo" thing.
My other guess is that there are some here who might suggest more
efficient ways to do this if you took a few minutes to describe exactly
what result you want from what you are doing, and with what
technologies you are working.

Jun 9 '06 #2

P: n/a
Lyle Fairfield wrote:
Eric wrote:
I want to open up 2 record sets. Then, copy everything from one record
set to another (excluding a few fields). Is this possible?

I need to exclude my primary key, and fields starting with "TMP_".

I'm trying this, but it's saying ADODB.Recordset error '800a0c93'
Operation is not allowed in this context.

Public Function saveChanges()

Dim strSQL : strSQL = "SELECT * FROM imf WHERE imf_id = " & Form_id
Dim strSQLTMP : strSQLTMP = "SELECT * FROM imf WHERE TMP_imf_id
= " & Form_id
Dim rs : Set rs = Server.CreateObject("adodb.recordset")
Dim rsTMP : Set rsTMP = Server.CreateObject("adodb.recordset")
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
rs.CursorLocation = adUseServer
rs.open strSQL,DBHandle.DBConn
rsTMP.open strSQL,DBHandle.DBConn

Dim i, strF
rs.movefirst
For i = 0 To rs.Fields.Count -1
echo i & "- " & rs.Fields(i).Name & "<br>"
strF = rs.Fields(i).Name
If strF = "imf_id" Then
' do nothing
echo "skipping " & strF & "<br>"
ElseIf Left(strF,3) = "TMP" Then
' do nothing
echo "tmpskipping " & strF & "<br>"
Else
rs.Fields.Item(i) = rsTMP.Fields.Item(i)
End If
Next

rs.close : rsTMP.close
Response.End

End Function

My guess is the Cursor Location but who could know when you don't tell
use the line where the error occurs.
My other guess is that you are doing this for some reason, probably to
display the values of some of the fields in HTML, but I'm not familiar
with the "echo" thing.
My other guess is that there are some here who might suggest more
efficient ways to do this if you took a few minutes to describe exactly
what result you want from what you are doing, and with what
technologies you are working.


I have a record in the database that I am trying to update. I want to
pretty much make an exact copy of another record that is in the same
database, and table. I just want to exclude the primary key, and a few
fields that begine with "TMP_". There are over 100 fields in here, and
more will be added, so i didn't want to hardcode the field names. I
know this is not the proper way to do it. I'm just looking for speed as
this was due a long time ago. Making it work the right way is for
version 2.
Jun 9 '06 #3

P: n/a
Are you trying to copy a group of rows (minus some fields) to a second
table, or trying to copy values from one group of rows into existing rows in
a second table?

If it's the former, an append query should do the trick. Only select the
fields you want to include, then append all the rows to the other table.
Jun 9 '06 #4

P: n/a
forgot a few things.

1. funny you mentioned it. the error is on the line the rs.close line.

2. echo is just a function the runs response.write
Jun 9 '06 #5

P: n/a
Rick Wannall wrote:
Are you trying to copy a group of rows (minus some fields) to a second
table, or trying to copy values from one group of rows into existing rows in
a second table?

If it's the former, an append query should do the trick. Only select the
fields you want to include, then append all the rows to the other table.

Believe it or not, its actually the same table.

1st i make a duplicate of the original record, and something to a temp
field. i make my changes in here. the user can freely navigate
throughout the site. when there done, they close it out, all temp
records are delete 30 minutes after there last update. If they choose
to keep the changes, then i want to copy them back over the original record.
Jun 9 '06 #6

P: n/a
An append query is still the way to go on creating the new rows.

An update query will probably do fine for the udpates
Append: Select all the fields you want to carry forward, INCLUDING the
primary key. YOu need a new column in this table to hold that primary key
value, but not AS the primary key. That is your pointer back to the orignal
record. You can either use the fact that this field is not empty to
indicate that this is a copy, or you can add a column that you populate in
the append query as "I am the copy" or some such.

Now you have your new records, and you have a link between them and the
records from which they were created.

As users review the records, I assume you have some mark that they set
indicating "Keep me. Copy my data to the original record." Assuming that:

First:

Delete * From MyTable Where CopyIndicatorField = "I am the copy" and KeepMe
= false;

Then use your recordset pair if you like, or join the table to itself in an
update query to copy the data back.

Then, delete the rows FROM which you copied the data.
Jun 9 '06 #7

P: n/a
"Eric" <no****@domain.tld> wrote
Believe it or not, its actually the same table.


Believe it or not, that could be important information that should have been
provided when you asked for help, not as an afterthought.

Larry Linson
Jun 10 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.