Insert Records - XML/ADO Marshalling Over The Net

By Robbe D. Morris

Printer Friendly Version

Robbe Morris
Robbe & Melisa Morris


Like many of our articles, this one was spawned from a forum question.  The basic question was this: "If I insert a record into an Access database on server A how can I marshall the ADO recordset and insert a duplicate record in another database on server B?".  Of course, most of us are aware of how to use the XMLHttp object and ADO to stream recordsets across the internet.  If you don't, that's ok.  Just read a past article entitled Web Services - Server To Server With XMLHttp and you'll learn all you need to know.  From this point forward, I'll assume you are familiar with the process and won't discuss it here.
In the code example below, I've combined both functions into one ASP file for ease of reading.  In a real world example, the sub InsertDB1() would reside on the web server supporting the database on server A.  The sub InsertDB2() would reside on the web server supporting the database on server B.  Of course, both would have your own functions for using XMLHttp to send and retrieve the data streams.
 


 
One of the major challenges I found was ADO's unwillingness to loadup an XML document with a connection open to a database.  To get around this in InsertDB2(), I opted to load the XML stream into a second disconnected ADO recordset.  Then, iterate through the recordset to build the standard .Fields parameters for the connected ADO recordset object all the while making sure I didn't create a parameter for the column in the table that holds the autonumber.  Upon completion, the .Update method is called and the new record is created in the second database.  Here's the code:


I'm guessing you'll want to make adjustments to the code above for reusability.  I wouldn't recommend using this type of methodology to replace large scale data replication available in enterprise level relational databases.  However, if the need ever arises where you would want to manipulate data in remote databases, you now know how to use the ADO recordset object and XML to complete your task.

Robbe has been a Microsoft MVP in C# since 2004.  He is also the co-founder of NullSkull.com which provides .NET articles, book reviews, software reviews, and software download and purchase advice.