Use ASP, VB, and XML to create applications running on the Internet

Let's analyze the client/server applications first. In a standard client/server application, you can initialize the database connection string at the beginning of the application, which means that the customer has the right to use the database connection string, this includes the user name and password.

However, if you are not allowed to send such information over the network, you must directly obtain data from the client and send it to the customer without connecting to the database. One solution is to create an ASP page on the server. In this example, it is called getData. asp) receives POST data in a specific format. It requires an XML string to create an ADO object and run stored procedures or dynamic SQL statement commands.

If the information is valid, getData. asp executes the stored procedure and returns an XML-format dataset, a list of returned values, or an XML string of error page information. For the command to return data, the client either re-instantiate or return values or use an error page in xml dom (Document Object Model) format.
Let's discuss how to implement this page!
The getData. asp page first uses a DOMDocument object to save the data sent by the client:
'Create a DOMDocument object
Set xml = Server. CreateObject ("msxml2.DOMDocument ")
Xml. async = False
Then it loads POST Data
'Load POST Data
Xml. Load Request
If xml. parseError. errorCode <> 0 Then
Call responseError ("XML Information cannot be loaded. "&" Description: "& xml. parseError. reason &" <br> Line: "& xml. parseError. Line)
End If
It can return the commandtext element value and returndata or returnvalue element value. Below I will only provide the code to return the commandtext element value. For other code, please refer to the source program that is attached below.
Set N = xml. selectSingleNode ("command/commandtext ")
If N Is Nothing Then
Call responseError ("the <sp_name> parameter is missing. ")
Else sp_name = N. Text
End If
Next, let the page create a Command object, read all <param> elements, and create a parameter for each element in the request. Finally, let the page open a connection and use the Stored Procedure adExecuteNoRecords option to execute the request.
Set conn = Server. CreateObject ("ADODB. Connection ")
Conn. Mode = adModeReadWrite
Conn. open Application ("ConnectionString ")
Set cm. ActiveConnection = conn
'Return data
If not returnsData then
Cm. Execute
Else
Set R = server. CreateObject ("ADODB. Recordset ")
R. CursorLocation = adUseClient
R. Open cm, adOpenStatic, adLockReadOnly
End if
If data can be returned, the returnData variable is the true value, and the result dataset is returned to the client, which is still an XML document.
If returnsData then
R. Save Response, adPersistXML
If err. number <> 0 then
Call responseError ("Storage error occurred in dataset" & "in command '" & CommandText & "':" & Err. Description)
Response. end
End if
If the return value of the output parameter is returned, an XML string containing the values is returned. The root element of the document is a <values> flag. Each returned value has its child element. If any error occurs, the page will format and return an XML string containing the error message:
Sub responseError (sDescription)
Response. Write "<response> <data> error:" & sDescription & "</data> </response>"
Response. end
End Sub
In the preceding example, we want to display the customer name list on the left half of the area in the application, and add two links after each customer name: purchase History and Recent Purchase. When a user clicks a link, the customer program runs a stored procedure and displays the result in the right area. To show the flexibility of this idea, I want the three operation units used to return data to execute different working processes, all of which call getData. asp.

