It starts by checking if the m_TableName variable has a value. Because m_TableName is private, the only way to set its value is to use the public let property TableName, like this:
Set dg1 = new DynamicGrid
Dg1.TableName = "employee"
Recall from earlier in this article that our DynamicGrid class also includes a member named m_RowsPerPage, which can optionally be set using the public let property RowsPerPage. It will be used to control the number of records returned from SQL Server 2000. If it hasn't been given a value, then we assign its value to 10 by default:
If m_RowsPerPage = "" Or IsNumeric(m_RowsPerPage) = False Then
m_RowsPerPage = 10 'Default number of rows
Lastly, we set the value of the m_Initialized variable to true, indicating that the InitializeGrid sub-routine has been called:
m_Initialized = True
The DisplayGrid Function
Public Sub DisplayGrid(TableWidth, BorderWidth, ColumnColor1, ColumnColor2, CanEdit, CanDelete)
Most of the arguments to the DisplayGrid function should be fairly obvious, but here's a list of what they are used for anyway:
TableWidth: The width of the table as it will be written to the browser. Can be either a numerical value such as 750, or a string value such as "75%".
BorderWidth: The width of the HTML table that the routine will create to display the records in.
ColumnColor1: Rows will be displayed in alternating colors so as to help differentiate between them. ColumnColor1 should be a string value that will be used for the color or the rows 1, 3, 5, 7, etc. For example, "#000000", "#3DF132", or "red'.
ColumnColor2: ColumnColor2 should be a string value that will be used for the color or the rows 2, 4, 6, 8, etc. For example, "#FFFFFF", "#130DFA", or "yellow".
CanEdit: A Boolean value indicating whether or not the individual fields in each returned row can be edited. Should be true for yes, false for no.
CanDelete: A Boolean value indicating whether or not each returned row can be deleted. Should be true for yes, false for no.
Let's look at the code for the DisplayGrid routine in detail.
As mentioned earlier, any instance of our DynamicGrid class must call the InitializeGrid method before a call to the DisplayGrid routine is made. If the InitializeGrid routine hasn't been called, then m_Initialized won't be true, and we raise an error informing the user that they must call InitializeGrid first.
Set objConn = GetConnection()
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.LockType = adLockReadOnly
objRS.CursorType = adOpenForwardOnly
objRS.CursorLocation = adUseClient
objRS.ActiveConnection = objConn
objRS.Open "SELECT TOP " & m_RowsPerPage & " * FROM " & m_TableName
Next, we get an ADO connection from the GetConnection method of our DynamicGrid class. We use a recordset object to run a query against our database. Notice in the select query above that the value of the m_RowsPerPage variable is used to only return a certain number of records. The table name for our query is the value of the m_TableName variable.
If we used the public let property methods to set the value of m_RowsPerPage to 20 and m_TableName to "authors", then the select query would look like this:
SELECT TOP 20 FROM authors
intRecordCounter = 0
intFieldCounter = 0
While Not objRS.EOF
For Each objField In objRS.Fields
If intFieldCounter Mod 2 Then
strBGCol = ColumnColor1
strBGCol = ColumnColor2
We've reached the main loop of the DisplayGrid function. We have one while loop that runs through each record in the results set. Notice how two variables, intRecordCounter and intFieldCounter are set to zero? These variables are incremented during the loops, and allow us to count how many records and fields have been displayed so far.
For each record, we also have a for each loop that works with the fields returned by a row. The Fields collection of our recordset object contains the name and value for each field in the record. For example, if I had a table that had two fields: field1, and field2, and I ran this query against that table:
SELECT field1, field2
... then we could get the value of the first field like this:
Sample output from the DisplayGrid method looks like this (In the example, I have specified the employee table of the pubs database. I have also passed true for CanEdit and true for CanDelete):
As you can see, the output to the browser is simple, and it's obvious that to update a field in a record, you simply type the new value into that field's text box and click on the update button. Likewise to remove a record, you click on its delete button.