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

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");

Jon
Tue Jun 01 10:55:28 CDT 2004

Joe Bonavita <J_no_spam@_no_spam_Fishinbrain.com> wrote:
> 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");

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.)

--
Jon Skeet - <skeet@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

Joe
Tue Jun 01 11:58:27 CDT 2004

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" <J_no_spam@_no_spam_Fishinbrain.com> wrote in message
news:eVyAH7#REHA.3944@TK2MSFTNGP11.phx.gbl...
> 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");
>
>

Cor
Tue Jun 01 13:06:22 CDT 2004

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

Joe
Tue Jun 01 13:18:46 CDT 2004

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


"Joe" <J_no_spam@_no_spam_Fishinbrain.com> wrote in message
news:eFf$pm$REHA.3056@TK2MSFTNGP11.phx.gbl...
> 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" <J_no_spam@_no_spam_Fishinbrain.com> wrote in message
> news:eVyAH7#REHA.3944@TK2MSFTNGP11.phx.gbl...
> > 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");
> >
> >
>
>

Jon
Tue Jun 01 13:21:06 CDT 2004

Joe <J_no_spam@_no_spam_Fishinbrain.com> wrote:
> one problem fixed. It seems that setting dr.ItemArray[index] =
> sqlReader.GetString(anotherIndex) wasn't setting the value.

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.

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.

--
Jon Skeet - <skeet@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

Cor
Tue Jun 01 13:55:50 CDT 2004

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.

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
>
>
>