Insert automatic generate id as foreign key to another table

Question

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 3-Apr-11 5:54am

nazila24

Updated 6-Aug-21 0:44am

Kristian Sixhøj

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)

Wendelius · Answer 1 · 2011-04-03T06:01:00

Solution 1

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.

Posted 3-Apr-11 6:01am

Wendelius

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.

Manfred Rudolf Bihy · Answer 2 · 2011-04-03T06:03:00

Solution 2

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!

Posted 3-Apr-11 6:03am

Manfred Rudolf Bihy

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

Member 15315276 · Answer 3 · 2021-08-06T00:44:00

Solution 3

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)

Posted 6-Aug-21 0:44am

Member 15315276

Comments

Richard Deeming 6-Aug-21 7:06am

Nothing to do with the question, which is over ten years old!