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

xml| Program | internet

The era of using stand-alone applications on PCs is about to pass, and most applications now develop web versions or most need to share rich data resources on the Web. Although we have written for a long time based on client/server applications, most of these programs only run in small LANs. However, there are many objective reasons that require us to modify these programs so that they can run on an intranet or even the Internet.

What is the reason that compels us to do it? First of all, as the size of an enterprise gradually expands, the company may cross regional or even multinational operations, the number of employees in each branch will increase every year, these staff in the field certainly need to visit the company's database resources frequently; second, centralize the application's data resources, Enables you to better monitor the access and use of your database. Third, you can maintain and update the global application settings by getting them from a centralized location, ultimately reducing application updates. Four, try to access the database from the Web server rather than accessing the database from the client, so that you can avoid the security risks by sending the login information and the client password over the network, and by using the browser to get the data from the background, you can avoid refreshing the entire page.

This requires us to create an application running on the Internet, and if we want to create a VB program that runs on the HTTP protocol, the key is to use XML and XMLHttpRequest objects. This object is Microsoft Part of the XML parser (Msxml.dll), XMLHttpRequest object allows you to send a GET and POST request to a remote server via HTTP, and a program running on a remote server receives the request, translates its contents, returns the data or an error page to the application that invoked it. Some of the research on network programming friends will see that this idea is very much like soap, but I don't want to use soap here, because if that makes the program very complicated.

It is not realistic to want to change a fully independent client-side program, but even so, downloading application settings from a centralized server is more independent and flexible than using a local INI file or a Windows registration label. For example, suppose you have a mobile phone sales force, they need to access centralized information to more efficient sales of mobile phones, each day, the head office collection of data, and then sent to the sales staff in the form of e-mail. However, market pressures and rapidly changing forms of sales are bound to enable salespeople to access the latest data information. However, network administrators insist that salespeople at remote clients are denied access to the corporate database server because they do not want to send their username and logon password over a public network. Therefore, it is bound to use a new technology to replace the client/server based technology, do not worry, I want to read this article you will solve the problem.

Let's analyze the client/server application 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, which includes the username and password. But if you do not allow this information to be sent on the network, you must send the data directly from the client to the customer without joining the database. One solution, then, is to create an ASP page on the server (called getdata.asp in this case) to receive post data in a specific format that requires an XML string to be used to create an ADO object and run a stored procedure or dynamic SQL Statement command. If the information is valid, getdata.asp executes the stored procedure and returns an XML-formatted dataset, a return value list, or an XML string of error page information. For commands that return data, the client either instantiates or returns a value or uses an error page formatted with the XML DOM (Document Object model).

OK, let's discuss how to implement this page.

The GetData.asp page first uses a DOMDocument object to hold the data sent by the client:

' Create a DOMDocument object
Set XML = Server.CreateObject ("MSXML2.") DOMDocument ")
Xml.async = False

It then loads the post data

' Load post data
Xml. Load Request
If xml.parseError.errorCode <> 0 Then
Call Responseerror ("Cannot load XML information.") "&" Description: "& Xml.parseError.reason &" <br> Line: "& Xml.parseError.Line)
End If

It can return CommandText element values and Returndata or returnvalue element values. I'll just give you the code to return the value of the CommandText element, and the rest of the code refer to the source program that I attached below.

Set N = Xml.selectsinglenode ("Command/commandtext")
If N is nothing Then
Call Responseerror ("missing <sp_name> parameter.) ")
Else Sp_name = N.text
End If

Next, you should have the page create a command object, read all the <param> elements, and create a parameter for each element in the request. Finally, let the page open a connection 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 the data can be returned, then the Returndata variable is true 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 ("DataSet has stored Error" & "in command" & CommandText & "':" & Err.Description)
Response.End
End If

If the output parameter returns a value, the page returns an XML string containing the values. The document's root element is a <values> tag, each return value has its corresponding child element, and if any errors occur, the page formats and returns an XML string containing the error message:

Sub Responseerror (sdescription)
Response.Write "<response> <data> Error:" & sdescription & "</data> </response>"
Response.End
End Sub

Let's say in the example we mentioned earlier, we wanted to display the list of customers ' names in the left half of the display area in the application, and then add two links to each customer's name: Purchase history and recent Purchase. When the user clicks on one of the links, the client runs a stored procedure and displays the results in the right area. To show the flexibility of this idea, I want to have the three units of action that are used to return data perform different work processes, all of which call getdata.asp. First, run a stored procedure by calling CustOrderHist to return to the customer's purchase History, which searches the Northwind database (for convenience I use the database that comes with MS SQL) and returns a DataSet. The query that returns recent Purchase runs a stored procedure called Recentpurchasebycustomerid that receives the input CustomerID parameters and returns the name of the most recent customer purchase through the ProductName parameter. The corresponding SQL statement defining its processing is as follows:

