StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » HELP.... Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF.

HELP.... Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF.

Ross L Rooker, Sr.(1) — Thu, 13 Nov 2014 06:35:23 GMT

By default the table has an auto incrementing primary key. I need to be able to specify the primary key in the insert command. I checked the insert statement being sent to the backend and that insert statement looks good. I must be missing something. Why am I getting this error?

string strSQL = "set identity_insert tblIndividual ON ";
ExecuteNonQuery(strSQL);

this.PrimaryKeyIsAutoIncremented = false;
this.PrimaryKeyIsUpdatable = true;

this.Add();
this.Individual_First = "John";
this.Individual_Last = "Doe";
this.Individual_Key = 12345;      // Primary Key
this.Save();         ---------------> this line gets the error

-------------------------------------------------------------------------------------------------
DataLayerSavingException
Cannot insert explicit value for identity column in table 'tblIndividual' when IDENTITY_INSERT is set to OFF.
SqlException
Cannot insert explicit value for identity column in table 'tblIndividual' when IDENTITY_INSERT is set to OFF.

Source     : MicroFour StrataFrame Business
-------------------------------------------------------------------------------------------------

string strSQL = "set identity_insert tblIndividual OFF ";
ExecuteNonQuery(strSQL);

this.PrimaryKeyIsAutoIncremented = true;
this.PrimaryKeyIsUpdatable = false;

RE: HELP.... Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF.

Ross L Rooker, Sr.(1) — Thu, 13 Nov 2014 06:35:23 GMT

Not using a stored procedure.

RE: HELP.... Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF.

Edhy Rijo — Wed, 12 Nov 2014 18:24:32 GMT

Hi Ross,

Are you using using Stored Procedures (sp) to insert records in this BO?

If so and the sp was build by the SF DDT, then you will not be able to do what you want because the sp is already pre-build by the DDT with the auto-incremented setting being enabled.

Assuming you are using a sp, then you will have to change the settings in the BO to not use the stored procedure and then you method should work as expected.