Editing Access Data on the Web - C# Database Basics (2012)

C# Database Basics (2012)

Chapter 6. Editing Access Data on the Web

There are a lot of reasons why you might want to add or edit data on a web page. The example you’ll go through in this chapter isn’t really something that you would do in a real-world situation, but I want to introduce you to a couple of concepts that you’ll find helpful if you need to interact with data through a web browser in the future.

It is worth noting that there are some great starter websites that you can build with Webmatrix. Currently, you can get Webmatrix on the Microsoft website, and from there you can install a prebuilt template to perform quick web development. Also, if you want to perform website functions like having a login, or if you want to use the MVC (model – view – controller) design pattern, you can use the preinstalled templates in Visual Studio. That said, it is still helpful for you to understand how to build a simple web page on your own.

In this example, you will begin with a blank ASP.NET website, as shown in Figure 6-1. I’ve called my website Chapter6. Note that you can do this from File→New→Web Site. If you want to build an MVC site, use File→New→Project and pick a Web project.

Setting up an empty website

Figure 6-1. Setting up an empty website

When you create this website, the only file in the website is Web.config. This means that you will need to add a Web Forms item to the website. To do this, go to Website→Add New Item and you will see a screen like the one shown in Figure 6-2. Call your web form Default.aspx and click Add.

Creating a web form

Figure 6-2. Creating a web form

We also want to create a reference to the Northwind database, and in this case, I want to put a copy of the Northwind database into the project. To do this, go to Website→Add Existing Item, select Data Files as the type, and navigate to where you have the Northwind database. Doing this will enable you to reference it at ~\Northwind 2007.accdb instead of having to add the entire physical path. Also, any changes that you make will happen in this copy and won’t impact the normal database.

Now that you have added the database and the new web forms page, go edit the Default.aspx page in design view. At the top, type Data from Customers table. Then drag on a GridView control and an AccessDataSource control. Click on the AccessDataSource object and go to Configure Data Source. When you hit browse, you will see a screen like the one shown in Figure 6-3. Click on the Northwind 2007 database and click OK. Notice how the file reference in Figure 6-3 shows the physical path. When you are working with files in C# in a web application, you can refer to the root directory with ~\, so I always recommend putting files into your project instead of trying to reference physical paths.

Choosing a specific database

Figure 6-3. Choosing a specific database

Once you click Next, you will get a screen like the one shown in Figure 6-4. On this screen, select the Customers table and leave the checkbox on the *. This will select all columns on the table. Then, click on Advanced and you will see the screen shown in Figure 6-5. Here, check the box to generate the Insert, Update, and Delete statements. Doing this will let you make changes to the table. Click Next and then click Finish. You can test the datasource on the last page if you wish, but generally you would only do that if you were writing a specific query, versus selecting an entire table. Also, please note that if you want the Insert, Update, and Delete statements, you must have the primary key field(s) selected in your query.

Choosing tables for the query

Figure 6-4. Choosing tables for the query

Generating Insert, Update, and Delete queries

Figure 6-5. Generating Insert, Update, and Delete queries

Next, click on your datagrid and hit the > button on the top right of the grid. That will open up the GridView Tasks box, and you can set the options. For the datasource, select AccessDataSource1. Then check the boxes to enable Paging, Sorting, Editing, and Deleting. See Figure 6-6.

Choosing GridView configuration

Figure 6-6. Choosing GridView configuration

Finally, click on Autoformat at the top of the GridView Tasks box. I selected Professional for mine, but you can pick whatever you’d like. At this point, it is ready to be opened. If you switch back to Source view on the web page, you will see the following:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

<title></title>

</head>

<body>

<form id="form1" runat="server">

<div>

Data from Customers table.<br />

<asp:GridView ID="GridView1" runat="server" AllowPaging="True"

AllowSorting="True" AutoGenerateColumns="False" CellPadding="4"

DataKeyNames="ID" DataSourceID="AccessDataSource1" ForeColor="#333333"

GridLines="None">

<AlternatingRowStyle BackColor="White" ForeColor="#284775" />

<Columns>

<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />

<asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False"

ReadOnly="True" SortExpression="ID" />

<asp:BoundField DataField="Company" HeaderText="Company"

SortExpression="Company" />

<asp:BoundField DataField="Last Name" HeaderText="Last Name"

SortExpression="Last Name" />

<asp:BoundField DataField="First Name" HeaderText="First Name"

SortExpression="First Name" />

