CODEWITHSUNDEEP
c#ado.netinsertauto-increment
kon apaz
kon apaz

Reputation: 78

How to save a new record when the id key is auto incremented

I have created a database in sql server 2008 and a table with auto increment id field

Also I develop a project in c#.net 2008

The problem occurs on click "save data" after of "add new" from BindingNavigator

for example I click "add new", fill all fields except id text (blanked id) and then click 'save data' then occurs the errors

The runtime exception is "notNullAllowedException: the column 'id' not allowed null.

But it is not required from the user to fill this field but has to returned from database after the save

I also set the properties of the id: (both c# project and sql server)

AutoIncrement = True
AutoIncrementStep = -1 (also I tested 1)
AutoIncrementSeed = -1 (also I tested 1)

No results.

How I could handled that?

Thanks

Upvotes: 0

Views: 8322

Answers (2)

n8wrl
n8wrl

Reputation: 19765

It sounds like you have two problems.

First, you're always inserting - what logic do you use to decide whether 'save' should be an insert or an update? I think this is the case becuase of the errors you're getting: "Cannot insert explicit..."

Second, you need to get the ID that SQL assinged on your insert so you can write it to your ID field for the next save=update. There are a couple of ways to do this, the way I prefer is to use a stored procedure to do the insert and pass an OUTPUT parameter that gets SCOPE_IDENTITY(). Something like this:

CREATE PROC xyz
(
    @data...
    @NEWID INT OUTPUT
)
AS
    ... do your INSERT ...
    SET @NewID = SCOPE_IDENTITY()

)

double-check my syntax - not sure what version of SQL you're using

Upvotes: 0

David
David

Reputation: 73554

Don't include the identity column in your Insert statement.

If your statement is 

INSERT INTO MyTable (ID, SomeField, SomeOtherField) VALUES (null, 'SomeValue', 'AnotherValue')

simply change it to

INSERT INTO MyTable ( SomeField, SomeOtherField) VALUES ( 'SomeValue', 'AnotherValue')

Upvotes: 4

Related Questions