How To Utilize String Buffering Within A VB Component, Part 2

In Part 1 we looked, from a high-level perspective, how to create a VB component that efficiently creates a large HTML TABLE based on Recordset data and passes it back to an ASP page for display. In this part we'll focus in on some of the details!

The MethodName() Method
We're creating two methods within our Class. The MethodName(), is very similar to the method explored in the article "How To Pass a Variant Array Populated with a RecordSet From a VB Component to an ASP File". Since the MethodName() method is fully covered in this previous article, I'll only focus on modifications needed for it to work with our DoBuffer() method.

Public Function MethodName(ByVal strDbConnectionString As String, _ ByVal strSQL As String, _ ByVal intFieldCount As Integer) As String

One parameter was added to the MethodName() method, intFieldCount. This variable will hold the number of fields we will be selecting in our SQL statement. It will also be the number of TABLE record data cells that will be displayed in the browser.

The first variables we declare in the MethodName() method will be the ones we send to the DoBuffer() method. The first variable (strBuffer) is a string that we'll set to a determined length. The MethodName() method will call the DoBuffer() method multiple times using strBuffer as it's first method parameter. The strBuffer variable will be used as a memory buffer for concatenating our strings. The DoBuffer() method defined this parameter as ByRef rather than ByVal so it will come back to the MethodName() method altered.

After declaring strBuffer as type String, we dimension it to a specific length and filled it with NULL characters.

Dim strBuffer As String strBuffer = String$(65536, Chr(0))

The length you set strBuffer to should be determined by how much buffering space you anticipate needing. We'll set it at 65536, which is much higher than needed for this example - but reasonable for a lengthy HTML TABLE record. Since the speed saved in avoiding using the & operant increases with the frequency in which it's used, I'll assume that you'll be using the DoBuffer() method when you have to concatenate many strings, i.e., when your database has enough records to make a large HTML TABLE.

The second parameter (lngBuffer) of the DoBuffer() method is used to keep track of the length of the data we'll be placing into the buffer. We declare this variable in the MethodName() method as type Long and set it to zero.

Dim lngBuffer As Long lngBuffer = 0

The lngBuffer variable is also set as ByRef by the DoBuffer() method definition. This variable value will reflect the length of the stored data within the buffer. We need this variable, like our strBuffer variable, to be modifiable by the DoBuffer() method. Thus a reference to this variable is sent rather than the value itself. It will come back to us changed.

The third, and last, parameter we'll be sending to the DoBuffer() method will hold the string we want to concatenate to the data that's being held in the buffer. As we build our HTML TABLE record, the buffer will accumulate the string fragments that will, when we're done, compose our HTML TABLE record. The DoBuffer() method accepts the AddString variable ByVal. This is done primarily because the strings you'll be sending to the DoBuffer() method will be relatively short.

The strings we'll be sending to our DoBuffer() method will be a combination of string literals, Integers changed to strings via CStr(), and string data from our database. As I mentioned previously, I'll only minimally focus on the MethodName() method since its basic workings are covered in another article within this series. Here, in a nut shell, is the MethodName() code that follows the declarations covered above.

'~~~~~ Variable to hold database array Dim vRecordArray As Variant '~~~~~ Set ADO objects Dim oRs As New ADODB.Recordset Dim oCmd As New ADODB.Command Dim oConn As New ADODB.Connection '~~~~~ Index variables for constructing HTML string Dim lngRecordCount As Long Dim lngRecordIndex As Long Dim intFieldCount As Integer Dim intFieldIndex As Integer '~~~~~ Open Database with method argument oConn.Open strDbConnectionString '~~~~~ Assign values to ADO objects oCmd.CommandText = strSQL oCmd.CommandType = adCmdText Set oCmd.ActiveConnection = oConn '~~~~~ Open the RecordSet oRs.Open oCmd '~~~~~ Assign RecordSet to Variant Array vRecordArray = oRs.GetRows '~~~~~ Close RecordSet/Connection oRs.Close oConn.Close '~~~~~ Set objects to nothing Set oRs = Nothing Set oCmd = Nothing Set oConn = Nothing

The MethodName() code above basically collects data from the same database structure used in the "How To Pass a Variant Array Populated with a RecordSet From a VB Component to an ASP File" article. The major difference is in the assignment of the GetRows() values to a local Variant variable (vRecordArray) rather than the method name as in the last article.

Once the data from the database is stored in the vRecordArray Variant variable array, we'll loop through it within the component rather than sending it back to the ASP file. To help us do this, we'll store the record count of this (zero based) array in a Long variable (lngRecordCount).

lngRecordCount = UBound(vRecordArray, 2)

Now we're ready to start constructing our HTML TABLE record with the database data. This is accomplished with outside and inside loops. The outside loop will loop through each database record while the inside loop will loop through each database field. Each database field value will be send to the DoBuffer() method to be added to the buffer before sending the buffer data back to the ASP file.

'***** OUTSIDE LOOP ***** For lngRecordIndex = 0 To lngRecordCount DoBuffer strBuffer, lngBuffer, "<TR>" '***** INSIDE LOOP ***** ' Loop through each field For intFieldIndex = 0 To intFieldCount - 1 DoBuffer strBuffer, lngBuffer, "<TD>" DoBuffer strBuffer, lngBuffer, _ CStr(vRecordArray(intFieldIndex, lngRecordIndex)) DoBuffer strBuffer, lngBuffer, "</TD>" Next DoBuffer strBuffer, lngBuffer, "</TR>" ' OPTIONAL STATEMENT FOR INCLUDING A LINEFEED ' IN HTML SOURCE CODE DoBuffer strBuffer, lngBuffer, vbCrLf Next

The statement before the outside loop is used to send a TR tag. I have found it best to send a TABLE record rather than the entire TABLE so the asp file can alter the TABLE attributes easier. Note that the inside loop indexes from 0 to (intFieldCount - 1). This is because the vRecordArray is zero based.

Then opening and closing TD tags sandwich the database data within the inside loop. This gives each TABLE data cell it's own record field. In order to make the resulting HTML source code more readable, you can place a vbCrLf anywhere in the code. This will cause a Control-Linefeed (line break) to occur in the HTML source code. The line break won't be displayed in the browser (but will be displayed if you go to View/Source, and, consequently, will make the resulting easier for a developer to read).

Once the loops are complete, it's a matter of cropping out the string data from our buffer with the following statement:

'~~~~~ Send back HTML as a string MethodName = Left$(strBuffer, lngBuffer)

Also notice that the strings we added to the buffer within the loop were sent with following structure:

BufferMethodName, StringBufferVariable, BufferDataSize "String you want to add to the buffer"

Which translates into the following statement using our method and variable names:

DoBuffer, strBuffer, lngBuffer "String you want to add to the buffer"

I typically go one step further and simplify my method and variable names into something like this:

S1, S2, S3 "String you want to add to the buffer"

This reduces the extra text on my precious screen landscape.

In Part 3 we'll examine the ASP code needed to make our whole project come together!