LogoASPAlliance: Articles, reviews, and samples for .NET Developers
CodeSnip: Editing Access Databases with ASP.NET 2.0
by Andrew Mooney
Average Rating: 
Views (Total / Last 10 Days): 23670/ 52

Editing and Deleting Rows in an Access Table

[ Download Code ]
First, we are going to need an Access database table that defines at least one primary key. The primary key is needed for making changes to the table. If you were just going to display the database table, you would not need the primary key. Listing 1 shows the database table schema for a list of authors.

Second, we need to create a new web form. For this I used Visual Web Developer 2005 Express Edition.  Now, drag and drop an AccessDataSource control and a GridView control onto your web form. These two controls are both new in version 2.0 of the .NET Framework, and I know they will save developers a lot of time. Now we just need to set a few properties.

Set the following properties on the AccessDataSource control:

  1. DataFile: "data\pubs.mdb"
  2. SelectCommand: "select * from [authors]"
  3. DeleteCommand: "DELETE FROM [authors] WHERE [au_id] = ?"
  4. UpdateCommand: "UPDATE [authors] SET [au_lname] = ?, [au_fname] = ?, [phone] = ?, [address] = ?, [city] = ?, [state] = ?, [zip] = ?, [contract] = ? WHERE [au_id] = ?"

Set the following properties on the GridView control:

  1. AllowPaging: True (Turns on paging, the default page size is 10)
  2. AllowSorting: True (Turns on column sorting)
  3. AutoGenerateEditButton: True (Allows you to edit rows)
  4. AutoGenerateDeleteButton: True (Allows you to delete rows)
  5. DataKeyNames: au_id (Primary key required for making changes)
  6. DataSourceID: DataSetDataSource1 (Reference to the DataSource control)

That's all there is!  You now have a web form that can edit and delete rows in an Access database table, all of this without writing a single line of code. Listing 2 shows the web form.  Notice the absence of code. Screen Shot 1 shows the web form in action.

There is only one piece missing--the ability to add rows to our Access table. The only problem is that GridView will not allow you to add rows. Not to worry, we can still accomplish this without writing code. Let's see how.

Adding Rows to an Access Table

[ Download Code ]
The GridView control will not allow you to add new rows to a data source, but the DetailsView (another new control in ASP.NET 2.0) will. While the GridView can display multiple rows, the DetailsView control displays only one row at a time, but the DetailsView will allow you to edit, delete, and add rows to a data source.

Using the same web form, drag and drop a second AccessDataSource control and a DetailsView control onto the web form. Now we just set a few properties. You need a second AccessDataSource control because you are going to set a filter expression on it so that it only displays one row (the row you select). Microsoft recommends using a separate DataSource control with each data control even if you are not using a filter.

Set the properties of the second AccessDataSource control are the same as the first with one exception and three changes:

  1. DataFile: data\pubs.mdb
  2. SelectCommand: "select * from [authors]"
  3. DeleteCommand: "DELETE FROM [authors] WHERE [au_id] = ?"
  4. UpdateCommand: "UPDATE [authors] SET [au_lname] = ?, [au_fname] = ?, [phone] = ?, [address] = ?, [city] = ?, [state] = ?, [zip] = ?, [contract] = ? WHERE [au_id] = ?"
  5. InsertCommand: "INSERT INTO [authors] ([au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip], [contract]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"
  6. FilterExpression: au_id='@auid' (Make sure to include single quotes)
  7. FilterParameters (Add Parameter)
    • Parameter source: Control
    • ControlID: GridView1
    • Name: auid

Set the properties of the GridView control:

  1. AutoGenerateEditButton: False
  2. AutoGenerateDeleteButton: False
  3. AutoGenerateSelectButton: True

Set the properties of the DetailsView control:

  1. AutoGenerateEditButton: True
  2. AutoGenerateDeleteButton: True
  3. AutoGenerateInsertButton: True

Save your web form and place the database file in the "data" sub-directory of the same virtual directory. Remember this code sample requires ASP.NET 2.0. Using four web server controls we now have a web page that will edit, delete, and add rows to an Access database table. All of this in just a few minutes. Wow! When you click the select button on the GridView, that row will be displayed in the DetailsView. You can then edit or delete that row. You can also add a new row from the DetailsView.

Screen Shot 2  shows the revised web form in action. Listing 3 shows the web form.  Don't panic!  It contains a small amount of code.  The code is not necessary for the web form to function, but it sure adds a nice feature. When you make changes in the DetailsView they are not updated in the GridView. To fix this we just bind the GridView to the database table every time we make a change.  I'm sorry, but this requires a small amount of code.

In Source view add the following properties to the DetailsView:

  1. onitemupdated="DetailsView1_ItemUpdated"
  2. onitemInserted="DetailsView1_ItemInserted"
  3. onitemdeleted="DetailsView1_ItemDeleted"

Then add the sub routines as show in Listing 3. In each we simply bind the database table to the GridView so that it reflects the changes made in the DetailsView. The code is not required, but without this code, you would have to sort or change pages in the GridView to see the changes.

In conclusion, let me say that the new controls in ASP.NET 2.0 are going to make editing Access databases much easier.  I also look forward to using the rest of these new controls to rapidly develop web applications.

©Copyright 1998-2020  |  Page Processed at 2020-07-04 8:18:07 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search