Insert Records - XML/ADO Marshalling Over The Net By Robbe Morris

Insert Records - XML/ADO Marshalling Over The Net

By Robbe D. Morris

Printer Friendly Version

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:

' Server A ASP Page

Response.Buffer = True
Response.AddHeader "cache-control", "private"
Response.AddHeader "pragma", "no-cache"
Response.ExpiresAbsolute = #January 1, 1990 00:00:01# 
Response.Expires=0 
  
  
  Function InsertDB1()

Dim oCon  
   Dim oRec  
   Dim oStream  
   Dim sNewID  
     
   Set oCon = Server.CreateObject("ADODB.Connection")
   Set oRec = Server.CreateObject("ADODB.Recordset")
   Set oStream =Server.CreateObject("ADODB.Stream")
   
          oCon.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\mydb_a.mdb"
          oRec.CursorLocation = adUseServer
         
   Set oRec.ActiveConnection = oCon

' Table A structure in both databases
  '
  '  RecordID     AutoNumber
  '  Dec              text(50)

With oRec
           .Open "TableA", oCon, adOpenKeyset, adLockOptimistic, adCmdTableDirect
           .AddNew
           .Fields("DESC") = "record description " & Now()
           .Update
           sNewID = .Fields("RECORDID")  ' Get newly inserted id.
    End With
    
  ' Save contents of recordset to stream object

oRec.Save oStream, adPersistXML  
 
   ' Convert contents of stream object to text XML
   ' string.
  
   InsertDB1 = oStream.ReadText(adReadAll)
   
    If oStream.State <> adStateClosed Then oStream.Close
    If oRec.State <> adStateClosed Then oRec.Close
    If oCon.State <> adStatedClosed Then oCon.Close
    Set oStream = Nothing
    Set oRec = Nothing
    Set oCon = Nothing
     
 End Function

'  Server B ASP Page

Sub InsertDB2(sXML)

Dim oCon  
   Dim oRec  
   Dim oRecXML  
   Dim oXML  
   Dim sNewID  
   Dim oCol
   
   On Error GoTo ErrHandler
   
   Set oCon = Server.CreateObject("ADODB.Connection")
   Set oRec = Server.CreateObject("ADODB.Recordset")
   Set oRecXML =Server.CreateObject("ADODB.Recordset")
   Set oXML = Server.CreateObject("MSXML2.DOMDocument")
   
        ' Load XML stream to XML Document object

oXML.loadXML sXML

' Load XML Document object directly into a disconnected
        ' Recordset object

oRecXML.Open oXML

oCon.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\mydb_b.mdb"
        oRec.CursorLocation = adUseServer
  
  Set oRec.ActiveConnection = oCon
  
        oRec.Open "TableA", oCon, adOpenKeyset, adLockOptimistic, adCmdTableDirect
       
        With oRec

.AddNew

' Iterate through the disconnected recordset object creating
                 ' fields in the blank connected recordset object.  Be careful
                 ' not to create a field for the AutoNumber column which in this
                 ' case is the RecordID column.

For Each oCol In oRecXML.Fields
                       If UCase(Trim(oCol.Name)) <> "RECORDID" Then .Fields(oCol.Name) = oCol.Value
                 Next

.Update

sNewID = .Fields("RECORDID")

End With
         
     If oRecXML.State <> adStateClosed Then oRecXML.Close
     If oRec.State <> adStateClosed Then oRec.Close
     If oCon.State <> adStatedClosed Then oCon.Close
        Set oRecXML = Nothing
        Set oXML = Nothing
        Set oRec = Nothing
        Set oCon = Nothing
  
 End Sub
 
 sXML = InsertDB1()
 Call InsertDB2(sXML)


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 EggHeadCafe which provides .NET articles, book reviews, software reviews, and software download and purchase advice.