locked
Cannot insert explicit value for identity column in table 'SS_Messeges' when IDENTITY_INSERT is set to OFF RRS feed

  • Question

  • User1461916663 posted

     I use SQLExpress2005 and I search about this problem , this is a BUG in MsSql 2000 but I use sql Express 2005.

    although  in my  table I set IDENTITY_INSERT on (master Key)

    Please help me

    Friday, December 21, 2007 8:19 AM

Answers

  • User-264375749 posted

    You want to insert and return the new ID?  It doesn't look like you are supplying a value for the MessageID that you are trying to insert.

    If that is the case, you can take the @MessageID out of the insert statement.  You're not really inserting anything into that field.  YOu want SQL server to create the next ID for you.

    SET NOCOUNT ON
    insert into SS_Messeges
        (MessageTitle,MessageBody,AddedDate,AddedByIP,isRead,ResMail,ResName)
        VALUES (@MessageTitle,@MessageBody,@AddedDate,@AddedByIP,@isRead,@ResMail,@ResName)
    SET @MessageID=SCOPE_IDENTITY()

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 21, 2007 12:19 PM

All replies

  • User436930739 posted

     I don't quite understand what your problem is.  Have you tried "Set identity_insert SS_Messages ON" and still after setting this you're not able to insert explicit value in your table ?  Can you explain your problem further ?
     

    Friday, December 21, 2007 8:55 AM
  • User1461916663 posted

     let's me explain the my problem

    I design a Messages table width this columns

    MessageID    >>  int - MasterKey - Is Identity yes - Identity Increment 1 - Identity seed NOTNULL

    MessageTitle   >> nvarchar(256)   NOTNULL

    MessageBody  >> ntext   NOTNULL

    AddedDate   >> dateTime   NOTNULL

    AddedByIP >> nvarchar(256)   NOTNULL

     IsRead    >> bit   NOTNULL

    ResName >> nvarchar(256)   NULL

    ResMail >> nvarchar(256) NOTNULL 

     my Insert producer

    ALTER PROCEDURE dbo.SS_Messages_InsertMessage
    (
        @MessageTitle    nvarchar(256),
        @MessageBody    nvarchar(256),
        @AddedDate        datetime,
        @AddedByIP        nvarchar(256),
        @isRead            bit,
        @ResMail        nvarchar(256),
        @ResName        nvarchar(256),
        @MessageID        int OUTPUT
    )
    as
    SET NOCOUNT ON
    insert into SS_Messeges
        (MessageID,MessageTitle,MessageBody,AddedDate,AddedByIP,isRead,ResMail,ResName)
        VALUES (@MessageID,@MessageTitle,@MessageBody,@AddedDate,@AddedByIP,@isRead,@ResMail,@ResName)
    SET @MessageID=SCOPE_IDENTITY()

     

     DAL Message class     (Insert Message methods)

     public override int InsertMessage(MessageDetails message)
            {
                using (SqlConnection cn = new SqlConnection(this.ConnectionString))
                {
                    SqlCommand cmd = new SqlCommand("SS_Messeges_InsertMessege", cn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@MessageTitle",SqlDbType.NVarChar).Value=message.MessageTitle;
                    cmd.Parameters.Add("@MessageBody", SqlDbType.NVarChar).Value=message.MessageBody;
                    cmd.Parameters.Add("@AddedDate",SqlDbType.DateTime).Value=message.AddedDate;
                    cmd.Parameters.Add("@AddedByIP",SqlDbType.NVarChar).Value=message.AddedByIP;
                    cmd.Parameters.Add("@isRead",SqlDbType.NVarChar).Value=message.IsRead;
                    cmd.Parameters.Add("@ResMail", SqlDbType.NVarChar).Value = message.ResMail;
                    cmd.Parameters.Add("@ResName", SqlDbType.NVarChar).Value = message.ResName;
                    cmd.Parameters.Add("@MessageID", SqlDbType.Int).Direction = ParameterDirection.Output;
                    cn.Open();
                    int ret = ExecuteNonQuery(cmd);
                    return (int)cmd.Parameters["@MessageID"].Value;
                }
            }

     

    BLL Messages Class >> Insert Message Methods

     public static int InsertMessage(string messageTitle, string messagebody,
                                            string resMail, string resName)
            {
                messageTitle = BizObject.ConvertNullToEmptyString(messageTitle);
                messagebody = BizObject.ConvertNullToEmptyString(messagebody);
                resName = BizObject.ConvertNullToEmptyString(resName);
                resMail = BizObject.ConvertNullToEmptyString(resMail);
                MessageDetails record = new MessageDetails(0, messageTitle, messagebody, DateTime.Now, BizObject.CurrentUserIP,
                    false, resMail, resName);
                int ret = SiteProvider.Messages.InsertMessage(record);
                return ret;
            }

     

    Contact US Code Behind

     protected void txtSubmit_Click(object sender, EventArgs e)
            {

                int ret = Message.InsertMessage(txtTitle.Text, txtBody.Text, txtResMail.Text, txtResName.Text);
            } 

    What is wrong??

    when I want to add an Message this error happened  :( 

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

    Friday, December 21, 2007 12:03 PM
  • User-264375749 posted

    You want to insert and return the new ID?  It doesn't look like you are supplying a value for the MessageID that you are trying to insert.

    If that is the case, you can take the @MessageID out of the insert statement.  You're not really inserting anything into that field.  YOu want SQL server to create the next ID for you.

    SET NOCOUNT ON
    insert into SS_Messeges
        (MessageTitle,MessageBody,AddedDate,AddedByIP,isRead,ResMail,ResName)
        VALUES (@MessageTitle,@MessageBody,@AddedDate,@AddedByIP,@isRead,@ResMail,@ResName)
    SET @MessageID=SCOPE_IDENTITY()

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 21, 2007 12:19 PM
  • User1461916663 posted

    thank Pyre

    I catch my problem with your post
     

    Friday, December 21, 2007 2:40 PM
  • User807886609 posted

    An identity column, by design, autogenerates its own value when an insert is performed. Apparently, your code must be including the IDENTITY column in the insert and supplying a value. I presume that this is NOT what you want to do.

    Friday, December 21, 2007 3:45 PM