Microsoft OWC and SQL 2005 Issue in C#

[TheEarthWillShake]

Hey, I'm trying to read a database and display it as a graph. I have accomplished this but I have an issue.

Database structure is one table called num1.

num1 only has ID, X, and Y columns. That's it.

The graph displays right but the tags at the bottom have all shifted over right so it's not lined up.

The process I did was, read a database into a dataset, convert it to XML, then display it as a graph.

http://i88.photobucket.com/alb...icemike_2006/chart.png

^ There's the picture.

Code is attached.

Agh that code looks impossible to read....

 

[TheEarthWillShake]

private void BuildCharts ()
{
//Create a SQL connection
String cn = ConfigurationManager.ConnectionStrings["OWCDEMOConnectionString"].ConnectionString;

//The SQL query
string sqlStatement = "SELECT x, y FROM [num1]";

//Create a new instance of an SqlConnection and assign our connection string to it
SqlConnection sqlConnection = new SqlConnection(cn);

//Open our connection
sqlConnection.Open();

//Create a new instance of a dataset object
DataSet dataSet = new DataSet();

//Assign the SQL statement and connectio to use for this operation
SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlStatement, sqlConnection);

//Fill our dataset with the data accumulated from the SQL statement
sqlAdapter.Fill(dataSet);

//ChartSpaceClass oChartSpace = new ChartSpaceClass();
System.IO.StringWriter sw = new System.IO.StringWriter();
XmlDocument xDoc = new XmlDocument();
dataSet.WriteXml(sw);


//Disgard connections
sqlConnection.Close();
dataSet.Dispose();
sw.Close();

//XML
xDoc.LoadXml(sw.ToString());
System.Xml.XmlNodeList nodes;
nodes = xDoc.ChildNodes.Item(0).ChildNodes;

int nCount = nodes.Count + 1;


string[] aNames = new string[nCount];
string[] aTotals = new string[nCount];
string names = String.Empty;
string totals = String.Empty;

int i = 1;
for (i = 1; i < nCount; i++)
{
//aNames = nodes.Item(i - 1).ChildNodes.Item(1).InnerText; //ChildNodes.Item(1) For Car Names
aTotals = nodes.Item(i - 1).ChildNodes.Item(0).InnerText; //ChildNodes.Item(0) For Numbers
}

int j = 0;
for (j = 1; j < nCount; j++)
{
aNames[j] = nodes.Item(j - 1).ChildNodes.Item(1).InnerText; //ChildNodes.Item(1) For Car Names
//aTotals = nodes.Item(i - 1).ChildNodes.Item(0).InnerText; //ChildNodes.Item(0) For Numbers
}

names = String.Join("\t", aNames);
totals = String.Join("\t", aTotals);


//Test Labels
//Convert.ToString(aNames); Label = System.String
//Convert.ToString(nodes); Label = System.Xml...
Label1.Text = names;
Label2.Text = totals;
Label3.Text = Convert.ToString(sw);
Label4.Text = Convert.ToString(nCount);


//**********************************

Microsoft.Office.Interop.Owc11.ChartSpaceClass newChartSpace = new Microsoft.Office.Interop.Owc11.ChartSpaceClass();
Microsoft.Office.Interop.Owc11.ChartChartTypeEnum newChartType;


newChartSpace.Charts.Add(0);
newChartSpace.Charts[0].SeriesCollection.Add(0);
newChartSpace.Charts[0].SeriesCollection[0].SetData(Microsoft.Office.Interop.Owc11.ChartDimensionsEnum.chDimCategories, Convert.ToInt32(Microsoft.Office.Interop.Owc11.ChartSpecialDataSourcesEnum.chDataLiteral), names);
newChartSpace.Charts[0].SeriesCollection[0].SetData(Microsoft.Office.Interop.Owc11.ChartDimensionsEnum.chDimValues, Convert.ToInt32(Microsoft.Office.Interop.Owc11.ChartSpecialDataSourcesEnum.chDataLiteral), totals);


byte[] byteArr = (byte[]) newChartSpace.GetPicture ("png", 800, 500);

//------------------------------------------------------------------------
//Store the chart image in Session to be picked up by an HttpHandler later
//------------------------------------------------------------------------
HttpContext ctx = HttpContext.Current;
string chartID = Guid.NewGuid().ToString();

ctx.Session [chartID] = byteArr;
imgChart.ImageUrl = string.Concat ("chart.ashx?", chartID);


}


}
}

 

[imported_Dhaval00]

Are you sure that the car names don't have an empty string at the beginning or something?

I guess debugging and stepping through the code might help as to where the 'shifting' is occurring.

 

[TheEarthWillShake]

I will try that.

The strange part is I made some Label boxes and I sent the numbers and the names into two label boxes and they both lined up correctly.

Mazda Toyota Ford Chevy Dodge Acura Honda
10 5 9 14 16 17 19

 

[TheEarthWillShake]

This is the issue..

names = String.Join("\t", aNames);
totals = String.Join("\t", aTotals);

The formating is messed up. If I take away the \t it centers everything but the graph bars become one or something.... What other formating options do I have?

EDIT: I don't think it's that...

It might be this?

int j = 0;
for (j = 1; j < nCount; j++)
{
aNames[j] = nodes.Item(j - 1).ChildNodes.Item(1).InnerText; //ChildNodes.Item(1) For Car Names
//aTotals = nodes.Item(i - 1).ChildNodes.Item(0).InnerText; //ChildNodes.Item(0) For Numbers
}

I tried stepping but I'm not sure how it works... This is all done in the Page Load.

I don't know. I have a feeling that it takes a number plots it, skips a spot and posts it's name so it's zigzaging instead of be parallel.

I'm stumped.

I followed this guide.... http://www.eggheadcafe.com/articles/20021223.asp

XML structure is this.

<NewDataSet>
<Table>
<x>25000 </x>
<y>Dodge </y>
</Table>
<Table>
<x>10000 </x>
<y>Chevy </y>
</Table>
<Table>
<x>15000 </x>
<y>Toyota </y>
</Table>
</NewDataSet>


EDIT 2:

In aNames and aTotals, they both start with "null" then list the contents.

if I change null to 50000 and Chevy, the graph displays right.

How do I remove the null?

 

[TheEarthWillShake]

If I add this after the for loop

aTotals[0] = "Kia";
aNames[0] = "27500";

my graph works!

WHY?

if it isn't put in,

aTotals[0] = "Null";
aNames[0] = "Null"

 

[MrChad]

Why are you starting your loop with j = 1?

int j = 0;
for (j = 1; j < nCount; j++)
{
aNames[j] = nodes.Item(j - 1).ChildNodes.Item(1).InnerText; //ChildNodes.Item(1) For Car Names
//aTotals = nodes.Item(i - 1).ChildNodes.Item(0).InnerText; //ChildNodes.Item(0) For Numbers
}

Why not:

int j = 0;
for (j = 0; j < nCount; j++)
{
aNames[j] = nodes.Item(j).ChildNodes.Item(1).InnerText; //ChildNodes.Item(1) For Car Names
}

 

[imported_Dhaval00]

I was looking at that, Chad, but if you look at nCount, it gets initialized to Items.Count + 1. So he is still accommodating all the elements in the array. But then, it just looks cryptic without the data in front of you. Gotta debug it!

I think you have solved the issue... just loop over the arrays and remove any "nulls". See if that fixes it. Or when you populate the arrays, check if the InnerText is "null". If it is, don't put it in the array?