Identity_insert ?

• Mitch-157840

  Mr or Mrs. 500

  Points: 501

  More actions

  October 18, 2004 at 6:30 pm

  #162844

  Hello all,

          trying to create a record on a webpage I created but i'm getting

  this error message....

  Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)

  [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert explicit value for identity column in table 'tblBooking' when IDENTITY_INSERT is set to OFF.

   

  How do I go about turning this feature on ?

  Regards,

  Mitch....

• Phil Parkin

  SSC Guru

  Points: 246962

  More actions

  October 18, 2004 at 7:32 pm

  #527107

  Hi Mitch

  Sounds like you are trying to insert an explicit value into a field that has been designed to assign its own values automatically on insert (possibly an auto-incrementing primary key in tblBooking).

  Assuming that you are aware of this and doing it deliberately for some reason, check out SET IDENTITY_INSERT in BOL and that should help you out.

  Alternatively, just remove the identity field from your insert query and let SQL Server handle the job.

  Regards

  Phil

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• Mitch-157840

  Mr or Mrs. 500

  Points: 501

  More actions

  October 19, 2004 at 12:03 pm

  #527224

  Thanks Phil,

                 yes, I created the table in Access and then upsized it.

  The ID field is an autonumber field so I guess that is causing me the trouble.

  Thanks again for your help,

  Mitch....