Databases
StackOverflow2013
Postsdbo
POWhy am I getting "Cannot insert explicit value for identity column" in LINQ to SQL when I'm not specifying a value for an identity column?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhy am I getting "Cannot insert explicit value for identity column" in LINQ to SQL when I'm not specifying a value for an identity column?
    text
    copied!<p>I have a table that looks like this: <img src="https://imgur.com/MXqRf.png" alt="alt text"></p> <p>ClientID is the only identity column I have in the table. UserID is a FK to a different tables primary key.</p> <p>Here is my Linq to SQL insert code:</p> <pre><code>public void InsertClientByUsername(string username, Entities.Client clientInfo) { using (LinqModelDataContext db = new LinqModelDataContext()) { var existingClient = (from client in db.Clients join ext_usr in db.User_Extendeds on client.UserID equals ext_usr.FriendlyUserID join asp_usr in db.aspnet_Users on ext_usr.UserID equals asp_usr.UserId where asp_usr.UserName.ToLower().Equals(username) select client).SingleOrDefault(); if (existingClient != null) { existingClient.Address1 = clientInfo.Address1; existingClient.Address2 = clientInfo.Address2; existingClient.City = clientInfo.City; existingClient.CompanyName = clientInfo.CompanyName; existingClient.CountryID = clientInfo.CountryID; existingClient.FaxNumber = clientInfo.Fax; existingClient.FirstName = clientInfo.FirstName; existingClient.LastName = clientInfo.LastName; existingClient.MailingAttention = clientInfo.Attention; existingClient.PhoneNumber = clientInfo.PhoneNumber; existingClient.StateID = clientInfo.StateID; existingClient.ZipCode = clientInfo.Zip; } else { int userID = (from ext_usr in db.User_Extendeds join asp_usr in db.aspnet_Users on ext_usr.UserID equals asp_usr.UserId where asp_usr.UserName.ToLower().Equals(username) select ext_usr.FriendlyUserID).SingleOrDefault(); Client newClient = new Client(); newClient.UserID = userID; newClient.Address1 = clientInfo.Address1; newClient.Address2 = clientInfo.Address2; newClient.City = clientInfo.City; newClient.CompanyName = clientInfo.CompanyName; newClient.CountryID = clientInfo.CountryID; newClient.FaxNumber = clientInfo.Fax; newClient.FirstName = clientInfo.FirstName; newClient.LastName = clientInfo.LastName; newClient.MailingAttention = clientInfo.Attention; newClient.PhoneNumber = clientInfo.PhoneNumber; newClient.StateID = clientInfo.StateID; newClient.ZipCode = clientInfo.Zip; db.Clients.InsertOnSubmit(newClient); } db.SubmitChanges(); } } </code></pre> <p>In case you are curious, the reason I have all those assignments is because I'm translating between my POCO domain objects and the linq generated objects. In the case of this exception, it is taking the path of the else statement, creating a new client.</p> <p>You can see that I'm <em>NOT</em> touching the ClientID property which is the ~only~ identity column in the table.</p> <p>Why am I getting the "Cannot insert explicit value for identity column in table 'Client' when IDENTITY_INSERT is set to OFF?</p> <p>In case it is useful, here is my stacktrace:</p> <blockquote> <p>System.Data.SqlClient.SqlException was unhandled by user code<br> Message="Cannot insert explicit value for identity column in table 'Client' when IDENTITY_INSERT is set to OFF."<br> Source=".Net SqlClient Data Provider" ErrorCode=-2146232060 Class=16<br> LineNumber=1 Number=544<br> Procedure=""<br> Server="192.168.168.190" State=1<br> StackTrace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult) at System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries) at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query) at System.Data.Linq.ChangeDirector.StandardChangeDirector.DynamicInsert(TrackedObject item) at System.Data.Linq.ChangeDirector.StandardChangeDirector.Insert(TrackedObject item) at System.Data.Linq.ChangeProcessor.SubmitChanges(ConflictMode failureMode) at System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode) at System.Data.Linq.DataContext.SubmitChanges() at DomainModel.Repository.Concrete.SqlClientRepository.InsertClientByUsername(String username, Client clientInfo)</p> </blockquote>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload