Updating records in SQL Server 2005 with ASP.NET 3.5 - HOW TO?

Question

I have ALWAYS used the SQLDataSource to update my records in my SQL Server 2005 database, so i am not really sure how this would be done in normal code. I am using VB.NET but if you are a C# developer and want to show me some code that is more than welcome as well.

I am using ASP.NET 3.5...............

My user will see a page with 5 Text boxes with their contact details in. Those details I will get from a SQL Server DB and when they go and change the text in the text box and click submit, it must then save the changes.

This is very easy with the SQLDataSource but how can i code this myself?

Thanks in advanced!

Answer 1

Something like the following in (vanilla) ADO.NET

using(var con = new SqlConnection("ConnectionString"))
{
    using (var cmd = con.CreateCommand())
    {
        // This Example uses a parameterized SQL statement
        // but you can also use SQL Server stored procedures

        cmd.CommandText = "UPDATE table SET field1 = @field1, field2 = @field2 WHERE id = @id"; // etc, etc    
        cmd.CommandType = CommandType.Text;

        // Add values for the parameter values for the 
        // parameter placeholders in the SQL statement
        cmd.Parameters.AddWithValue("field1", Textbox1.Text);
        cmd.Parameters.AddWithValue("field2", Textbox2.Text);  
        cmd.Parameters.AddWithValue("id", myObject.Id);                

        con.Open();

        // You might want to use ExecuteScalar instead and get a return value back
        // from the database to signal success
        cmd.ExecuteNonQuery();           
    }
}

A nice tutorial on ADO.NET - Accessing SQL Server data in C# with ADO.NET

Of course, using ASP.NET 3.5, you also have LINQ to SQL or the Entity Framework to choose from.

Then there is also NHibernate, a very popular open-source Object Relational mapper, which is excellent for basic CRUD applications.

Answer 2

AI AI AI.........i was stupid.

I was suppose to place a IF NOT ISPOSTBACK statement on my PAGE LOAD event because i re-loaded my records from the DB when i clicked the Submit button and it updated the records with the old records!