Solved

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

Posted on 2008-06-18
7
339 Views
Last Modified: 2013-11-11
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
Comment
Question by:carlosp_uk
  • 4
  • 3
7 Comments
 
LVL 21

Expert Comment

by:naspinski
Comment Utility
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
 

Author Comment

by:carlosp_uk
Comment Utility
@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
 
LVL 21

Expert Comment

by:naspinski
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:carlosp_uk
Comment Utility
Not at all, this is a brand new dbml created purely for testing purposes.
0
 
LVL 21

Expert Comment

by:naspinski
Comment Utility
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
 

Author Comment

by:carlosp_uk
Comment Utility
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
 

Accepted Solution

by:
carlosp_uk earned 0 total points
Comment Utility
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now