SQL Server CE Insert into table doesnt seem to increment identity

Question

I have a table defined like this:

CREATE TABLE ItemCategory
(
    ID INT IDENTITY NOT NULL PRIMARY KEY,
    CategoryID INT NOT NULL,
    ItemID INT NOT NULL
);

This table links to two tables on the two foreign keys.

I have this SQL statement

INSERT INTO ItemCategory (CategoryID, ItemID) 
VALUES (@category, @item)

with the parameters being set as follows:

var p1 = new SqlCeParameter("@category", SqlDbType.Int);
var p2 = new SqlCeParameter("@item", SqlDbType.Int);
p1.Value = categoryID;
p2.Value = itemID;

The values of the parameters are correct (I have debugged and checked) therefore there shouldn't be any foreign key errors.

When I execute the SQL statement I get the following error:

The column cannot contain null values. [ Column name = ID,Table name = ItemCategory ]

However I don't understand this as the ID column is set as an Identity column and therefore I should not have to supply a value and it should be auto incremented automagically.

Why could this be?

• C#
• SQL Server Compact Edition 4.0
• Visual Studio 2012

Answer 1

Try adding IDENTITY (1,1)

 [Id] int NOT NULL  IDENTITY (1,1)