Display a chart using a recordset

[jon24422531]

Hi everyone

This is not pretty, but I am trying to modify some borrowed code for our internal company website. (80% of the code is "borrowed")

I can understand most of the workings, but I am not sure how to get it step through my recordset. I have tried several methods including getRows, but I am just going round in circles.

Dim MyConn
Dim rsData
Dim strSQL

set MyConn = Server.CreateObject("ADODB.Connection")
MyConn.Open "Driver={SQL Server}; Server=FTLive;Database=WorkLog;Uid=app;Pwd=;" 

Set rsData = Server.CreateObject("ADODB.Recordset")

strSQL = "select distinct sortedby as Name, count(*) as cnt from .........."

rsData.Open strSQL, MyConn

So that's the connection. Now I want to draw a graph (with the "borrowed" code)

lTotal = 0
for i=1 to 5
  ' Check if the value exists
  if rsData.fieldItem("value" & i)<>""then
    if cLng(rsData("value" & i))>0 then
      lTotal= cLng(rsData("value" & i)) + lTotal
    end if
  end if
next

str="<TABLE BORDER=0 CELLSPACING=0 CELLPADDING=2>" 
str=str & "<TR bgcolor=lightgrey><TH align=left>Label</TH>"
str=str & "<TH WIDTH=5>&nbsp;</TH><TH>Value</TH>" 
str=str & "<TH WIDTH=10>&nbsp;</TH><TH align=center>"
str= tr & "%</TH></TR>" 
for i=1 to 5
  lValue = rsData("value" & i)
  ' If value exists, process it
  if rsData("value" & i) <> "" then
    lValue = cLng(lValue)
    ' Calculate percentage of total
    iPercent = Round((lValue/lTotal) * 100) 
    str = str & " <TR><TD>" & rsData("name" & i) 
    str = str & "</TD><TD>&nbsp;</TD><TD align=center>"
    str = str & lValue & "</TD><TD>&nbsp;</TD>"
    str = str & "<TD valign=middle><TABLE><TR><TD bgcolor="
    ' Set bar color
    if iPercent>50 then 
      str = str & "darkgreen"
    else
      str = str & "darkred"
    end if
    str = str & "><IMG SRC='/images/s.gif' width=" & ((iPercent/100)*barSize) & " height=5>"
    str = str & "</TD><TD><FONT SIZE=1>" & iPercent
    str = str & "%</FONT></TD></TR></TABLE></TD></TR>"
  end if
next
str = str & "</TABLE>"
response.write(str)
%>
Put a line here...
<%

rsData.Close
set rsData = Nothing
set MyConn = Nothing

The error is "Object doesn't support this property or method: 'fieldItem'"

Could some please explain how I can get the code to step through the rsData recordset

Coding is like the works of Shakespeare, I can read it and appreciate it, but I could never write it.

Thanks

Jonathan

http://www.telecom-stuff.com

 

[wvdba]

this: rsData.fieldItem
should be:
rsData.Fields.Item

 

[jon24422531]

Hi wvdba

Thanks for the response. I changed the rsData.fieldItems to rsData.Fields.Item as you suggested:

lTotal = 0
for i=1 to 5
  ' Check if the value exists
  if rsData.fields.Item("value" & i)<>""then
    if cLng(rsData.fields.Item("value" & i))>0 then
      lTotal= cLng(rsData.fields.Item("Value" & i)) + lTotal
    end if
  end if
next
.........

and elsewhere, but the error is now:
ADODB.Fields error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name or ordinal.

Could it be that the fields I am requesting are cnt & name

I have tried so many variations I am getting confused

Jonathan

http://www.telecom-stuff.com

 

[BigRed1212]

Yes. When I get that error in my apps it means I am asking for a field I didn't go get with my SQL.

If my SQL was "select col1, col2 from tab1" and I tried to do something with rs("col3") it would throw that error 'cause it ain't in the recordset.

 

[jon24422531]

Hi BigRed

