Dumping an ADODB recordset to XML, then back to a recordset, then saving to the db
- by Mark Biek
I've created an XML file using the .Save() method of an ADODB recordset in the following manner.
dim res
dim objXML: Set objXML = Server.CreateObject("MSXML2.DOMDocument")
'This returns an ADODB recordset
set res = ExecuteReader("SELECT * from some_table)
With res
Call .Save(objXML, 1)
Call .Close()
End With
Set res = nothing
Let's assume that the XML generated above then gets saved to a file.
I'm able to read the XML back into a recordset like this:
dim res : set res = Server.CreateObject("ADODB.recordset")
res.open server.mappath("/admin/tbl_some_table.xml")
And I can loop over the records without any problem.
However what I really want to do is save all of the data in res to a table in a completely different database. We can assume that some_table already exists in this other database and has the exact same structure as the table I originally queried to make the XML.
I started by creating a new recordset and using AddNew to add all of the rows from res to the new recordset
dim outRes : set outRes = Server.CreateObject("ADODB.recordset")
dim outConn : set outConn = Server.CreateObject("ADODB.Connection")
dim testConnStr : testConnStr = "DRIVER={SQL Server};SERVER=dev-windows\sql2000;UID=myuser;PWD=mypass;DATABASE=Testing"
outConn.open testConnStr
outRes.activeconnection = outConn
outRes.cursortype = adOpenDynamic
outRes.locktype = adLockOptimistic
outRes.source = "product_accessories"
outRes.open
while not res.eof
outRes.addnew
for i=0 to res.fields.count-1
outRes(res.fields(i).name) = res(res.fields(i).name)
next
outRes.movefirst
res.movenext
wend
outRes.updatebatch
But this bombs the first time I try to assign the value from res to outRes.
Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
Can someone tell me what I'm doing wrong or suggest a better way for me to copy the data loaded from XML to a different database?
