Creating a Server Component with VB - Redesigned - Part 2

By Doug Dean

Rating: 4.1 out of 5

Introduction

This is part 2 of an article that demonstrates how to design a maintainable server-side DLL that can be called from an ASP page and rendered in any browser. This part shows how to apply the ideas discussed in Part 1 (see http://www.15seconds.com/Issue/010730.htm). It will cover different aspects of retrieving data from a database and how best to work with that data within a Visual Basic (VB) DLL so that the method string that is returned contains a structure ready to be rendered within a browser.

Returning a compact browser-ready string from a component can be very convenient for the end developer, although not without issue. Debates regarding the sanctity of the separation between presentation and business tiers are not merely academic. I have found that speed can be as important as maintenance, especially with Internet applications running on servers. Just as in the normalization process related to database table design, sometimes allowing a little practical redundancy goes a long way in relieving processing burdens and speeding up transactions. In the case of populating an HTML TABLE with values from a database in an ASP/VB environment, many different options are open to the programmer, as mentioned in the first part of this article.

The code demonstrated here will construct fully structured HTML TABLE data records encapsulated between <TD></TD> tags within the VB code itself. The values used to construct this return string, containing ready-to-render HTML TABLE data, will come from three sources. The data contained within the VB-constructed HTML TABLE tags will be stored within a database table. Values that deal with controlling the immediate retrieval of this data, such as which database record to display, will be sent to the VB code via method parameters that originate from the ASP file that calls the method. Presentation data, the HTML TABLE TD and FONT tag values, will be retrieved from a separate database table, although default values for these variables will also be written into the VB code itself.

Because of the "code application" nature of this second article, there's much more example code than in the first article. I've tried to simplify the entirety of the example code as much as possible without omitting any of the processes that this article is attempting to exemplify. The difficulty with a program of this complexity is preserving the overall conceptual nature of a significant amount of code while presenting it in smaller, topical sections. Most of the following example code sections can be placed within VB and run "as-is," although some example code fragments are also included and are labeled as such. Once the functioning of a section of example code is explained, any further code added to the same example code will be highlighted in bold so that you can more easily see the newly introduced code and how it functions within the context of previously discussed code.

Download source code and other documentation for this article.

What the Example Application Does

The processes the example application will demonstrate are:

How to utilize VB method parameter variables sent from a calling ASP file.

VB code that utilizes database transactions.

VB code that utilizes the GetRows() method for retrieving database data.

How to access data in a Variant variable populated by the GetRows() method to construct an HTML-ready formatted string.

Using a database "style table" that can provide a quick way to modify HTML presentation code.

The use of string buffering to avoid relying on VB's processing-intense concatenation operator.

A way to utilize error code to inform the calling ASP file when a database record is not successfully retrieved by the VB code. This is a tall order for a simple example program that's best kept as uncomplicated as possible. The application I picked demonstrates all these processes, although the database transaction code is included without a practical transactional application.

This example application is very limited and lacks VB methods for adding, editing, and deleting the text used in the examples from the database. But then again, this is just enough code to demonstrate the processes needed for you to add these features yourself -- if you feel inclined to do so.

The example VB project, named NoteProject, populates an HTML TABLE record with two table record data areas. The left table record data area contains a list of hyperlinked titles that are stored in a database. In our example code, we'll list some of the topics found in this article. When a user selects any of these title links, database-stored text related to the title will fill the table record area to the right of the titles. So that users won't get confused, we'll provide a way to alter the color of the currently selected title and remove its hyperlink.

The VB code produces HTML that displays the following TABLE record data:

We'll get the titles from the database using the GetRows() method, which conveniently stores our record fields in a multidimensional array. In order to identify which title the user selects, a query string will be attached to the end of each hyperlinked title within the HTML code. This query string will use the titles' NoteID database field value, which is unique for each title.

Here's an example of how the title HTML <A> tags are structured within the TABLE TD tags sent back from our VB code to an ASP file, which we'll name NoteExample.asp. Notice that the user must have selected the "Buffering" title since <A> tags don't enclose it.

<A HREF="NoteExample.asp?ID=1">Intro</A>

<A HREF="NoteExample.asp?ID=2">Style Tables</A>

<A HREF="NoteExample.asp?ID=3">GetRows()</A>

Buffering

<A HREF="NoteExample.asp?ID=5">Transactions</A>

</TD>

The VB code will send back the <TD></TD> tags along with the tags' content. We'll also provide tags, even though the <A> tag will override the font title colors. We can use this to our advantage and set the FONT COLOR property value to a different color than the page's BODY LINK value so that the selected title will display a different color than the linked titles. Remember, our VB code will omit the <A></A> tags for the title that is selected by the user, thereby using the FONT tag color parameter rather than the BODY LINK color parameter.

The URL for each title will send the user back to the current ASP file being displayed -- basically having the ASP file call itself when one of the titles is selected. The VB code will also assign a query string named "ID" to the end of each titles' URL within the <A> tag. The value of the ID query string will match each title's unique database record NoteID value. Every time our example NoteExample.asp file is called with an ID query string attached to the end of its URL, we'll send the ID query strings value to the VB code as a method parameter value so that the VB code will know which title was selected. So whether a URL with an ID query string is called from within the ASP file, or from another Internet link, the VB code will display the text of the title whose NoteID equals the ID query string value.

Below is the line of ASP code that will precede the call to the VB method and assign the "ID" query string variable value to a local ASP file variable. Basically, this line of code sets the local ASP variable named aspNoteID to the NoteID value of the title that the user selected. We'll then use the aspNoteID variable as a VB method parameter to let the VB method code know which notes' title text to display.

aspNoteID = Request.QueryString("ID")

If the NoteExample.asp file is called without a query string named ID, then the following line of ASP code will set the aspNoteID to the default of 1. Sending the VB method code a parameter value that is less than 1, or greater than the number of titles in the database table, would cause an error.

If aspNoteID < 1 Then aspNoteID = 1

The right side of our HTML TABLE record data area will be simpler than the left-hand side. It basically will contain our selected titles' text enclosed in HTML TD and FONT tags.

This is an example application written for an article on server-side VB programming

</TD>

Since the VB method will return data within two sets of<TD></TD> tags, we'll need to provide the <TABLE></TABLE> and <TR></TR> tags from within the ASP file that calls the VB method. Although these tags could also be constructed within the VB method, I have found that leaving them in the ASP file allows the end developer greater freedom to change the structure and appearance of the table, while still allowing the VB code to iterate through the database information.

This, again, is a balancing of where to draw the line between presentation and business code. On one extreme, you could construct the entire ASP page's HTML code in the VB code, which would consist of all the code enclosed in, and including, the <HTML></HTML> tags. Other programmers would prefer to construct all the <A> tags, title values, title text, tags, <TD> tags, etc., in the ASP file itself and just send back a recordset, or an array, from the VB code filled with the title names and selected title text. (See articles "How To Pass a RecordSet From a VB Component to an asp File" and "How To Pass a Variant Array Populated with a RecordSet From a VB Component to an asp File" at http://www.dougdean.com for more information on these alternatives). I have found sending back database field values within <TABLE> or <TD> tags is the best compromise that balances issues like maintenance, speed, source code security, etc.

Here's the entire HTML code that is sent back from the example VB method along with the supporting HTML code that we'll provide in the ASP file. All the HTML code not sent from the VB method is in bold text. The browser rendered code is displayed before the HTML source code.

The combined ASP file code and the HTML code that's returned from the VB code:

<HTML><HEAD><TITLE>Note Example</TITLE></HEAD>

<A HREF="NoteExample.asp?ID=1">Intro</A>

<A HREF="NoteExample.asp?ID=2">Style Tables</A>

<A HREF="NoteExample.asp?ID=3">GetRows()</A>

Buffering

<A HREF="NoteExample.asp?ID=5">Transactions</A>

</TD>

Using string buffering can optimize concatenation operations and avoid slowdowns

</TD>

</TR></TABLE>

</BODY>

</HTML>

Although the VB method code will be covered in detail later in this article, we need to know what will be needed in order to design its method parameters. Again, we'll be getting values from three sources outside of the VB code itself; values sent as method parameters, a database table storing different HTML "presentation-style" values, and the database table that stores the titles and title's text.

Method Parameters

It has already been mentioned that we'll be sending the NoteID value to the VB code via a method parameter. The NoteID value will be passed back to our ASP file via a query string when a user selects a title link. But there are also other nonpresentation values that will change frequently and can be provided to the VB code via method parameters.

Since the ASP developer using our VB method may want to use it in a number of different files, or rename the callback ASP file something other than NoteExample.asp, we'll provide a method parameter for changing the name of the ASP file. The VB method will use this file name parameter value to construct the HREF values of the <A> tags within the title list. We'll call this method parameter strURL.

Another frequently changed, and necessary, value is the database connection string. We'll use an Open DataBase Connectivity (ODBC) Data Source Name (DSN) name for our example, but it can also be set to a DSN-less connection string. This method parameter name will be strConnectionString. You'll need to set an ODBC DSN connection to the example database, NoteDb.mdb, and name it "NotesDSN" to use this article's default code. (How to establish a ODBC DSN connection is described later.)

Lastly, we need a way of picking which database table record is used for the HTML presentation-style values, although a set of local default VB variables will be provided as an alternative to using these database values. The intStyleID method parameter will be used for this. Since this value may be used more frequently than the previous two method parameters described above, we'll place it second in the method parameter list, right behind the lngNoteID method parameter.

Below is the completed VB method signature. Since the browser rendered TABLE sent back by the method displays text related to various note titles, we'll call the method ShowNotes().

The VB Method Signature

Public Function ShowNotes(ByVal lngNoteID As Long, _

Optional ByVal intStyleID As Integer = 0, _

Optional ByVal strURL As String = "NoteExample.asp", _

Optional ByVal strDbConnectionString As String = "NotesDSN" ) As String

Notice that the intStyleID, strURL, and strConnectionString parameter variables are set as "optional" and have default values that match the ASP file and DSN names used in this articles' example code. The method also returns a string that will contain our constructed HTML code, combined with the values generated from the database and method parameters.

The ASP Code

Below is the entire ASP file code that will call our VB method and create the HTML code displayed in the "What the Example Application Does" section. The specific details of calling a VB method with parameters were covered in the first article. The details of the VB project, class, and method used in this ASP code will be discussed later in this article.

The ASP code that calls the VB code:

<HTML><HEAD><TITLE>Note Example</TITLE></HEAD>

'///// Set local variables used as VB method parameter variables

aspNoteID = Request.QueryString("ID")

aspStyleID = 0

aspURL = "NoteExample.asp"

aspDbConnectionString = "NotesDSN"

'///// Set the NoteID to 1 if no ID query string was received

If aspNoteID < 1 Then aspNoteID = 1

'///// Instantiate the component object

Set objReference = Server.CreateObject("NoteProject.NoteClass")

'///// Call the components method and store the returned string

strMethodReturn = objReference.ShowNotes(aspNoteID, aspStyleID, aspURL, aspDbConnectionString)

'///// Send the returned string to the visiting browser

Response.Write(strMethodReturn)

'///// Clean up

Set objReference = Nothing

</TR></TABLE>

</BODY>

</HTML>

Remember that three of the four method parameters are optional. If the default settings are used, as they are in this article, you can call the VB method by just sending the aspNoteID method parameter and avoid using the other three variables in the ASP file.

Here's an example-code fragment that uses the single, nonoptional aspNoteID method parameter.

'///// Call the components method and store the returned string

strMethodReturn = objReference.ShowNotes(aspNoteID)

The Database Tables

Now that we know how the end product will be structured and how it is to function, we'll need to design the database tables that store the data that is used. We'll use two database tables contained in an Access database named NoteDb. The first table, named NoteTable, will have three fields:

NoteTable

NoteIDAutoNumber

NoteTitleText

NoteTextMemo

Notice that the NoteID field is an AutoNumber type, meaning that Access will assign a unique number to the NoteID field for each NoteTitle or NoteText entered. Also note that the NoteText field is set as a Memo type. This is overkill for this example application since the Memo type can contain a very large amount of text and we'll only be using short strings that could be stored in a Text type. But I wanted to show that a Memo type could be used for when you need to store text greater than 256 bytes.

In a real-world application, I would want to use another field name for the primary key AutoNumber field and set a second field named NoteID field set to type Number. When you write code to add note title and text content (not covered in this article), you would then want to use the AutoNumber field's value generated by the database to populate the NoteID field with the same value. This provides a little more flexibility since the values of the AutoNumber field can't be changed.

We'll name the second database table NoteStyleTable since it provides the values related to the presentation style of our database data. It, too, has a primary key field of type AutoNumber that is uniquely generated by the database. The next field is the intStyleID, which we'll use to determine which note style to use. This use of a corresponding AutoNumber and Number field exemplifies the issue of primary key flexibility mentioned in the above paragraph.

I've lined up the <TD> and tags used in the VB code with the field names used in the NoteStyleID table. This is a fairly arbitrary process, and the names aren't limited to what an end developer could use for the values of these fields. For instance, an ASP developer could use the strTitleDateStart and strTextDataEnd fields to store the <TABLE><TR> and </TR></TABLE> tags that we are providing in the ASP file.

strTitleDataStart<TD WIDTH="74" NOWRAP VALIGN="top">

strTitleFontStart

[TITLES CREATED BY VB CODE GO HERE]

strTitleFontEnd

strTitleDataEnd</TD>

strTextDataStart<TD WIDTH="124" VALIGN="top" BGCOLOR="#EFEFEF">

strTextFontStart

[TITLE TEXT CREATED BY VB CODE GO HERE]

strTextFontEnd

strTextDataEnd </TD>

I'm always intrigued by how developers think of various ways to sneak other code into variables that I never considered at the time of development. Because of this, I try to provide as many presentation variables as reasonably possible when my VB method code constructs ready-to-render HTML return strings.

Given these needs for our HTML formatting, we'll design the NoteStyleTable with the fields listed below. Notice that the identification fields AutoStyleID and intStyleID have been separated, as explained in the discussion of the AutoNumber field above.

NoteStyleTable

AutoStyleIDAutoNumber

intStyleIDNumber

strTitleDataStartText

strTitleFontStart Text

strTitleFontEnd Text

strTitleDataEnd Text

strTextDataStart Text

strTextFontStart Text

strTextFontEnd Text

strTextDataEnd Text

Here are a few of the alternative styles stored in the example database:

Now that the functioning of the example application has been all mapped out; all that's needed is the VB method code that will produce the return string that displays our note titles and text in HTML formatted code.

The VB Environment Setup

The VB example code that we'll be covering in this article will use the following names:

VB Code:

Project name:NoteProject

Class name:NoteClass

Method name:ShowNotes

ASP Code:

ASP file name:NoteExample.asp

The typical way to start a VB DLL for server-side use is to select the ActiveX DLL icon in the VB Projects window. After changing the Project and Class names to NoteProject and NoteClass for our example code, you can use an ASP file for testing and debugging your code. To have VB call an ASP file when the code is run, select the "Projects/NoteProject Properties..." from the VB menu and then select the "Debugging" tab. Select "Start browser at URL:" and enter the path and the name for an ASP file (NoteExample.asp for our example file). By instantiating NoteClass in the NoteExample.asp file and calling the components method, you can test the VB code by selecting the "Run" option from VB.

Here's another example of ASP file code that will successfully call our example VB method:

'///// Instantiate the component object

Set objReference = Server.CreateObject("NoteProject.NoteClass")

'///// Call the components method and store the returned string

strMethodReturn = objReference.ShowNotes(1)

'///// Send the returned string to the visiting browser

Response.Write(strMethodReturn)

'///// Clean up

Set objReference = Nothing

There are a number of ways to begin coding a server-side DLL component in VB,, and I'd like to demonstrate an alternative way that I have found useful. You can skip this section if you prefer to start your VB coding as an ActiveX DLL project type and you will not have to change the project and class settings after starting it alternatively as a Standard EXE project type. I added this section for those who like to experiment with different ways to test and debug their initial server-side DLL code.

To make the initial code easy to test and debug, start a Standard EXE from VB and change the default Project1 project name to NoteProject. Next, add a class module (Project/Add Class Module from the VB menu) and name it NoteClass. Save both the project and class files in a directory of your choosing.

Since this is a Standard EXE project, we'll have to eventually change some of the settings of the class and project so we can use it as a server-side DLL. But for now we'll use the Standard EXE's default Form1 code to test our code instead of using an ASP file.

The method of our example class (ShowNotes) will return a string that contains data with HTML formatting, so eventually you'll want to test your code by calling the class method from an ASP file. But it's sometimes faster to initially test your code by sending the method's return string to the VB Immediate window for quicker debugging. This method even has the advantage of developing a server-side DLL on a computer system regardless of whether it has ASP installed on it or not.

To set up VB to test your code in this fashion, select the Form1 object (created by VB when you selected Standard EXE as a new project type) and double click the form. Place the following code within the Form1 code window.

Form Code Used to Test Method Class

Private Sub Form_Load()

Dim objReference As New NoteProject.NoteClass

Debug.Print objReference.ShowNotes(1)

End

End Sub

After you enter the code for the example class, you'll be able to run the code and have the method's return string displayed in VB's Immediate window. From there you can cut and paste the string into an HTML editor for initial changes before actually calling the classes method from an ASP file.

I have found that it takes about 5 seconds to do this when using an HTML editor like Allaire's HomeSite. I just run the VB code and then, while holding down the Control key, hit the "G," "A," and "X" keys sequentially. This displays VB's Immediate window, selects the class method's return string, and removes it while placing it in Windows' memory work space. I then paste it to my HomeSite's editor to see what it looks like rendered in a browser.

When you're ready to start testing your VB code by calling the Class method from an ASP file, remove the Form1 form by right clicking its name in the Project Explorer window and selecting "Remove Form1". Go to the VB "Projects/NoteProject Properties..." menu selections and in the Project Properties window change the Standard EXE value in the drop-down box to ActiveX DLL. Change the "Single Threaded" value to "Apartment Threaded" in the Threading Model area on the same window. The drop-down list labeled 'Startup Object:' should have "(None)" selected. Also, make sure that "Unattended Execution" and "Upgrade ActiveX Controls" are selected. VB will respond with "Project 'Start Mode' property has been changed" when you select the Project Properties OK button. Just click OK in this warning window. Select the NoteClass name in the Project Explorer and change its Instancing value in the Properties window to "5 - MultiUse". You may want to go back to the Project Properties window and select the "Retain In Memory" box, which may only now be selectable. Save your new DLL project, and you're ready to run your VB code from an ASP file.

The ShowNotes Method's Code

Well, enough with the conceptual and preliminary set up. Let's get into the code of the method. In order to demonstrate all the processes that are important to a server-side DLL meant to interact with ASP files, our example code will grow somewhat large. I'll section off code that exemplifies specific functionality within our example method code and present topics one at a time. This means that each code section will cover a particular topic. Some code sections will be introduced without including the code context that was previous covered, while other sections will add code in an accumulative fashion.

As you can imagine, explaining code incorporating this much topical functionality can get confusing at first. That's why it's presented one mouthful at a time. Here's the overall structural layout of what our finished method code will look like, although we'll add some string concatenation optimization to this structure once it's covered.

Error code statement

Dim and initialize variables

Open a database connection and begin a transaction

Get all the note titles from the database (using Rs.Fields method)

Get the user-selected note text (using Rs.GetRows)

Set the note style properties (using Rs.Fields method)

Commit the transaction and close the database connection

Construct the note title table record

Construct the note text table record

Send back the constructed HTML to the calling ASP file

Error capture code including transaction rollback

Basic Method Code

The code covered in this initial code section is the basic skeleton of our example method, which we'll flesh out later.

As you can see, the code begins with the method signature, covered previously, and ends with an End Function statement. Between the beginning and ending function code statements we have two lines of code. The first declares a string and the second sends the string's value back to the code that called the method. This section of code can be cut and pasted into VB and successfully called, albeit without a value being returned since we haven't set a value to the return string variable (strReturnString) yet.

Basic Method Code

Public Function ShowNotes(ByVal lngNoteID As Long, _

Optional ByVal intStyleID As Integer = 0, _

Optional ByVal strURL As String = "NoteExample.asp", _

Optional ByVal strDbConnectionString As String = "NotesDSN") As String

'///// Variable to hold string to send back to calling code

Dim strReturnString As String

'--> METHOD CODE GOES HERE

'///// SEND BACK STRING

ShowNotes = strReturnString

End Function

To reiterate, this method is declared as a Public function, meaning that it can be called from any COM-enabled code (it's public information) and will return a value (it's a function as opposed to a Sub, which doesn't return a value). We have four method parameters, three of which are Optional, meaning that the calling code doesn't have to include them. Default values will be used for any optional parameter method that is not set by the calling code.

Our method parameters include a required variable that indicates which NoteID has been selected by the user from within their browser (lngNoteID), an optional variable that indicates which note-style database record to use (intStyleID), an optional variable set to the URL that we want the returning title links in the HTML table record set to (strURL - typically the same name as the ASP file calling the method), and the optional variable containing the database string used for connecting to the database (strConnectionString).

The Method's Error Handling

Since our method code doesn't do anything meaningful when called at this point, let's take this opportunity and add some error code that will demonstrate how a method error can be handled. In this example code, an error is generated, or raised, each time the method is called. Later we'll take a look at how this type of error code is typically used.

To capture and control any errors that may occur in our method, we'll use an On Error statement as the first line of method code. By placing "On Error GoTo ErrorCode" before any other internal method code, any error occurring within the method will skip any remaining method code and direct the flow of control to the "ErrorCode:" line of code at the bottom of the method. Since we want all the error-free code to not issue error messages, we'll have to place an Exit Function statement directly before the ErrorCode statement. This way if no error occurs within the method, the function will be properly ended and not run the code past the ErrorCode statement.

Adding Error Handling

Public Function ShowNotes(ByVal lngNoteID As Long, _

Optional ByVal intStyleID As Integer = 0, _

Optional ByVal strURL As String = "NoteExample.asp", _

Optional ByVal strDbConnectionString As String = "NotesDSN") As String

'///// ERROR CODE //////////////////////////////////////////////

On Error GoTo ErrorCode

'///// Variable to hold string to send back to calling code

Dim strReturnString As String

'--> METHOD CODE GOES HERE

'///// IF/THEN STATEMENT WITH THE FOLLOWING AS A POSSIBLE ERROR CONDITION

If True Then

Err.Number = 40000

Err.Source = "String indicating where error is occurring"

Err.Description = "Error description string"

Err.Raise Err.Number, Err.Source, Err.Description

End If

'--> METHOD CODE GOES HERE

'///// SEND BACK STRING

ShowNotes = strReturnString

'///// EXIT FUNCTION IF NO ERROR RAISED ////////////////////////

Exit Function

'///// CODE IF ERROR OCCURS ////////////////////////////////////

ErrorCode:

'///// SEND BACK RAISED ERROR

Err.Raise Err.Number, Err.Source, Err.Description

End Function

To exemplify what would happen if an error occurs, or any condition we want to be returned as an error, we'll use an If/Then statement that's guaranteed to be processed (If True Then) for this particular error code example.

If True Then

Err.Number = 40000

Err.Source = "String indicating where error is occurring"

Err.Description = "Error description string"

Err.Raise Err.Number, Err.Source, Err.Description

End If

Obviously, we'll replace the 'If True Then' statement with a more practical condition within our method code, such as whether a database record correctly returned at least one record given the value of the NoteID sent by the calling code.

When an error is caused within VB code, an error message is displayed. The first three of the four lines within the If/Then statement sets three VB variables used to describe a custom error message. Normally, VB sets these variables and sends them back automatically so the calling code can be programmed to handle how the error is to be dealt with. Since we're going to be getting information from a database, and it's the calling code that's setting the values used to select which record we're retrieving, we'll use the same error-messaging system to let our calling ASP file code know when a database record was not successfully returned.

For now we'll set the Err.Number, Err.Source, and Err.Description variables to dummy values and send them back by telling VB that we want it to think a real error has occurred. Setting the three Err variables, and then "raising" an error with the fourth line, accomplishes this. One caveat, since VB uses it's own error-number values, we'll want to use numbers that won't interfere with the VB error-numbering system. At this point in time you can safely use at least a few thousand numbers starting with 40000.

Err.Raise Err.Number, Err.Source, Err.Description

The above code essentially sends the flow of code to the line of code (ErrorCode:) indicated in the On Error statement that we used at the start of the method, skipping all other method code. So after the three Err variables are set and the error is raised with the Err.Raise statement, the next line of code that is processed is the code following the OnError statement.

'///// CODE IF ERROR OCCURS ////////////////////////////////////

ErrorCode:

'///// SEND BACK RAISED ERROR

Err.Raise Err.Number, Err.Source, Err.Description

End Function

We again use the Err.Raise statement, with its three error descriptors and send back the error messages to the calling code. After the error-handling code is completed, we end the function. This area of code below the ErrorCode and End Function statements, will be used to clean up any open database connections, rollback the transaction, etc.

During your debugging and testing phase, you may want to comment out the "On Error GoTo ErrorCode" statement so that it won't divert any "real" errors to the "ErrorCode:" line. If you don't comment out the On Error statement, VB will always send you to the ErrorCode statement when tracking down the location of a bug in your method, which is not too informative when debugging any real method errors you may initially introduce.

Accessing the Database Tables from within the Method

Let's put away the error code for the moment so it won't clutter up our next important method code topic -- database access.

First, for any code which instantiates the ADODB object, you'll need to tell VB that you want to reference the ADODB object. This is done by going to the Project/References VB menu selection and checking the "Microsoft ActiveX Data Objects 2.0 Library" reference, although you'll probably have one with a higher version number.

Now, VB will recognize ADO objects because we referenced it and we can connect to the example NoteDb database. I recommend using ODBC to establish a data-source name for use as a database-connection string.

To establish a Data Source Name (DSN) with ODBC, select the "ODBC Data Sources" icon in the Windows Control Panel. Select the "System DSN" tab on the "ODBC Data Source Administrator" and then click the "Add..." button. The driver you want should be at, or near, the top of the driver list that will be displayed. Highlight the "Microsoft Access Driver (*.mdb)" driver and click the "Finish" button. The "OBDC Microsoft Access Setup" window will appear. In the "Data Source Name" input field, enter the DSN used in this article's example code, which is"NotesDSN." Then click the "Select" button and use the directory tree to drill down to the directory that stores this article's example Access database file. The file NoteDb.mdb will be displayed in the left window when its directory is selected in the directory tree. Select the example NoteDb.mdb file and click the "OK" button. Click "OK" twice more to exit from the ODBC Data Source Administrator. The "NotesDSN" data source name can now be used on your system via ActiveX Data Objects (ADO) from VB code.

ADO can be effectively used in many different ways to accomplish the same results when accessing database information. The code below is a template that will connect to the example database, using NotesDSN as an ODBC data source name, and then disconnect from the database without any fanfare.

Basic Database Code Template

Public Function ShowNotes(ByVal lngNoteID As Long, _

Optional ByVal intStyleID As Integer = 0, _

Optional ByVal strURL As String = "NoteExample.asp", _

Optional ByVal strDbConnectionString As String = "NotesDSN") As String

'///// DB Connectivity Variables

Dim Rs As New ADODB.Recordset

Dim objCmd As New ADODB.Command

Dim objConn As New ADODB.Connection

'///// OPEN DATABASE ///////////////////////////////////////////

objConn.Open strDbConnectionString

objConn.BeginTrans

'--> DO DATABASE PROCESSING HERE (SELECT/UPDATE/DELETE/INSERT)

'///// FINISH AND CLOSE UP DATABASE PROCESSES //////////////////

objConn.CommitTrans

objConn.Close

End Function

The database code first declares variables set to the Recordset, Command, and Connection objects of the ADODB class we referenced with VB previously.

Dim Rs As New ADODB.Recordset

Dim objCmd As New ADODB.Command

Dim objConn As New ADODB.Connection

Then the code opens a database connection using the data source name variable strDbConnectionString. Setting the strDbConnectionString method parameter from a calling ASP file can change this connection string variable, but it defaults to NotesDSN, which we'll use as our ODBC data source name for our example code.

objConn.Open strDbConnectionString

The next line of code isn't really necessary for our example program, but I included it to exemplify how to code for transactions. (See the article title "How to Utilize Database Transactions Within a VB Component From an asp File" at http://www.dougdean.com for more information on database transactions.)

objConn.BeginTrans

By using transactions, a method can treat a series of database processes (SQL statements) as a complete unit. If only one SQL statement in a series of SQL statements fails, then all the SQL statements within the same transaction are rolled back to the state the database was in before the transaction began. So if you're moving a record from one table to another table by adding it to the second table and then deleting it from the first table, you can be assured that one SQL statement won't take place without the other when they are within a transaction.

As I mentioned, our database sample code will not actually need this type of transaction commitment since all we're doing is reading from the tables. But if you do any editing or deleting, you'll find using the transaction code demonstrated here to be necessary at some point.

Once the database connection is opened, and we have indicated that we want a transaction to begin, we do some database processing (adding, deleting, or changing fields via SQL statements) and then finish up our database effort by telling ADO that we want all of our SQL statements processed as a single unit (objConn.CommitTrans) before closing the database connection (objConn.Close).

'--> DO DATABASE PROCESSING HERE (SELECT/UPDATE/DELETE/INSERT)

'///// FINISH AND CLOSE UP DATABASE PROCESSES //////////////////

objConn.CommitTrans

objConn.Close

Getting Note Text from the Database

To demonstrate how to retrieve one of the database records, we can add code to the template described above. This method code example will access the database and then send the note text of a record back to the ASP file code that called it. This method code assumes that a valid NoteID will be used as the first method parameter.

Adding Database Select Code

Public Function ShowNotes(ByVal lngNoteID As Long, _

Optional ByVal intStyleID As Integer = 0, _

Optional ByVal strURL As String = "NoteExample.asp", _

Optional ByVal strDbConnectionString As String = "NotesDSN") As String

'///// DB Connectivity Variables

Dim sql As String

Dim Rs As New ADODB.Recordset

Dim objCmd As New ADODB.Command

Dim objConn As New ADODB.Connection

'///// DB Value Storage variables

Dim strNoteText As String

'///// OPEN DATABASE ///////////////////////////////////////////

objConn.Open strDbConnectionString

objConn.BeginTrans

'///// GET SELECTED NOTE TEXT FROM DATABASE ////////////////////

sql = "SELECT NoteText FROM NoteTable WHERE NoteID = " & lngNoteID

objCmd.CommandText = sql

objCmd.CommandType = adCmdText

Set objCmd.ActiveConnection = objConn

Rs.Open objCmd

'///// Get NextText from database

strNoteText = Rs.Fields("NoteText")

'///// Close this Recordset

Rs.Close

'///// FINISH AND CLOSE UP DATABASE PROCESSES //////////////////

objConn.CommitTrans

objConn.Close

Set Rs = Nothing

Set objCmd = Nothing

Set objConn = Nothing

'///// SEND BACK NOTE TEXT FROM DATABASE

ShowNotes = strNoteText

End Function

Added to the database template code are two string variables -- sql and NoteText. The former is used to hold a valid SQL statement that selects the NoteText from the NoteTable. The value that was sent as the first method parameter (lngNoteID) will be used in the SQL statement to select which note text the user wants to view. The value of the "sql" string variable could be assigned directly to objCmd.ComandText, but using a string variable can add flexibility, especially if you later decide to make the SQL statement a method parameter value. The latter string variable is used to hold the note text returned from the database.

Once the "sql" variable is assigned an SQL select statement using the sent lngNoteID, we indicate that the command we're sending the Command object is an SQL text statement with the adCmdText VB constant (objCmd.CommandType = adCmdText). We then set the Command object to the Connection object (Set objCmd.ActiveConnection = objConn) and open the recordset (Rs.Open objCmd) with the SQL statement set within the Command object.

'///// GET SELECTED NOTE TEXT FROM DATABASE ////////////////////

sql = "SELECT NoteText FROM NoteTable WHERE NoteID = " & lngNoteID

objCmd.CommandText = sql

objCmd.CommandType = adCmdText

Set objCmd.ActiveConnection = objConn

Rs.Open objCmd

Now that the recordset is open, the NoteText field of the NoteTable is used within the Rs.Field("NoteText") method to retrieve the NoteText field value from our database record and assign it to our strNoteText variable.

'///// Get NextText from database

strNoteText = Rs.Fields("NoteText")

The remaining added code closes up the recordset (Rs.Close), sets the database objects to "Nothing" (Set Rs = Nothing, Set objCmd = Nothing, Set objConn = Nothing), and sends the note text value back to the calling code (ShowNotes = strNoteText).

'///// Close this Recordset

Rs.Close

'///// FINISH AND CLOSE UP DATABASE PROCESSES //////////////////

...

Set Rs = Nothing

Set objCmd = Nothing

Set objConn = Nothing

'///// SEND BACK NOTE TEXT FROM DATABASE

ShowNotes = strNoteText

Handling Database Errors

We're ready to combine the error code we explored previously with the database code we just developed. Again, this code example will demonstrate how to use transactional processes by "rolling back" all SQL statements when an error occurs in any one SQL statement. Of course, we're only using a single select SQL statement, but the process is the same for including multiple SQL statements that necessitate an "all or nothing" database commitment.

We'll also use the error processing to send a customized error message back to the calling code if our SQL statement happens to be invalid because an incorrect NoteID was sent as a method parameter. This is important because we really don't have any control over what NoteID value might be sent to our method code and we want to let the calling code deal with an erroneous NoteID in its own way - since it's the code that sent it.

Adding Error Handling to Database Code

Public Function ShowNotes(ByVal lngNoteID As Long, _

Optional ByVal intStyleID As Integer = 0, _

Optional ByVal strURL As String = "NoteExample.asp", _

Optional ByVal strDbConnectionString As String = "NotesDSN") As String

'///// ERROR CODE //////////////////////////////////////////////

On Error GoTo ErrorCode

'///// DB Connectivity Variables

Dim sql As String

Dim Rs As New ADODB.Recordset

Dim objCmd As New ADODB.Command

Dim objConn As New ADODB.Connection

'///// DB Value Storage variables

Dim strNoteText As String

'///// OPEN DATABASE ///////////////////////////////////////////

objConn.Open strDbConnectionString

objConn.BeginTrans

'///// GET SELECTED NOTE TEXT FROM DATABASE ////////////////////

sql = "SELECT NoteText FROM NoteTable WHERE NoteID = " & lngNoteID

objCmd.CommandText = sql

objCmd.CommandType = adCmdText

Set objCmd.ActiveConnection = objConn

Rs.Open objCmd

'///// Check if any data was returned

If Rs.EOF = True And Rs.BOF = True Then

'///// No data returned, set error information, raise error

Err.Number = 40001

Err.Source = "NoteProject::NoteClass::ShowNotes"

Err.Description = "00000002 Possible invalid lngNoteID entered as method value"

Err.Raise Err.Number, Err.Source, Err.Description

Else

strNoteText = Rs.Fields("NoteText")

End If

'--> ANY OTHER DATABASE CODE GOES HERE

'///// FINISH AND CLOSE UP DATABASE PROCESSES //////////////////

objConn.CommitTrans

objConn.Close

Set Rs = Nothing

Set objCmd = Nothing

Set objConn = Nothing

'--> OTHER METHOD CODE GOES HERE

'///// SEND BACK NOTE TEXT FROM DATABASE

ShowNotes = strNoteText

'///// EXIT FUNCTION IF NO ERROR RAISED ////////////////////////

Exit Function

'///// CODE IF ERROR OCCURS ////////////////////////////////////

ErrorCode:

'///// ROLLBACK, CLOSE DB REFERENCES, AND SEND BACK RAISED ERROR

objConn.RollbackTrans

If IsObject(Rs) Then Set Rs = Nothing

If IsObject(objCmd) Then Set objCmd = Nothing

If IsObject(objConn) Then Set objConn = Nothing

Err.Raise Err.Number, Err.Source, Err.Description

End Function

To verify whether a record was correctly returned from the database, we'll check the recordset end of file (EOF) and beginning of file (BOF) values. If both EOF and BOF are true, then we know that no record has been returned and something went astray. The most likely explanation is that the ASP code sent a NoteID as a method parameter value that doesn't exist in our database. Even though we know that the NoteID's links with "ID" query string values within the titles that the VB method sends back to the ASP file are valid, this method needs to handle links from any URL Internet connection that might attach an ID query string value.

'///// Check if any data was returned

If Rs.EOF = True And Rs.BOF = True Then

If no record was returned, we'll invoke the same type of error-messaging code covered previously. Using a custom error-number value that typifies this particular category of error will let the calling code check for a situation that results in nonexistent NoteID records. Also, clearly stating the location of the error, along with a succinct description string, will help with debugging as your code grows large.

'///// No data returned, set error information, raise error

Err.Number = 40001

Err.Source = "NoteProject::NoteClass::ShowNotes"

Err.Description = "00000002 Possible invalid lngNoteID entered as method value"

Err.Raise Err.Number, Err.Source, Err.Description

Here "40001" is used to indicate an invalid NoteID value. The project, class, and method names are used as the source value. The description includes a unique numbering scheme (I use a unique hexadecimal number for each error I trap) and a short description of the error, which includes an HTML since an ASP file will be used to call this method.

After assigning our error message values, we raise our error and send it off to the ErrorCode section at the very bottom of the method code, skipping all other method code. Of course, if a record is successfully returned, the Else section of our If/Then code will retrieve the record's NoteText field value and omit any of our error code.

Else

strNoteText = Rs.Fields("NoteText")

End If

You may want to test this example code with both valid and invalid NoteID values to see the error processing in action. (Compile the code to test this rather than just running the code since VB will process the error differently in each situation.)

Down at the bottom of our method we have our error section that processes any errors that occur in our method, as well as the ones we raise ourselves. Since we originally opened a database transaction (and getting to this error code section assumes that we have skipped over the section of code that commits our database transaction), we need to let ADO know that we want any previous SQL statements undone. We do this with the objConn.RollbackTrans statement that rolls back any database work from the point in our code where we placed our objConn.BeginTrans statement.

'///// ROLLBACK, CLOSE DB REFERENCES, AND SEND BACK RAISED ERROR

objConn.RollbackTrans

If IsObject(Rs) Then Set Rs = Nothing

If IsObject(objCmd) Then Set objCmd = Nothing

If IsObject(objConn) Then Set objConn = Nothing

The remaining three statements close the database objects if they are open (errors can occur before they are used, thus the conditionality).

Complete Code for Retrieving a Note's Text Here is the complete code for retrieving a note's text from the database. It's put together here so you can get a good overview of what has been discussed up to this point. We'll be adding more code to our method. At this point it might be advantageous to make sure you comprehend this code before going on to the next code section, which will demonstrate an alternative way to handle retrieved database values.

Here's what we covered so far:

Complete Code for Select Database Process

Public Function ShowNotes(ByVal lngNoteID As Long, _

Optional ByVal intStyleID As Integer = 0, _

Optional ByVal strURL As String = "NoteExample.asp", _

Optional ByVal strDbConnectionString As String = "NotesDSN") As String

'///// ERROR CODE //////////////////////////////////////////////

On Error GoTo ErrorCode

'///// DB Connectivity Variables

Dim sql As String

Dim Rs As New ADODB.Recordset

Dim objCmd As New ADODB.Command

Dim objConn As New ADODB.Connection

'///// DB Value Storage variables

Dim strNoteText As String

'///// OPEN DATABASE ///////////////////////////////////////////

objConn.Open strDbConnectionString

objConn.BeginTrans

'///// GET SELECTED NOTE TEXT FROM DATABASE ////////////////////

sql = "SELECT NoteText FROM NoteTable WHERE NoteID = " & lngNoteID

objCmd.CommandText = sql

objCmd.CommandType = adCmdText

Set objCmd.ActiveConnection = objConn

Rs.Open objCmd

'///// Check if any data was returned

If Rs.EOF = True And Rs.BOF = True Then

'///// No data returned, set error information, raise error

Err.Number = 40001

Err.Source = "NoteProject::NoteClass::ShowNotes"

Err.Description = "00000002 Possible invalid lngNoteID entered as method value"

Err.Raise Err.Number, Err.Source, Err.Description

Else

strNoteText = Rs.Fields("NoteText")

End If

Rs.Close

'--> ANY OTHER DATABASE CODE GOES HERE

'///// FINISH AND CLOSE UP DATABASE PROCESSES //////////////////

objConn.CommitTrans

objConn.Close

Set Rs = Nothing

Set objCmd = Nothing

Set objConn = Nothing

'--> OTHER METHOD CODE GOES HERE

'///// SEND BACK NOTE TEXT FROM DATABASE

ShowNotes = strNoteText

'///// EXIT FUNCTION IF NO ERROR RAISED ////////////////////////

Exit Function

'///// CODE IF ERROR OCCURS ////////////////////////////////////

ErrorCode:

'///// ROLLBACK, CLOSE DB REFERRENCES, AND SEND BACK RAISED ERROR

objConn.RollbackTrans

If IsObject(Rs) Then Set Rs = Nothing

If IsObject(objCmd) Then Set objCmd = Nothing

If IsObject(objConn) Then Set objConn = Nothing

Err.Raise Err.Number, Err.Source, Err.Description

End Function

In the next code section the VB GetRows() method will be used in place of the Rs.Fields() VB method. GetRows() will be used to obtain all of the note title names stored in the example NoteTable database table. We'll also be adding code that transforms our raw database values into an HTML-formatted string ready for viewing in a browser.

Getting Data with the GetRows() VB Method

The VB GetRows() method provides a convent and fast way to store rows of database record fields. It can seem a bit tricky to access the values stored in this special type of array, but getting the values from the database into the array couldn't be easier. In this code section I've omitted the database and error code previous covered so you can see GetRows() without other distracting code.

Here we'll need to get the NoteTitle and NoteID for each of our five example records. We could use the Rs.Fields() method within a loop to store the data from the two fields in a couple of arrays, or even compose the HTML text on-the-fly as the code loops though the open recordset. But the GetRows() method makes for a cleaner, simpler, and optimized way to work with rows of record fields.

The generic code for opening and closing a database used here is basically the same as described before, sans the code specific to the note text and error processes. We'll combine the previous code for retrieving the note's text and title after the GetRows() method is discussed.

Code Using GetRows()

Public Function ShowNotes(ByVal lngNoteID As Long, _

Optional ByVal intStyleID As Integer = 0, _

Optional ByVal strURL As String = "NoteExample.asp", _

Optional ByVal strDbConnectionString As String = "NotesDSN") As String

'///// DB Connectivity Variables

Dim sql As String

Dim Rs As New ADODB.Recordset

Dim objCmd As New ADODB.Command

Dim objConn As New ADODB.Connection

'///// Variable to hold constructed text

Dim strReturnString As String

'///// DB Value storage array variable

Dim vRecordArray As Variant

'///// Title Array Constant and Variable

Const ARRAY_NOTE_ID_INDEX = 0

Const ARRAY_TITLE_INDEX = 1

Dim lngArrayCount As Long

Dim lngIndexCount As Long

'///// OPEN DATABASE ///////////////////////////////////////////

objConn.Open strDbConnectionString

objConn.BeginTrans

'///// GET NOTE TITLES FROM DB AND PLACE IN GetRows ////////////

sql = "SELECT NoteID, NoteTitle FROM NoteTable"

objCmd.CommandText = sql

objCmd.CommandType = adCmdText

Set objCmd.ActiveConnection = objConn

Rs.Open objCmd

'///// Populate a Variant array via the GetRows() method

vRecordArray = Rs.GetRows

'///// Close this Recordset

Rs.Close

'--> ANY OTHER DATABASE CODE GOES HERE

'///// FINISH AND CLOSE UP DATABASE PROCESSES //////////////////

objConn.CommitTrans

objConn.Close

Set Rs = Nothing

Set objCmd = Nothing

Set objConn = Nothing

'--> OTHER METHOD CODE GOES HERE

'///// Get the number of titles stored in the array

lngArrayCount = UBound(vRecordArray, 2)

'///// Loop through array

For lngIndexCount = 0 To lngArrayCount

'///// Get data from the array populated by the GetRows() method

strReturnString = strReturnString & "NoteID = "

strReturnString = strReturnString & vRecordArray(ARRAY_NOTE_ID_INDEX, lngIndexCount)

strReturnString = strReturnString & " NoteText = "

strReturnString = strReturnString & vRecordArray(ARRAY_TITLE_INDEX, lngIndexCount)

strReturnString = strReturnString & " " & vbCrLf

'///// SEND BACK CONSTRUCTED STRING /////////////////////////////

ShowNotes = strReturnString

End Function

Notice that the "vRecordArray" variable (shown below), which is used to store the records fields returned by the GetRows() method in a multidimensional array, is of type Variant. To make our multidimensioned array data easy to read, two constants (ARRAY_NOTE_ID_INDEX and ARRAY_TITLE_INDEX) will be used to identify the location of the two database fields within the array. Two other variables (lngArrayCount and lngIndexCount) will be used to: 1) hold a value representative of the number of records in the array and; 2) as an indexer used when looping through the array.

