Solved

Filling a chart with recordset data in asp

Posted on 2003-10-22
13
616 Views
Last Modified: 2012-06-21
Hello Everybody

It's me again, but this time I have an ASP question for you

I've the following code provided by the msnd service

<%

Dim oChart, c, Categories(5), Vals(5), i, sCaption, nData, nOrg

' Get the input value
nData = Request.QueryString("sOrg")

'When the page loads the first time, set ndata to 5
if len(nData) = 0 then nData = 5

' Generate random categories and values for the chart
' These values can come from some existing data source
for i = 1 to 5
      Categories(i) = "Machine" & CStr(i)
      Vals(i) = nData * Rnd(100)
next

' Create a Chart Object
Set oChart = CreateObject("OWC.Chart")
Set c = oChart.Constants

' Set the different parameters for the ChartSpace
oChart.Border.Color = c.chColorNone

' Get Organization number and use it to set the Caption
nOrg = nData/5
sCaption = "Current Utilizations for Org"
sCaption = sCaption & CStr(nOrg)

' Add a chart and set parameters for the chart
oChart.Charts.Add
oChart.Charts(0).Type = oChart.Constants.chChartTypeColumnClustered
oChart.Charts(0).SeriesCollection.Add
oChart.Charts(0).SeriesCollection(0).Caption = sCaption
oChart.Charts(0).SeriesCollection(0).SetData c.chDimCategories, c.chDataLiteral, Categories
oChart.Charts(0).SeriesCollection(0).SetData c.chDimValues, c.chDataLiteral, Vals
oChart.Charts(0).HasLegend = True
oChart.Charts(0).HasTitle = True

' Get a temporary filename to save chart in that file
sFname = Session("FSO").GetTempName & session.SessionID & ".gif"

' Export the chart to the temporary file
oChart.ExportPicture server.MapPath(sFname), "gif", 600, 512

' Create a link to the generated file
Response.Write "<img src='" & sFname & "'>"

' Store the file with its path in the session object for cleanup
Session("sTempFile" & Session("n")) = Server.MapPath(sFname)

' Increment the number of files
Session("n") = Session("n") + 1

%>
The code uses Rnd function to generate random values that are shown in the chart, of course. Also, there is the possibility to fill the chart with data from a recordset or database, but i don't know how can it be. Hope someone knows about that, and can help me with this little issue.

Thanks in advanced

Jcaicedc
0
Comment
Question by:jcaicedc
  • 6
  • 4
  • 3
13 Comments
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9598652
It should be something like this:

intCounter  = 0
do while not objRS.EOF
     Categories(intCounter) = objRS("Category")
     Vals(intCounter) =objRS("Value")
     intCounter  = intCounter  + 1
    objRS.MoveNext
next
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9598665
The idea here is that you have a series of categories and values that need to be populated. The code above iterates through your recordset, grabs the values for each pair, and then populates the chart.

Fritz the Blank
0
 
LVL 4

Accepted Solution

by:
farzinm earned 250 total points
ID: 9598695
set oChart = Server.CreateObject("OWC.Chart")
set objChart = oChart.Charts.Add()
Set objConn = Server.CreateObject ("ADODB.Connection")
objConn.Open strConnection
'strConnection is your connection string
set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open sqlQuery,objConn
set oChart.DataSource = objRS
objChart.SetData c.chDimCategories, 0, "columnname1"
objChart.SetData c.chDimValues, 0, "columnname2"
'columnname1 is the column name in your sql query
'columnname2 is the column name in your sql query
' Get a temporary filename to save chart in that file
sFname = Session("FSO").GetTempName & session.SessionID & ".gif"

' Export the chart to the temporary file
oChart.ExportPicture server.MapPath(sFname), "gif", 600, 512

HTH



0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9598811
If you can bind the data to the chart in the manner that  farzinm suggests, that would be a much better solution than mine.

Fritz the Blank
0
 

Author Comment

by:jcaicedc
ID: 9599280
Ok

I tried both solutions. First one returned me an error due the size of array related to the index of array. The second one returned me the following error
 
Microsoft Office Chart 9.0 error '80070057'

The given ADO Recordset must be set to client cursor and must not be forward only reading.

/tesis/chart.asp, line 34

Could you tell me why?

Thanks in advance
0
 

Author Comment

by:jcaicedc
ID: 9599296
Oops, I forgot to post the code. Here he goes

<%

Dim oChart, c, sCaption, nData, nOrg

' Get the input value
nData = Trim(Request.Form("partidos"))


' Generate random categories and values for the chart
' These values can come from some existing data source


' Create a Chart Object
Set oChart = CreateObject("OWC.Chart")
Set c = oChart.Constants

' Set the different parameters for the ChartSpace
oChart.Border.Color = c.chColorNone

' Get Organization number and use it to set the Caption
nOrg = nData
sCaption = "Current Utilizations for Org"
sCaption = sCaption & nOrg

' Add a chart and set parameters for the chart
set oChart = Server.CreateObject("OWC.Chart")
set objChart = oChart.Charts.Add()
Set oConn = Server.CreateObject ("ADODB.Connection")
oConn.open = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source="& Server.MapPath("./tesis.mdb")
'strConnection is your connection string
set RS = Server.CreateObject("ADODB.Recordset")
sql="Select partido, votos from concejos88 where partido = '"& nData &"'"
RS.Open sql,oConn
set oChart.DataSource = RS
objChart.SetData c.chDimCategories, 0, "partido"
objChart.SetData c.chDimValues, 0, "votos"
'columnname1 is the column name in your sql query
'columnname2 is the column name in your sql query

' Get a temporary filename to save chart in that file
sFname = Session("FSO").GetTempName & session.SessionID & ".gif"

' Export the chart to the temporary file
oChart.ExportPicture server.MapPath(sFname), "gif", 600, 512

' Create a link to the generated file
Response.Write "<img src='" & sFname & "'>"

' Store the file with its path in the session object for cleanup
Session("sTempFile" & Session("n")) = Server.MapPath(sFname)

' Increment the number of files
Session("n") = Session("n") + 1

%>
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9599414
Try:

RS.Open sql,oConn,3,3

Fritz the Blank
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9599436
Or this:

RS.CursorLocation = 3
RS.Open sql,oConn,3,3


Fritz the Blank
0
 
LVL 4

Expert Comment

by:farzinm
ID: 9599570
yes forgot to mention this before you open your connection

objRS.CursorType = adOpenStatic
objRS.CursorLocation = 3
0
 
LVL 4

Expert Comment

by:farzinm
ID: 9599587
where adOpenStatic =3
0
 

Author Comment

by:jcaicedc
ID: 9599742
Fritz thanks a lot, you were as helpful as farzinm.  Sorry 'bout the points but EE has no option to divide the points. Thanks, and thanks and thanks a lot. See you soon

Jcaicedc
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9599844
Actually they do!

In this case, farzinm provided your solution so it is best that points go to him/her anyway.

Good luck with you project,

Fritz the Blank
0
 
LVL 4

Expert Comment

by:farzinm
ID: 9602348
jcaicedc
thanks for the points, as fritz_the_blank  mentioned there is a feature called split points.
http://oldlook.experts-exchange.com/help/closing.jsp#3
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now