DataSet.Merge(DataTable) bug or am I missing something?

[Bob]

Hello,

I create a DataSet with 7 empty DataTables - no rows.

I then create 7 "stand-alone" DataTables with same schema - and same
tablename - as the ones in the DS, and query the data source, filling
each DT individually.

At this point if I merge the stand-alone DTs into the "empty" DS it
works fine.

DS.Merge((DT1)
DS.Merge((DT2)
DS.Merge((DT3)
DS.Merge((DT4)
DS.Merge((DT5)
DS.Merge((DT6)
DS.Merge((DT7)

The DS now has DTs with the correct rows - the rows in each DT of the
DS match the rows of its stand-alone DT counterpart.

However, if I try to immediately "re-Merge" all the stand-alone DTs
into the DS (which in reality should Merge nothing into the DS as none
of the rows have changed) I get an error on two of the seven DTs:

"Invalid cast from DateTime to Int16"

I have tried it with DS.EnforceConstraints = True and = False.

There are relationships in the DS, involving all tables. The two DTs
that error have FK constraints, but so, too, do a couple other DTs
that don't error.

To prove that this should work, instead of "re-merging" one of the DTs
that cause the error I loop through each row in the DT and use
LoadDataRow to "merge" the rows in one at a time.

This works!

For i = 0 to DT.Rows.Count - 1
DR = DT.Rows(i)
DS.Tables("MatchingTableNameAsDT").LoadDataRow(DR.ItemArray, False)
Next

I also tried to .Merge the individual DataRows as opposed to
LoadDataRow.

This did not work - produced same error:

Dim aryDR(0) as DataRow
For i = 0 to DT.Rows.Count - 1
aryDR(0) = DT.Rows(i)
DS.Merge(aryDR)
Next

Is this a bug in DataSet.Merge(DataTable) or am I missing something?

Thanks,

Bob

 

[Brad Williams]

Bob,

Do you AcceptChanges before secord set of merges? And are there FK's
between the tables, and so you merge the tables in correct order (parents
before children)? And what does DateTime have to do with foreign keys,
anything? Is this a record timestamp generated by db?

Brad Williams

 

[Bob]

Brad,

Do you AcceptChanges before secord set of merges?

Yes, I left it out of the code, sorry.
I grab the returned DR from LoadDataRow and AcceptChanges on it.

Dim drRet as DataRow
For i = 0 to DT.Rows.Count - 1
DR = DT.Rows(i)
drRet = DS.Tables("MatchingTableNameAsDT").LoadDataRow(DR.ItemArray,
False)
drRet.AcceptChanges()
Next

I guess I should just AcceptChanges on the DataSet itself (or
DataTables).

But it really should not make a difference whether AcceptChanges is
called or not between DS.Merges, right? Theoretically I should be able
to repeatedly .Merge data into a DS without ever calling
AcceptChanges, right?

Okay, I just tried calling AcceptChanges on the DS between Merges and
it didn't help.

And are there FK's between the tables, and so you merge the tables in

correct order (parents before children)?

Good call, yes. There are FKs, and I *do* merge the tables in the
correct order. Anyway, I set DS.EnforceConstraints = False before the
merges. And it makes no difference what DS.EnforceConstraints is
(other than erroring if the chikdren are done before the parents).

I just tried switching the order to children before parents for grins
but I got the same weird error:

"Invalid cast from DateTime to Int16"

And what does DateTime have to do with foreign keys, anything?

Nope, nothing. All the PKs/FKs are straightforward - single key, Int32
column.datatypes (simple ID numbers).

Is this a record timestamp generated by db?

Nope.

After my initial post, I tried cloning the DS, merging the DTs into
the clone, then merging the cloned DS into the real DS - and this
worked (as it should).

Dim dsClone As DataSet = DS.Clone
dsClone.Merge(DT1)
dsClone.Merge(DT2)
dsClone.Merge(DT3)
dsClone.Merge(DT4)
dsClone.Merge(DT5)
dsClone.Merge(DT6)
dsClone.Merge(DT7)
DS.Merge(dsClone)

I made other discoveries using a DS.Clone. To reiterate the problem,
if I do this:

DS.Merge(DT1) 'works
DS.Merge(DT1) 'works

DS.Merge(DT2) 'works
DS.Merge(DT2) 'works

DS.Merge(DT3) ' works
DS.Merge(DT3) ' errors

....the initial merge works and the second fails on DT3.

And if I do this the second one also fails on DT3.

dsClone.Merge(DT3) 'works
dsClone.Merge(DT3) 'errors

What I failed to mention is if I repeatedly call *the Sub that this
stuff is in* it either fails or works depending on if I merge directly
into the real DS (fails) or its clone (works).

(Looping and using LoadDataRow *always* works, regardless of
repetition inside the Sub, or of the Sub itself. So it's a .Merge
problem.)

So I have two ways to overcome this - looping through rows in the DTs
using LoadDataRow or using an intermediary DS clone - but it bugs me
that repeatedly merging DTs into a DS doesn't work. And the error
produced is just too seemingly unrelated.

Thanks for your interest in my problem Brad, it just seems like it's
not working when it should.
Bob

 

[Brad Williams]

"Invalid cast from DateTime to Int16"

Are *any* columns DateTime? If there are,

DS.Merge(DT1) 'works
DS.Merge(DT1) 'works

DS.Merge(DT2) 'works
DS.Merge(DT2) 'works

DS.Merge(DT3) ' works -- #1
DS.Merge(DT3) ' errors -- #2

I wonder if the row states of the rows in DS.DT3 are getting updated in such
a way by the first DS.Merge(DT3) line that the second call can't work
because the "original" row is lost.

I would try watching in a debugger: Find a simple case in which DT3 only
has one row yet breaks the above code. Then watch that row in the floating
DT3 table, and its corresponding row in the DS dataset. Specifically watch
how the DS row changes when line #1 I've marked above is executed. It may
be wise to add some code in between to Select the original verses current
versions of that row in DS, and see how those change when #1 is executed.
That's my only idea, that the original row state gets lost after #1 so then
Merge in #2 cannot find what it is looking for. I may be off though,
because you are adding these rows directly so there really is no original PK
value, there is only the current added value.

DT3 has no children, right?

Are these typed datasets by definition, or did you add DataRelations in
code?

Brad Williams

 

[Bob]

Brad,

I'm sorry it took so long to respond but I moved on past this problem
venturing into a whole new realm of them. ;-}

I really, really appreciate your efforts to help me. Many thanks.

To put some "closure" to this for you, and others:

Are *any* columns DateTime?

Yes, there are some DateTime cols.

I wonder if the row states of the rows in DS.DT3 are getting updated in such
a way by the first DS.Merge(DT3) line that the second call can't work
because the "original" row is lost.

I agree, the first Merge is somehow messing things up for the second
Merge.

I would try watching in a debugger:
Find a simple case in which DT3 only has one row yet breaks the above code.

Again, I agree. This was my next move - to create a simple single row,
single col table that reproduces the problem and go from there. Or the
smallest number of rows/cols that can reproduce it.

Quite curious to see if the problem still occurs when there are no
DateTime cols, which is along the lines of your suggestion.

DT3 has no children, right?

Correct, DT3 has no children.

Are these typed datasets by definition, or did you add DataRelations in code?

These are not typed datasets; yes, I did the relations in code.

What I ended up doing is cloning the DS, merging the DTs into the
cloned DS, then merging the cloned DS into the real DS. (This seemed
less hassle than LoadDataRow, which also worked. But I'm not sure that
LoadDataRow isn't faster.)

Thanks again sir,

Bob