'///// DB Value storage array variable

Dim vRecordArray As Variant

'///// Title Array Constant and Variable

Const ARRAY_NOTE_ID_INDEX = 0

Const ARRAY_TITLE_INDEX = 1

Dim lngArrayCount As Long

Dim lngIndexCount As Long

Here's the easiest part. Once the database objects are all in place, and the SQL statement is used to open the Command object, only one line of code is needed to assign the Rs.GetRows() method to the Variant variable.

'///// Populate a Variant array via the GetRows() method

vRecordArray = Rs.GetRows

The only moderately tricky aspect to using the GetRows() method is indexing the array stored in vRecordArray, where the database record field values can now be found. But our constants will make this easier to do. VRecordArray is a variable name used to store an array. The nature of the array assigned to the vRecordArray Variant variable will depend on the structure of the SQL statement assigned to the database Command object. Since the NoteID and NoteTitle field values are the fields designated in the SQL statement (SELECT NoteID, NoteTitle FROM NoteTable), the vRecordArray will hold a two-dimensional array. The first dimension corresponds to the record values of the NoteID field and the second to the values of the NoteTitle field. Obviously, the order and number of record fields within the SQL statement will correspond to the dimensional indexing of the vRecordArray.

The records returned from the NoteTitle table of our example database, via the SQL statement used here, hold the following values:

