I get this exception thrown whenever I try and submit data through a form to this database :-
Exception
Cannot insert explicit value for identity column in table 'ClientDetails' when IDENTITY_INSERT is set to OFF.
However, the form doesn't have a field so data can enter the identity column (PK) so I'm at a loss as to why this is occuring.
At the moment I'm using the standard asp.net mvc submit button but I will link it eventually to a jquery dialog button
The ClientNo Column which is the said column the exception is referring to has the following attributes
The ClientNo has data 900 onwards etc
This exception is also thrown when the client form has no data entered in form
Its thrown on a DataCOntext.SubmitChanges() method
[AcceptVerbs(HttpVerbs.Post)]
public ActionResult Create1(ClientDetail client)
{
if(ModelState.IsValid)
{
client = new ClientDetail();
UpdateModel(client);
//Debug Code
System.Diagnostics.Debug.WriteLine("AddNewClient Create1");
repo.AddNewClient(client);
//Debug Code
System.Diagnostics.Debug.WriteLine("Save Create1");
// System.Data.SqlClient.SqlException thrown at this line
repo.Save();
//Debug Code - never reached
System.Diagnostics.Debug.WriteLine("Saved Changes");
// return RedirectToAction("Details", new { id = client.ClientNo });
}
return View(client);
}
public void AddNewClient(ClientDetail client)
{
System.Diagnostics.Debug.WriteLine("Start Insert On Submit");
db.ClientDetails.InsertOnSubmit(client);
System.Diagnostics.Debug.WriteLine("Finish Insert On Submit");
}
public void Save()
{
System.Diagnostics.Debug.WriteLine("In Save Method");
db.GetChangeSet();
System.Diagnostics.Debug.WriteLine("Got ChangeSet");
db.SubmitChanges();
System.Diagnostics.Debug.WriteLine("Exit Save Method");
}
Is this the query everyone is talking about
[Column(Storage="_ClientNo", DbType="Int NOT NULL", IsPrimaryKey=true, UpdateCheck=UpdateCheck.Never)]
public int ClientNo
{
get
{
return this._ClientNo;
}
set
{
if ((this._ClientNo != value))
{
this.OnClientNoChanging(value);
this.SendPropertyChanging();
this._ClientNo = value;
this.SendPropertyChanged("ClientNo");
this.OnClientNoChanged();
}
}
}
Does anyone know a solution or reason as to why this is occuring?
Thank you
You cannot supply values to be inserted into an Identity column as part of an insert. They are automatically generated as an atomic part of the INSERT
operation. This can be temporaily disabled on a per table basis with the SET IDENTITY_INSERT
command.
Either, you should alter the database shcema but I do not reccomend this or better, you should not supply a value, or supply a NULL
value for the Client_NO
column in your insert.
In SQL you can use the SCOPE_IDENTITY()
function to get the auto genrated value. the model will be automatically updated with this value.
Use archil's answer to tell the model not pass the this column in the generated select.
[Column(Storage="_ClientNo",
DbType="Int NOT NULL",
IsDbGenerated=true,
IsPrimaryKey=true,
UpdateCheck=UpdateCheck.Never)]
public int ClientNo
Note the edition of IsDBGenerated to the arrtributes.
Add IsDbGenerated=true To ClientNo Property
[Column(Storage="_ClientNo", DbType="Int NOT NULL", **IsDbGenerated=true,** IsPrimaryKey=true, UpdateCheck=UpdateCheck.Never)]
public int ClientNo
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With