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

carlosp_ukAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.