Accessing SQL Server on ASP.NET Web Application using SqlDataSource Web Control, Part 2: UPDATE Data

This entry is part 2 of 2 in the series Accessing SQL Server on ASP.NET using SqlDataSource

From part 1: SELECT Data, you see how to query data from SQL Server 2005 to display on a GridView object using SqlDataSource web control. But the data is read-only, you cannot modify any data on a GridView. So now you will see how to modify data on the GridView which will update back on the database.

Step-by-step to update data

Note: This example is continue from part 1: SELECT Data.

  1. On SqlDataSource1, click on Configure Data Source.
    Configure Data Source
  2. On Choose Your Data Connection, click Next.
    Choose Your Data Connection
  3. On Configure Select Statement, change the option from Specify columns from a table or view to Specify a custom SQL statement or stored procedure. Click Next.
    Configure Select Statement
  4. On Define Custom Statements or Stored Procedures, you see 4 tabs which are SELECT, UPDATE, INSERT and DELETE. Here you can define custom SQL statements for each SQL operations. On SELECT tab, enter the SQL statement as below:
    SELECT [ProductID], [ProductName], [UnitPrice] FROM [Products]

    Define Custom Statements or Stored Procedures

  5. Click on UPDATE tab, enter the SQL statement below and click Next.
    UPDATE Products SET ProductName = @ProductName, UnitPrice = @UnitPrice
    WHERE [ProductID] = @ProductID

    Update SQL Query

  6. On Test Query, Click Finish.
    Test Query
  7. On GridView1, click Edit Columns.
    Edit Columns
  8. On Fields, select Command Fields -> Edit, Update, Cancel on Available fields and click Add. Then, move the field Edit, Update, Cancel to the top by click on up arrow button. Next, click OK.
    Add Fields on Grid View Web Control
  9. On GridView1, check Enable Editing to allow edit data on the Grid View.
    Enable Editing
  10. Build and run the project. You see the Edit column in front of each row. Click Edit to edit the row.
    Query Result
  11. You can modify the value of product name and unit price of the row which also update back on the database, too.
    Edit a Row
Series Navigation<< Accessing SQL Server on ASP.NET Web Application using SqlDataSource Web Control, Part 1: SELECT Data

One Response

  1. A.Chandramouli September 4, 2010

Leave a Reply