none
IDENTITY_INSERT is set to OFF

    Question

  • I am experimenting with the code here:

     

    http://csharp.net-informations.com/dataadapter/insertcommand-sqlserver.htm

     

    I can't do the insert because I'm getting this error message: 'Cannot insert explicit value for identity column in table Shippers when IDENTITY_INSERT is set to OFF'.  (I changed the SQL a bit; that's not the problem).  So I go to my Northwind DB and run this tiny script:

     

    -- SET IDENTITY_INSERT to ON.

    SET IDENTITY_INSERT Shippers ON

    GO

     

    Then I get another error message: ‘Msg 1088, Level 16, State 11, Line 2

    Cannot find the object "Shippers" because it does not exist or you do not have permissions.

     

    What is going on here?  I most certainly have permissions to do whatever I want with this DB!!

    Tuesday, October 18, 2011 7:34 PM

Answers

  • Your problem is that you are using two different sessions, one set it on, but the other not. You need to set it on in the same session where you are doing the update. Either you send a batch or you you execute the setting before the insert without closing the connection.

    sql = "set identity_insert do.Shippers on;insert into Shippers (ShipperID, CompanyName, Phone) values(5, 'Shipper Name', '(646) 498-6538)');set identity_insert do.Shippers off;";

     


    AMB

    Some guidelines for posting questions...

    • Proposed as answer by Naomi NModerator Tuesday, October 18, 2011 8:54 PM
    • Marked as answer by ryguy72 Tuesday, October 18, 2011 8:58 PM
    Tuesday, October 18, 2011 8:51 PM
  • Hi ryguy !

    You need to execute both statement on one GO. Execute both statements as a single set.

    Thanks, Hasham

    • Marked as answer by ryguy72 Tuesday, October 18, 2011 8:58 PM
    Tuesday, October 18, 2011 8:20 PM

All replies

  • Be sure you are connecting to the right database and use also the schema name to reference the table.

     


    AMB

    Some guidelines for posting questions...

    Tuesday, October 18, 2011 7:36 PM
  • Thanks Hunchback, but I don't think that's the problem.  This is bizarre!!

    I

     just ran this:

    SET IDENTITY_INSERT [Northwind].[dbo].[Shippers] ON
    GO

    I got this message:
    Command(s) completed successfully.

     

    This I run this code and get the same message I described above:

     

    private void button1_Click(object sender, EventArgs e)

    {

        string connetionString = null;

        SqlConnection connection;

        SqlDataAdapter adapter = new SqlDataAdapter();

        string sql = null;

        connetionString = connetionString = "Data Source=BWSMJXAK\\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=SSPI";

        connection = new SqlConnection(connetionString);

        sql = "insert into Shippers (ShipperID, CompanyName, Phone) values(5, 'Shipper Name', '(646) 499-9999)')";

        try

        {

            connection.Open();

            adapter.InsertCommand = new SqlCommand(sql, connection);

            adapter.InsertCommand.ExecuteNonQuery();

            MessageBox.Show("Row inserted !! ");

        }

        catch (Exception ex)

        {

            MessageBox.Show(ex.ToString());

        }

     



    • Edited by ryguy72 Tuesday, October 18, 2011 9:11 PM
    Tuesday, October 18, 2011 8:10 PM
  • Hi ryguy !

    You need to execute both statement on one GO. Execute both statements as a single set.

    Thanks, Hasham

    • Marked as answer by ryguy72 Tuesday, October 18, 2011 8:58 PM
    Tuesday, October 18, 2011 8:20 PM
  • Your problem is that you are using two different sessions, one set it on, but the other not. You need to set it on in the same session where you are doing the update. Either you send a batch or you you execute the setting before the insert without closing the connection.

    sql = "set identity_insert do.Shippers on;insert into Shippers (ShipperID, CompanyName, Phone) values(5, 'Shipper Name', '(646) 498-6538)');set identity_insert do.Shippers off;";

     


    AMB

    Some guidelines for posting questions...

    • Proposed as answer by Naomi NModerator Tuesday, October 18, 2011 8:54 PM
    • Marked as answer by ryguy72 Tuesday, October 18, 2011 8:58 PM
    Tuesday, October 18, 2011 8:51 PM
  • Yeap, works perfect.  Can you please delete me phone number.

    Just one more question.  What is that logic for?  Why do I need to set Identity_Insert ON and OFF?

    Thanks!!  Learned something new today.

    Tuesday, October 18, 2011 8:58 PM
  • By default, the identity field automatically updates the field which is set to be identity. If you need to override this behavior and supply the ID field yourself, then we need to SET IDENTITY_INSERT ON. Normally you would not want to do this and let SQL Server generate the value.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Tuesday, October 18, 2011 9:09 PM
  • Hi ryguy !

    You can't insert value of a column explicitly if its defined as IDENTITY in your table DLL. So first you need to set IDENTITY_INSERT property to ON for your table so you can INSERT rows by defining your identity value for that column explicitly and after that you need to put it back to OFF (IDENTITY_INSERT) property.

    Thanks, Hasham

    Tuesday, October 18, 2011 9:13 PM
  • You are correct Naomi!  All I had to do was change the CompanyName and Phone, and the CompanyID auto-incremented by itself.

    Thanks everyone!!

    Tuesday, October 18, 2011 9:15 PM
  • In addition to what Naomi described , sometimes there are cases when we need to INSERT_IDENTITY value explicitly , scenario might be Identity value might be refferencing to some other table as a FK constraint.

    Thanks, Hasham

    Tuesday, October 18, 2011 9:31 PM