4GuysFromRolla.com - Creating a Dynamic Bar Chart

.NET Developer
Steinman Recruiting Associates
US-PA-York

Published: Wednesday, April 21, 1999

Creating a Dynamic Bar Chart using a Database Query
By Scott Mitchell

The real power of charts come when the chart can be created dynamically from a database query. This isn't difficult to do with the BarChart routine provided in the previous article.

- continued -

First, we're going to connect to a database, so we need to include ADOVBS.inc. (If you're unfamiliar with adovbs.inc, there is a handy FAQ on the site.) So, let's go ahead and do that, as well as stating our usual two lines of ASP code:

<%@ LANGUAGE="VBSCRIPT" %>
<% Option Explicit %>

<!--#include virtual="/adovbs.inc"-->

I will not reprint the BarChart function; it's avilable, with full explanation, at this URL. You can also download the source for this article. For this article, we will just look at connecting to a database, retrieving records, and populating the data and label arrays with the query results.

So, let's first connect to our database:


'Open connection to database
Dim objConnection
Set objConnection = Server.CreateObject("ADODB.Connection")
objConnection.Open "DSN=Products"

Now we need our query string. What are we going to ask SQL for? In this example I used a database with the following table:

Products
ProductID	Autonumber
Name	Text
UnitsSold	Number

I want to chart out the units sold for each of the products in the database. Here is the SQL string I'm going to use:

Dim strSQL
strSQL = "SELECT Name,UnitsSold FROM Products"

Now, I just need to create a recordset object. I am going to create a recordset object with a Static cursor so I can easily obtain the RecordCount. I will need to know the size of my recordset so I can set the array accordingly. If you are unfamiliar with recordset cursors, there is, if you haven't guessed, an article on the site which deals with cursors. It's available here. In fact, you might want to search the site if you get too confused. :)

So, here is the declaration and creation and opening of our recordset variable:

Dim rsProducts
Set rsProducts = Server.CreateObject("ADODB.Recordset")
rsProducts.Open strSQL, objConnection, adOpenStatic

Now we want to get the number of records returned, and use that information to set our array sizes accoringly. So, first we will use the RecordCount property and then create two arrays. We will then redim the arrays so that they are both the size of the recordset.

Dim numRecords
numRecords = rsProducts.RecordCount

Dim unitsSoldArray(), labelArray()
Redim unitsSoldArray(numRecords)
Redim labelArray(numRecords)

Now we want to initialize the arrays. We basically will just step through our recordset one row at a time, setting the values of the two arrays as we go along. (Again, remember that our arrays are zero-based!)

Dim i
for i = 0 to numRecords-1
	unitsSoldArray(i) = rsProducts("UnitsSold")
	labelArray(i) = rsProducts("Name")
	rsProducts.MoveNext
next
%>

Now that we've created our two arrays, all we have left to do is call the BarChart subroutine. Here is how we could do that:

<HTML>
<BODY>
  <% BarChart unitsSoldArray, labelArray,"Products Sold","Products" %>
</BODY>
</HTML>

Of course we should always explicitly close our recordset and connection objects.

<%
	rsProducts.Close
	Set rsProducts = Nothing

	objConnection.Close
	Set objConnection = Nothing
%>

So there you have it! I hope the article is fairly self-explanatory. You can check out the output of the script. You may want to check out the two attachments to this article. If you have any questions, you can always post them on the messageboard or email me!

Happy Programming!

Attachments:

Bar Chart Example of Static Data Points

Bar Chart Example using Database Results