-2

I am trying to run my program in Visual Studio 2015. I created a .mdf database and a table in it. When I try to run it, I get this the following error:

cannot insert explicit value for identity column in table tblinfo when IDENTITY_INSERT is set to OFF".

Table

sql connection codes

Stored Procedure

Hope someone can help me. Thanks.

Luuuud
  • 4,206
  • 2
  • 25
  • 34
KarmaKid
  • 25
  • 2
  • 1
    You cannot insert into a column that has been defined as an `IDENTITY` column. The entire point of that is to let the server create the value for you. – DavidG Dec 05 '17 at 11:39
  • @ShiranDror That's just a hack around the problem though, instead it's better to let the database create the value. – DavidG Dec 05 '17 at 11:40
  • 2
    @KarmaKid, please, post code, not images where is possible – mnieto Dec 05 '17 at 11:41
  • 2
    @DavidG setting IDENTITY_INSERT to ON is not a hack, it's an option. I've used it several times to move data from old to new databases – Shiran Dror Dec 05 '17 at 11:42
  • 1
    @ShiranDror As have I, but in this case it would be a hack to get the code working. Instead, OP should fix the code to not try and insert that value. – DavidG Dec 05 '17 at 11:43

2 Answers2

0

Scope for "SET IDENTITY_INSERT" is based on the connection only. For the new connection, it will be reset.

Here, if you want to insert the data into the identity column, you have to set the SET IDENTITY_INSERT ON in the stored procedure first before insert statement. Once data is inserted again set it to ON. SET IDENTITY_INSERT OFF after the insert statement.

Mittal Patel
  • 2,732
  • 14
  • 23
  • As pointed out in the comments above, this is a hack and probably shouldn't be recommended. – DavidG Dec 05 '17 at 11:55
  • @Mittal, SET INDENTITY_INSERT ON|OFF is intended for bulk insert not for normal operation, although your code is possible, it's not recommendable. Whats happen in your code if the insert fails? The set indentity_insert off never happens – mnieto Dec 05 '17 at 11:59
  • @mnieto, Agree with you, it is not recommended for the single insert. It should be used when we insert bulk data. Regarding the indentity_insert off, we can reset it in the try and catch block in a stored procedure. – Mittal Patel Dec 05 '17 at 12:08
-1

You should not insert your IDENTITY column. Instead, let SQL Server assign the value for you and return it back to your application code, with scope_identity

create procedure dbo.addorupdate
...
begin
  if @node = 'add'
  begin
    insert into tblinfo (firstname, lastname, age, gender) values (@firstname, @lastname, @age, @gender)
    return scope_identity()
  end
mnieto
  • 3,744
  • 4
  • 21
  • 37