CREATE PROCEDURE recentpurchasebycustomerid @CustomerID nchar (5), @ProductName nchar () output as SELECT @ProductName = ( SELECT top 1 ProductName from the 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 contains a dynamic SQL statement or a stored procedure that returns a recordset or outputs a return value, the method for handling post messages is the same:

Set xhttp = CreateObject ("MSXML2. XMLHTTP ")
Xhttp.open "POST", "http://localhost/myWeb/getData.asp", False
Xhttp.send s

Okay, now let's take a look at how to send and receive data

The XML information for the client is comprised of a <command> element and some child elements: <commandtext> element contains the name of the stored procedure, and the <returnsdata> element tells the server whether the client requires the return data to be received, and the <param> element contains parameter information. If you don't use parameters, the simplest way to send a string query is as follows:

<command>
<commandtext>

Stored procedures or dynamic SQL statements

</commandtext>
<returnsvalues> True </returnsvalues>
</command>

You can add parameters by using an <param> element for each parameter. Each <param> element has five child elements: Name,type,direction,size and value. The order of the child elements can be exchanged at will, but all elements should be indispensable, and I usually define them in the order in which they define the values of an ADO object. For example, the CustOrderHist stored procedure requires a customid parameter, so the code used to create the 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-side, and the ADO constants are not defined by the client-that's why they surround themselves with <%%> tags. The server replaces them with the correct values before sending the response. The GetData.asp page has a Response.ContentType whose property is "Text/xml" so that you can use the Responsexml property to return the result. When the 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, create a DOMDocument by setting the Responsexml property of the XMLHttpRequest object:

Dim XML
Set xml = Xhttp.responsexml

Each return value of an output parameter's XML string contains an element, which is a direct child 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 is in another country's text, you may want to replace the encoding attribute with the corresponding encoding, for example, for most European languages, you can use the Iso-8859-1

The client page uses the returned data to format an HTML string for display, such as:

document.all ("Details"). InnerHTML = "Some formatted HTML string"

We've already covered using ASP and XML for mixed programming, because ASP pages make it easy for us to see what the application is doing, but if you don't want to use ASP, you can use any technology you know to create a client program. Below, I provide a section of VB code, its function and ASP page, can also display the same data, but this VB program will not create the XML string sent to the server. It queries the contents of the Clientcommands table by running a stored procedure called Initialize and retrieving the XML string from the server.

The Clientcommands table includes two domains: Command_name domain and Command_xml domain. The client program requires three specific command_name domains: Getcustomerlist,custorderhist and Recentpurchasebycustomerid. The command_ of every command An XML field includes an XML string that a program sends to a getdata.asp page, so that you can centralize control over an XML string, just as the stored procedure name means, before sending an XML string to GetData.asp, the client program uses the XML Dom to set the parameter values of the stored procedure. The code that I provide contains the SQL statements that define the initialize process and the Clientcommands table to create.

The routines I provide also explain how to use the Xhttprequest object to implement the commitments I made at the outset of this article: GetData.asp can be accessed by applications on any remote machine; Of course, you can also restrict access to ASP pages by setting IIS and NTFS permissions, and you can store global application settings on the server rather than on the client computer, and you can avoid the pitfalls of sending database username and password over the network. Also, in IE, an application can display only the data that is needed without refreshing the entire page.

In the actual programming process, you should use a number of methods to make your application more efficient. You can move the code in the ASP to a COM application and then create an XSLT transformation to display the returned data. Well, I don't say much, now all you have to do is try it!

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 ()
' Returns the command name and corresponding value 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 as 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 no 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= ' CustomerID ']/value")
N.text = CustomerID
Set XML = Executespwithreturn (Xml.xml)
ProductName = Xml.selectsinglenode ("Values/productname"). Text
' Show Text field
Txtresult.text = ""
Me.txtResult.Visible = True
Dgresult.visible = False
' Show product name
Txtresult.text = "The most recent 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)
' Find the ID number associated with 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= ' CustomerID ']/value")
N.text = CustomerID
Set R = GetRecordSet (xml.xml)
' Hide text because it's a recordset
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.documentElement.nodeName = "Values" Then
Set Executespwithreturn = xml
Else
' An error occurred
　
Set N = Xml.selectsinglenode ("Response/data")
If not N are 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 two, 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 ("Cannot load XML information.") Description: "& Xml.parseError.reason &" <br> Line number: "& 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=adcmdtext
Else
Cm.commandtype = adCmdStoredProc
End If

' Create parameters
Set nodes = Xml.selectnodes ("Command/param")
If nodes is Nothing Then

' If there are no parameters
ElseIf nodes.length = 0 Then
' If there are no parameters
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: Cannot find name label.") ")
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 (Parameters with parameter name ' & ParamName & ' are missing necessary fields ")
End If
Cm. Parameters.Append cm. CreateParameter (Paramname,paramtype,paramdirection,paramsize,paramvalue)
If Err.Number <> 0 Then
Call Responseerror ("Cannot create or add a parameter named ' & ParamName &". " "& Err.Description)
Response.End
End If
Next
On Error Goto 0
End If

' Open the 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 command
If Returnsdata Then
' Open a recordset with a 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 ("Execute command Error" & CommandText & "':" & Err.Description)
Response.End
End If

If Returnsdata Then
R.save Response, adPersistXML
If Err.Number <> 0 Then
Call Responseerror ("DataSet has stored error, at 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 all 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
%>