Click here to Skip to main content
15,064,140 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
Posted
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.
   
Comments
Manfred Rudolf Bihy 3-Apr-11 12:04pm
   
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
   
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
   
ok...so the statement is SQL function??...but you have told before i can use scope_identity after the insert..im 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: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.aspx 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).

Cheers!
   
Comments
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
   
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
   
It's a SQL statement called SET that you'd have to use. Please read this documentation: http://msdn.microsoft.com/en-us/library/ms188059%28v=SQL.90%29.aspx
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)
   
Comments
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