The heart of your in-process ActiveX component is the Person class. This class module contains a method called Register, which is called by oleisapi.dll. The Register method takes two string arguments, strRequest and strReturn. The strRequest argument contains the data passed by the ACTION attribute of the form, and the strReturn argument contains the return HTML code to be displayed by the browser.
Start a new Visual Basic project. Remove the default form by selecting Remove File from the File menu. Since this component will be running on the server, any forms created in Visual Basic will be displayed only on the server, not on the client computer. For this project, creating a user interface in Visual Basic is not necessary.
Add a new class module to your project by selecting Class Module from the Insert menu. When the class module is visible, press F4 to display its properties sheet. Change the properties of the class module as follows:
| Instancing | 2 - Creatable, MultiUse |
| Name | Person |
| Public | True |
Setting the Public property to True allows OLEISAPI to access the functions in this module. Changing the Instancing property to Creatable, MultiUse allows an object from this class to be created, if one is not already running. The Name property is the name used in the ACTION attribute of your calling form. Figure 7-7 shows the properties sheet for the Person class.
Figure 7-7.
The properties sheet for the Person class.
When the form is submitted, OLEISAPI creates an object from the Person class and then calls the Register method. The Register method receives the information from the form and takes action to make entries in the database. After all the actions have been taken, the program dynamically creates an HTML page and returns it to the browser. To start the Register method, type the following into the code window for the Person class:
Option Explicit
Public Sub Register(strRequest As String, strReturn As String) The Register method accepts two string arguments, and both are passed by reference. By reference is the default argument-passing convention in Visual Basic. When you pass an argument by reference, you pass the address of the string argument, as opposed to a copy of the actual data. This is important in our program because OLEISAPI expects your routine to return HTML code by changing the text located at the address specified by strReturn.
The Register routine is responsible for calling additional functionality contained in your class module. The Register method will call the routine to parse the incoming string, call the routine to update the information in the database, call the routine to create the return HTML page, and call an error handler if a runtime error occurs. You will create these routines in later steps. We begin the Register method by enabling the error-handling routine and declaring a dynamic array that will hold the parsed incoming string:
On Error GoTo RegisterErr ReDim arrArgVal(0) As String The first thing your program will need to do is parse the incoming string. Parsing is handled by a routine called sParseRequest. The sParseRequest routine takes the incoming string (which has a format of Field=Value), breaks it up into parts, and stores the parts in an array called arrArgVal(). Add the following code to call the parsing routine:
Call sParseRequest(strRequest, arrArgVal()) Next the parsed information in the array will be added to the datasource. This is handled in a routine called sUpdateData. Add the following code to call the database update routine:
Call sUpdateData(arrArgVal()) Next a routine is called to create the HTML code that will be returned to the browser. This routine, fCreateHTML, creates an HTML confirmation screen that is returned as a string. The string is assigned to strReturn. Remember that strReturn is passed by reference, which means that when it is modified, this change is reflected wherever it is referenced. Therefore, assigning the created HTML string to strReturn is all that is required to have the confirmation screen returned and eventually displayed in the browser. Add the following code to call the routine for creating the HTML string and assigning it to strReturn:
strReturn = fCreateHTML(arrArgVal()) All code should have an error trap to handle runtime errors. Later you will build a central error-handling routine that can generate HTML pages to display errors . Add the following code (next page)to call the error-handling routine:
RegisterExit:
Exit Sub
RegisterErr:
strReturn = fCreateHTMLError(Err.Number, Err.Description)
Resume RegisterExit
End Sub Now you will create the routines that the Register method calls. The first thing the program must do is parse the incoming data. The data is sent from the client in the argument strRequest, which is a string that contains all of the input data from the submitted form. The data has the following format:
field1=value1&field2= value2…&fieldn=valuen Spaces in the values are replaced with plus signs, and nonalphanumeric characters are replaced with hexadecimal representations of the data. For example, if you pass a phone number as (800) 555 1212, it will appear in your request string as
%28800%29+555+1212 As you can see, the percent sign indicates that a hexadecimal value follows. The spaces are replaced by plus signs. This format is consistent for all data that is passed and allows you to build an algorithm to decode it. This step shows you how this is accomplished.
Now you will create a parsing routine that will break the string down and populate an array with the fields and values. This array, arrArgVal(), is an array of strings. The parsing routine creates an array that alternates between fields and values. This way, you can access all of the data in the array by using For…Next loops. Add the following code to the Person class to start the parsing routine:
Private Sub sParseRequest(strRequest As String, _
arrArgVal() As String)
On Error GoTo ParseRequestErr
Dim intIndex As Integer
Dim intPosition As Integer
Dim intStart As Integer
Dim strParse As String
Dim strTemp As String
Dim intTest As Integer
intIndex = 0
intStart = 1 To capture all of the data sent to the back-end server, your code must loop through all of the characters in the request string. You can identify each field by looking for the equals sign. Add the following code to your routine to search for equals signs and find the field entries:
Do While InStr(intStart, strRequest, "=", 1) > 0
intIndex = intIndex + 1
ReDim Preserve arrArgVal(intIndex + 1)
intPosition = InStr(intStart, strRequest, "=", 1)
arrArgVal(intIndex) = Trim$(Mid$(strRequest, intStart, _
intPosition - intStart))
intIndex = intIndex + 1
intStart = intPosition + 1
intPosition = InStr(intStart, strRequest, "&", 1) Once you have identified the field, you can find the associated value by searching for the ampersand symbol (&). This symbol separates field and value pairs. Once a field and a value are known, they can easily be stored in an array. Add the following code to finish the parsing process:
If intPosition > 0 Then
arrArgVal(intIndex) = Trim$(Mid$(strRequest, _
intStart, intPosition - intStart))
intStart = intPosition + 1
Else
arrArgVal(intIndex) = Trim$(Right$(strRequest, _
Len(strRequest) - intStart + 1))
intStart = Len(strRequest)
End If
Loop When the entries have been parsed, they might still contain hexadecimal characters that are used to represent nonalphanumeric symbols. These hexadecimal values are always preceded by percent signs. They can be converted back to ASCII format with the Val function. Add the code at the top of page 276 to convert the hexadecimal characters back to ASCII format and change plus signs back to spaces.
For intIndex = 1 To UBound(arrArgVal)
strTemp = ""
intPosition = 1
Do
Select Case Mid$(arrArgVal(intIndex), intPosition, 1)
Case "+"
strTemp = strTemp & " "
intPosition = intPosition + 1
Case "%"
strTemp = strTemp & Chr$(Val("&H" & _
Mid$(arrArgVal(intIndex), intPosition + 1, 2)))
intPosition = intPosition + 3
Case Else
intTest = Asc(Mid$(arrArgVal(intIndex), _
intPosition, 1))
If Not (intTest < 32 Or (intTest > 126 And _
intTest < 145) Or (intTest > 146 And _
intTest < 160)) Then
strTemp = strTemp & _
Mid$(arrArgVal(intIndex), intPosition, 1)
End If
intPosition = intPosition + 1
End Select
Loop While intPosition <= Len(arrArgVal(intIndex))
arrArgVal(intIndex) = strTemp
Next
To complete the sParseRequest routine, add the following code to perform error handling:
ParseRequestExit:
Exit Sub
ParseRequestErr:
ReDim arrArgValue(4)
arrArgValue(1) = "Error"
arrArgValue(2) = Err.Description
arrArgValue(3) = "Number"
arrArgValue(4) = Format$(Err.Number)
Resume ParseRequestExit
End Sub
After the fields and values have been parsed and stored in an array, your application must update the database. The database is updated with Remote Data Object (RDO) technology. RDO is a feature of Visual Basic 4.0, Enterprise Edition, that is specifically designed to interact with ODBC datasources. RDO itself is nothing more than a component that wraps the ODBC API. This API provides direct access to ODBC functionality. RDO itself adds little overhead to the ODBC functionality, so the update performance is excellent.
Before you use RDO, you must make sure that you have set a reference to it. Select References from the Tools menu, and set a reference to the Remote Data Object. Figure 7-8 shows the References dialog box with the Microsoft Remote Data Object 1.0 selected.
Figure 7-8.
The References dialog box with RDO selected.
Now you can add the following code to your project, to begin the database update routine:
Private Sub sUpdateData(arrArgVal() As String)
On Error GoTo UpdateDataErr
Dim strSQL As String
Dim i As Integer
Dim dbRegistrations As RDO.rdoConnection You open a connection to the datasource with the OpenConnection method of the rdoConnection object. This method opens the datasource for use and takes as an argument the name of the datasource to be opened. In this case, you will open the datasource you created earlier in this project by adding the following code:
Set dbRegistrations = rdoEngine.rdoEnvironments(0). _
OpenConnection("eventreg") To add the data to the database, you must create a SQL INSERT statement. The INSERT statement will add the fields and values you specify. In this case, you will use the field names as they were passed in from the browser. The HTML code you built previously uses the field names from the datasource as the NAME attributes for the form. The only catch is that the names in the form are prefixed with a three-letter designation. For example, the ticket information is referenced as optTicket in the HTML code, and the field name in the database is Ticket. Therefore, you must strip the first three letters of each field name prior to building the SQL INSERT string. The SQL INSERT string takes the following form:
INSERT INTO table (field1, field2, …fieldn) VALUES ('value1', 'value2',… 'valuen')
Add the following code to build the SQL INSERT string from the parsed input data:
strSQL = "INSERT INTO Registrations ("
For i = 1 To UBound(arrArgVal) - 1 Step 2
strSQL = strSQL & Trim$(Right$(arrArgVal(i), _
Len(arrArgVal(i)) - 3)) & ","
Next
strSQL = Left$(strSQL, Len(strSQL) - 1) & ") "
strSQL = strSQL & "VALUES ("
For i = 2 To UBound(arrArgVal) Step 2
strSQL = strSQL & "`" & Trim$(arrArgVal(i)) & "`,"
Next
strSQL = Left$(strSQL, Len(strSQL) - 1) & ") " Executing the SQL statement is simply a matter of using the Execute method, which takes action immediately. Add the following code to execute the SQL INSERT statement:
dbRegistrations.Execute strSQL Like all routines, this subroutine needs proper closing and error handling. To complete the database update routine, add the following code to close the database connection and handle errors:
UpdateDataExit:
dbRegistrations.Close
Set dbRegistrations = Nothing
Exit Sub
UpdateDataErr:
Resume UpdateDataExit
End Sub Once the database has been updated, the program must supply a return HTML page for the browser to display. The HTML page is formatted as a single string that is returned through the strReturn argument. OLEISAPI expects the return HTML page to be located at the memory location addressed by strReturn. Add the following code to start the formatting of the return HTML page:
Private Function fCreateHTML(arrArgVal() As String) As String
On Error GoTo CreateHTMLErr
Dim strHTML As String The key to the proper formatting of the return string is to supply the correct header. The header portion of the return string identifies the kind of information that the browser will be receiving. (Browsers can handle more types of data than just HTML code—they can accept sound and video clips, for example.) The header must be followed by a blank line. Add the following code to set up the correct header:
strHTML = "Content-Type: text/html" & vbCrLf & vbCrLf
The rest of the code just completes the return string by using HTML tags. Add the following code to finish the return string and include error-handling for this routine:
strHTML = strHTML & "<HTML><HEAD><TITLE>Registered!"
strHTML = strHTML & "</TITLE></HEAD><BODY "
strHTML = strHTML & "BGCOLOR=#FFFFFF><CENTER>"
strHTML = strHTML & "<H2>Thanks for Registering!</H2>"
strHTML = strHTML & "</CENTER></BODY></HTML>"
fCreateHTML = strHTML
CreateHTMLExit:
Exit Function
CreateHTMLErr:
fCreateHTML = fCreateHTMLError(Err.Number, Err.Description)
Resume CreateHTMLExit
End Function The ActiveX component must handle any runtime errors that occur. The main error-handling routine will create a return HTML page for any error that occurs. This means that all errors will be reported back to the browser in HTML code. Add the following code to your project, to provide runtime error handling:
Private Function fCreateHTMLError(lngNumber As Long, _
strDescription As String) As String
Dim strHTML As String
strHTML = strHTML = "Content-Type: text/html" & vbCrLf _
& vbCrLf
strHTML = strHTML & _
"<HTML><HEAD><TITLE>Error Report</TITLE></HEAD><BODY>"
strHTML = strHTML & _
"<H2><CENTER>OLEISAPI ERROR!</CENTER></H2><P>"
strHTML = strHTML & _
"<TABLE><TR><TH>NUMBER</TH><TD>" & Format$(lngNumber) _
& "</TD></TR>"
strHTML = strHTML & "<TR><TH>DESCRIPTION</TH><TD>" & _
strDescription & "</TD></TR>"
strHTML = strHTML & "</TABLE></BODY></HTML>"
fCreateHTMLError = strHTML
End Function You have now finished creating the Person class. This class module contains all of the functionality necessary to access the database and return HTML pages. Listing 7-2 shows the complete code for the Person class.
Option Explicit
Public Sub Register(strRequest As String, strReturn As String)
`Author: New Technology Solutions, Inc.
`Purpose: Receive calls from OLEISAPI
`5/1/96 Original
On Error GoTo RegisterErr
ReDim arrArgVal(0) As String
`Parse the incoming string
Call sParseRequest(strRequest, arrArgVal())
`Update the database
Call sUpdateData(arrArgVal())
`Return the HTML page to the browser
strReturn = fCreateHTML(arrArgVal())
RegisterExit:
Exit Sub
RegisterErr:
strReturn = fCreateHTMLError(Err.Number, Err.Description)
Resume RegisterExit
End Sub
Private Sub sParseRequest(strRequest As String, _
arrArgVal() As String)
`Author: New Technology Solutions, Inc.
`Purpose: Parse request string into entries in array
`5/1/96 Original
On Error GoTo ParseRequestErr
`Incoming string is in the format
`Field1=Value1&Field2=Value2
Dim intIndex As Integer
Dim intPosition As Integer
Dim intStart As Integer
Dim strParse As String
Dim strTemp As String
Dim intTest As Integer
intIndex = 0
intStart = 1
Do While InStr(intStart, strRequest, "=", 1) > 0
intIndex = intIndex + 1
ReDim Preserve arrArgVal(intIndex + 1)
`Search for equals sign to get field name
intPosition = InStr(intStart, strRequest, "=", 1)
arrArgVal(intIndex) = Trim$(Mid$(strRequest, intStart, _
intPosition - intStart))
intIndex = intIndex + 1
intStart = intPosition + 1
intPosition = InStr(intStart, strRequest, "&", 1)
`Search for ampersand to get value
If intPosition > 0 Then
arrArgVal(intIndex) = Trim$(Mid$(strRequest, _
intStart, intPosition - intStart))
intStart = intPosition + 1
Else
arrArgVal(intIndex) = Trim$(Right$(strRequest, _
Len(strRequest) - intStart + 1))
intStart = Len(strRequest)
End If
Loop
For intIndex = 1 To UBound(arrArgVal)
`This loop cleans up each entry, changing
`plus signs back to spaces and restoring
`ASCII characters represented by hexadecimal
`notation preceded by percent signs
strTemp = ""
intPosition = 1
Do
Select Case Mid$(arrArgVal(intIndex), intPosition, 1)
Case "+"
strTemp = strTemp & " "
intPosition = intPosition + 1
Case "%"
strTemp = strTemp & Chr$(Val("&H" & _
Mid$(arrArgVal(intIndex), intPosition + 1, 2)))
intPosition = intPosition + 3
Case Else
`Remove any ASCII characters not supported
`by Windows
intTest = Asc(Mid$(arrArgVal(intIndex), _
intPosition, 1))
If Not (intTest < 32 Or (intTest > 126 And _
intTest < 145) Or (intTest > 146 And _
intTest < 160)) Then
strTemp = strTemp & _
Mid$(arrArgVal(intIndex), intPosition, 1)
End If
intPosition = intPosition + 1
End Select
Loop While intPosition <= Len(arrArgVal(intIndex))
arrArgVal(intIndex) = strTemp
Next
ParseRequestExit:
Exit Sub
ParseRequestErr:
ReDim arrArgValue(4)
arrArgValue(1) = "Error"
arrArgValue(2) = Err.Description
arrArgValue(3) = "Number"
arrArgValue(4) = Format$(Err.Number)
Resume ParseRequestExit
End Sub
Private Sub sUpdateData(arrArgVal() As String)
`Author: New Technology Solutions, Inc.
`Purpose: Update database with registration info
`5/23/96 Original
On Error GoTo UpdateDataErr
Dim strSQL As String
Dim i As Integer
Dim dbRegistrations As RDO.rdoConnection
`Open a connection to the datasource by using
`Remote Data Objects
Set dbRegistrations = rdoEngine.rdoEnvironments(0). _
OpenConnection("eventreg")
`Build SQL INSERT string
strSQL = "INSERT INTO Registrations ("
For i = 1 To UBound(arrArgVal) - 1 Step 2
strSQL = strSQL & Trim$(Right$(arrArgVal(i), _
Len(arrArgVal(i)) - 3)) & ","
Next
strSQL = Left$(strSQL, Len(strSQL) - 1) & ") "
strSQL = strSQL & "VALUES ("
For i = 2 To UBound(arrArgVal) Step 2
strSQL = strSQL & "`" & Trim$(arrArgVal(i)) & "`,"
Next
strSQL = Left$(strSQL, Len(strSQL) - 1) & ") "
`Execute SQL statement
dbRegistrations.Execute strSQL
UpdateDataExit:
`Close connection
dbRegistrations.Close
Set dbRegistrations = Nothing
Exit Sub
UpdateDataErr:
Resume UpdateDataExit
End Sub
Private Function fCreateHTML(arrArgVal() As String) As String
`Author: New Technology Solutions, Inc.
`Purpose: Generate a sample return HTML page
`5/1/96 Original
On Error GoTo CreateHTMLErr
Dim strHTML As String
strHTML = "Content-Type: text/html" & vbCrLf & vbCrLf
strHTML = strHTML & "<HTML><HEAD><TITLE>Registered!"
strHTML = strHTML & "</TITLE></HEAD><BODY "
strHTML = strHTML & "BGCOLOR=#FFFFFF><CENTER>"
strHTML = strHTML & "<H2>Thanks for Registering!</H2>"
strHTML = strHTML & "</CENTER></BODY></HTML>"
fCreateHTML = strHTML
CreateHTMLExit:
Exit Function
CreateHTMLErr:
fCreateHTML = fCreateHTMLError(Err.Number, Err.Description)
Resume CreateHTMLExit
End Function
Private Function fCreateHTMLError(lngNumber As Long, _
strDescription As String) As String
`Author: New Technology Solutions, Inc.
`Purpose: Create an HTML page describing an error
`5/1/96 Original
Dim strHTML As String
strHTML = strHTML = "Content-Type: text/html" & vbCrLf _
& vbCrLf
strHTML = strHTML & _
"<HTML><HEAD><TITLE>Error Report</TITLE></HEAD><BODY>"
strHTML = strHTML & _
"<H2><CENTER>OLEISAPI ERROR!</CENTER></H2><P>"
strHTML = strHTML & _
"<TABLE><TR><TH>NUMBER</TH><TD>" & Format$(lngNumber) _
& "</TD></TR>"
strHTML = strHTML & "<TR><TH>DESCRIPTION</TH><TD>" & _
strDescription & "</TD></TR>"
strHTML = strHTML & "</TABLE></BODY></HTML>"
fCreateHTMLError = strHTML
End Function Listing 7-2.
The complete code for the Person class.