Search
Monday, November 24, 2014 ..:: Technology » Connection Strings » ADO Recordset URL ::.. Register  Login
Site Navigation

 ADO Recordset URL Minimize

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, adPersistXML
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
%>

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
%>


  

Copyright 2005 CarlProthman.NET   Terms Of Use  Privacy Statement