Home arrow ASP arrow Page 4 - Real-Time Data Grid Part 1/2
ASP

Real-Time Data Grid Part 1/2


In part one of this two-part article Annette, show us how to create an ASP class that allows us to work with our databases through a web browser. It will allow us to update and delete records dynamically using JavaScript and XMLHTTP, without ever having to refresh the page.

Author Info:
By: Annette Tennison
Rating: 4 stars4 stars4 stars4 stars4 stars / 16
February 07, 2002
TABLE OF CONTENTS:
  1. · Real-Time Data Grid Part 1/2
  2. · Project Overview
  3. · The GetConnection function
  4. · The InitializeGrid routine
  5. · Conclusion

print this article
SEARCH DEVARTICLES

Real-Time Data Grid Part 1/2 - The InitializeGrid routine
(Page 4 of 5 )

As with the GetConnection function, the InitializeGrid sub-routine is fairly straightforward and does some basic error checking and variable assignment.

If m_TableName = "" Then

Err.Raise vbObjectError + 1001, "DynamicGrid", "Must initialize variables first"

m_Initialized = False

Exit Sub

End If


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:

Dim dg1

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

End If


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

The last and most complex sub-routine of our DynamicGrid class is DisplayGrid. The DisplayGrid routine actually retrieves the data from SQL server and formats it as HTML based on the values of the arguments passed to it. It also outputs the JavaScript code that works with XMLHTTP to update our database dynamically. Its signature looks like this:

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.

If Not m_Initialized Then

Err.Raise vbObjectError + 1001, "DynamicGrid", "Must call InitializeGrid() first"

End If


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

The next part of the DisplayGrid routine involves outputting some JavaScript to the browser. We will skip it now, and look at it in the second part of this article, tommorow.

<%



intRecordCounter = 0

intFieldCounter = 0



While Not objRS.EOF

...

For Each objField In objRS.Fields

If intFieldCounter Mod 2 Then

strBGCol = ColumnColor1

Else

strBGCol = ColumnColor2

End If

...

Next

objRS.MoveNext

Wend

%>


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

FROM someTable

WHERE 1


... then we could get the value of the first field like this:

objRS.Fields(0).value

Between the for each...next loop, we also determine whether or not to show the update button, which calls a custom JavaScript function to update a record on the server:

<% If CanEdit Then %>

<input id="grid_<%=intRecordCounter%>_<%=intFieldCounter%>" type="text" name="<%=objField.Name%>" value="<%=objField.Value%>" style="width:75%">

<input type="button" value="Update" onClick="doUpdate(<%=intRecordCounter%>, <%=intFieldCounter%>)">

<% Else %>

<font face="verdana" size="1" color="black">

<%=objField.Value%>

</font>

<% End If %>


In the outer while…wend loop (which displays each record), we use the CanDelete argument that has been passed to the DisplayGrid function to determine whether or not to show a button that calls a custom JavaScript function to delete a record on the server:

<% If CanDelete Then %>

<input type="button" value="Delete" onClick="doDel(<%=intRecordCounter%>)">

<% End If %>


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):

Sample output from the DisplayGrid routine

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.
blog comments powered by Disqus
ASP ARTICLES

- Central Scoreboard with Flash and ASP
- Calorie Counter Using WAP and ASP
- Creating PGP-Encrypted E-Mails Using ASP
- Be My Guest in ASP
- Session Replacement in ASP
- Securing ASP Data Access Credentials Using t...
- The Not So Ordinary Address Book
- Adding and Displaying Data Easily via ASP an...
- Sending Email From a Form in ASP
- Adding Member Services in ASP
- Removing Unconfirmed Members
- Trapping HTTP 500.100 - Internal Server Error
- So Many Rows, So Little Time! - Case Study
- XDO: An XML Engine Class for Classic ASP
- Credit Card Fraud Prevention Using ASP and C...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials