how to find out which field in my database is set as the identity field?

• morten-908506

  Mr or Mrs. 500

  Points: 506

  More actions

  January 9, 2009 at 10:47 am

  #199855

  Hi

  My subscription fuction on the websute has stopped working

  when i click "submit" I get following error:

  An explicit value for the identity column in table 'tblMails' can only be specified when a column list is used and IDENTITY_INSERT is ON.

  ------------------

  Got an advice to go into SQL Server Enteprise manager and make a query in the database:

  SET IDENTITY_INSERT dbo.tblMails ON

  And it executed successfully

  Its still not working

  Then I got an advice to check what field in my database is set as identity field, in case there is a 2nd one.

  Now I am stuck

  How to I check this.

  This database has about 30 tables where one of them is "tblMails"

  I am very new to SQL and the only tool I know is SQL Server Enteprise Manager so if I have do to anything else, remember I am a newbe

  Hope anyone can help me where to go from here

• Lester Policarpio

  SSCertifiable

  Points: 7109

  More actions

  January 13, 2009 at 12:06 am

  #925668

  try this...

  sp_mshelpcolumns 'table_name'

  in the result, search for the col_identity column if value is 1 then that column has an identity

  "-=Still Learning=-"

  Lester Policarpio

• Old McDonald

  SSCertifiable

  Points: 5768

  More actions

  January 13, 2009 at 1:10 am

  #925691

  Also, verify the INSERT query that gets fired on click of Submit button; you should use the name of the columns in the query

  INSERT INTO yourtable

  (col1, col2,.....) -- dont add the IDENTITY column/s name in the list

  VALUES (value1, value2,...)

  -Vikas Bindra

• morten-908506

  Mr or Mrs. 500

  Points: 506

  More actions

  January 13, 2009 at 1:27 am

  #925698

  Hi

  Thanks for the advice from the 2 of you.

  I can see I have some reading to do 🙂

  Will get cracking with it.

  Will give it a go on the 2 suggestions

  Thanks alot

  Regards

  Morten