Build Your Own ASP.NET 3.5 Website Using C# & VB (71 page)

"WHERE
UniqueField
=@
UniqueFieldParameter
", conn);

comm.Parameters.Add("@
Parameter1
", System.Data.SqlDbType.
Type1
);

comm.Parameters["@
Parameter1
"].Value =
value1
;

comm.Parameters.Add("@
Parameter2
", System.Data.SqlDbType.
Type2
);

comm.Parameters["@
Parameter2
"].Value =
value2;

Once the SqlCommand object has been created using this UPDATE statement, we simply

pass in the necessary parameters, as we did with the INSERT statement. The important

thing to remember when you’re updating records is that you must take care to perform the UPDATE on the correct record. To do this, you must include a WHERE clause that specifies the correct record using a value from a suitable unique column (usually

the primary key), as shown above.

Handle Updates with Care!

If you don’t specify a WHERE clause when you’re updating a table with new data,

every record in the table will be updated with the new data, and (usually) there’s

no way to undo the action!

Let’s put all this theory into practice as we build the Admin Tools page. The database

doesn’t contain a table that’s dedicated to this page; however, we’ll use the Admin

Tools page as a centralized location for a number of tables associated with other

pages, including the Employees and Departments tables. For instance, in this section,

we’ll allow an administrator to change the details of a specific employee.

Create a new web form named
AdminTools.aspx
in the same way you created the

other web forms we’ve built so far in Dorknozzle. Use the
Dorknozzle.master
master

page and a code-behind file. Then, add the following code to the content placeholder,

and modify the page title as shown below:

Dorknozzle\VB\07_AdminTools.aspx
(excerpt)

<%@ Page Language="VB" MasterPageFile="~/Dorknozzle.master"

AutoEventWireup="true" CodeFile="AdminTools.aspx.vb"

Inherits="AdminTools" title="
Dorknozzle Admin Tools
" %>

Licensed to [email protected]

ADO.NET

397

Runat="Server">

ContentPlaceHolderID="ContentPlaceHolder1" runat="Server">

Admin Tools

Select an employee to update:

Name:

User Name:

Address:

City:

State:

Zip:

Home Phone:

Extension:

Mobile Phone:

Width="200" Enabled="False" runat="server" />

Licensed to [email protected]

398

Build Your Own ASP.NET 3.5 Web Site Using C# & VB

Now, add the following CSS style rule to
Dorknozzle.css
(remember the
Dorknozzle.css
file is under the
Blue
theme, under the
App_Themes
folder):

Dorknozzle\VB\08_Dorknozzle.css
(excerpt)

.widelabel {

display:-moz-inline-block;

display:inline-block;

width: 100px;

}

You can switch to Design view to ensure that you created your form correctly; it

should look like the one shown in Figure 9.14.

Figure 9.14. Viewing the Admin Tools page in Design view

We’ve added the following controls to our form:

employeesList

In order for administrators to select the record for the employee whose details

they want to update, we’ll first have to bind the Employees table to this

DropDownList control.

Licensed to [email protected]

ADO.NET

399

selectButton

Once the users select the record for the employee whose details they want to

update, they’ll click this Button control. The Click event will be raised, and

the Employee ID that’s selected from employeesList will be passed to the web

form—this will be used in an SqlCommand to retrieve the details for this employee.

nameTextBox, userNameTextBox, addressTextBox, cityTextBox, stateTextBox,

zipTextBox, homePhoneTextBox, extensionTextBox, mobilePhoneTextBox

Within the selectButton’s Click event handler, we’ll add some code that binds

user information to these TextBox controls.

updateButton

When the users make the desired changes to the TextBox controls listed above,

they’ll click this button to update the database.

dbErrorLabel

We use dbErrorLabel to display an error message if a database operation fails.

Our first task is to populate the employeesList control with the list of employees

from our database. Use Visual Web Developer to generate the page’s Page_Load

event handler, then add the following code. First we need to import the required

namespaces. If you’re using VB these go right at the top of the file, but if you’re using

C# place the using statement at the end of the existing list of using statements:

Visual Basic

Dorknozzle\VB\09_AdminTools.aspx.vb
(excerpt)

Imports System.Data.SqlClient

Imports System.Configuration

Partial Class AdminTools

Inherits System.Web.UI.Page

⋮

End Class

C#

Dorknozzle\CS\09_AdminTools.aspx.cs
(excerpt)

using System;

⋮

using System.Data.SqlClient;

Licensed to [email protected]

400

Build Your Own ASP.NET 3.5 Web Site Using C# & VB

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

{

⋮

}

Next, add the following to the Page_Load method:

Visual Basic

Dorknozzle\VB\09_AdminTools.aspx.vb
(excerpt

Protected Sub Page_Load(ByVal sender As Object,

➥ ByVal e As System.EventArgs) Handles Me.Load

If Not IsPostBack Then

LoadEmployeesList()

End If

End Sub

C#

Dorknozzle\CS\09_AdminTools.aspx.cs
(excerpt)

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

LoadEmployeesList();

}

}

You’ve probably noticed in the code above that we’ve added a call to a separate

subroutine called LoadEmployeeList. We’ll place the code to populate the

employeesList in this method. Later on, we’ll need to reload the names in this list

in case any of those names have been edited; we put this code into its own subroutine

so that we don’t need to repeat it. Our next task is to add the code for the

LoadEmployeeList subroutine after the Page_Load method, but within the

AdminTools partial class:

Visual Basic

Dorknozzle\VB\09_AdminTools.aspx.vb
(excerpt)

Partial Class AdminTools

Inherits System.Web.UI.Page

Licensed to [email protected]

ADO.NET

401

Protected Sub Page_Load(ByVal sender As Object,

➥ ByVal e As System.EventArgs) Handles Me.Load

⋮

End Sub

Private Sub LoadEmployeesList()

Dim conn As SqlConnection

Dim comm As SqlCommand

Dim reader As SqlDataReader

Dim connectionString As String = _

ConfigurationManager.ConnectionStrings( _

"Dorknozzle").ConnectionString

conn = New SqlConnection(connectionString)

comm = New SqlCommand( _

"SELECT EmployeeID, Name FROM Employees", conn)

Try

conn.Open()

reader = comm.ExecuteReader()

employeesList.DataSource = reader

employeesList.DataValueField = "EmployeeID"

employeesList.DataTextField = "Name"

employeesList.DataBind()

reader.Close()

Catch

dbErrorLabel.Text = _

"Error loading the list of employees!
"

Finally

conn.Close()

End Try

updateButton.Enabled = False

nameTextBox.Text = ""

userNameTextBox.Text = ""

addressTextBox.Text = ""

cityTextBox.Text = ""

stateTextBox.Text = ""

zipTextBox.Text = ""

homePhoneTextBox.Text = ""

extensionTextBox.Text = ""

mobilePhoneTextBox.Text = ""

End Sub

End Class

Licensed to [email protected]

402

Build Your Own ASP.NET 3.5 Web Site Using C# & VB

C#

Dorknozzle\CS\09_AdminTools.aspx.cs
(excerpt)

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

{

protected void Page_Load(object sender, EventArgs e)

{

⋮

}

private void LoadEmployeesList()

{

SqlConnection conn;

SqlCommand comm;

SqlDataReader reader;

string connectionString =

ConfigurationManager.ConnectionStrings[

"Dorknozzle"].ConnectionString;

conn = new SqlConnection(connectionString);

comm = new SqlCommand(

"SELECT EmployeeID, Name FROM Employees", conn);

try

{

conn.Open();

reader = comm.ExecuteReader();

employeesList.DataSource = reader;

employeesList.DataValueField = "EmployeeID";

employeesList.DataTextField = "Name";

employeesList.DataBind();

reader.Close();

}

catch

{

dbErrorLabel.Text =

"Error loading the list of employees!
";

}

finally

{

conn.Close();

}

updateButton.Enabled = false;

nameTextBox.Text = "";

userNameTextBox.Text = "";

addressTextBox.Text = "";

cityTextBox.Text = "";

stateTextBox.Text = "";

zipTextBox.Text = "";

Licensed to [email protected]