MSSQL - Cannot insert explicit value for identity column

wuchi · Post by **wuchi** » Wed Dec 03, 2014 12:58 am

Hello,

I have a task to solve a problem. I have four tables. 1 - Pub ,2 - Food , 3 - Accesories, 4 - Ingredients. I need to link together in this order: Pub -> Food -> ingredients -> Accesories.

So basicly one PUB can have MANY Food and ONE Food can have MANY ingredients and ONE ingredients can have MANY accesories...and i want that there will not be duplicate FOOD (one flour for example).

I tried with foreign keys no luck every time that error with IDENTITY. - > in database it is set to autoincreasement. I am using SQL 2012.

Wuchi

mobhar · Post by **mobhar** » Wed Dec 03, 2014 12:25 pm

What is the complete error message that you have seen?

wuchi · Post by **wuchi** » Wed Dec 03, 2014 3:39 pm

Hello,

Failed to execute SQL: INSERT INTO [dbo].[sifrantsestavin] ([sestavina],[ID]) VALUES ('234',1). Error: Cannot insert explicit value for identity column in table 'sifrantsestavin' when IDENTITY_INSERT is set to OFF.

IDENTITY is set to autoincreasement. Is there no way around this then to program Row_Inserted and discard ID from it...Problem is only when i have relation sestavina -> veznafoodsestavine -> food...it seems that it ignores that i have put ID out from EDIT in definition of food or sestavine...probably there is only simple tweet around this...maybe i am doing something wrong with the definition...

br,
Wuchi

mobhar · Post by **mobhar** » Wed Dec 03, 2014 6:05 pm

Just uncheck the [ID] field under "Add Page" of "Fields" setup to exclude it from "Add" form.
Or, if the issue still remains, then try to insert the following code in "Row_Inserting" server event:

$rsnew["ID"] = NULL;

Post by **Webmaster** » Thu Dec 04, 2014 10:29 am

Use:

unset($rsnew["ID"]);

wuchi · Post by **wuchi** » Wed Dec 10, 2014 4:45 pm

Hello,

thanks everyone. Works...

Best regards,
Wuchi