<asp:BoundField DataField="E-mail Address" HeaderText="E-mail Address"

SortExpression="E-mail Address" />

<asp:BoundField DataField="Job Title" HeaderText="Job Title"

SortExpression="Job Title" />

<asp:BoundField DataField="Business Phone" HeaderText="Business Phone"

SortExpression="Business Phone" />

<asp:BoundField DataField="Home Phone" HeaderText="Home Phone"

SortExpression="Home Phone" />

<asp:BoundField DataField="Mobile Phone" HeaderText="Mobile Phone"

SortExpression="Mobile Phone" />

<asp:BoundField DataField="Fax Number" HeaderText="Fax Number"

SortExpression="Fax Number" />

<asp:BoundField DataField="Address" HeaderText="Address"

SortExpression="Address" />

<asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />

<asp:BoundField DataField="State/Province" HeaderText="State/Province"

SortExpression="State/Province" />

<asp:BoundField DataField="ZIP/Postal Code" HeaderText="ZIP/Postal Code"

SortExpression="ZIP/Postal Code" />

<asp:BoundField DataField="Country/Region" HeaderText="Country/Region"

SortExpression="Country/Region" />

<asp:BoundField DataField="Web Page" HeaderText="Web Page"

SortExpression="Web Page" />

<asp:BoundField DataField="Notes" HeaderText="Notes" SortExpression="Notes" />

<asp:BoundField DataField="Attachments" HeaderText="Attachments"

SortExpression="Attachments" />

</Columns>

<EditRowStyle BackColor="#999999" />

<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />

<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />

<PagerStyle BackColor="#284775" ForeColor="White"

HorizontalAlign="Center" />

<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />

<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True"

ForeColor="#333333" />

<SortedAscendingCellStyle BackColor="#E9E7E2" />

<SortedAscendingHeaderStyle BackColor="#506C8C" />

<SortedDescendingCellStyle BackColor="#FFFDF8" />

<SortedDescendingHeaderStyle BackColor="#6F8DAE" />

</asp:GridView>

<asp:AccessDataSource ID="AccessDataSource1" runat="server"

DataFile="~/Northwind 2007.accdb"

DeleteCommand="DELETE FROM [Customers] WHERE [ID] = ?"

InsertCommand="INSERT INTO [Customers] ([ID], [Company], [Last Name], [First Name], [E-mail Address], [Job Title], [Business Phone], [Home Phone], [Mobile Phone], [Fax Number], [Address], [City], [State/Province], [ZIP/Postal Code], [Country/Region], [Web Page], [Notes], [Attachments]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

SelectCommand="SELECT * FROM [Customers]"

UpdateCommand="UPDATE [Customers] SET [Company] = ?, [Last Name] = ?, [First Name] = ?, [E-mail Address] = ?, [Job Title] = ?, [Business Phone] = ?, [Home Phone] = ?, [Mobile Phone] = ?, [Fax Number] = ?, [Address] = ?, [City] = ?, [State/Province] = ?, [ZIP/Postal Code] = ?, [Country/Region] = ?, [Web Page] = ?, [Notes] = ?, [Attachments] = ? WHERE [ID] = ?">

<DeleteParameters>

<asp:Parameter Name="ID" Type="Int32" />

</DeleteParameters>

<InsertParameters>

<asp:Parameter Name="ID" Type="Int32" />

<asp:Parameter Name="Company" Type="String" />

<asp:Parameter Name="Last_Name" Type="String" />

<asp:Parameter Name="First_Name" Type="String" />

<asp:Parameter Name="column1" Type="String" />

<asp:Parameter Name="Job_Title" Type="String" />

<asp:Parameter Name="Business_Phone" Type="String" />

<asp:Parameter Name="Home_Phone" Type="String" />

<asp:Parameter Name="Mobile_Phone" Type="String" />

<asp:Parameter Name="Fax_Number" Type="String" />

<asp:Parameter Name="Address" Type="String" />

<asp:Parameter Name="City" Type="String" />

<asp:Parameter Name="column2" Type="String" />

<asp:Parameter Name="column3" Type="String" />

<asp:Parameter Name="column4" Type="String" />

<asp:Parameter Name="Web_Page" Type="String" />

<asp:Parameter Name="Notes" Type="String" />

<asp:Parameter Name="Attachments" Type="String" />

</InsertParameters>

<UpdateParameters>

<asp:Parameter Name="Company" Type="String" />

<asp:Parameter Name="Last_Name" Type="String" />

