SQL Server IDENTITY Column - VB.NET Insert Error

[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

 

[Miha Markic]

Hi,

The first thing to change is identityseed/step on DataTable to negative
values.
Read also this:

HOW TO: Retrieve an Identity Value from a Newly Inserted Record from
SQL Server by Using Visual C# .NET

http://support.microsoft.com/default.aspx?scid=kb;en-us;320897&Product=vcSnet
and
Managing an @@IDENTITY Crisis

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/manidcrisis.asp

 

[Maxim V. Karpov]

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]

Thank you Miha

That article solved the problem.

 

[harry]

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

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?]

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

 

[W.G. Rowland]

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

 

[W.G. Rowland]

All mention of .GetUpdates in the previous post were supposed to be
..GetChanges... Sorry..