|
ADO 2.5 (or greater) allows you to open up an ADO Recordset based on XML returned from an ASP file over HTTP. This feature does NOT use RDS at all.
To get records from a remote database oRs.Open "http://myServer/AuthorsGetByState.asp?state=CA",, _
adOpenStatic, adLockBatchOptimistic
Where "AuthorsGetByState.asp" is shown below.
To save changes ' Save Recordset into Stream
Set oStm = New ADODB.Stream
oRs.Save oStm, adPersistXML
' Use MSXML's XMLHTTP object to open ASP
Set oXMLHTTP = New MSXML2.XMLHTTP30
oXMLHTTP.Open "POST", "http://myServerName/AuthorsSave.asp"
oXMLHTTP.Send oStm.ReadText
' If an error occurred
If oXMLHTTP.Status = 500 Then
Debug.Print oXMLHTTP.statusText
End If
Where "AuthorsSave.asp" is shown below.
For more information, see: ADO Recordset's Open Method
AuthorsGetByState.asp
<%@Language=VBScript%>
<%Option Explicit%>
<%
Dim oConn
Dim oRs
Dim sSQL
Dim sState
On Error Resume Next
Const adPersistXML = 1
Const adStateOpen = 1
Const adOpenStatic = 3
Const adLockBatchOptimistic = 4
Const adCmdText = 1
' Create and open a new Connection
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open "provider=sqloledb;" & _
"data source=(local);" & _
"initial catalog=pubs;" & _
"user id=myUsername;" & _
"password=myPassword;"
If Err.Number <> 0 Then
Response.Status = "500 " & Err.Source & ": " & Err.Description
Response.End
End If
' Create and open an updatable recordset
Set oRs = Server.CreateObject("ADODB.Recordset")
sState = Request.QueryString("state") & ""
If Len(sState) Then
sSQL = "Select * From Authors Where State = '" & sState & "'"
Else
sSQL = "Select * From Authors"
End If
oRs.Open sSQL, oConn, adOpenStatic, adLockBatchOptimistic, adCmdText
If Err.Number <> 0 Then
Response.Status = "500 " & Err.Source & ": " & Err.Description
Response.End
End If
' ' Save data into IIS 4.0’s response object
' Set oStream = Server.CreateObject("ADODB.Stream")
' oRs.Save oStream, adPersistXML
' Response.Write oStream.ReadText
' Save data into IIS 5.0's response object
Response.ContentType = "text/xml"
oRs.Save Response, adPersistXMLIf Err.Number <> 0 Then
Response.Status = "500 " & Err.Source & ": " & Err.Description
Response.End
End If
' Clean Up
Set oRs = Nothing
If oConn.State = adStateOpen Then
oConn.Close
End If
Set oConn = Nothing
%>
AuthorsSave.asp <%@Language=VBScript%>
<%Option Explicit%>
<%
Dim oConn
Dim oRs
On Error Resume Next
' Create and open a new Connection
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open "provider=sqloledb;" & _
"data source=(local);" & _
"initial catalog=pubs;" & _
"user id=myUsername;" & _
"password=myPassword;"
If Err.Number <> 0 Then
Response.Status = "500 " & Err.Source & ": " & Err.Description
Response.End
End If
' Create and open a new recordset
Set oRs = Server.CreateObject("ADODB.Recordset")
oRs.Open Request
If Err.Number <> 0 Then
Response.Status = "500 " & Err.Source & ": " & Err.Description
Response.End
End If
' Update the database
oRs.ActiveConnection = oConn
oRs.UpdateBatch
If Err.Number <> 0 Then
Response.Status = "500 " & Err.Source & ": " & Err.Description
Response.End
End If
' Clean Up
Set oRs = Nothing
If oConn.State = adStateOpen Then
oConn.Close
End If
Set oConn = Nothing
%> |