datatable.import row bug/issue

12 May 2006 3:56 PM

hharry

hello all,

i have 2 datatables and am trying to transfer rows from datatable a to
datatable b
i use the datatable.importrow method.

the importrow method fails (but does not throw an exception) when
importing a datarow that has numeric columns. columns of type string
are imported fine.

e.g.

Dim objManualDataSet As DataSet
objMasterDataSet.Tables.Add(New DataTable(TableNames.Unique_DoB))

objMasterDataSet.Tables(TableNames.Unique_DoB).Columns.Add(New
DataColumn("dob_key", System.Type.GetType("System.String")))

objMasterDataSet.Tables(TableNames.Unique_DoB).Columns.Add(New
DataColumn("mn", System.Type.GetType("System.Int16")))

objMasterDataSet.Tables(TableNames.Unique_DoB).Columns.Add(New
DataColumn("dd", System.Type.GetType("System.Int16")))

objMasterDataSet.Tables(TableNames.Unique_DoB).Columns.Add(New
DataColumn("yy", System.Type.GetType("System.Int16")))

My 2nd dataset is generated by a call to a sql stored proc and has the
same structure as the 1st dataset..

My code to import the row is:

For Each objRow As DataRow In objManualDataSet.Tables(2).Rows
     objMasterDataSet.Tables(TableNames.Unique_DoB).ImportRow(objRow)
Next

This is the result:

? objManualDataSet.Tables(2).Rows(0).ItemArray
{Length=4}
    (0): "mn=1dd=12yy=1955"
    (1): 1 {Short}
    (2): 12 {Short}
    (3): 1955 {Integer}
? objMasterDataSet.Tables(TableNames.Unique_DoB).Rows(0).ItemArray
{Length=4}
    (0): "mn=3dd=29yy=1973"
    (1): {System.DBNull}
    (2): {System.DBNull}
    (3): {System.DBNull}

Any ideas as to why cols 1, 2, 3 = System.DBNull ?

Thanks in advance

12 May 2006 5:34 PM

Chris

hharry wrote:
Show quoteHide quote

> hello all,
>
> i have 2 datatables and am trying to transfer rows from datatable a to
> datatable b
> i use the datatable.importrow method.
>
> the importrow method fails (but does not throw an exception) when
> importing a datarow that has numeric columns. columns of type string
> are imported fine.
>
> e.g.
>
> Dim objManualDataSet As DataSet
> objMasterDataSet.Tables.Add(New DataTable(TableNames.Unique_DoB))
>
> objMasterDataSet.Tables(TableNames.Unique_DoB).Columns.Add(New
> DataColumn("dob_key", System.Type.GetType("System.String")))
>
> objMasterDataSet.Tables(TableNames.Unique_DoB).Columns.Add(New
> DataColumn("mn", System.Type.GetType("System.Int16")))
>
> objMasterDataSet.Tables(TableNames.Unique_DoB).Columns.Add(New
> DataColumn("dd", System.Type.GetType("System.Int16")))
>
> objMasterDataSet.Tables(TableNames.Unique_DoB).Columns.Add(New
> DataColumn("yy", System.Type.GetType("System.Int16")))
>
> My 2nd dataset is generated by a call to a sql stored proc and has the
> same structure as the 1st dataset..
>
> My code to import the row is:
>
> For Each objRow As DataRow In objManualDataSet.Tables(2).Rows
>      objMasterDataSet.Tables(TableNames.Unique_DoB).ImportRow(objRow)
> Next
>
> This is the result:
>
> ? objManualDataSet.Tables(2).Rows(0).ItemArray
> {Length=4}
>     (0): "mn=1dd=12yy=1955"
>     (1): 1 {Short}
>     (2): 12 {Short}
>     (3): 1955 {Integer}
> ? objMasterDataSet.Tables(TableNames.Unique_DoB).Rows(0).ItemArray
> {Length=4}
>     (0): "mn=3dd=29yy=1973"
>     (1): {System.DBNull}
>     (2): {System.DBNull}
>     (3): {System.DBNull}
>
> Any ideas as to why cols 1, 2, 3 = System.DBNull ?
>
> Thanks in advance
>

I don't think you can do this. I seem to remember an issue that rows
can not be attached to two datatables at once. I could be wrong though.
Have you thought about cloning the datatable.

DataTable.Clone

12 May 2006 6:20 PM

hharry

Chris,

Yes, I tried using the Add method and got the row cannot belong to more
than one datatable error. The ImportRow method worlks fine if all
columns are of type string. I only get the error if any columns are
numeric.

12 May 2006 7:06 PM

hharry

here is my workaround:

For Each objRow As DataRow In objManualDataSet.Tables(2).Rows
                    Dim tmpRow(3) As Object
                    tmpRow(0) = objRow.Item(0)
                    tmpRow(1) = objRow.Item(1)
                    tmpRow(2) = objRow.Item(2)
                    tmpRow(3) = objRow.Item(3)

objMasterDataSet.Tables(TableNames.Unique_DoB).LoadDataRow(tmpRow,
True)
                Next

12 May 2006 7:15 PM

jayeldee

You could try using something like this

objMasterDataSet.Tables(TableNames.Unique_DoB).Rows.Add(objRow.ItemArray)

in place of

objMasterDataSet.Tables(TableNames.Unique_DoB).ImportRow(objRow)

This call will depend on the Columns of both tables having the same
indexes or it will error (Mismatched data types) or worse - it will
copy the wrong data to the wrong columns.