RE: Need help graphing a query

musicman — Tue, 15 Apr 2008 08:12:08 GMT

That worked, thank you.

RE: Need help graphing a query

Arindam — Tue, 15 Apr 2008 03:17:34 GMT

Hi musicman,
I went through your program, please do this way I thing it will work for you

DbConn oRs;

string strQuery;

string strXML;

strXML = "<graph caption='Number of Trouble Tickets' subCaption='By Day' decimalPrecision='0' showNames='1' formatNumberScale='0'>";

strQuery = "select CONVERT(VARCHAR(11),tkt_time,106) AS cTime, count(tkt_id) as TotTickets from ticket group by CONVERT(VARCHAR(11),tkt_time,106) order by CONVERT(VARCHAR(11),tkt_time,106) desc ";

oRs = new DbConn(strQuery);

//Iterate through each Record

while (oRs.ReadData.Read()){

strXML += "<set name='" + oRs.ReadData["cTime"].ToString() + "' value='" + oRs.ReadData["TotTickets"].ToString() + "' />";

}

oRs.ReadData.Close();

strXML += "</graph>";

Response.ContentType = "text/xml";

Response.Write(strXML);

Need help graphing a query

musicman — Fri, 11 Apr 2008 09:15:53 GMT

I need help graphing the below query. I can't seem to figure it out.

select CONVERT(VARCHAR(11),tkt_time,106) AS 'Time', count(tkt_id) as TotTickets from ticket

group by CONVERT(VARCHAR(11),tkt_time,106)

order by CONVERT(VARCHAR(11),tkt_time,106) desc

Gives me an output of:

28 Feb 2008 6
27 Mar 2008 1
26 Mar 2008 5
24 Mar 2008 1
19 Mar 2008 2
18 Mar 2008 1
08 Apr 2008 1
07 Apr 2008 1
06 Mar 2008 2
05 Mar 2008 6
04 Mar 2008 11
04 Apr 2008 2

The reason for the Convert is because tkt_time is stored as a TIMESTAMP and of course since I want a count of tickets for that day not that specific time thus it had to be converted.

DbConn oRs; string strQuery;

//strXML will be used to store the entire XML document generated

string strXML;

//Generate the graph element

strXML = "<graph caption='Number of Trouble Tickets' subCaption='By Day' decimalPrecision='0' showNames='1' formatNumberScale='0'>";

//Iterate through each date

strQuery = "select * from Ticket";

oRs = new DbConn(strQuery);

while (oRs.ReadData.Read())

// {

//Now create second recordset to get details for this datestrQuery = "select count(tkt_id) as TotTickets from ticket where tkt_time=" + oRs.ReadData["tkt_time"].ToString();

DbConn oRs2 = new DbConn(strQuery);

oRs2.ReadData.Read();

//Generate <set name='..' value='..' />

strXML += "<set name='" + oRs.ReadData["tkt_time"].ToString() + "' value='" + oRs2.ReadData["TotTickets"].ToString() + "' />";

//Close recordset

oRs2.ReadData.Close();

//}

oRs.ReadData.Close();

//Finally, close <graph> element

strXML += "</graph>";

//Set Proper output content-type

Response.ContentType = "text/xml";

//Just write out the XML data

//NOTE THAT THIS PAGE DOESN'T CONTAIN ANY HTML TAG, WHATSOEVER

Response.Write(strXML);

I would think that the above code would give me:

1 2008-02-28 14:41:47.187
1 2008-02-28 14:42:15.623
1 2008-02-28 14:43:24.310
1 2008-02-28 14:54:28.260
1 2008-02-28 15:22:55.530

Same as above, just not converted nor grouped. But it doesn't even work. I have tried the original query just by itself and I get the correct data for the first date in the table, but of course it stops at that because I'm not iterating through each date. I'm at a loss for what to do. These are the kind of queries that we need graphed. I'm very new to asp.net and programming in general, so any help would be much appreciated.

Here's what I've tried:

DbConn oRs; string strQuery;

//strXML will be used to store the entire XML document generated

string strXML;

//Generate the graph element

strXML = "<graph caption='Number of Trouble Tickets' subCaption='By Day' decimalPrecision='0' showNames='1' formatNumberScale='0'>";

//Iterate through each date

strQuery = "select * from Ticket";

oRs = new DbConn(strQuery);

while (oRs.ReadData.Read())

// {

//Now create second recordset to get details for this date

strQuery = "select count(tkt_id) as TotTickets from ticket where CONVERT(VARCHAR(11),tkt_time,106)=" + oRs.ReadData["CONVERT(VARCHAR(11),tkt_time,106)"].ToString();

DbConn oRs2 = new DbConn(strQuery);

oRs2.ReadData.Read();

//Generate <set name='..' value='..' />

strXML += "<set name='" + oRs.ReadData["CONVERT(VARCHAR(11),tkt_time,106)"].ToString() + "' value='" + oRs2.ReadData["TotTickets"].ToString() + "' />";

//Close recordset

oRs2.ReadData.Close();

//}

oRs.ReadData.Close();

//Finally, close <graph> element

strXML += "</graph>";

//Set Proper output content-type

Response.ContentType = "text/xml";

//Just write out the XML data

//NOTE THAT THIS PAGE DOESN'T CONTAIN ANY HTML TAG, WHATSOEVER

Response.Write(strXML);

FusionCharts Forum / FusionCharts v3 / Using FusionCharts / FusionCharts and ASP.NET / Need help graphing a query / Latest Posts

RE: Need help graphing a query

RE: Need help graphing a query

Need help graphing a query