SQL Server IDENTITY Column - VB.NET Insert Error

  • Thread starter Thread starter harry
  • Start date Start date
H

harry

Hi,

I am unable to insert new rows into an SQL table with a Identity Column (The
Identity column is also the Primary Key).

The table is a new empty table, and the Identity column is set to Seed=1 and
Increment=1

The error I receive when attempting to insert 3 new rows is:
Column RecNum is contrained to be unique. Value '1' is already present.

These are the values being inserted.

0 1188110 False 3
1 1216300 False 4
2 1182714 False 3,4

The first Column with IDENTITY value=0 is inserted however as it is the
first row it's Identity is set to 1. Therefore the subsequent row with
Identity=1 triggers exception I guess?

How do I solve this? Shouldn't ADO.NET manage this bu itself?

Thanks
Harry
 
Thanks Maxim - that "SCOPE_IDENTITY() vs. @@IDENTITY" article on your site
is useful to know.

Maxim V. Karpov said:
Harry,
- Simply execute two SQL statement in the row one to Insert; second Select
@SCOPE_IDENTITY() and it will update dataset value for you.
- Use RowUpdated Event to retrieve SCOPE_IDENTITY()

Do not use @@Identity see my post:
http://ipattern.com/simpleblog/PermLink.aspx?entryid=25

I hope this helps, Maxim

[www.ipattern.com do you?]
harry said:
Hi,

I am unable to insert new rows into an SQL table with a Identity Column (The
Identity column is also the Primary Key).

The table is a new empty table, and the Identity column is set to Seed=1 and
Increment=1

The error I receive when attempting to insert 3 new rows is:
Column RecNum is contrained to be unique. Value '1' is already present.

These are the values being inserted.

0 1188110 False 3
1 1216300 False 4
2 1182714 False 3,4

The first Column with IDENTITY value=0 is inserted however as it is the
first row it's Identity is set to 1. Therefore the subsequent row with
Identity=1 triggers exception I guess?

How do I solve this? Shouldn't ADO.NET manage this bu itself?

Thanks
Harry
Click to expand...
Click to expand...
 
Hi, here's hoping someone out there remembers this thread..

After reading your very useful article I tried what you suggested (well
actually I cut and pasted the wizard's adapter code into my class).. At
first it didn't work.. The rows were updating, but after it was done the
newly added rows in my datatable would still contain the client-side
generated identities.. But when I re-ran the app they would show up with
proper identity numbers proving they'd saved..

After a little tracking I realized my problem was that before using the
adapter's update command I was using the GetUpdates command of my datatable,
so that I would only be updating the rows that had changed, instead of
having the adapter loop through every row of the table... The newly created
table of just the updates would then append itself to my DB and the update
command would return the propery identity values to the new table's rows..

And then, of course, the program would promptly discard the table, leaving
me with my original table and it's improperly ID'd rows...

Now if I don't use .GetUpdates everything works flawlessly, but I'm
confused. By skipping this step aren't I making the computer do as much
work as I would be by running the update, and then simply running a new
select to refresh the datatable? The test data I'm using now is small
enough that one wouldn't notice the difference, but I like to at least think
I'm striving for the most efficient way of coding things..

So am I wrong? If not, is there a way to put the values from a .GetUpdates
created table back into the original table they came from?

*scratches head*

Bill Rowland
 
All mention of .GetUpdates in the previous post were supposed to be
..GetChanges... Sorry..
 
Back
Top