SELECT NoteID, NoteTitle FROM NoteTable

NoteIDNoteTitle

1Intro

2Style Tables

3GetRows()

4Buffering

5Transactions

The vRecordArray holds a zero-based array with the first array dimension corresponding to the database fields, which are the NoteID and NoteTitle fields in our example code. NoteID will have an index of "0", since it was the first field to be returned in the SQL statement, and NoteTitle will have an index of "1". The second dimension of the array will index the number and order of the NoteID and NoteTitle record fields values that were returned, starting with "0". This all sounds more complex than it is. Here are the corresponding array indexes that match the database field values of the tables displayed above.

sql = "SELECT NoteID, NoteTitle FROM NoteTable"

NoteIDNoteTitle

-------------------------------------------------------------------------

vRecordArray(0, 0) = 1vRecordArray(1, 0) = "Intro"

vRecordArray(0, 1) = 2vRecordArray(1, 1) = "Style Tables"

vRecordArray(0, 2) = 3vRecordArray(1, 2) = "GetRows()"

vRecordArray(0, 3) = 4vRecordArray(1, 3) = "Buffering"

vRecordArray(0, 4) = 5vRecordArray(1, 4) = "Transactions "

Since the ones and zeros can be confusing, we can replace them with the constants that were declared at the start of our code. This makes the code more readable, albeit more verbose.

