Click here to Skip to main content
15,441,149 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
how can i insert generate id from SQL server as foreign key to another table. This error has occur 'Cannot insert explicit value for identity column in table 'Kursus' when IDENTITY_INSERT is set to OFF.'

How i can solve this problem. Thank You
Updated 6-Aug-21 0:44am

When you insert a row to a table which has an identity field, you don't mention this field in the insert statement at all. For example, you have a table with two columns:
- Id (identity)
- Name (varchar)

The insert statement could look like
INSERT INTO INTO TableName (Name) VALUES ('Some name');

After the insert you can use SCOPE_IDENTITY[^] to get the value of the inserted Id so you can use the value for a foreign key in some other insert into a child table.
Share this answer
Manfred Rudolf Bihy 3-Apr-11 12:04pm     CRLF
For most cases correct! 5+ Rare circumstances may nescessitate the usage of IDENTITY_INSERT = ON.
nazila24 3-Apr-11 12:33pm    
where must i used scope_identity?it is in .vb file?
Wendelius 3-Apr-11 13:05pm     CRLF
No, it's a SQL function so you can for example execute a query: SELECT SCOPE_IDENTITY() which would return the last inserted identity value.
nazila24 3-Apr-11 13:43pm     CRLF the statement is SQL function??...but you have told before i can use scope_identity after the still cannot understand where to use it?either in .vb file or sql query?? sorry because im still cannot understant..thank you because i have learn a new things
Wendelius 3-Apr-11 14:00pm    
After you have executed the INSERT statement you can use for example SqlDataReader class to fetch the new id value using the SCOPE_IDENTITY() function. Have a look at the example in the end of the SqlDataReader clas documentation: If you place the statement I previously mentioned to the queryString, you can read the value of the new id.
The error message implies that your trying to do something different from what you wrote in your question. You receive this error if you try to specify a value for an autogenerated ID field in an INSERT statement. It is possible to do that but you'd have to set IDENTITY_INSERT to ON, which will allow you to specify a value for an autogenerated field (generally not advisable except in rare circumstances).

Share this answer
Wendelius 3-Apr-11 12:07pm    
This is correct. But as you said, it's under very rare circumstances, like loading master-child data originating from somewhere else. My 5.
nazila24 3-Apr-11 12:35pm     CRLF
so, where should i set the identity_insert to on?i heard about it many time, but i don't know where i should set it? thank you
Manfred Rudolf Bihy 4-Apr-11 6:21am     CRLF
It's a SQL statement called SET that you'd have to use. Please read this documentation:
Inserting Values into forieng keys on another table goes like this.
Your table1 has columns of id, name, and column_id
Column_id is the forieng key from table2,
While table2 has the entities column_id
The query goes like this=>
Insert into table1_name(name, column_id) Values (Mike, (Select table2_id from table2_name)
Share this answer
Richard Deeming 6-Aug-21 7:06am    
Nothing to do with the question, which is over ten years old!

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900