Published: Wednesday, February 14, 2001
Vertical Graphing of Database Data
By Jim Rudnick
Introduction:
Recently a client asked me if I could "graph" their divisional standings on a Web page for their automotive
division. At first thought, this seemed relatively easy, till I got well into the project! Figuring on sourcing
some code for what I needed from the Web led only to a half a day of searching and reading and then downloading of
code that wasn't at all right till I finally realized that I'd have to do the basic work myself...sigh, so much
for easy street on this one!
What follows then herein is a look at what I'd planned and then what code I wrote to enable just such a graphic
layout for the client and how it all came together.
First of all, the graphics. If you're looking at them right now in the screenshot below, you'll note that they are
just terrible, and isn't that the truth. These are substituted graphics from what I used for the client, as is
the small database I've included herein for this article only. Please do realize that you can use your own
graphics for the cars used here -- although you may want to change the paradigm from my own "cars-on-a-garage-hoist"
one to one of your own.
Obtaining the Raw Database Data
Enough said up front, lets move to the code. You will note that I've made a DNS-less connection to the database
that is held in name by the variable called dbName, and I create a select statement (SQL) that asks for
all the data in the dbase table named Table1.
This database table holds only the names of the divisions, and their current percentages. While this is a simple
database, it could be as
involved as you need it in the real world. And while for purposes of ease of explanation, I'm using an Access
database here, the same items could be pulled from an SQL dbase too, if that was what you're using. (You can
download the Access database (along with the support files) at the
end of this article...)
<%@ Language=VBScript %>
<% Option Explicit %>
<% Response.Buffer = TRUE %>
<%
'-- Declare your variables
Dim DataConnection, cmdDC, RecordSet
Dim RecordToDelete, dbName, cnPath, SQL
Dim percentNum, carNum, regionNum, workingNum
Dim divisionNum, bCount, cCount, pCount
Dim arrDivisions(3)
' an array to hold the percentages...
dbname="hoist.mdb"
'set up of database file to use
'-- Create object and open database
Set DataConnection = Server.CreateObject("ADODB.Connection")
cnpath="DBQ=" & server.mappath(dbname)
DataConnection.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & cnpath
'this is a DNSless connection...
Set cmdDC = Server.CreateObject("ADODB.Command")
cmdDC.ActiveConnection = DataConnection
'-- default SQL
SQL = "SELECT * FROM Table1"
cmdDC.CommandText = SQL
Set RecordSet = Server.CreateObject("ADODB.Recordset")
'-- Cursor Type, Lock Type
'-- ForwardOnly 0 - ReadOnly 1
'-- KeySet 1 - Pessimistic 2
'-- Dynamic 2 - Optimistic 3
'-- Static 3 - BatchOptimistic 4
RecordSet.Open cmdDC, , 0, 2
%>
|
So far we've just looked at code to get us started by grabbing the appropriate database table. In
Part 2 we'll continue our examination of this ASP script and look at how
this raw data can be turned into a fancy graph!
Read Part 2!
