CODEWITHSUNDEEP
c#sql-server
ryan l
ryan l

Reputation: 43

Datatable is not being populated

Attempting to populate a datatable with a SqlDatareader (I don't think I can use DataAdapter because of the way I am parsing a xml string). Just by looking at examples of datatables online this SHOULD work, but it doesn't. When I debug it table is always {} when it runs through the do while loop. What's the deal?

string sqlEntry = ConfigurationManager.ConnectionStrings["sqlPass"].ConnectionString;
SqlConnection conn = new SqlConnection(sqlEntry);

try
{
    conn.Open();
    conn.ChangeDatabase(ConfigurationManager.ConnectionStrings["DB"].ConnectionString);

    string sqlQuery = "SELECT * from equipInspection";
    SqlCommand sqlComm = new SqlCommand(sqlQuery, conn);

    SqlDataReader myReader;
    myReader = sqlComm.ExecuteReader();

    DataTable table = new DataTable();

    table.Columns.Add("Equipment", typeof(String));
    table.Columns.Add("SerialNo",typeof(String));
    table.Columns.Add("Contractor",typeof(String));
    table.Columns.Add("Date", typeof(String));
    table.Columns.Add("Deficiencies", typeof(String));

    if (myReader.Read())
    {
        do
        {
            string stringtosplit = myReader["formXML"].ToString();
            string[] xmlInfo = stringtosplit.Split(new string[] { ";ANKR!" }, StringSplitOptions.None);

            DataRow row = table.NewRow();
            row["Equipment"] = xmlInfo[0];
            row["SerialNo"] = xmlInfo[1];
            row["Contractor"] = myReader["Name"].ToString();
            row["Date"] = myReader["Date"].ToString();
            row["Deficiencies"] = xmlInfo[12];
            table.Rows.Add(row);
        } while (myReader.Read());

Here is a sample of the xml format. It may be the way it is structured or the way I am parsing it. 

    <!--Equipment inspection form by Test-->
<EquipmentInspection Date="2013/08/07" Time="12:05 AM" Location="Somewhere" ContractorName="Joe" Operator="Jane" Position="Boss" Contact="2132213421">
  <Field ID="txtEquipment" Type="Textbox">Jackhammer;ANKR!</Field>
  <Field ID="txtSerial" Type="Textbox">1234A5A-1;ANKR!</Field>
  <Field ID="txtManufacturer" Type="Textbox">Test;ANKR!</Field>
  <Field ID="txtUsage" Type="Textbox">Test;ANKR!</Field>
  <Field ID="txtService" Type="Textbox">2013/08/05;ANKR!</Field>
  <Field ID="rblRecentInspect" Type="RadioButtonList">No;ANKR!</Field>
  <cblShift>2, ;ANKR!</cblShift>
  <cblDaysInspected>W, F, ;ANKR!</cblDaysInspected>
  <Field ID="rblDamage" Type="RadioButtonList">Yes;ANKR!</Field>
  <Field ID="txtDamage" Type="Textbox">;ANKR!</Field>
  <Field ID="txtRepairs" Type="RadioButtonList">No;ANKR!</Field>
  <Field ID="rblDef" Type="RadioButtonList">No;ANKR!</Field>
  <Field ID="txtDef" Type="Textbox">it broke;ANKR!</Field>
</EquipmentInspection>

Upvotes: 3

Views: 133

Answers (2)

Joel Coehoorn
Joel Coehoorn

Reputation: 415600

Take a look at this excerpt from your code:

conn.Open();

conn.ChangeDatabase(ConfigurationManager.ConnectionStrings["DB"].ConnectionString);

The second line forces the connection closed again. Just swap the two lines and you should be fine.

Upvotes: 0

Mike D.
Mike D.

Reputation: 391

Are you clicking the spyglass to look at the actual table? From a quick glance you seem to be doing everything right and if debugging isn't erroring out then you're almost certainly getting your data back just fine. The table isn't going to have the data until you add the row, of course. I know it sounds silly, but make sure you click the spyglass after at least one iteration of table.Rows.Add(row); ....

Upvotes: 3

Related Questions