Const ARRAY_NOTE_ID_INDEX = 0

Const ARRAY_TITLE_INDEX = 1

NoteIDNoteTitle

-------------------------------------------------------------------------

vRecordArray(ARRAY_NOTE_ID_INDEX, 0) = 1vRecordArray(ARRAY_TITLE_INDEX, 0) = "Intro"

vRecordArray(ARRAY_NOTE_ID_INDEX, 1) = 2vRecordArray(ARRAY_TITLE_INDEX, 1) = "Style Tables"

vRecordArray(ARRAY_NOTE_ID_INDEX, 2) = 3vRecordArray(ARRAY_TITLE_INDEX, 2) = "GetRows()"

vRecordArray(ARRAY_NOTE_ID_INDEX, 3) = 4vRecordArray(ARRAY_TITLE_INDEX, 3) = "Buffering"

vRecordArray(ARRAY_NOTE_ID_INDEX, 4) = 5vRecordArray(ARRAY_TITLE_INDEX, 4) = "Transactions "

Since all our database information is tightly stored away in our vRecordArray variable, we'll close up our database connection and loop through the array to send back some example demonstration code as a first test of our GetRows() array.

The first thing to do is store the value of the last index of our array in the lngArrayCount variable. This gives us a numerical value of were the array ends (see the code below). Remember, this array is zero based, so the value of lngArrayCount will really be one minus the actual number of items within the array.

