To read the article online, visit http://www.4GuysFromRolla.com/webtech/021401-1.shtml

Vertical Graphing of Database Data

By Jim Rudnick


For More Information...
For more information on graphing with ASP be sure to check out our Graphing with ASP Article Index!

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.

The graph representing database data.

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!


  • Article Information
    Article Title: Vertical Graphing of Database Data
    Article Author: Jim Rudnick
    Published Date: Wednesday, February 14, 2001
    Article URL: http://www.4GuysFromRolla.com/webtech/021401-1.shtml


    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers