Tiny File Manager

Back Back Up Advanced Editor

<?xml version="1.0" encoding="iso-8859-1"?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>FusionCharts Free Documentation</title>
<link rel="stylesheet" href="Style.css" type="text/css" />
</head>

<body>
<table width="98%" border="0" cellspacing="0" cellpadding="3" align="center">
 <tr> 
 <td><h2 class="pageHeader">Using FusionCharts with ASP &gt; Plotting data from a database </h2></td>
 </tr>
 <tr> 
 <td valign="top" class="text">In this section, we'll show you how to use FusionCharts and ASP to plot charts from data contained in a database. We'll create a pie chart to show &quot;Production by Factory&quot; using: 
 <ul>
 <li>dataXML method first.</li>
 <li>Thereafter, we'll convert this chart to use dataURL method. </li>
 </ul>
 For the sake of ease, we'll use an Access Database. The database is present in Download Package &gt; Code &gt; ASP &gt; DB folder. You can, however, use any database with FusionCharts including MS SQL, Oracle, MySQL etc. 
 Before you go further with this page, we recommend you to please see the previous section &quot;Basic Examples&quot; as we start off from concepts explained in that page. 
 The code examples contained in this page are present in Download Package &gt; Code &gt; ASP &gt; DBExample folder. The Access database is present in Download Package &gt; Code &gt; ASP &gt; DB. </td>
 </tr>
 <tr>
 <td valign="top" class="text">&nbsp;</td>
 </tr>
 <tr>
 <td valign="top" class="header">Database Structure </td>
 </tr>
 <tr>
 <td valign="top" class="text">Before we code the ASP pages to retrieve data, let's quickly have a look at the database structure. </td>
 </tr>
 <tr>
 <td valign="top" class="text"><img src="Images/Code_DB.gif" /></td>
 </tr>
 <tr>
 <td valign="top" class="text">The database contains just 2 tables:
 <ol>
 <li>Factory_Master: To store the name and id of each factory</li>
 <li>Factory_Output: To store the number of units produced by each factory for a given date.</li>
 </ol>
 For demonstration, we've fed some dummy data in the database. Let's now shift our attention to the ASP page that will interact with the database, fetch data and then render a chart. </td>
 </tr>
 <tr>
 <td valign="top" class="text">&nbsp;</td>
 </tr>
 <tr>
 <td valign="top" class="header">Building the ASP Page for dataXML Method </td>
 </tr>
 <tr>
 <td valign="top" class="text">The ASP page for dataXML method example is named as BasicDBExample.asp (in DBExample folder). It contains the following code: </td>
 </tr>
 <tr>
 <td valign="top" class="codeBlock">&lt;%@ Language=VBScript %&gt; 
 &lt;HTML&gt; 
 &nbsp;&nbsp;&nbsp;&lt;HEAD&gt; 
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;TITLE&gt;FusionCharts Free - Database Example&lt;/TITLE&gt; 
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;SCRIPT LANGUAGE=&quot;Javascript&quot; SRC=&quot;../../FusionCharts/FusionCharts.js&quot;&gt;&lt;/SCRIPT&gt; 
 &nbsp;&nbsp;&nbsp;&lt;/HEAD&gt; 
 &nbsp;&nbsp;&nbsp;&lt;!-- #INCLUDE FILE=&quot;../Includes/FusionCharts.asp&quot; --&gt; 
 &nbsp;&nbsp;&nbsp;'Adding library script that will connect to the Access Databse file automatically. 
 &nbsp;&nbsp;&nbsp;&lt;!-- #INCLUDE FILE=&quot;../Includes/DBConn.asp&quot; --&gt; 
 &nbsp;&nbsp;&nbsp;&lt;BODY&gt; 
 &nbsp;&nbsp;&nbsp;&lt;CENTER&gt; 
 &nbsp;&nbsp;&nbsp;&lt;%&nbsp;&nbsp;&nbsp; 
 &nbsp;&nbsp;&nbsp;'In this example, we show how to connect FusionCharts to a database. 
