How to increment an auto-increment field with ADO.

Question

I have a database table with three fields:

Product(ProdId, Name, Price)

Where ProdId is a auto-increment field. The auto-increment is with seed 1, starting from 0.

I perform operation on the database by using C# in an ASP.NET application.

If I use LINQ to INSERT a new record I just assign values to Name and Price fields and when I submit the changes the database is responsible to increment the ProdId.

How can I do it with the standard ADO.NET? If I write an INSERT statement

INSERT INTO Product (Name, Price) VALUES (@Name, @Price)

when I execute the command an exception is thrown;

Cannot insert NULL in ProdId.

Anybody has a solution? Thanks

Answer 1

How did you define the ProdId, try INT NOT NULL AUTO_INCREMENT ...

Answer 2

I would start debugging in this order:

• Make sure in your SQL you do have AUTO Increment on

Data Types can be numeric, money, decimal, float, bigint and int

• using the Studio Management tool, run the insert query manually

like:

DECLARE @Name nvarchar(100), @Price money

SET @Name = 'Bruno Alexandre';
SET @Price = 100.10;

INSERT INTO Product (Name, Price) VALUES (@Name, @Price)

or

INSERT INTO Product SELECT @Name, @Price

• Make SURE that you are pointing in your ADO Connection to the correct Database.

• If using EF, refresh your table using the Update Table Wizard on the .edmx file