Cannot insert explicit value for identity column in table SS_Messeges when IDENTITY_INSERT

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

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 ?

|||

let's me explain the my problem

I design a Messages table width this columns

MessageID >> int - MasterKey - Is Identityyes- Identity Increment1 - Identity seed1 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

|||

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()

|||

thank Pyre

I catch my problem with your post

|||

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.