• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 362
  • Last Modified:

Linq to Sql on ASP.NET - No error, but record doesn't update

Hi there, I hope that somebody can help.

I'm working in ASP.NET on what is actually a WCF webservice, but for debugging purposes I'm using a standard Default.aspx to try to establish the problem.

I have a remote MS SQL server, with connection string:

connectionString="Data Source=sql387.mysite4now.com;Persist Security Info=True;User ID=fadb;Password=mypass"

This string allows me to write to the database using the Visual Studio 2008 IDE, so I'm fairly confident there are no issues with login / password / etc.

I added a new LINQ to Sql class, DataClasses.dbml, and dragged a test table onto the design surface.  Yet when I execute the attached code snippet, the record in the database is not updated.  There are no error messages raised and the output on the page indicates that the row was updated:

Data Source=sql387....
Row name is currently: John
Updated row to new name.


The DataClasses.dbml properties include:
Serializationmode: Unidirectional             (so I can access the service from Silverlight)


Both columns in my test table are set (for debug purposes) to:
Auto-Sync:    Always
Update Check:  Never


The structure of my test table is simply:
Int ID                           (isIdentity=Yes)  (No Nulls)
string name                (No Nulls)


....but none of this seems to help!  I'd be grateful for any assistance.  


C


PS: I have searched the web and found interesting articles at:
http://www.rocksthoughts.com/blog/archive/2008/01/14/linq-to-sql-caching-gotcha.aspx
http://borrell.parivedasolutions.com/2008/02/linq-to-sql-updating-in-aspnet-right.html
but neither are entirely relevant.
protected void Page_Load(object sender, EventArgs e)
    {
        DataClassesDataContext db = new DataClassesDataContext();
        Response.Write(db.Connection.ConnectionString + "<br>");
        tblTest row = db.tblTests.Single(r => r.ID == 1);
       
        if (row != null) // not found - can't update
        {
            // Read the row
            Response.Write("Row name is currently: " + row.name + "<br>");
            // Update the row
            row.name = "John Lennon";
            Response.Write("Updated row to new name.<br>");
 
            db.SubmitChanges();
            db.Connection.Close();
            Response.Write("Done.");
        }
    }

Open in new window

0
carlosp_uk
Asked:
carlosp_uk
  • 4
  • 3
1 Solution
 
naspinskiCommented:
Your syntax is correct.  I just tried the same thing on a test DB and DBML and it worked fine.  

Have you been successful trying an insert or anything like that?

Since this syntax is correct, I am thinking it may be a SQL permissions problem?
0
 
carlosp_ukAuthor Commented:
@naspinksi
Thanks for trying to reproduce this, it's good to know that the basic syntax is correct.  

I am fairly new to LINQ but I don't understand how it could be a permissions problem, since I am able to update records via the Visual Studio IDE.  This, presumably, uses the same connection string, username and password?     (will try an Insert when I'm back at the machine)
0
 
naspinskiCommented:
That is true with the same credentials you shouldn't have a problem.  Has the DB been altered at all (structure not data) since you made your dbml?  Possibly try deleting then re-making the dbml?
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
carlosp_ukAuthor Commented:
Not at all, this is a brand new dbml created purely for testing purposes.
0
 
naspinskiCommented:
to make sure everything is working, just run a quick test like this before and after the change:

Response.Write(row.name + "<br />");

That way we can make sure you are pulling the correct record and if it is changing correctly.
0
 
carlosp_ukAuthor Commented:
Thanks naspinksi.  The result is as expected, the local 'row' object thinks that it has a new value:

Row name is currently: John.
Updated row to new value.
Row name is currently:  John Lennon.

...however, when I then connect to the database through the IDE (or simply refresh teh page) the record itself is not updated.  

Perhaps the row is just being cached locally?  

C
0
 
carlosp_ukAuthor Commented:
Dear Everyone

After some days of head-scratching and nail-biting I have solved this problem.

The database field for ID needed the property "Primary Key" setting to "True" in the properties within the DBML.  (see attached picture)  Setting "IsIdentity" within the SQL table is not enough to achieve this.

The 'Auto-Sync' and 'Update Check' fields are irrelevant in this case as there is an explicit db.SubmitChanges() performed and there are no concurrency issues.  (note that setting auto-sync to Always on your primary key throws an exception)

Thanks for your time everyone - hope this information helps somebody else!

Carl

temp.jpg
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now