Insert into identity column

Question

0.00/5 (No votes)

See more:

Hi All,

I have table which has identity column. Many threads will be running from dotnet side and will be inserting records in this table.

When it is happening I am getting error some time:
"Cannot insert the value NULL into column 'identity column', table 'xyz'; column does not allow nulls."

Thanks in advance.

Posted 15-Apr-11 2:58am

govardhan4u

Updated 15-Apr-11 3:22am

Chris Maunder773.1K

v3

Add a Solution

Comments

Chris Maunder 15-Apr-11 9:22am

You need to send us the statement you are using to insert the records.

5 solutions

Solution 4

Inserting values into the identity column is usually disallowed so I presume you have used SET IDENTITY_INSERT ON to enable this?
Inserting values into an identity column from more than one thread should prove to be difficult. How are you keeping the threads from using the same value for the identity column on ~~each~~ every insert and what kind of data are you inserting where you need to explicitely set the identity column.

It would be best if you showed us the code that did these inserts. We only need the relevant bits like the SQL statement and how you're supplying the data.

Best Regards,

-MRB

Posted 15-Apr-11 3:09am

Manfred Rudolf Bihy

Updated 15-Apr-11 3:17am

v2

Comments

Espen Harlinn 15-Apr-11 9:39am

SET IDENTITY_INSERT ON|OFF works nicely, my 5

Solution 5

Are you sure it's an identity column that's been configured with an Identity Increment?

It sounds like you've created a Primary Key (which of course wont allow NULL) - if it was an identity, it would automatically assign an ID after the insert.

If you're still stuck, post your CREATE TABLE syntax for the table and your SQL for inserting records.

Posted 15-Apr-11 3:09am

Dylan Morley

Solution 2

Probably you insert nothing into column identity and the column must be filled because it does not allow nulls!

Posted 15-Apr-11 3:07am

Jeffrey Enzo

Comments

OriginalGriff 15-Apr-11 9:09am

Have a look at this: http://en.wikipedia.org/wiki/Identity_column

Manfred Rudolf Bihy 15-Apr-11 9:15am

Please do as Griff suggested and read the page he linked to.
Not voted.

Jeffrey Enzo 15-Apr-11 9:17am

Sorry Guys, I thought he mentioned something else. I have read his link, and I know what he means. Thans OrginalGriff!!

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)

Sandeep Mewara · Accepted Answer · 2011-04-15T03:06:00

"Cannot insert the value NULL into column 'identity column', table 'xyz'; column does not allow nulls."
Which part of it was not clear to you?

What it means in other words: You have defined a column as an identity column. Since it is an identity column in the table, it cannot hold any NULL value. You are trying to insert a NULL in that and thus you get an error.

Now, what I think you must be missing here is setting the 'AutoIncrement' to true. Just do it and then if you ignore that column at the time of inserts, it would automatically increment by 1 on each insertion and error should be gone.

Just in case of how to do it: Edit the table design. Select the column and then in it's property grid, you will find the Identity part where you will have an option to make it autoincrement.

OriginalGriff · Accepted Answer · 2011-04-15T03:08:00

You can't do it.

It won't work. Ever. At all. It isn't allowed.

When you make a column "identity" you are saying to the database: "You take care of this: I don't care what numbers you give this field, just make sure that you never give a duplicate".

If you could write to the field, then the "don't give a duplicate" bit goes right out the window. The database (very rightly) complains because it conflicts with the instructions you have already given it.

Think about it: Would you be allowed to pick your own house number? No, because every street would have six houses with the number "007" and twenty "42"s.

Insert into identity column

5 solutions

Solution 1

Solution 4

Solution 3

Solution 5

Solution 2

Add your solution here

Preview 0