Lesson 2 : The VB Component
--------------------------------------------------------------------------------
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 = No
