looping through xml in .net

Question

I'm looping through Some xml in .net and assigning Xml elements to string variables.

Those variables are passed into a method that takes three arguments. That method runs a stored procedure in SQL Server 2005 that queries the database with those three arguments.

The method and sproc run fine when passing in the arguments manually. But when i try to get the arguments from the xml file Visual studio says "Procedure or function usp_CreateOrgDataSet has too many arguments specified."

This is the code for the method below

private void GenChart_Click(object sender, EventArgs e)
    {


        //Open Connection
        conn_Org.ConnectionString = Set_OrgChartConn();
        conn_Org.Open();

                //Load xml Config file
                XmlDocument doc = new XmlDocument();
                doc.Load("Config.xml");

                XmlElement root = doc.DocumentElement;
                XmlNodeList nodes = root.SelectNodes("/root/Org");

                foreach (XmlNode  node in nodes)
                {
                    string Title1 = node["OC_Ttl1"].InnerText;
                    string Title2 = node["OC_Ttl2"].InnerText;
                    string OrgName = node["OC_OL31"].InnerText;



                    //Grab Chart data
                    GetChartData(Title1, Title2, OrgName);
                }
        conn_Org.Close();


                }

this is the getChartdate Method

private  void GetChartData(string OC_Ttl1, string OC_Ttl2, string OC_OL31)
    {
        OC_Ttl_1 = OC_Ttl1;
        OC_Ttl_2 = OC_Ttl2;
        OC_OL3_1 = OC_OL31;



        //Execute Stored Procedure
        cmd_Org.Connection = conn_Org;
        cmd_Org.CommandText = "dbo.usp_CreateOrgDataSet";
        cmd_Org.CommandType = CommandType.StoredProcedure;
        cmd_Org.Parameters.AddWithValue("@OC_Ttl_1", OC_Ttl1);
        cmd_Org.Parameters.AddWithValue("@OC_Ttl_2", OC_Ttl1);
        cmd_Org.Parameters.AddWithValue("@OC_OL3_1", OC_OL31);


        //Output xml
        DataSet orgDataSet = new DataSet();
        orgDataSet.ReadXml(cmd_Org.ExecuteXmlReader(), XmlReadMode.Auto);
        orgDataSet.WriteXml("InputXMLFiles/" + OC_OL3_1.Replace(" ","_") + ".xml");





    }

THis is the XML

<?xml version="1.0" encoding="utf-8"?>  
<root>  
  <Org>  
    <OC_Ttl1>Test1</OC_Ttl1>
    <OC_Ttl2>Test1</OC_Ttl2>
    <OC_OL31>OrgName1</OC_OL31>  
  </Org>
  <Org>
    <OC_Ttl1>Test2</OC_Ttl1>
    <OC_Ttl2>Test2</OC_Ttl2>
    <OC_OL31>OrgName2</OC_OL31>
  </Org>
  <Org>
    <OC_Ttl1>Test3</OC_Ttl1>
    <OC_Ttl2>Test3</OC_Ttl2>
    <OC_OL31>OrgName3</OC_OL31>
  </Org> 
</root>

At the point of the error the locals window has the following values:

OC_Ttl1 "Test2" string OC_Ttl2 "Test2" string OC_OL31 "OrgName2"string

The first Iteration succeeds, but fails on the second.

Answer 1

In short - I don't think this has anything to do with your xml code, nor any of the code posted. I expect you're going to have to clarify what the other code (GetChartData) does for us to help.

Well, for me:

foreach (XmlNode node in nodes)
{
    string Title1 = node["OC_Ttl1"].InnerText;
    string Title2 = node["OC_Ttl2"].InnerText;
    string OrgName = node["OC_OL31"].InnerText;

     Console.WriteLine(Title1 + "/" + Title1 + "/" + OrgName);
}

Outputs:

Test1/Test1/OrgName1
Test2/Test2/OrgName2
Test3/Test3/OrgName3

So it seems to work fine. Treble-check your xml, and treble-check your report method (GetChartData). It all seems fine in the code you have posted.

Answer 2

Also what is the prototype of the sp and the sql you're executing?

Are you just appending to a query with each iteration loop?

Put the function in the question.

Answer 3

There is no need to start at the document element. In fact, try //Org as your xpath to the node list.

Answer 4

You are adding the paramaters to the command on each iteration which would cause the 2nd iteration to throw this error. You will need to Add the params once and then set the values on each iteration. You could also fix this by insuring that cmd_Org is scoped within GetChartData.

private  void GetChartData(string OC_Ttl1, string OC_Ttl2, string OC_OL31)
    {
        OC_Ttl_1 = OC_Ttl1;
        OC_Ttl_2 = OC_Ttl2;
        OC_OL3_1 = OC_OL31;


        //Execute Stored Procedure
        SqlCommand cmd_Org.Connection = conn_Org;
        cmd_Org.CommandText = "dbo.usp_CreateOrgDataSet";
        cmd_Org.CommandType = CommandType.StoredProcedure;
        cmd_Org.Parameters.AddWithValue("@OC_Ttl_1", OC_Ttl1);
        cmd_Org.Parameters.AddWithValue("@OC_Ttl_2", OC_Ttl1);
        cmd_Org.Parameters.AddWithValue("@OC_OL3_1", OC_OL31);


        //Output xml
        DataSet orgDataSet = new DataSet();
        orgDataSet.ReadXml(cmd_Org.ExecuteXmlReader(), XmlReadMode.Auto);
        orgDataSet.WriteXml("InputXMLFiles/" + OC_OL3_1.Replace(" ","_") + ".xml");





    }