Solved

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

Posted on 2008-06-18
7
350 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

726 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