How to insert a new record which has a auto increment number as primary key?

Question

For example, I have the following Table:

CustomerInfo

cus_id: auto increment, int, is Identity
cus_name: nvarchar

If I use the follow codes to insert the record "Peter",

string name = "Peter";
DataContext DC = new DataContext();
CustomerInfo newCustomer = new CustomerInfo();
newCustomer.cus_name = name;

DC.CustomerInfos.InsertOnSubmit(newCustomer);
DC.SubmitChanges();

The following error returns,

Can't perform Create, Update, or Delete operations on 'Table(CustomerInfo)' because it has no primary key.

Do I need to self-define the cus_id or any other solutions? Thanks!

`cus_id` is not a Primary Key, or at least that's what the error says — Cristian Lupascu, Jul 31 '13 at 06:09
Sorry, it is not a PK but is identity. I have changed it in my question. — Pang, Jul 31 '13 at 06:14
@Pang you'll need to add a Primary Key to the table; that's the way LinqToSql works. And generally [this is good practice](http://stackoverflow.com/a/840182/390819) anyway — Cristian Lupascu, Jul 31 '13 at 06:22
Hi, I have set the cus_id as primary key but the other error comes out: Incorrect AutoSync specification for member 'cus_id' — Pang, Jul 31 '13 at 09:42

score 10 · Accepted Answer · answered Jul 31 '13 at 06:32

First of all LINQ-To-SQL needs primary keys in order to be able to do Inserts and Updates, so you probably have to add the Primary Key in your table.

Now, because it is an auto incremented identity column, in your dbml, you have to select the column "cus_id" of the "CustomerInfo" table and go to the properties and set the following:

Auto Generated Value : True
Auto-Sync : OnInsert

This will ensure that when you insert a new row it will get a new id.

score 1 · Answer 2 · edited May 23 '17 at 12:02

1

naratting from answer 1 of question you'll have to make cus_id as primary key too. you can also try to do following

CREATE TABLE Customers
(
cus_id int NOT NULL AUTO_INCREMENT,
cus_name varchar(255) NOT NULL,
PRIMARY KEY (cus_id)
)

edited May 23 '17 at 12:02

Community

1
1

answered Jul 31 '13 at 06:20

Anfal

344
1
3
15

score 0 · Answer 3 · answered Jul 31 '13 at 06:19

I got this error to. As far as I manged to fix it was to add a primary key. This code made me a primary key and made it autoincrement. Maybe it's what you are looking for? (When you create the table)

columnname bigint IDENTITY(1,1) NOT NULL,
CONSTRAINT pkcolumnname PRIMARY KEY CLUSTERED

How to insert a new record which has a auto increment number as primary key?

3 Answers3

Linked