Help with SQL query to XML conversion

[musicman]

I have a page that is producing correct XML. If I use the page as the source, the graph doesn't render properly, but if I copy/paste the XML that that page produces to a separate XML document and use that as the source, then the graph works.

 

 

 

Here's my code:

 

 

 

 

 

 

protected void Page_Load(object sender, EventArgs e)

 

 

 

{

 

 

 

DbConn1 oRs;

 

 

 

string strQuery;

 

 

 

string strXML;

 

 

 

strXML = "<graph bgColor='F1f1f1' caption='Admissions' animation='1' yAxisMinValue='0' formatNumber='1' numdivlines='3' divLineColor='333333' decimalPrecision='0' showLegend='1' showColumnShadow='1' formatNumberScale='0'>";

 

 

 

strXML += "<categories font='Arial' fontsize='10' fontcolor='000000'>";

 

 

 

strXML += "<category name='Applied' hovertext='Applied' />";

 

 

 

strXML += "<category name='Admitted' hovertext='Admitted' />";

 

 

 

strXML += "<category name='Enrolled' hovertext='Enrolled' />";

 

 

 

strXML += "</categories>";

 

 

 

strXML += "<dataset seriesname= 'First-Time Students' color='FF0000'>";

 

 

 

strQuery = "select * from admissions where student_type = 'First-Time Students' ";

 

 

 

oRs = new DbConn1(strQuery);

 

 

 

//Iterate through each Record

 

 

 

while (oRs.ReadData.Read())

 

 

 

{

 

 

 

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

 

 

 

}

 

 

 

oRs.ReadData.Close();

 

 

 

strXML += "</dataset>";

 

 

 

strXML += "<dataset seriesname= 'Transfer Students' color='009900'>";

 

 

 

strQuery = "select * from admissions where student_type = 'Transfer Students' ";

 

 

 

DbConn1 oRs2 = new DbConn1(strQuery);

 

 

 

//Iterate through each Record

 

 

 

while (oRs2.ReadData.Read())

 

 

 

{

 

 

 

strXML += "<set value='" + oRs2.ReadData["student_count"].ToString() + "' />";

 

 

 

}

 

 

 

oRs2.ReadData.Close();

 

 

 

strXML += "</dataset>";

 

 

 

strXML += "</graph>";

 

 

 

Response.ContentType = "text/xml";

 

 

 

Response.Write(strXML);

 

 

 

}

 

 

 

 

 

 

Produces the following XML:

 

 

 

- <graph bgColor="[b]F1f1f1[/b]" caption="[b]Admissions[/b]" animation="[b]1[/b]" yAxisMinValue="[b]0[/b]" formatNumber="[b]1[/b]" numdivlines="[b]3[/b]" divLineColor="[b]333333[/b]" decimalPrecision="[b]0[/b]" showLegend="[b]1[/b]" showColumnShadow="[b]1[/b]" formatNumberScale="[b]0[/b]">- <categories font="[b]Arial[/b]" fontsize="[b]10[/b]" fontcolor="[b]000000[/b]"> <category name="[b]Applied[/b]" hovertext="[b]Applied[/b]" />  <category name="[b]Admitted[/b]" hovertext="[b]Admitted[/b]" />  <category name="[b]Enrolled[/b]" hovertext="[b]Enrolled[/b]" />  </categories>- <dataset seriesname="[b]First-Time Students[/b]" color="[b]FF0000[/b]"> <set value="[b]1103[/b]" />  <set value="[b]912[/b]" />  <set value="[b]443[/b]" />  </dataset>- <dataset seriesname="[b]Transfer Students[/b]" color="[b]009900[/b]"> <set value="[b]231[/b]" />  <set value="[b]156[/b]" />  <set value="[b]101[/b]" />  </dataset> </graph>

 

 

 

Any help on where I've gone wrong would be appreciated. I figure I'm just missing something. Also, I am using the stacked column charts.

Edited April 17, 2008 by Guest

[Arindam]

Hi musicman,

I went through your problem. Your XML generation is ok. are you calling this page via dataURL method?

e.g

string dataURL = Server.UrlEncode("ChartXML.aspx");

Literal1.Text = FusionCharts.RenderChart("FusionCharts/FCF_MSColumn2D.swf", dataURL, "", "Chart1", "400", "400", false, false); 

If it does not work please send us the code.

[musicman]

I'm calling it using the following code:

<%

string strDataURL3;

strDataURL3 = "admissiondata.aspx";

Response.Write(FusionCharts.RenderChart("FusionCharts/FCF_StackedColumn3D.swf", strDataURL3, "", "Admissions3", "425", "300", false, false));

%>

Tried what you posted and get the following error:

CS0103: The name 'Literal1' does not exist in the current context

 

Edited April 18, 2008 by Guest

[Arindam]

Hi musicman,

Please use with in method on script section , after that call it.

public

void CallFusionChart()

{

string strDataURL3;

strDataURL3 =

"admissiondata.aspx";

Response.Write(

FusionCharts.RenderChart("FusionCharts/FCF_StackedColumn3D.swf", strDataURL3, "", "Admissions3", "425", "300", false, false));

}

and call it

<% CallFusionChart(); %>

[musicman]

Still no luck. I've attached what the chart looks like that is being produced. As well as the correct chart, which is being produced by the xml I pasted into a new document.

[FusionCharts Support]

Hi,

Could you please check once again whether the values are coming zero or not from the XML?

The chart would show this when the values are zero or not numbers.

[musicman]

The xml that is being produced is correct, no zeros or non-numbers. As I stated before if I use the aspx page as the source, my chart is incorrect, but if I take the xml that is produced by the aspx page and copy/paste it into an xml document and use that as the source then the chart is correct.

 

 

 

I've attached the data aspx page as well as the data xml document.

 

 

 

This produces an incorrect graph:

 

 

 

<%

 

 

 

string strDataURL;

 

strDataURL3 = "admissiondata.aspx";

 

Response.Write(FusionCharts.RenderChart("FusionCharts/FCF_StackedColumn3D.swf", strDataURL3, "", "Admissions1", "425", "300", false, false));

 

 

 

%>

 

 

 

This does not:

 

 

 

<%

 

 

 

string strDataURL2;

 

strDataURL2 = "admissions.xml";

 

Response.Write(FusionCharts.RenderChart("FusionCharts/FCF_MSColumn3D.swf", strDataURL2, "", "Admissions2", "425", "300", false, false));

 

 

 

%>

 

 

 

Both contain or output the same exact xml data.

admissiondata.zip

admissions.xml