Cannot INSERT data into table with identity column

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

In my program I have an EmployeeRegistry table which holds data of each employee's work attendance. Every attendance record is uniquely distinguished by an IDENTITY COLUMN called "AttendanceID" which has an IDENTITY SEED of 1 and an IDENTITY increment of 1. However when I try to insert an attendance record to the table via my VB.NET program. it gives an error saying:

"An explicit value for the identity column in table 'EmployeeRegistry' can only be specified when a column list is used and IDENTITY_INSERT is ON." I tried to insert data into the table using an SQL query via the SQL management studio and it inserted the data without a problem. What might be wrong?

Posted 8-Jan-10 16:03pm

obhasha07

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

thatraja · Answer 1 · 2010-01-13T21:38:00

Solution 3

Sounds like you are inserting values to the identity column. Don't give value for that column. Then verify again Identity increment & Identity Seed values.

execute the following statement

SET IDENTITY_INSERT table_name ON

then retry

Posted 13-Jan-10 21:38pm

thatraja

Christian Graus · Answer 2 · 2010-01-08T16:16:00

Solution 1

What's wrong is what the error says. You wrote code that specifies the identity value, when it is auto generated. Edit your post and post some code if you need more specific help.

Posted 8-Jan-10 16:16pm

Christian Graus

Abhinav S · Answer 3 · 2010-01-08T17:01:00

Solution 2

Are you accidentally trying to insert data into the key column?
In such a case, do not insert anything into the identity column and the rest of query should work fine.

Posted 8-Jan-10 17:01pm

Abhinav S