First, call CustOrderHist to run a stored procedure, return the customer's Purchase History, which searches for the databases in the Northwind database for convenience) and returns a dataset. The query statement used to return Recent Purchase runs a stored procedure called RecentPurchaseByCustomerID to receive the input CustomerID parameter and return the product name recently purchased by the customer through the ProductName parameter. The corresponding SQL statement for defining the processing process is as follows:
Create procedure RecentPurchaseByCustomerID @ CustomerID nchar (5), @ ProductName nchar (40) output as select @ ProductName = (SELECT top 1 ProductName FROM Products inner join ([Order Details] inner join Orders ON Orders. orderID = [Order Details]. orderID) ON Products. productID = [Order Details]. productID WHERE Orders. orderDate = (select max (orders. orderdate) FROM Orders
Where CustomerID = @ CustomerID) AND Orders. CustomerID = @ CustomerID) GO
Whether your query statement contains a dynamic SQL statement, a stored procedure that returns a record set, or a returned value, the method for processing POST messages is the same:
Set xhttp = createObject ("msxml2.XMLHTTP ")
Xhttp. open "POST", "http: // localhost/myWeb/getData. asp", False
Xhttp. send s
Now let's take a look at how to send and receive data.
The XML Information of the client is composed of a <command> element and some sub-elements: the <commandtext> element contains the name of the stored procedure, and the <returnsdata> element tells the server that, whether the client needs to receive returned data. The <param> element contains parameter information. If no parameter is used, the simplest query method for sending strings is as follows:
<Command>
<Commandtext>
Stored Procedure or dynamic SQL statement
</Commandtext>
<Returnsvalues> True </returnsvalues>
</Command>
You can use a <param> element for each parameter to add a parameter. Each <param> element has five sub-elements: name, type, ction, size, and value. The sequence of child elements can be changed at will, but all elements should be indispensable. I usually define them according to the order of defining the values of An ADO object. For example, the CustOrderHist stored procedure requires a CustomID parameter, so the code used to create an XML string sent to getData. asp is:
Dim s
S = "<? Xml version = "" 1.0 ""?> "& Vbcrlf
S = s & "<command> <commandtext>"
S = s & "CustOrderHist"
S = s & "</commandtext>"
S = s & "<returnsdata>" & True </returnsdata>"
S = s & "<param>"
S = s & "<name> CustomerID </name>"
S = s & "<type> <% = adVarChar %> </type>"
S = s & "<direction>" & <% = adParamInput %> </direction>"
S = s & "<size>" & len (CustomerID) & "</size>"
S = s & "<value>" & CustomerID & "</value>"
S = s & "</param>"
S = s & "</command>"
Note that the previous code is client code, and the ADO constant is not defined by the client-that is why they are surrounded by the <%> MARK. The server replaces them with the correct values before sending the response. The getData. asp page has a Response. ContentType whose attribute is "text/xml". In this way, you can use the ResponseXML attribute to return the result. When a request returns a record, you can create a Recordset object and use XMLHTTP to open it:
Dim R
Set R = createObject ("ADODB. Recordset ")
R. open xhttp. responseXML
When the query statement returns data, you can set the responseXML attribute of the XMLHTTPRequest object to create a DOMDocument:
Dim xml
Set xml = xhttp. responseXML
Each return value of the XML string of the output parameter contains an element, which is a direct sub-element of the root element <values>. For example:
<? Xml version = "" 1.0 "" encoding = "" gb2312 ""?>
<Values>
<Paramname> value </paramname>
<Paramname> value </paramname>
</Values>
If your data uses text from another country, you may need to replace the encoding property with the corresponding encoding, for example for most European languages, you can use ISO-8859-1
The client page uses the returned data to format an HTML string for display, such:
Document. all ("details"). innerHTML = <some formatted HTML strings>
We have already introduced the use of ASP and XML hybrid programming, because ASP pages can easily show us what the application is doing, but if you don't want to use ASP, you can also use any technology you are familiar with to create a client program. Below, I provide a piece of VB code. The function is the same as that on the ASP page, and the same data can be displayed. However, this VB program does not create XML Strings sent to the server. It retrieves the XML string from the server by running a stored procedure named Initialize to query the contents of the ClientCommands table.
The ClientCommands table contains two fields: command_name and command_xml. The client program requires three specific command_name fields: getCustomerList, CustOrderHist, and RecentPurchaseByCustomerID. The command_xml field of each command includes the code sent by the program to getData. the XML string of the asp page, so that you can control the XML string in a centralized manner, just as the name of the stored procedure represents, when sending the XML string to getData. before asp, the client uses xml dom to set the parameter values of stored procedures. The Code provided by me contains the SQL statements used to define the Initialize process and to create a ClientCommands table.
The provided routine also explains how to use the XHTTPRequest object to implement the promise I made at the beginning of this article: Any application on a remote machine can access getData. asp; of course, you can also restrict access to ASP pages by setting IIS and NTFS permissions; you can store global application settings on the server rather than on the client; you can avoid potential risks caused by sending database usernames and passwords over the network. Also, in IE, applications can only display the required data without refreshing the entire page.
In the actual programming process, you should use some methods to make the application more efficient. You can move the ASP code to a COM application and create an XSLT transform to display the returned data. Okay, I'm not talking about it anymore. Now all you have to do is give it a try!
Option Explicit
Private RCommands As Recordset
Private RCustomers As Recordset
Private RCust As Recordset
Private sCustListCommand As String
Private Const dataURL = "http: // localhost/XHTTPRequest/getData. asp"
Private arrCustomerIDs () As String
Private Enum ActionEnum
VIEW_HISTORY = 0
VIEW_RECENT_PRODUCT = 1
End Enum
Private Sub dgCustomers_Click ()
Dim CustomerID As String
CustomerID = RCustomers ("CustomerID"). Value
If CustomerID <> "" Then
If optAction (VIEW_HISTORY). Value Then
Call getCustomerDetail (CustomerID)
Else
Call getRecentProduct (CustomerID)
End If
End If
End Sub
Private Sub Form_Load ()
Call initialize
Call getCustomerList
End Sub
Sub initialize ()
'COMMAND name and corresponding value returned from the database
Dim sXML As String
Dim vRet As Variant
Dim F As Field
SXML = "<? Xml version = "" 1.0 ""?> "
SXML = sXML & "<command> <commandtext> Initialize </commandtext>"
SXML = sXML & "<returnsdata> True </returnsdata>"
SXML = sXML & "</command>"
Set RCommands = getRecordset (sXML)
Do While Not RCommands. EOF
For Each F In RCommands. Fields
Debug. Print F. Name & "=" & F. Value
Next
RCommands. MoveNext
Loop
End Sub
Function getCommandXML (command_name As String) As String
RCommands. MoveFirst
RCommands. Find "command_name = '" & command_name & "'", adSearchForward, 1
If RCommands. EOF Then
MsgBox "Cannot find any command associated with the name '" & command_name &"'."
Exit Function
Else
GetCommandXML = RCommands ("command_xml ")
End If
End Function
Sub getRecentProduct (CustomerID As String)
Dim sXML As String
Dim xml As DOMDocument
Dim N As IXMLDOMNode
Dim productName As String
SXML = getCommandXML ("RecentPurchaseByCustomerID ")
Set xml = New DOMDocument
Xml. loadXML sXML
Set N = xml. selectSingleNode ("command/param [name = 'mermerid']/value ")
N. Text = CustomerID
Set xml = executeSPWithReturn (xml. xml)
ProductName = xml. selectSingleNode ("values/ProductName"). Text
'Display text fields
TxtResult. Text = ""
Me.txt Result. Visible = True
DgResult. Visible = False
'Display product Name
TxtResult. Text = "the latest product is:" & productName
End Sub
Sub getCustomerList ()
Dim sXML As String
Dim I As Integer
Dim s As String
SXML = getCommandXML ("getCustomerList ")
Set RCustomers = getRecordset (sXML)
Set dgCustomers. DataSource = RCustomers
End Sub
Sub getCustomerDetail (CustomerID As String)
'Locate the associated ID in the list
Dim sXML As String
Dim R As Recordset
Dim F As Field
Dim s As String
Dim N As IXMLDOMNode
Dim xml As DOMDocument
SXML = getCommandXML ("CustOrderHist ")
Set xml = New DOMDocument
Xml. loadXML sXML
Set N = xml. selectSingleNode ("command/param [name = 'mermerid']/value ")
N. Text = CustomerID
Set R = getRecordset (xml. xml)
'Hide text because it is a record set
TxtResult. Visible = False
DgResult. Visible = True
Set dgResult. DataSource = R
End Sub
Function getRecordset (sXML As String) As Recordset
Dim R As Recordset
Dim xml As DOMDocument
Set xml = getData (sXML)
Debug. Print TypeName (xml)
On Error Resume Next
Set R = New Recordset
R. Open xml
If Err. Number <> 0 Then
MsgBox Err. Description
Exit Function
Else
Set getRecordset = R
End If
End Function
Function executeSPWithReturn (sXML As String) As DOMDocument
Dim d As New Dictionary
Dim xml As DOMDocument
Dim nodes As IXMLDOMNodeList
Dim N As IXMLDOMNode
Set xml = getData (sXML)
If xml.doc umentElement. nodeName = "values" Then
Set executeSPWithReturn = xml
Else
'Error occurred
　
Set N = xml. selectSingleNode ("response/data ")
If Not N Is Nothing Then
MsgBox N. Text
Exit Function
Else
MsgBox xml. xml
Exit Function
End If
End If
End Function
Function getData (sXML As String) As DOMDocument
Dim xhttp As New XMLHTTP30
Xhttp. Open "POST", dataURL, False
Xhttp. send sXML
Debug. Print xhttp. responseText
Set getData = xhttp. responseXML
End Function
Private Sub optAction_Click (Index As Integer)
Call dgCustomers_Click
End Sub
Code 2: getData. asp
<% @ Language = VBScript %>
<% Option explicit %>
<%
Sub responseError (sDescription)
Response. Write "<response> <data> Error:" & sDescription & "</data> </response>"
Response. end
End Sub
Response. ContentType = "text/xml"
Dim xml
Dim commandText
Dim returnsData
Dim returnsValues
Dim recordsAffected
Dim param
Dim paramName
Dim paramType
Dim paramDirection
Dim paramSize
Dim paramValue
Dim N
Dim nodeName
Dim nodes
Dim conn
Dim sXML
Dim R
Dim cm
'Create a DOMDocument object
Set xml = Server. CreateObject ("msxml2.DOMDocument ")
Xml. async = False
'Load POST Data
Xml. Load Request
If xml. parseError. errorCode <> 0 Then
Call responseError ("XML Information cannot be loaded. Description: "& xml. parseError. reason &" <br> Number of rows: "& xml. parseError. Line)
End If
'The client must send a commandText Element
Set N = xml. selectSingleNode ("command/commandtext ")
If N Is Nothing Then
Call responseError ("Missing <commandText> parameter .")
Else
CommandText = N. Text
End If
'The client must send a returnsdata or returnsvalue Element
Set N = xml. selectSingleNode ("command/returnsdata ")
If N is nothing then
Set N = xml. selectSingleNode ("command/returnsvalues ")
If N is nothing then
Call responseError ("Missing <returnsdata> or <returnsValues> parameter .")
Else
ReturnsValues = (lcase (N. Text) = "true ")
End if
Else
ReturnsData = (lcase (N. Text) = "true ")
End if
Set cm = server. CreateObject ("ADODB. Command ")
Cm. CommandText = commandText
If instr (1, commandText, "", vbBinaryCompare)> 0 then
Cm. CommandType = ad1_text
Else
Cm. CommandType = adw.storedproc
End if
'Create Parameters
Set nodes = xml. selectNodes ("command/param ")
If nodes is nothing then
'If no parameter exists
Elseif nodes. length = 0 then
'If no parameter exists
Else
For each param in nodes
'Response. Write server. HTMLEncode (param. xml) & "<br>"
On error resume next
ParamName = param. selectSingleNode ("name"). text
If err. number <> 0 then
Call responseError ("create parameter: Name tags cannot be found. ")
End if
ParamType = param. selectSingleNode ("type"). text
ParamDirection = param. selectSingleNode ("direction"). text
ParamSize = param. selectSingleNode ("size"). text
ParamValue = param. selectSingleNode ("value"). text
If err. number <> 0 then
Call responseError ("the parameter name is '" & paramName & "' and the required field is missing ")
End if
Cm. Parameters. Append cm. CreateParameter (paramName, paramType, paramDirection, paramSize, paramValue)
If err. number <> 0 then
Call responseError ("You cannot create or add a parameter named '" & paramName & ".'" & err. description)
Response. end
End if
Next
On error goto 0
End if
'Open link
Set conn = Server. CreateObject ("ADODB. Connection ")
Conn. Mode = adModeReadWrite
Conn. open Application ("ConnectionString ")
If err. number <> 0 then
Call responseError ("Link error:" & Err. Description)
Response. end
End if
'Link Command object
Set cm. ActiveConnection = conn
'Execute the command
If returnsData then
'Open A Recordset with the command
Set R = server. CreateObject ("ADODB. Recordset ")
R. CursorLocation = adUseClient
R. Open cm, adOpenStatic, adLockReadOnly
Else
Cm. Execute recordsAffected, adExecuteNoRecords
End if
If err. number <> 0 then
Call responseError ("command execution error '" & Commandtext & "':" & Err. Description)
Response. end
End if
If returnsData then
R. Save Response, adPersistXML
If err. number <> 0 then
Call responseError ("A storage error occurs in the dataset. In the command '" & CommandText & "':" & Err. Description)
Response. end
End if
Elseif returnsValues then
SXML = "<? Xml version = "" 1.0 "" encoding = "" gb2312 ""?> "& Vbcrlf &" <values>"
Set nodes = xml. selectNodes ("command/param [direction = '2']")
For each N in nodes
NodeName = N. selectSingleNode ("name"). text
SXML = sXML & "<" & nodename & ">" & cm. Parameters (nodename). Value & "&" </"& nodename &">"
Next
SXML = sXML & "</values>"
Response. Write sXML
End if
Set cm = nothing
Conn. Close
Set R = nothing
Set conn = nothing
Response. end
%>