&nbsp;&nbsp;&nbsp;'For the sake of ease, we've used an Access database which is present in 
&nbsp;&nbsp;&nbsp;'../DB/FactoryDB.mdb. It just contains two tables, which are linked to each 
&nbsp;&nbsp;&nbsp;'other. 
 
&nbsp;&nbsp;&nbsp;'Database Objects - Initialization 
 &nbsp;&nbsp;&nbsp;Dim oRs, oRs2, strQuery 
 &nbsp;&nbsp;&nbsp;'strXML will be used to store the entire XML document generated 
 &nbsp;&nbsp;&nbsp;Dim strXML 
 
 &nbsp;&nbsp;&nbsp;'Create the recordset to retrieve data 
 &nbsp;&nbsp;&nbsp;Set oRs = Server.CreateObject(&quot;ADODB.Recordset&quot;)
 &nbsp;&nbsp;&nbsp;'Generate the chart element 
 &nbsp;&nbsp;&nbsp;strXML = &quot;&lt;graph caption='Factory Output report' subCaption='By Quantity' decimalPrecision='0' showNames='1' numberSuffix=' Units' pieSliceDepth='30' formatNumberScale='0'&gt;&quot; 
 
 &nbsp;&nbsp;&nbsp;'Iterate through each factory 
 &nbsp;&nbsp;&nbsp;strQuery = &quot;select * from Factory_Master&quot; 
 &nbsp;&nbsp;&nbsp;Set oRs = oConn.Execute(strQuery) 
 
 &nbsp;&nbsp;&nbsp;While Not oRs.Eof 
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Now create second recordset to get details for this factory 
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Set oRs2 = Server.CreateObject(&quot;ADODB.Recordset&quot;) 
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strQuery = &quot;select sum(Quantity) as TotOutput from Factory_Output where FactoryId=&quot; &amp; ors(&quot;FactoryId&quot;) 
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Set oRs2 = oConn.Execute(strQuery) 
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Generate &lt;set name='..' value='..'/&gt;
 
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strXML = strXML &amp; &quot;&lt;set name='&quot; &amp; ors(&quot;FactoryName&quot;) &amp; &quot;' value='&quot; &amp; ors2(&quot;TotOutput&quot;) &amp; &quot;' /&gt;&quot; 
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Close recordset 
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Set oRs2 = Nothing 
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;oRs.MoveNext 
 &nbsp;&nbsp;&nbsp;Wend 
 &nbsp;&nbsp;&nbsp;'Finally, close &lt;chart&gt; element 
 &nbsp;&nbsp;&nbsp;strXML = strXML &amp; &quot;&lt;/chart&gt;&quot; 
 &nbsp;&nbsp;&nbsp;Set oRs = nothing 
 
 &nbsp;&nbsp;&nbsp;'Create the chart - Pie 3D Chart with data from strXML 
 &nbsp;&nbsp;&nbsp;Call renderChart(&quot;../../FusionCharts/FCF_Pie3D.swf&quot;, &quot;&quot;, strXML, &quot;FactorySum&quot;, 650, 450) 
 %&gt; 
 &lt;/BODY&gt; 
 &lt;/HTML&gt;</td>
 </tr>
 <tr>
 <td valign="top" class="text">The following actions are taking place in this code:
 <ol>
 <li>We first include FusionCharts.js JavaScript class and FusionCharts.asp , to enable easy embedding of FusionCharts.</li>
 <li>We then include DBConn.asp, which contains connection parameters to connect to Access database. </li>
 <li>Thereafter, we generate the XML data document by iterating through the recordset and store it in strXML variable. </li>
 <li>Finally, we render the chart using renderChart() method and pass strXML as dataXML. </li>
 </ol>
 When you now run the code, you'll get an output as under: </td>
 </tr>
 <tr>
 <td valign="top" class="text"><img src="Images/Code_DBOut.jpg" class="imageBorder" /></td>
 </tr>
 <tr>
 <td valign="top" class="text">&nbsp;</td>
 </tr>
 <tr>
 <td valign="top" class="header">Converting the example to use dataURL method </td>
 </tr>
 <tr>
 <td valign="top" class="text">Let's now convert this example to use dataURL method. As previously explained, in dataURL mode, you need two pages:
 <ol>
 <li>Chart Container Page - The page which embeds the HTML code to render the chart. This page also tells the chart where to load the data from. We'll name this page as Default.asp. </li>
 <li>Data Provider Page - This page provides the XML data to the chart. We'll name this page as PieData.asp</li>
 </ol>
 The pages in this example are contained in Download Package &gt; Code &gt; ASP &gt; DB_dataURL folder. </td>
 </tr>
 <tr>
 <td valign="top" class="text">&nbsp;</td>
 </tr>
 <tr>
 <td valign="top" class="header">Chart Container Page - Default.asp </td>
 </tr>
 <tr>
 <td valign="top" class="text">Default.asp contains the following code to render the chart: </td>
 </tr>
 <tr>
 <td valign="top" class="codeBlock">&lt;%@ Language=VBScript %&gt; 
 &lt;HTML&gt; 
 &lt;HEAD&gt; 
