none
Help with: Cannot insert explicit value for identity column in table 'Pictures' when IDENTITY_INSERT is set to OFF.

    Question

  • I am working on a web app for an online photo album. It is the last project in Scott Mitchell’s book: “ASP.Net 2.0 in 24 hours”.

    I am creating a page where users can enter new photo images.  The functionality for uploading an image will come later. I am working on just inserting a new row into the Pictures table with an optional category, a required title and a required description. I am getting an error when I try to insert a new row.

    Here is a screenshot of the page with a shot of the dropdown list.
    The Categories are user-specific. I am getting the correct values retrieved.

     

    http://www.flickr.com/photos/41863933@N07/4882454228/


    There are two pretty simple database tables involved here.
    NOTE: The CategoryID and PIctureID are both autoincrement integer columns.

     

    http://www.flickr.com/photos/41863933@N07/4881847301/


    The page uses a DetailsView that uses a SqlDataSource that uses the Pictures table. (The DetailsView’s Default Mode property is set to “Insert”. The “Enable Inserting” checkbox is also checked.)

    The dropdown list uses a dropdown control that uses a SqlDataSource that uses the Categories table.

    When the “Insert” button is clicked the CategoryID value associated with selected (Category) Name on the dropdown list will be used along with the Title and Description values to insert a row in to the Pictures table.  (If no Category value is selected, then a null value will be used for the CategoryID. This is OK because the CategoryID column in the Pictures table allows nulls.)

    My problem is that I am getting this error:

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

    (FYI: I have the full version of both Visual Studio and SQL Server.)

     

    *From reading online on MS SQL Server websites I have found...

     

    So, because I am using an explicit value (CategoryID) from the Categories table

    and trying to insert it into the Pictures table's CategoryID column, I am getting this error.

    Is that what is going on?

    I read something about turning the Identity of a table On and then turning it off because only one table can be On in a session.

    So, can I turn the Identity On for the table, do the insert and then turn it off as the app runs?

     

    Wednesday, August 11, 2010 5:28 PM

Answers

  • Hi,

    It seems that PictureID column has identity property. So you not can expicitly insert value into this filed.

    Kindly have look at the query execting when you add picture. I suggest you to change a insert query for picture table and remove pictureID from insert and value clause

    But still, when you need to insert value in table, first you need to set

    1. SET IDENTITY_INSERT PICTURES ON

    2. After running this you can insert value in this column. (insert query)

    3. SET IDENTITY_INSERT PICTURES OFF

    Hope this helps.


    -Chintak
    • Marked as answer by Marz_K Wednesday, August 11, 2010 11:41 PM
    Wednesday, August 11, 2010 5:38 PM

All replies

  • Hi,

    It seems that PictureID column has identity property. So you not can expicitly insert value into this filed.

    Kindly have look at the query execting when you add picture. I suggest you to change a insert query for picture table and remove pictureID from insert and value clause

    But still, when you need to insert value in table, first you need to set

    1. SET IDENTITY_INSERT PICTURES ON

    2. After running this you can insert value in this column. (insert query)

    3. SET IDENTITY_INSERT PICTURES OFF

    Hope this helps.


    -Chintak
    • Marked as answer by Marz_K Wednesday, August 11, 2010 11:41 PM
    Wednesday, August 11, 2010 5:38 PM
  • My guess is tat the problem is not with the CategoryID column, which doesn't seem like a likely candidate for an indentity column. A IDENTITY column is like a counter, and SQL Server automatically supplies the values for that column, and it can also be thought of as an auto-increment value, but SQL Server doesn't use that term.

    You do say that Category ID is an autoincrement value, but this really makes no sense. Maybe when you add new categories, you want to generate a new CategoryID, but in the Pictures table, it should not be autoincrement.

    I really can't tell from your pictures what your insert is trying to do behind the scenes, and I don't know ASP.NET, must SQL Server.

    You might want to read about IDENTITY columns in the SQL Server documentation, and also read the docs for the option called SET IDENTITY_INSERT.


    HTH, Kalen Delaney www.SQLServerInternals.com
    Wednesday, August 11, 2010 6:13 PM
    Moderator
  • Chintak Chhapia:

    Thank you for your help.

     

    The problem was that, when the SqlDataSource for the Pictures DetailsView was being created, all the columns were selected.

    However, when the Insert takes place, the PicturesID has to be removed from the Insert Parameters because that value would be autoincremented and automatically generated.

    You were correct.

    (I did not need to set the Identity_Insert.)

     

    Wednesday, August 11, 2010 11:41 PM