DataTable.New() and Rows.Add() not working right

  • Thread starter Thread starter Joe Bonavita
  • Start date Start date
J

Joe Bonavita

I'm using a SqlReader to read results from a query and insert them into a
DataTable. The DataTable is from a DataSet which contains an Xml that was
read in using ReadXml().

My problem is, only 1 row gets written correctly. The others don't have any
attributes and are not in the proper hierarchy in the Xml yet they're all
created from the same loop.

while (sqlReader.Read() )
{
DataRow dr = dtProducts.NewRow();
dr.ItemArray[dr.Table.Columns.IndexOf("Col1")] =
sqlReader.GetString(indexPT);
...
...
// set more rows
dtProducts.Rows.Add(dr);
}

ds.WriteXml(@"X:\temp\Test.xml");
 
Joe Bonavita said:
I'm using a SqlReader to read results from a query and insert them into a
DataTable. The DataTable is from a DataSet which contains an Xml that was
read in using ReadXml().

My problem is, only 1 row gets written correctly. The others don't have any
attributes and are not in the proper hierarchy in the Xml yet they're all
created from the same loop.

while (sqlReader.Read() )
{
DataRow dr = dtProducts.NewRow();
dr.ItemArray[dr.Table.Columns.IndexOf("Col1")] =
sqlReader.GetString(indexPT);
...
...
// set more rows
dtProducts.Rows.Add(dr);
}

ds.WriteXml(@"X:\temp\Test.xml");
Click to expand...

Could you post a short but complete program which demonstrates the
problem?

See http://www.pobox.com/~skeet/csharp/complete.html for details of
what I mean by that.

(I would suggest using Northwind as a sample database, as most people
have access to that in some form or other.)
 
one problem fixed. It seems that setting dr.ItemArray[index] =
sqlReader.GetString(anotherIndex) wasn't setting the value.

To correct this I created an object array and used the LoadDataRow method.
Now my nodes are created but they're still in the wrong place. They're being
created at the root level.
 
Hi Joe,

Why that ItemArray and not the Item, when I understand what you want is it
to find the proper column by the proper item you read with the datareader.


while (sqlReader.Read() )
{
DataRow dr = dtProducts.NewRow();
dr.Item["Col1"] = sqlReader.GetString(0);
\\ set more Items
dtProducts.Rows.Add(dr);
}
ds.WriteXml(@"X:\temp\Test.xml");

I hope this helps?

Cor
 
fixed the next problem. I had to call DataRow.SetParentRow() before adding
the row. Now everything is good :).


Joe said:
one problem fixed. It seems that setting dr.ItemArray[index] =
sqlReader.GetString(anotherIndex) wasn't setting the value.

To correct this I created an object array and used the LoadDataRow method.
Now my nodes are created but they're still in the wrong place. They're being
created at the root level.

Joe Bonavita said:
I'm using a SqlReader to read results from a query and insert them into a
DataTable. The DataTable is from a DataSet which contains an Xml that was
read in using ReadXml().

My problem is, only 1 row gets written correctly. The others don't have any
attributes and are not in the proper hierarchy in the Xml yet they're all
created from the same loop.

while (sqlReader.Read() )
{
DataRow dr = dtProducts.NewRow();
dr.ItemArray[dr.Table.Columns.IndexOf("Col1")] =
sqlReader.GetString(indexPT);
...
...
// set more rows
dtProducts.Rows.Add(dr);
}

ds.WriteXml(@"X:\temp\Test.xml");
Click to expand...
Click to expand...
 
Joe said:
one problem fixed. It seems that setting dr.ItemArray[index] =
sqlReader.GetString(anotherIndex) wasn't setting the value.
Click to expand...

Ah - presumably ItemArray is creating a copy of the array each time
instead of returning the underlying array. Kinda reasonable :)
To correct this I created an object array and used the LoadDataRow
method. Now my nodes are created but they're still in the wrong
place. They're being created at the root level.
Click to expand...

So are your rows themselves correct, it's just the conversion to XML
which is incorrect? If so, it sounds like it might be your data
relations which are causing problems - where are you defining them? As
before, if you could give a short but complete program which
demonstrates the problem, that would be great - and if you've now got
the code for loading a table working, your complete program needn't use
a real database at all, just creating your DataTables manually instead.
 
Hi Joe,
Why that ItemArray and not the Item, when I understand what you want is it
to find the proper column by the proper item you read with the datareader.
Click to expand...

That last part of the text belongs not with the code I povided. When I was
looking at your code I saw it was even simpler and I was making it myself as
well only difficult.
while (sqlReader.Read() )
{
DataRow dr = dtProducts.NewRow();
dr.Item["Col1"] = sqlReader.GetString(0);
\\ set more Items
dtProducts.Rows.Add(dr);
}
ds.WriteXml(@"X:\temp\Test.xml");

I hope this helps?

Cor
Click to expand...
 
Back
Top