Way O/T: ASP/SQL problem
Discussion
I posted this on the Dreamweaver developers forum, but got no reply so far, so I'm throwing it over to all the IT bods here
BTW: Using ASP3/VBScript/Access DB on IIS
I need to be able to import (ie copy) a record from one Access database into another in my web app. Table and data structure is identical in both DBs. The DB I want to write to uses a DSN connection, the DB I'm reading from uses a DSN-less connection built from form data. Both connections work fine.
What's the best way to go about this short of reading all the data in from one DB and then writing it all back out to the other DB the long winded way (there are over 400 fields so I don't want to have to do that!). I've been looking into Record.CopyRecord but can't figure it out and also using a sub-query in the Insert statement, but again I can't figure this out either
Please help before I go insane...
BTW: Using ASP3/VBScript/Access DB on IIS
I need to be able to import (ie copy) a record from one Access database into another in my web app. Table and data structure is identical in both DBs. The DB I want to write to uses a DSN connection, the DB I'm reading from uses a DSN-less connection built from form data. Both connections work fine.
What's the best way to go about this short of reading all the data in from one DB and then writing it all back out to the other DB the long winded way (there are over 400 fields so I don't want to have to do that!). I've been looking into Record.CopyRecord but can't figure it out and also using a sub-query in the Insert statement, but again I can't figure this out either
Please help before I go insane...
maybe you could try something like this (not too sure about the exact syntax)...
while not rs.eof
'build the insert into statement
sql = "INSERT INTO tbl ("
for i = 0 to rs.fields.count
if i < rs.fields.count then
sql = sql & rs(i).Name & ","
else
sql = sql & rs(i).Name & ")"
end if
next
sql = sql & " VALUES ("
for i = 0 to rs.fields.count
if i < rs.fields.count then
sql = sql & rs(i) & ","
else
sql = sql & rs(i) & ")"
end if
next
conn2.execute(sql)
rs.movenext
wend
while not rs.eof
'build the insert into statement
sql = "INSERT INTO tbl ("
for i = 0 to rs.fields.count
if i < rs.fields.count then
sql = sql & rs(i).Name & ","
else
sql = sql & rs(i).Name & ")"
end if
next
sql = sql & " VALUES ("
for i = 0 to rs.fields.count
if i < rs.fields.count then
sql = sql & rs(i) & ","
else
sql = sql & rs(i) & ")"
end if
next
conn2.execute(sql)
rs.movenext
wend
Interesting question.
The only solution I can think of at the moment is to enumerate through the results from a query. That allows you to get the field name and value from each column. Then you could build an insert query from that. There must be an easier way though...
--Edited because Tim's beaten me to it...
The only solution I can think of at the moment is to enumerate through the results from a query. That allows you to get the field name and value from each column. Then you could build an insert query from that. There must be an easier way though...
--Edited because Tim's beaten me to it...
Gassing Station | General Gassing [Archive] | Top of Page | What's New | My Stuff