<asp:Parameter Name="First_Name" Type="String" />

<asp:Parameter Name="column1" Type="String" />

<asp:Parameter Name="Job_Title" Type="String" />

<asp:Parameter Name="Business_Phone" Type="String" />

<asp:Parameter Name="Home_Phone" Type="String" />

<asp:Parameter Name="Mobile_Phone" Type="String" />

<asp:Parameter Name="Fax_Number" Type="String" />

<asp:Parameter Name="Address" Type="String" />

<asp:Parameter Name="City" Type="String" />

<asp:Parameter Name="column2" Type="String" />

<asp:Parameter Name="column3" Type="String" />

<asp:Parameter Name="column4" Type="String" />

<asp:Parameter Name="Web_Page" Type="String" />

<asp:Parameter Name="Notes" Type="String" />

<asp:Parameter Name="Attachments" Type="String" />

<asp:Parameter Name="ID" Type="Int32" />

</UpdateParameters>

</asp:AccessDataSource>

<br />

</div>

</form>

</body>

</html>

This is really handy if you are trying to make a web page that is identical to another web page. You can just copy the code and it will create the page. This isn’t as easy to do in Microsoft Access or even in Classic VB. If you read through this, you will see that every option that you selected on your objects are shown here in the markup.

Before you add any code, open up this page and see what it looks like. Then click on the Edit link on the first row and you will see a page like the one shown in Figure 6-7. You can make a change to that row and then click Update to accept your changes, or click Cancel to discard your changes. Because you have enabled sorting, you can click on any of the column headings to sort the data on that column. Also, because paging is enabled, you can click on the numbers at the bottom of the page to page through the records.

Web page in action, even without any code

Figure 6-7. Web page in action, even without any code

Now that you have seen what happens with no code, close out the browser and go to the page again. On your datagrid, go to the GridView Tasks box and check the box to enable selection. Then, add a button and a text box at the bottom of the page in design view and then go to the code behind the web page. What you’ll be doing here is customizing the look of the page somewhat. The first thing that you’ll change is the page size. This sets how many records show on each page. Next, you will put code behind the button. It is easier if you go back to the design view and double-click on the button, as it will add the procedure, so you’ll just need to edit it. For that button, you will be adding code to put some data from the selected record in the text box. That code will look like this:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data;

public partial class _Default : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

this.GridView1.PageSize = 5;

}

protected void Button1_Click1(object sender, EventArgs e)

{

this.TextBox1.Text = "No Record Selected";

if (this.GridView1.SelectedRow != null)

{

this.TextBox1.Text = this.GridView1.SelectedRow.Cells[3].Text.ToString();

}

}

}

When the page loads, the grid will be updated to only show five records at a time. If you click on the button and haven’t selected a record, it will put “No Record Selected” in the text box. If a record is selected, it will put the third column (last name) in the text box. See Figure 6-8.

The updated web table

Figure 6-8. The updated web table

To show you how you can catch an event, add the following code into the page open procedure and then add the event code shown below:

protected void Page_Load(object sender, EventArgs e)

{

this.GridView1.PageSize = 5;

GridView1.SelectedIndexChanged += new EventHandler(GridView1_SelectedIndexChanged);

}

void GridView1_SelectedIndexChanged(object sender, EventArgs e)

{

this.TextBox1.Text = this.GridView1.SelectedRow.Cells[2].Text.ToString();

}

This code will put the Company name (column 2) in the text box when you select a record, and if you press the button, it will put the last name (column 3) into the text box. I’m showing you this because you could use code like this to show details for an item. As an example, you might have a query showing customer invoices. Then you might want to add code to populate a datagrid with invoice details when you click Select. That would be a better user experience than having to select a record and then also click a button. But, there are other times where you might want to do something only when a button is clicked—maybe something like calling a web service to check on the shipping status of an order.

In any case, you can see with this example that it is possible to create a usable form with out-of-the-box controls and very little code. While you probably won’t do anything exactly like this example, hopefully this demonstrates how to accomplish these tasks. As an additional note, you could easily substitute the AccessDataSource control for an SQLDataSource, XmlDataSource, EntityDataSource, etc., and use those for the datagrid.

What’s Next?

If you look back at the goals in Chapter 1, all those topics and a few others have been covered. In the next chapter, I will review the key concepts and discuss some other possibilities. In addition, you will see some additional resources that will help you. Most programmers I speak with just need a working example of something that is close to what they want to do—they can run with it from there.