AddNewRow

[szabelin]

Hi,I am trying to a several rows like this:

1. Get new datarow from typed DataTable object (datatable)
2. Set datarow's values (except the primary key - it's
marked as identity and is generated by sql server)
3. Add datarow to my datatable object

after adding several rows I call Update on dataadapter
passing my datatable object to it.

The error I get is that primary key constraint is being
violated. Why? When I dump primary keys of my datarow
collection, they are all unique. Any help?

Thanks!

 

[William Ryan]

Is anyone else trying to update while you are
testing/getting this error? It's possible that other
code is generating the original ID value and that the
seeds are setting values on different datasets (on
different machines) to the same things. Then, when you
send the updates back to the db, that could be causing
the problem. What seed value are you using and what
increment value?


For what it's worth, the whole Identity thing can be a
real pain in the b**** when you are working with large
disconnected sets with multiple users.

Can you try in your exception handler, update one row at
a time. If you throw the exception, catch it in your
handler and increment the value by one. Try to update
again and see what happens. This can be done
recursively, althought intentionally throwing exceptions
over and over can lead to performance problems.

Let me know what happens.

Bill

W.G. Ryan
(e-mail address removed)
www.knowdotnet.com

 

[William Ryan]

David:

I just read your response below and I'm intrigued. My
post was in response to the one above, but I had a lot of
trouble with the Identity thing, that I developed a
scheme to do away with it. I guess I didn't think of it
before, but if the value is negative as you mention, will
it just add it to the next avaiable number once it's
submitted to the db? If so, that's a lot simpler than
what we came up with, which entailed requerying the db
for the next largest value...not using real identities.
Then, if I an exception was thrown, we'd grab it in the
exception handler and bump it up until it worked.

If the negative numbers will work, man that's a much
better way.

BTW, you're ADO.NET Core Reference is unquestionably one
of the best books I've purchased in forever. Appreciate
both the book and the reponse.

Thanks,

Bill

 

[David Sceppa]

Bill,

if the value is negative as you mention, will
it just add it to the next avaiable number once it's
submitted to the db?

I'm not sure I understand the question. Hopefully my
response covers the question, but if I'm way off base, let me
know.

Think of the auto-increment values that ADO.NET generates
for pending inserts as placeholders. You're not going to submit
these values to the database. The database will have no idea
whether ADO.NET used a placeholder value of 17 or -12345. The
database will generate the actual values. You can configure your
DataAdapter's InsertCommand (or add code to the RowUpdated event)
to fetch the new auto-increment values that the database
generates. You can also use DataRelations to cascade the newly
retrieved values down to related child rows, a major improvement
over this scenario in prior data access models.

Generating placeholder values that may exist in the database
can cause problems. Say you create a few new order rows and
ADO.NET generates 1, 2, 3 for the OrderID column for those new
rows. Then, you query for an existing customer's order history
and one of that customer's orders has an OrderID of 2. If you
try to add that row to your DataTable by calling
DataAdapter.Fill, you'll receive a constraint exception because a
row with that same OrderID already exists within the DataTable.

Similarly, if you try to submit these new orders to the
database and the first new OrderID returned is 3, you'll receive
a constraint exception. (I believe this is the scenario that led
to the initial post in the thread.) You could temporarily
disable enforcement of constraints (DataSet.EnforceConstraints =
False) prior to calling DataAdapter.Update to avoid the
exception. After submitting the orders and re-enabling the
constraints, you would have orders with OrderIDs of 3, 4, and 5,
but even temporarily allowing duplicate primary key values in
your DataTable can cause major headaches. ADO.NET will help you
cascade newly retrieved key values to related child rows, but if
you have multiple parent rows with the same key value(s) you can
wind up with mis-matched child rows.

Generating negative placeholder values (-1, -2, -3) prevents
these problems from occurring, and also makes it easier to
identify data that has not been committed to the database. You
don't want the application user to get the false impression that
the placeholder value for a pending insert will be the actual
value when the row is submitted to the database.

The approach you described can also work especially if you
can't or don't want to use auto-increment columns or sequences in
your database, though I'd imagine the code gets a little hairy if
you have to make repeated attempts to submit multiple new rows.
I generally try to avoid discussions on whether identity/sequence
or GUID columns are good or bad ideas and stick to safer topics
like politics.

Thank you for the kind words regarding the book and for
helping out in the community.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2003 Microsoft Corporation. All rights reserved.

 

[szabelin]

Thank you both for replying. I ended up setting my PK to
(-1)*rowIndex for every new row and it works fine now. So
I didn't have to change anything in sql server. There
might be another solution setting ID = SCOPE_IDENTITY() in
SqlInsertCommand, as described in
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/dnadonet/html/manidcrisis.asp but I
didn't read into it.

S