How2Project4.vbp

The DoBuffer() Method
The DoBuffer() method will be sent a string buffer (strBuffer), the value of the length of the string data
already placed within the buffer (lngBuffer), and the string to add to the buffer (AddString). These
arguments will be declared and sent from our MethodName() method, which will be covered later in this
article.

Private Sub DoBuffer(ByRef strBuffer As String, ByRef lngBuffer As Long, ByVal AddString As String)

Notice that the DoBuffer() method is declared as a subroutine rather than a function. This is because it
doesn"t really return any data from the method itself. Rather, it manipulates the data sent by reference
from the calling method.

Two local variables need to be declared for internal use.

Dim strHold As String
Dim lngIndex As Long

Now our first line of business will be to determine whether our buffer is large enough to hold the string
data we want to add to it. But before we do this, we want to check out whether our calling method sent a
NULL string or not.

If Not Trim$(AddString) = "" Then

If the calling method sends a NULL string, we"ll quietly skip the concatenating process and avoid the
whole affair via this If-Then statement. On the other hand, if the sent string holds characters, we"ll
need to check that the buffer is large enough to hold them. To do this the current length of the data in
the buffer is added to the length of the sent string and then the resulting sum is compared to the length
of the overall buffer size.

If lngBuffer + Len(AddString) > Len(strBuffer) Then

If the buffer is large enough to fit the sent string, then the code discussed next will be skipped. This
will occur more often than not since we"ll set our string buffer to hold a large amount of data when we
declare it in the MethodName() method. But lets assume that this isn"t the first time the DoBuffer()
method was called and that the buffer is too small to add the sent string.

We first need to store the existing buffer data in a local String variable:

strHold = strBuffer

Now we"ll go through a Do-Loop to exponentially increase a test of the size of the buffer. Each time
through the loop a new buffer size is tested against the previous buffer size. Once the stored buffer data
and the new string fit the buffer, the loop will be exited.

Do
lngIndex = lngIndex + 1
If (Len(strBuffer) + (65536 * lngIndex)) >= (lngBuffer + Len(AddString)) Then

Exit Do
End If
Loop

The 65536 size increase is arbitrary and you can set this number to what you think will be appropriate for
the total size of the string data you"re building.

The buffer can now be rebuilt to its new expanded size...

strBuffer = String$(Len(strBuffer) & (65536 * lngIndex), Chr(0))

....and refilled with the stored string data that it previously held:

Mid$(strBuffer, 1, lngBuffer) = strHold

Notice that the string we want to add to the buffer hasn"t been added yet. We simply increased the size of
the buffer.

Here"s the code that increases the buffer size if our sent string doesn"t fit into the buffer. It"s
repeated here so you can see it in one glance.

If lngBuffer + Len(AddString) > Len(strBuffer) Then
strHold = strBuffer
Do
lngIndex = lngIndex + 1
If (Len(strBuffer) + (65536 * lngIndex)) >= (lngBuffer + Len(AddString)) Then
Exit Do
End If
Loop
strBuffer = String$(Len(strBuffer) + (65536 * lngIndex), Chr(0))
Mid$(strBuffer, 1, lngBuffer) = strHold
End If

Once the buffering size has past our size test, or actually resized, we can add our sent string to the
data stored in the buffer.

Mid$(strBuffer, lngBuffer + 1, Len(AddString)) = AddString

Now that the sent string (AddString) has been added to the buffer, we need to increase the lngBuffer
variable value to reflect the new length of the buffer"s string data stored in it.

lngBuffer = lngBuffer + Len(AddString)

Here"s a repeat of the code that adds the sent string to the buffer and increases the buffer data size
variable.

If Not Trim$(AddString) = "" Then
"-----> CODE FOR OCCASIONALLY RESIZING BUFFER GOES HERE
Mid$(strBuffer, lngBuffer + 1, Len(AddString)) = AddString
lngBuffer = lngBuffer + Len(AddString)
End If

Mission complete! We added a string to a previously established string buffer without using the
concatenation (&) operant except when we needed to expand the size of the buffer, which should be
infrequently since we"re doing it exponentially and the original buffer size was set to a size that was
large enough for our string data.

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 it"s 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 *****
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, " "

" 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 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.

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

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.