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.
First, we're going to connect to a database, so we need to include
(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
<%@ 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:
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
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!