&nbsp;&nbsp;&nbsp;&lt;TITLE&gt;	FusionCharts Free - dataURL and Database Example&lt;/TITLE&gt; 
&nbsp;&nbsp;&nbsp;&lt;SCRIPT LANGUAGE=&quot;Javascript&quot; SRC=&quot;../../FusionCharts/FusionCharts.js&quot;&gt;&lt;/SCRIPT&gt; 
&lt;/HEAD&gt; 
&lt;!-- #INCLUDE FILE=&quot;../Includes/FusionCharts.asp&quot; --&gt; 
&lt;BODY&gt; 
&nbsp;&nbsp;&nbsp;&lt;% 
&nbsp;&nbsp;&nbsp;'In this example, we show how to connect FusionCharts to a database 
&nbsp;&nbsp;&nbsp;'using dataURL method. In our previous example, we've used dataXML method 
&nbsp;&nbsp;&nbsp;'where the XML is generated in the same page as chart. Here, the XML data 
&nbsp;&nbsp;&nbsp;'for the chart would be generated in PieData.asp. 
 
&nbsp;&nbsp;&nbsp;'For the sake of ease, we've used an Access database which is present in 
&nbsp;&nbsp;&nbsp;'../DB/FactoryDB.mdb. It just contains two tables, which are linked to each 
&nbsp;&nbsp;&nbsp;'other. 
 
&nbsp;&nbsp;&nbsp;'Variable to contain dataURL 
&nbsp;&nbsp;&nbsp;Dim strDataURL	 
&nbsp;&nbsp;&nbsp;'the asp file piedata.asp interacts with the database, 
&nbsp;&nbsp;&nbsp;'converts the data into proper XML form and finally 
&nbsp;&nbsp;&nbsp;'relays XML data document to the chart 
&nbsp;&nbsp;&nbsp;strDataURL = &quot;PieData.asp&quot; 
 
&nbsp;&nbsp;&nbsp;'Create the chart - Pie 3D Chart with dataURL as strDataURL 
&nbsp;&nbsp;&nbsp;Call renderChart(&quot;../../FusionCharts/FCF_Pie3D.swf&quot;, strDataURL, &quot;&quot;, &quot;FactorySum&quot;, 650, 450) 
%&gt; 
&lt;/BODY&gt; 
&lt;/HTML&gt;</td>
 </tr>
 <tr>
 <td valign="top" class="text">In the above code, we:
 <ol>
 <li>Include FusionCharts.js JavaScript class and FusionCharts.asp,</li>
 <li>Create the dataURL string and store it in strDataURL variable. </li>
 <li>Finally, we render the chart using renderChart() method and set dataURL as strDataURL. </li>
 </ol> </td>
 </tr>
 <tr>
 <td valign="top" class="header">Creating the data provider page PieData.asp </td>
 </tr>
 <tr>
 <td valign="top" class="text">PieData.asp contains the following code to output XML Data: </td>
 </tr>
 <tr>
 <td valign="top" class="codeBlock">&lt;%@ Language=VBScript %&gt;
 &lt;!-- #INCLUDE FILE=&quot;../Includes/DBConn.asp&quot; --&gt; 
 &lt;% 
 &nbsp;&nbsp;&nbsp;'This page generates the XML data for the Pie Chart contained in 