'///// Get the number of titles stored in the array

lngArrayCount = UBound(vRecordArray, 2)

Next, we'll loop through our array while storing the string concatenations in the strReturnString variable.

'///// Loop through array

For lngIndexCount = 0 To lngArrayCount

'///// Get data from the array populated by the GetRows() method

strReturnString = strReturnString & "NoteID = "

strReturnString = strReturnString & vRecordArray(ARRAY_NOTE_ID_INDEX, lngIndexCount)

strReturnString = strReturnString & " NoteText = "

strReturnString = strReturnString & vRecordArray(ARRAY_TITLE_INDEX, lngIndexCount)

strReturnString = strReturnString & " " & vbCrLf

The code above will construct a single string, held in strReturnString, with the value displayed below.

NoteID = 1 NoteText = Intro

NoteID = 2 NoteText = Style Tables

NoteID = 3 NoteText = GetRows()

NoteID = 4 NoteText = Buffering

NoteID = 5 NoteText = Transactions

This VB constant vbCrLf that is assigned to the last line of the code within the loop, and directly after the string value, will produce a Carriage Return/LineFeed that places a line break at the end of each record line of text for easy HTML source-code reading.

GetRows() Error Code

The error code that we'll add to our new database code using the GetRows() method will look very similar to the error code that was discussed above.

