?
Solved

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

Posted on 2008-06-18
7
Medium Priority
?
355 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 21

Expert Comment

by:naspinski
ID: 21811189
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
ID: 21812012
@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
ID: 21812036
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

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

Expert Comment

by:naspinski
ID: 21812392
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
ID: 21813467
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
ID: 21832632
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

801 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