&nbsp;&nbsp;&nbsp;'Default.asp. 
 
&nbsp;&nbsp;&nbsp;'For the sake of ease, we've used an Access database which is present in 
&nbsp;&nbsp;&nbsp;'../DB/FactoryDB.mdb. It just contains two tables, which are linked to each 
&nbsp;&nbsp;&nbsp;'other. 
 
&nbsp;&nbsp;&nbsp;'Database Objects - Initialization 
 &nbsp;&nbsp;&nbsp;Dim oRs, oRs2, strQuery 
 &nbsp;&nbsp;&nbsp;'strXML will be used to store the entire XML document generated 
&nbsp;&nbsp;&nbsp;Dim strXML 
 
 &nbsp;&nbsp;&nbsp;'Create the recordset to retrieve data 
 &nbsp;&nbsp;&nbsp;Set oRs = Server.CreateObject(&quot;ADODB.Recordset&quot;)
 &nbsp;&nbsp;&nbsp;'Generate the graph element 
 &nbsp;&nbsp;&nbsp;strXML = &quot;&lt;graph caption='Factory Output report' subCaption='By Quantity' decimalPrecision='0' showNames='1' numberSuffix=' Units' pieSliceDepth='30' formatNumberScale='0'&gt;&quot; 
 
 &nbsp;&nbsp;&nbsp;'Iterate through each factory 
 &nbsp;&nbsp;&nbsp;strQuery = &quot;select * from Factory_Master&quot; 
 &nbsp;&nbsp;&nbsp;Set oRs = oConn.Execute(strQuery) 
 
 &nbsp;&nbsp;&nbsp;While Not oRs.Eof 
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Now create second recordset to get details for this factory 
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Set oRs2 = Server.CreateObject(&quot;ADODB.Recordset&quot;) 
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strQuery = &quot;select sum(Quantity) as TotOutput from Factory_Output where FactoryId=&quot; &amp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ors(&quot;FactoryId&quot;) 
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Set oRs2 = oConn.Execute(strQuery) 
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Generate &lt;set name='..' value='..' /&gt; 
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strXML = strXML &amp; &quot;&lt;set name='&quot; &amp; ors(&quot;FactoryName&quot;) &amp; &quot;' value='&quot; &amp; ors2(&quot;TotOutput&quot;) &amp; &quot;' /&gt;&quot; 
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Close recordset 
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Set oRs2 = Nothing 
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;oRs.MoveNext 
 &nbsp;&nbsp;&nbsp;Wend 
 &nbsp;&nbsp;&nbsp;'Finally, close &lt;graph&gt; element 
 &nbsp;&nbsp;&nbsp;strXML = strXML &amp; &quot;&lt;/graph&gt;&quot; 
 &nbsp;&nbsp;&nbsp;Set oRs = nothing 
 
 &nbsp;&nbsp;&nbsp;'Set Proper output content-type 
 &nbsp;&nbsp;&nbsp;Response.ContentType = &quot;text/xml&quot; 
 
 &nbsp;&nbsp;&nbsp;'Just write out the XML data 
 &nbsp;&nbsp;&nbsp;'NOTE THAT THIS PAGE DOESN'T CONTAIN ANY HTML TAG, WHATSOEVER 
 &nbsp;&nbsp;&nbsp;Response.Write(strXML) 
 %&gt; 
 </td>
 </tr>
 <tr>
 <td valign="top" class="text">In the above page:
 <ol><li>We generate the data and store it in strXML variable</li>
 <li>Finally, we write this data to output stream without any HTML tags. </li>
 </ol>
 When you view this page, you'll get the same output as before. </td>
 </tr>
</table>
</body>
</html>