Adding Error Handling Code With GetRows()

'///// Check if any data was returned

If Rs.EOF = True And Rs.BOF = True Then

'///// No data returned, set error information, raise error

Err.Number = 40000

Err.Source = "NoteProject::NoteClass::ShowNotes"

Err.Description = "00000001 NoteID and NoteTitle database select statement failure " & sql

Err.Raise Err.Number, Err.Source, Err.Description

Else

'///// Data returned - NoteID and NoteTitle go into vRecordArray

vRecordArray = Rs.GetRows

End If

We basically assign a different error number (40000) and a different error description to the error code. If the SQL statement fails to return a record, this error message will be the one that's returned.

Presenting with Style

Since our method is designed for use from ASP files, we'll want to return a string that is browser ready. The details of the database table (NoteStyleTable), which contains the fields used as the HTML tag values, were covered earlier in "The Database Tables" section.

The string that's returned from the ShowNotes() method will contain two HTML TABLE data records, one containing the note titles and the other note text. So we'll need to enclose the method call within HTML TABLE record tags within our ASP file. You can design the style table to include fields for the TABLE and TR tags, thus returning an entire table rather than just the record data code. But I found that changes to the TABLE and TR tags are much more frequent and having them in the ASP file makes changes quicker to get to. You can always sneak the TABLE and TR tags within the database table structure that's described here, if you decide you want an entire HTML table returned rather that just the HTML table record data.

ASP file code that places the method in HTML TABLE record tags

<TABLE>

<TR>

'--> Method call goes here

</TR>

</TABLE>

We need to write method code that will supply the HTML values that are used to format our database information and which will be inserted in the ASP file at the method call's location. The eight variables for the HTML formatting will either be assigned default values within the method or retrieved from the database. When the intStyleID method parameter is set to zero, the default style values will be used. If the value of intStyleID is above zero, it will be used to retrieve the correct style database record values.

For this section of example code, the process of assigning the default values and, alternatively, retrieving the HTML values from the database will be demonstrated. For now we'll leave out the code that supplies our note database information and just return a method string that contains the HTML formatting of the style variable values. Once this section is covered, we'll use the HTML formatting values to demonstrate how to construct the actual HTML return string that contains the database note information.

The style code structure is basic If/Then code. Here's the gist of it.

If intStyleID = 0 Then

'--> ASSIGN DEFAULT VAULES TO THE HTML STYLE VARIABLES

Else

'--> OPEN A DATABASE RECORDSET

'--> RETRIEVE VALUES FROM DATABASE AND ASSIGN THEM TO THE HTML STYLE VARIABLES

'--> CLOSE THE DATABASE RECORDSET

End If

Here's the code that fills in this structure.

Code for Using Presentation Styles

Public Function ShowNotes(ByVal lngNoteID As Long, _

Optional ByVal intStyleID As Integer = 0, _

Optional ByVal strURL As String = "NoteExample.asp", _

Optional ByVal strDbConnectionString As String = "NotesDSN") As String

'///// DIM AND INIT ////////////////////////////////////////////

'///// DB Connectivity Variables

Dim sql As String

Dim Rs As New ADODB.Recordset

Dim objCmd As New ADODB.Command

Dim objConn As New ADODB.Connection

'///// Variable to hold constructed text

Dim strReturnString As String

'///// Note Style Properties

Dim strTitleDataStart As String

Dim strTitleFontStart As String

Dim strTitleFontEnd As String

Dim strTitleDataEnd As String

Dim strTextDataStart As String

Dim strTextFontStart As String

Dim strTextFontEnd As String

Dim strTextDataEnd As String

'///// OPEN DATABASE ///////////////////////////////////////////

objConn.Open strDbConnectionString

objConn.BeginTrans

'--> ANY OTHER DATABASE CODE GOES HERE

'///// SET NOTE STYLE PROPERTIES ///////////////////////////////

If intStyleID = 0 Then

'///// SET NOTE STYLE TO DEFAULT VALUES

'///// Note Title Default Style Values

strTitleDataStart = "<TD WIDTH=""74"" NOWRAP VALIGN=""top"">"

strTitleFontStart = ""

strTitleFontEnd = ""

strTitleDataEnd = "</TD>"

'///// Note Text Default Style Values

strTextDataStart = "<TD WIDTH=""124"" VALIGN=""top"" BGCOLOR=""#EFEFEF"">"

strTextFontStart = ""

strTextFontEnd = ""

strTextDataEnd = "</TD>"

Else

'///// SET NOTE STYLE FROM VALUES IN DATABASE

sql = "SELECT * FROM NoteStyleTable WHERE intStyleID = " & intStyleID

objCmd.CommandText = sql

objCmd.CommandType = adCmdText

Set objCmd.ActiveConnection = objConn