I did try replacing rsData.fields.Item("value" & i) with rsData.fields.Item("cnt" & i), but no change in the error message.

As I said, I've tried so many combinations that I am dizzy.... It will be a simple solution, I am sure, but it is still eluding me.

Jonathan

http://www.telecom-stuff.com

 

[DreXor]

depends on the actual recordset and remember you're working with "borrowed" code, so more than likelynot EVERYTHING is going to mesh properly.

this might be of benefit, it dumps your current recordset out on screen so you can see fieldnames, values etc. you can add any of the other ADO properties/methods of the recordset object as well to see datatype, defined size etc.
a list of these and other similar loops can be found :

http://www.tek-tips.com/faqs.cfm?fid=3804

do while not rs.eof
  for each field in rs.fields
    response.write field.name & ":" & RS(Field.name) & "<BR>" & vbcrlf
  next
  rs.movenext
loop

i'm not going to sit and re-write all your code because it appears that you're using a non cross tab based data set so the name/numbering conventions aren't there.

the main thing it seems you're doing is taking a fairly static recordset of say 5 fields and their counts to get a ratio, you'll need to arbitrary this against either the sum of all, or some grand sum or what ever, then apply this math in ratio to the value you have, then apply a arbitrary scale to your graph area, in respect to the altered image widths on the "bars"

example is for the percentage values of a half dollar, a quarter, a dime, a nickel, and a penny graphed against the arbitrary max value of a dollar, on a table area that spans 300 px, would make the formula look like :

area = 300 units
dollar = 100 units
half = 50 units
quarter ... etc etc

(area/dollar) * Coin = width of bar
so your half dollar is 300/100 * 50 = 150px wide.
making it half the graph area.

the main thing is you need this arbitrary max value, and the area it will be applied in order to properly place the bars down.

the borrowed code is doing that in a sense, but more of a dynamic automation based of a structured recordset being fed to it and appearantly it's based on an MS Access wizard style crosstab query.

i'll put this on email notification should u need more assistance


DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!
" I always think outside the 'box', because I'm never in the 'loop' " - DreX 2005

 

[jon24422531]

DreX (Robert)

Thanks for your response, and I am sorry it has taken a little while to reply....

Your comments made me rethink and after a little searching and cutting 'n pasting I have come up with what I think is a simpler solution.

<%
Do While not rsData.EOF

Response.Write("<tr>" &vbcrlf)
Response.Write("<td width=""5"" height=""20"">&nbsp;</td>" &vbcrlf)
Response.Write("<td width=""100"" height=""20""><font face=""arial"" size=""1"">" &rsData("Name")& "</font></td>" &vbcrlf)
Response.Write("<td width=""30"" height=""20""><font face=""arial"" size=""1"">" &rsData("1 Day Total")& "</font></td>" &vbcrlf)
Response.Write("<td width=""200"" height=""20""><img src=""images/poll.gif"" height=""15"" width=""" & (rsData("1 Day Total")*3) & """></td>" &vbcrlf)
Response.Write("<td width=""30"" height=""20""><font face=""arial"" size=""1"">" &rsData("7 Day Total")& "</font></td>" &vbcrlf)
Response.Write("<td width=""200"" height=""20""><img src=""images/poll.gif"" height=""15"" width=""" & (rsData("7 Day Total")*3) & """></td>" &vbcrlf)
Response.Write("<td width=""30"" height=""20""><font face=""arial"" size=""1"">" &rsData("1 Month Total")& "</font></td>" &vbcrlf)
Response.Write("<td width=""200"" height=""20""><img src=""images/poll.gif"" height=""15"" width=""" & (rsData("1 Month Total")*3) & """></td>" &vbcrlf)
Response.Write("</tr>")
	rsData.MoveNext
	Loop

%>

Many thanks

Jonathan

http://www.telecom-stuff.com

 

[DreXor]

looks alot less confusing that way

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!
" I always think outside the 'box', because I'm never in the 'loop' " - DreX 2005