SubmitChanges does not work unless using Integrated Security connection

Question

An example code like this:
void Rename(int id)
{
      var book = DatabaseContext.Books.Where(b=>b.bookId == id).Single();
      book.Name = "newName";
      DatabaseContext.SubmintChanges();
}
The bookId is primary key.
The code updates when using Integrated Security in database connection string. But it does not update if using user/password for the connection.  The user has read, right, and execute permission on the database.

My environment is as following: 
SQL server version: 2012 
DataLinq.dll file version: 4.0.30319.34211
Application runs on: Windows10 or Windows Server 2012.

Any idea? Help is appreciated.

Answer 1

Hi Franklin F. Wang,

Based on your description, I create a demo with SQL authentication, it works fine, please compare the connection string as below with yours.

<connectionStrings>
   <add name="LinqToSQLWithSQLAuthentication.Properties.Settings.EFDemoConnectionString"
            connectionString="Data Source=10.168.172.127;Initial Catalog=EFDemo;User ID=YourUserId;Password=YourPassword"
            providerName="System.Data.SqlClient" />
</connectionStrings>

In addition, if the issue still exists, do you encounter any exception, could you please provide related connection string and detailed error message.

Best regards,

Cole Wu

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 2

Hi, Cole Wu:

Sorry for the long delay to reply. I have left out for long time and just come back to the work. 

Yes, it does throw exception  which I didn't catch before. The problem is that after the table content cached in DataContext ( I use LINQ to SQL), other process changed the record value in database. When trying to update the value, information from database does not match the memory image.  In such case, LINQ won't let update it. I resolved this by calling DataConext.Refresh(System.Data.Linq.RefreshMode.OverwriteCurrentValues, object).

Previously mentioned the update could success if using integrated security connection seems to be wrong. I cannot reapet it now.

Thanks for your help.

Franklin F. Wang