Rs.Open objCmd

'///// Note title database style values

strTitleDataStart = "" & Rs.Fields("strTitleDataStart")

strTitleFontStart = "" & Rs.Fields("strTitleFontStart")

strTitleFontEnd = "" & Rs.Fields("strTitleFontEnd")

strTitleDataEnd = "" & Rs.Fields("strTitleDataEnd")

'///// Note text database style values

strTextDataStart = "" & Rs.Fields("strTextDataStart")

strTextFontStart = "" & Rs.Fields("strTextFontStart")

strTextFontEnd = "" & Rs.Fields("strTextFontEnd")

strTextDataEnd = "" & Rs.Fields("strTextDataEnd")

'///// Close this Recordset

Rs.Close

End If

'///// FINISH AND CLOSE UP DATABASE PROCESSES //////////////////

objConn.CommitTrans

objConn.Close

Set Rs = Nothing

Set objCmd = Nothing

Set objConn = Nothing

'--> OTHER METHOD CODE GOES HERE

'///// TEMPARY RETURN STRING EXAMPLE

strReturnString = strTextDataStart

strReturnString = strReturnString & strTextFontStart

strReturnString = strReturnString & "Test"

strReturnString = strReturnString & strTextFontEnd

strReturnString = strReturnString & strTextDataEnd

strReturnString = strReturnString & strTitleDataStart

strReturnString = strReturnString & strTitleFontStart

strReturnString = strReturnString & "Another Test"

strReturnString = strReturnString & strTitleFontEnd

strReturnString = strReturnString & strTitleDataEnd

'///// SEND BACK CONSTRUCTED STRING /////////////////////////////

ShowNotes = strReturnString

End Function

The intStyleID is the second method parameter that is sent from the calling ASP file code. It's an optional method parameter with a default value of zero, if no value is sent to the method (or deliberately set to zero). We'll use the default value as a way of telling whether the developer using the method wants to use a customized HTML format that is stored in the database.

But first, the code assigns the default values to the HTML style variables if intStyleID is set to zero.

'///// SET NOTE STYLE PROPERTIES ///////////////////////////////

If intStyleID = 0 Then

'///// SET NOTE STYLE TO DEFAULT VALUES

'///// Note Title Default Style Values

strTitleDataStart = "<TD WIDTH=""74"" NOWRAP VALIGN=""top"">"

strTitleFontStart = ""

strTitleFontEnd = ""

strTitleDataEnd = "</TD>"

'///// Note Text Default Style Values

strTextDataStart = "<TD WIDTH=""124"" VALIGN=""top"" BGCOLOR=""#EFEFEF"">"

strTextFontStart = ""

strTextFontEnd = ""

strTextDataEnd = "</TD>"

The code also conditionally sets the HTML style variables from a database record if intStyleID is set to a value other than zero.

Else

'///// SET NOTE STYLE FROM VALUES IN DATABASE

sql = "SELECT * FROM NoteStyleTable WHERE intStyleID = " & intStyleID

objCmd.CommandText = sql

objCmd.CommandType = adCmdText

Set objCmd.ActiveConnection = objConn

Rs.Open objCmd

'///// Note title database style values

strTitleDataStart = "" & Rs.Fields("strTitleDataStart")

strTitleFontStart = "" & Rs.Fields("strTitleFontStart")

strTitleFontEnd = "" & Rs.Fields("strTitleFontEnd")

strTitleDataEnd = "" & Rs.Fields("strTitleDataEnd")

'///// Note text database style values

strTextDataStart = "" & Rs.Fields("strTextDataStart")

strTextFontStart = "" & Rs.Fields("strTextFontStart")

strTextFontEnd = "" & Rs.Fields("strTextFontEnd")

strTextDataEnd = "" & Rs.Fields("strTextDataEnd")

'///// Close this Recordset

Rs.Close

End If

The database code should be more than familiar to you now. All the fields from the NoteStyleTable are being retrieved from the record that is indicated by the intStyleID value. The GetRows() method is not used since we're not looping through a set of records. Notice that this code lacks the error-handling code demonstrated in the note title and text database code. It's left out here for clarity sake, but you'd want to include error handling since a nonexistent intStyleID value could be sent.

Here's the string that is returned from this example code when the default style values are used.

The above code returns the following:

Test

</TD>

Another Test

</TD>

Another option is to have multiple default settings that are checked with ElseIf statements within the If/Then code. By doing this, a developer can set the intStyleID method parameter to negative values (-1, -2, -3, etc.) for various other default settings and positive values (1, 2, 3, etc.) for customizable settings that are stored in the database.

Constructing the Return String

The code fragment in this section uses concatenation to build up the string that fuses the HTML style variable values and the database note information. In the next code section, string buffering will be demonstrated as an optional, and optimal, replacement for VB's native string concatenation.

The code here assigns the VB construct vbCrLf to the ends of each HTML tag so that the HTML source code will be easier to read. You can remove these line breaks to save a few bits of HTML source code space at the expense of clarity. They are optional.

The basic structure of this code appears below. Notice that the first table we construct is the note titles table. This is where our vRecordArray will be used. Since multiple note titles are retrieved from the database, we'll loop through them while constructing an <A> HTML tag with a query string set to each title's NoteID. We'll also omit the <A> tags for the title that was selected by the user.

CONSTRUCT THE NOTE TITLES TABLE

Include the beginning table data and font tags

Loop through the vRecordArray array

Include an <A> tag if not the selected NoteID

Include the note title text from the array

Include an </A> tag if not the selected NoteID

End the title line with a line break

End looping

Include the ending font and table data tags

CONSTRUCT NOTE TEXT BODY

Include the beginning table data and font tags

Include the note text body

Include the ending font table data tags

The second table, which displays the selected note text, is much simpler and basically combines the HTML style formatting with the note text value retrieved from the database.

Here's the code fragment that constructs the return string composed of the database note information and the HTML styling. This code section will be placed after all the database work has been completed. Since all the database values have been tucked away in local variables (strTextNote and vRecordArray), the database can be closed before this string construction code.

Code Fragment for Using Concatenation to Construct a Return String

'///// CONSTRUCT NOTE TITLES ///////////////////////////////////

'///// Include the beginning table data and font tags

strReturnString = strTitleDataStart & vbCrLf

strReturnString = strReturnString & strTitleFontStart & vbCrLf

'///// Get the number of titles stored in the array

lngArrayCount = UBound(vRecordArray, 2)

'///// Set selected NoteID to array index (for not inserting the <A> tag)

lngNoteIdIndex = (lngNoteID - 1)

'///// Loop through array

For lngIndexCount = 0 To lngArrayCount

'///// Include an <A> tag if not the selected NoteID

If Not lngNoteIdIndex = lngIndexCount Then

strReturnString = strReturnString & "<A HREF=""" & strURL

strReturnString = strReturnString & "?ID=" & vRecordArray(ARRAY_NOTE_ID_INDEX, lngIndexCount) & """>"

End If

'///// Include the note title text from the array

strReturnString = strReturnString & vRecordArray(ARRAY_TITLE_INDEX, lngIndexCount)

'///// Include an </A> tag if not the selected NoteID

If Not lngNoteIdIndex = lngIndexCount Then strReturnString = strReturnString & "</A>" & vbCrLf

'///// End the title line with a line break

strReturnString = strReturnString & " " & vbCrLf

'///// Include the ending font and table data tags

strReturnString = strReturnString & strTitleFontEnd & vbCrLf

strReturnString = strReturnString & strTitleDataEnd & vbCrLf & vbCrLf

'///// CONSTRUCT NOTE TEXT BODY ////////////////////////////////

'///// Include the beginning table data and font tags

strReturnString = strReturnString & strTextDataStart & vbCrLf

strReturnString = strReturnString & strTextFontStart & vbCrLf

'///// Include the note text body

strReturnString = strReturnString & strNoteText & vbCrLf

'///// Include the ending font table data tags

strReturnString = strReturnString & strTextFontEnd & vbCrLf

strReturnString = strReturnString & strTextDataEnd & vbCrLf

'///// SEND BACK CONSTRUCTED STRING /////////////////////////////

ShowNotes = strReturnString

After the first two HTML style variables are set (strTitleDataStart and strTitleFontStart), the code declares a local variable (lngNoteIdIndex) that will be used within the loop to see if the note that is currently being itinerated is the same note that was selected by the user. The user-sent lngNoteID is subtracted by one and stored in this lngNoteIdIndex variable. It is subtracted by one because our array is zero based and the database table starts with one rather than zero.

'///// Set selected NoteID to array index (for not inserting the <A> tag)

lngNoteIdIndex = (lngNoteID - 1)

We itinerate through the array by starting at the first item in the array, set at zero, and end with the last item in the array. The lngArrayCount variable is used to indicate the end of the array by using the VB UBound() method.

'///// Loop through array

For lngIndexCount = 0 To lngArrayCount

The first thing to do within the record-array looping code is to check if the NoteID stored in the array, and currently being processed by the loop, matches the NoteID selected by the user. If it's not the user-selected NoteID, then we need to construct an HTML <A> tag that will link back to the ASP file with a query string that indicates the NoteID value of the title. An "If Not lngNoteIdIndex = lngIndexCount Then" statement will fulfill this need since the lngIndexCount variable value contains the current loop count.

'///// Include an <A> tag if not the selected NoteID

If Not lngNoteIdIndex = lngIndexCount Then

strReturnString = strReturnString & "<A HREF=""" & strURL

