New Row insertion with CommandBuilder not working

  • Thread starter Thread starter Learning SQL Server
  • Start date Start date
L

Learning SQL Server

My row insertion keeps failing while using the CommandBuilder. I believe I
know why, but I am not sure how to resolve it.

I have a datagrid that will update rows in a database, as well as add a few
more. I am adding the new row to the dataset before sending my changes back
to the database. The updates of existing rows works fine. When I insert
rows, however, I get an error stating that my column is declared as unique
and that inserting the row will violate the primary key constraint. It also
shows the numeric key value in the error, which I see is the already
assigned primary key of the row I am trying to UPDATE.

So I think I need to get the next value of the primary key of the table and
assign this to the row I am creating and adding to my dataset. But how do I
do this?

Again, my updates are working fine, but Insertions fail. Basically I thought
you could do both insertions and updates with a single CommandBuilder object
but it doesnt appear to be the case...
 
Yep, you are right.

You'll need to either change the value of the column or
using something like the autoincrement property fo the
colummn.

You can also trap the exception, and in the trap, reset
the value to value + 1 or some other number that you know
will work. Reset the value and then fire the update
again.

Good Luck,

Bill


Cordially,


W.G. Ryan

(e-mail address removed)
www.knowdotnet.com
 
William:

Thanks for your fast reply.

I notice that the SqlDataAdapter's MissingSchemaAction.AddWithKeys might
help, but I worry about concurrency. About 50 - 100 concurrent users are
expected to use my application. I am retrieving key information for the
dataset. Can I reliably still use this?
 
What didn't work? If you are on the desktop, the
disconnected thing isn't really your buddy. Especially if
you have 100 users. All of that data is gotten call and
kill so it'll get stale immediately. Here's the best way
I've found...

try the insert, and if it fails, sobeit.

Trap a specific exception like System.SQLException-Don't
Trap System.Exception!!!!!!!!!!!!! unless you do it after
looking for the specific one. If your sql fails, requery
the database and find a value that will work. update the
row and fire it again. Implement this recursively if
necessary (make sure you have an exit condition though
that will be hit) until you find an insert that will work.

I typically use somethign I dub Max+1. Query the DB
immediately after the Cell value is changed, find out the
max DB value. Add 1 to it, and implement the recursive
exception handler.

The more connections you have, the more you'll hate
identity / autoincrement fields.

Think about it conceptually - I had to. Do you think
that the folks that created them, as faithful to the good
Dr. Codd as they were, even conceptualized a disconnected
environment? Hint (NO) So a lot of how we think about
Key generation is obsolete. Hence GUID columsn in SQL
Server. Such values (autoincremnet) are not only useless
in a disconnected 2+ client environment, they are an
impediment. Thank to Lord for structured exceptions.

If you need more specific help, let me know.

Best Wishes,

Bill

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

I worry in your scenario about the key values changing after acquiring the
last value. Do you simply continue to query until the exception is no longer
raised?
 
Yes, I keep looking until I get a clean hit. However, if
you get the value and push the update immediately, as in
the next line, you'll be ok except in really extreme
scenarios.

For what it's worth, I have an app that I inherited and
would have used GUID's as the key and used other vals
locally for relationships. But I was stuck using
autoincrements. I have over 120+ people banging on it
and I write to a messageque when I get an exception. In
over 6 months with the volume I reference, I've had 12
successive exceptions that nested more than one level.
Only 3 nested nested past three levels. The problem is
incorrect keys when it's all said and done, and changing
it at some point will be worht the effort. But you can
accomplish this what you need recursively with little
effort. on a scale of 1 to 10, it's about a 1.5, it'll
work.

Good Luck,

Bill

Cordially,

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