Hi all,
Run command: Cannot insert explicit value for identity column in table ‘Table name’ when IDENTITY_INSERT is set to OFF.
How to resolve this issue. Please help me on this.
As per error you are trying to insert a value into identity column which does not allow insert it would automatically generate the value…please leave that column and insert into other columns
cheers
insert into PphShopFindingReports (Id, RequestReceivedOn, EngineModel, PrimaryModuleSerialNumber, PrimaryModule2SerialNumber, SecondaryModuleSerialNumber, ReasonForRemovalBW, PrimaryModuleWorkScopeFinal, PrimaryModule2WorkScopeFinal, SecondaryModuleWorkScopeFinal, PrimaryModuleTSN, PrimaryModuleCSN, PrimaryModuleTSO, PrimaryModuleCSO, PrimaryModuleTSHSI, PrimaryModuleTSLV, PrimaryModuleCSLV, PrimaryModule2TSN, PrimaryModule2CSN, PrimaryModule2TSO, PrimaryModule2CSO, PrimaryModule2TSHSI, PrimaryModule2TSLSV, SecondaryModuleTSN, SecondaryModuleCSN, SecondaryModuleTSO, SecondaryModuleTSLR, SecondaryModuleTSLV, HSICategory, PrimaryModuleOVHCategory, PrimaryModule2OVHCategory, SecondaryModuleOVHCategory, ProdSapUpdate, StagSapUpdate, SDOrder, ReasonForRemovalAdditionalInfo, SBIncorpDuringShopVisit, DateOfRemoval, FileName, Status, ReplaceWorkScope, StagUpdate, ProdUpdate, [File], StatusId, Description, ProcessTime, RequestUserId, IsLocked, LockedTime, ShopEventDateRecieved, Discrepancy, EngineMarket) values (’ ‘,’ ‘,’“+APS1+”‘,’ ‘,’ ‘,’ ‘,’“+Reason_remove+”‘,’“+power_sec+”‘,’“+Pri_load+”‘,’“+Sec_gear+”‘,’“+tt1+”‘,’“+tslv1+”‘,’ ‘,’ ‘,’ ‘,’“+tsr2+”‘,’“+cslv1+”‘,’ ‘, ’ ‘, ’ ‘, ’ ‘, ’ ‘, ’ ‘,’ ‘,’ ‘,’ ‘,’ ‘,’ ‘,’ ‘,’ ‘,’ ‘,’ ‘,’ ‘,’ ‘,’“+ SD1+”’,’“+add_info_rm+”’,’“+SB_incorp1+”’,‘“+str2+”’,‘“+filename+”’,’ ‘,’ ‘,’ ‘,’ ‘, 0,’ ', ’ ', ’ ', ’ ', ’ ', ’ ', ‘“+str1+”’, ’ ', ’ ')
This is the query
Hi @lakshmi.mp
some syntax errors and potential issues with the way the values are being concatenated.
Try With this
INSERT INTO PphShopFindingReports (
Id,
RequestReceivedOn,
EngineModel,
PrimaryModuleSerialNumber,
PrimaryModule2SerialNumber,
SecondaryModuleSerialNumber,
ReasonForRemovalBW,
PrimaryModuleWorkScopeFinal,
PrimaryModule2WorkScopeFinal,
SecondaryModuleWorkScopeFinal,
PrimaryModuleTSN,
PrimaryModuleCSN,
PrimaryModuleTSO,
PrimaryModuleCSO,
PrimaryModuleTSHSI,
PrimaryModuleTSLV,
PrimaryModuleCSLV,
PrimaryModule2TSN,
PrimaryModule2CSN,
PrimaryModule2TSO,
PrimaryModule2CSO,
PrimaryModule2TSHSI,
PrimaryModule2TSLSV,
SecondaryModuleTSN,
SecondaryModuleCSN,
SecondaryModuleTSO,
SecondaryModuleTSLR,
SecondaryModuleTSLV,
HSICategory,
PrimaryModuleOVHCategory,
PrimaryModule2OVHCategory,
SecondaryModuleOVHCategory,
ProdSapUpdate,
StagSapUpdate,
SDOrder,
ReasonForRemovalAdditionalInfo,
SBIncorpDuringShopVisit,
DateOfRemoval,
FileName,
Status,
ReplaceWorkScope,
StagUpdate,
ProdUpdate,
[File],
StatusId,
Description,
ProcessTime,
RequestUserId,
IsLocked,
LockedTime,
ShopEventDateRecieved,
Discrepancy,
EngineMarket
)
VALUES (
‘’,
‘’,
‘" + APS1 + "’,
‘’,
‘’,
‘’,
‘" + Reason_remove + "’,
‘" + power_sec + "’,
‘" + Pri_load + "’,
‘" + Sec_gear + "’,
‘" + tt1 + "’,
‘" + tslv1 + "’,
‘’,
‘’,
‘’,
‘" + tsr2 + "’,
‘" + cslv1 + "’,
‘’,
‘’,
‘’,
‘’,
‘’,
‘’,
‘’,
‘’,
‘" + SD1 + "’,
‘" + add_info_rm + "’,
‘" + SB_incorp1 + "’,
‘" + str2 + "’,
‘" + filename + "’,
‘’,
‘’,
‘’,
‘’,
0,
‘’,
‘’,
‘’,
‘’,
‘’,
‘" + str1 + "’,
‘’,
‘’
);
Hope it helps!!
@pravallikapaluri, this error getting Run command: Incorrect syntax near ‘‘’.
Looks like your id is a identify column…remove that column and the value and check
Cheers
@Anil_G , Id column has to be removed from both sql database and query?
Not from database only the query…
First check if the identify column is ID or not…for that go to your db and check the column types…
cheers
@Anil_G ,
Run command: The INSERT statement conflicted with the FOREIGN KEY constraint “FK_PphShopFindingReports_Users_RequestUserId”. The conflict occurred in database “AFM_AUTOMATION_DB”, table “dbo.Users”, column ‘Id’.
The statement has been terminated.
this error i am getting now.
“insert into PphShopFindingReports ( RequestReceivedOn, EngineModel, PrimaryModuleSerialNumber, PrimaryModule2SerialNumber, SecondaryModuleSerialNumber, ReasonForRemovalBW, PrimaryModuleWorkScopeFinal, PrimaryModule2WorkScopeFinal, SecondaryModuleWorkScopeFinal, PrimaryModuleTSN, PrimaryModuleCSN, PrimaryModuleTSO, PrimaryModuleCSO, PrimaryModuleTSHSI, PrimaryModuleTSLV, PrimaryModuleCSLV, PrimaryModule2TSN, PrimaryModule2CSN, PrimaryModule2TSO, PrimaryModule2CSO, PrimaryModule2TSHSI, PrimaryModule2TSLSV, SecondaryModuleTSN, SecondaryModuleCSN, SecondaryModuleTSO, SecondaryModuleTSLR, SecondaryModuleTSLV, HSICategory, PrimaryModuleOVHCategory, PrimaryModule2OVHCategory, SecondaryModuleOVHCategory, ProdSapUpdate, StagSapUpdate, SDOrder, ReasonForRemovalAdditionalInfo, SBIncorpDuringShopVisit, DateOfRemoval, FileName, Status, ReplaceWorkScope, StagUpdate, ProdUpdate, [File], StatusId, Description, ProcessTime, RequestUserId, IsLocked, LockedTime, ShopEventDateRecieved, Discrepancy, EngineMarket) values (’ ‘,’”+APS1+“‘,’ ‘,’ ‘,’ ‘,’”+Reason_remove+“‘,’”+power_sec+“‘,’”+Pri_load+“‘,’”+Sec_gear+“‘,’”+tt1+“‘,’”+tslv1+“‘,’ ‘,’ ‘,’ ‘,’”+tsr2+“‘,’”+cslv1+“‘,’ ', ’ ', ’ ', ’ ', ’ ', ’ ‘,’ ‘,’ ‘,’ ‘,’ ‘,’ ‘,’ ‘,’ ‘,’ ‘,’ ‘,’ ‘,’ ‘,’”+ SD1+“‘,’”+add_info_rm+“‘,’”+SB_incorp1+“‘,’”+str2+“‘,’”+filename+“‘,’ ‘,’ ‘,’ ‘,’ ‘, 0,’ ', ’ ', ’ ', ’ ', ’ ', ’ ', '”+str1+“', ’ ', ’ ')”
this is the query
@Anil_G , Identify column means int type right?
No …on the table one column is set as an identify column…please check which column it is and remove that column from The insert statement
Please check this on how to find identity column
Cheers
You have a column that is automatically populated with a number sequence. You’re trying to insert a value into that column but it won’t let you because it’s an “autonumber” column. There is a setting in your database system that will allow that column to be manually populated. However, you have to consider that you must insert only unique values.
Hi @postwick,
How to change the setting in database system.
Hi all,
Run command: The INSERT statement conflicted with the FOREIGN KEY constraint “FK_PphShopFindingReports_Users_RequestUserId”. The conflict occurred in database “AFM_AUTOMATION_DB”, table “dbo.Users”, column ‘Id’.
The statement has been terminated.
I removed Id from query then I am getting this error, not getting how to resolve this.
Please guide me on this…
You should talk to your database administrator about how to properly do this query.
parvathi_ayanala
jamiejam
Anirudh_Tugawe