strReturnString = strReturnString & "?ID=" & vRecordArray(ARRAY_NOTE_ID_INDEX, lngIndexCount) & """>"

End If

Within the If/Then statement the strURL is concatenated to the first part of the <A> tag and then a query string named "ID" is assigned the NoteID valued from the vRecordArray. Double quotations marks are used to include quotation marks for the file name within the HTML <A> tag.

The two lines of code within this If/Then process result in the following string, given that the current loop count is at 3 and it isn't the note that was selected by the user.

Since the <A> tag has been constructed, we'll add in the note title values.

'///// Include the note title text from the array

strReturnString = strReturnString & vRecordArray(ARRAY_TITLE_INDEX, lngIndexCount)

We again check to see if the current note within the loop is other than the user-selected note and end the <A> tag. An optional vbCrLf is added to the string for clearer HTML source-code reading.

'///// Include an </A> tag if not the selected NoteID

If Not lngNoteIdIndex = lngIndexCount Then strReturnString = strReturnString & "</A>" & vbCrLf

We'll also add a so the note titles won't all end up on one line within the HTML table record.

'///// End the title line with a line break

strReturnString = strReturnString & " " & vbCrLf

This HTML table ends by adding the ending two HTML style variables (strTitleFontEnd and strTitleDataEnd) to our return string.

The second HTML table record is much simpler since it basically involves adding the two HTML style variables (strTextDataStart and strTextFontStart), the variable containing the selected note text (strNoteText), and the last two HTML style variables (strTextFontEnd and strTextDataEnd).

'///// Include the beginning table data and font tags

strReturnString = strReturnString & strTextDataStart & vbCrLf

strReturnString = strReturnString & strTextFontStart & vbCrLf

'///// Include the note text body

strReturnString = strReturnString & strNoteText & vbCrLf

'///// Include the ending font table data tags

strReturnString = strReturnString & strTextFontEnd & vbCrLf

strReturnString = strReturnString & strTextDataEnd & vbCrLf

After that, the return string is ready to send back to the calling code.

'///// SEND BACK CONSTRUCTED STRING /////////////////////////////

ShowNotes = strReturnString

Optimizing by Using String Buffering

Before you look at the code that combines all the various code sections we covered, there's one issue that needs to be mentioned. Although the example program is not in need of optimization, you may find that concatenating many strings within looping code can noticeably slow down your method's efficiency. String buffer can significantly increase processing speed by creating a large string and inserting other shorter strings with the Mid$() method.

Since you can find a more complete explanation of string buffering (see "How To Utilize String Buffering Within A VB Component" at http://www.dougdean.com for an article on string buffering), I'll just briefly review the buffer code and demonstrate how it can be used to construct the return string used in the ShowNotes() method.

To use the string buffering technique with our example method, we'll need a second method. This second method, named S1 in the code below, will be used to combine all the strings and variables we concatenated in the last code section. The code below uses cryptic method and variable names (S1, S2, S3) for string buffering in order to make the code more concise. Again, refer to the string-buffering article mentioned above for further details.

This code section uses the ShowNotes() method structure, without any extraneous string-buffering code, to exemplify building a simple string by sending string fragments to the S1() method.

'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

' ShowNotes CLASS

'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

Public Function ShowNotes(ByVal lngNoteID As Long, _

Optional ByVal intStyleID As Integer = 0, _

Optional ByVal strURL As String = "NoteExample.asp", _

Optional ByVal strDbConnectionString As String = "NotesDSN") As String

'///// String buffering variables and values

Dim S2 As String

S2 = String$(65536, Chr(0))

Dim S3 As Long

S3 = 0

S1 S2, S3, "This is an "

S1 S2, S3, "example of "

S1 S2, S3, "using the string-buffering "

S1 S2, S3, "method in place of "

S1 S2, S3, "concatenation"

'///// SEND BACK CONSTRUCTED TEXT IN BUFFER ////////////////////

ShowNotes = Left$(S2, S3)

End Function

'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

' STRING BUFFERING

' S1 = Method / S2 = Buffer / S3 = Buffer Length

'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

Private Sub S1(ByRef S2 As String, ByRef S3 As Long, ByVal AddString As String)

Dim strTemp As String

Dim lngLoop As Long

'///// Empty strings will cause a fatal error if not eliminated

If Not Trim$(AddString) = "" Then

'[1]///// DOES BUFFER NEED TO BE INCREASED?

If S3 + Len(AddString) > Len(S2) Then

'///// STORE S2

strTemp = S2

'///// Increase memory storage bytes

lngLoop = lngLoop + 1

If (Len(S2) + (65536 * lngLoop)) >= (S3 + Len(AddString)) Then

Exit Do

End If

Loop

'///// Resize buffer

S2 = String$(Len(S2) + (65536 * lngLoop), Chr(0))

'///// RESTORE S2

Mid$(S2, 1, S3) = strTemp

End If

'[2]///// ADD STRING TO BUFFER

Mid$(S2, S3 + 1, Len(AddString)) = AddString

'[3]///// SET STRING LENGTH IN BUFFER

S3 = S3 + Len(AddString)

End If

End Sub

'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\Running the code above will produce the following string:

This is an example of using the string-buffering method in place of concatenation

I'll let you play around with this code on your own since the article mentioned above should provide a conceptual understanding of what's happening here. But, so you won't have to apply a string buffer to the example ShowNotes() method code yourself, here's the same code fragment covered in this article's "Constructing the Return String" section, but using string buffering rather than concatenation. The entire ShowNotes() sample code is also available optimized with string buffering (STEVE: TO EDITOR: Link to the ShowNotesClassOptimizedCode.doc text as a HTML file).

Code Fragment for Using String Buffering to Construct a Return String

'///// CONSTRUCT NOTE TITLES ///////////////////////////////////

'///// Include the beginning table data and font tags

S1 S2, S3, strTitleDataStart

S1 S2, S3, strTitleFontStart

'///// Get the number of titles stored in the array

lngArrayCount = UBound(vRecordArray, 2)

'///// Set selected NoteID to array index (for not inserting the <A> tag)

lngNoteIdIndex = (lngNoteID - 1)

'///// Loop through array

For lngIndexCount = 0 To lngArrayCount

'///// Include an <A> tag if not the selected NoteID

If Not lngNoteIdIndex = lngIndexCount Then

S1 S2, S3, "<A HREF="""

S1 S2, S3, strURL

S1 S2, S3, "?ID="

S1 S2, S3, vRecordArray(ARRAY_NOTE_ID_INDEX, lngIndexCount)

S1 S2, S3, """>"

End If

'///// Include the note title text from the array

S1 S2, S3, vRecordArray(ARRAY_TITLE_INDEX, lngIndexCount)

'///// Include an </A> tag if not the selected NoteID

If Not lngNoteIdIndex = lngIndexCount Then S1 S2, S3, "</A>"

'///// End the title line with a line break

S1 S2, S3, " "

'///// Include the ending font and table data tags

S1 S2, S3, strTitleFontEnd

S1 S2, S3, strTitleDataEnd

'///// CONSTRUCT NOTE TEXT BODY ////////////////////////////////

'///// Include the beginning table data and font tags

S1 S2, S3, strTextDataStart

S1 S2, S3, strTextFontStart

'///// Include the note text body

S1 S2, S3, strNoteText

'///// Include the ending font table data tags

S1 S2, S3, strTextFontEnd

S1 S2, S3, strTextDataEnd

'///// SEND BACK CONSTRUCTED TEXT IN BUFFER ////////////////////

ShowNotes = Left$(S2, S3)

Conclusion

The code examples in this article are the main essential processes needed to write large server-side applications like my own EZsite Forum DEV and EZsite Daily Planner. Both applications are available as free trial downloads at my site, http://www.dougdean.com. Please feel free to download either application, without obligation, and explore how the ASP code and method signatures work together. Both trial applications have documented method descriptions that incorporate the processes described in this article.

I also want to acknowledge the many programmers and developers who contact me with information and suggestions about the articles I have written. Your comments are appreciated and I feel fortunate that I can have a venue to 'brain-dump' the server-side information that I acquire.

One other thought I'd like to share is the issue of transition to Microsoft's .NET. At the time this article was published, Beta 2 had just been released and books, magazines, and conferences have all dedicated themselves to the .NET environment. I'm very happy with C# and have already started coding an application for the .NET environment. One question on my mind was answered at recent conference. Simply stated, the recommendation was to use C# for .NET component development and stay exclusively with VB for COM development. Not mixing the new and old VBs hits a chord with me and I wanted to pass it on to any readers who may share the same intuitions I have about the future direction of server-side component development. COM components written with VB6 will be necessary for quite a while and the thought of having to keep track of two versions of VB inside my head is disconcerting, to say the least. I, for one, will use be using C# for .NET server-side component development and VB6 for server-side COM development.

About the Author

Doug Dean lives in southern California where he manages Doug Dean Software, Inc. His company is dedicated to creating server-side component products. His product line also includes EZsite Forum DEV, EZsite Daily Planner, EZsite Forum 3, EZsite Calendar, and EZsite WebNotes. Other helpful articles on ASP/VB component issues can be found at http://www.dougdean.com. Contact Doug Dean at dougdean@deltanet.com.