- •Table of Contents
- •Preface
- •What is ASP.NET?
- •Installing the Required Software
- •Installing the Web Server
- •Installing Internet Information Services (IIS)
- •Installing Cassini
- •Installing the .NET Framework and the SDK
- •Installing the .NET Framework
- •Installing the SDK
- •Configuring the Web Server
- •Configuring IIS
- •Configuring Cassini
- •Where do I Put my Files?
- •Using localhost
- •Virtual Directories
- •Using Cassini
- •Installing SQL Server 2005 Express Edition
- •Installing SQL Server Management Studio Express
- •Installing Visual Web Developer 2005
- •Writing your First ASP.NET Page
- •Getting Help
- •Summary
- •ASP.NET Basics
- •ASP.NET Page Structure
- •Directives
- •Code Declaration Blocks
- •Comments in VB and C# Code
- •Code Render Blocks
- •ASP.NET Server Controls
- •Server-side Comments
- •Literal Text and HTML Tags
- •View State
- •Working with Directives
- •ASP.NET Languages
- •Visual Basic
- •Summary
- •VB and C# Programming Basics
- •Programming Basics
- •Control Events and Subroutines
- •Page Events
- •Variables and Variable Declaration
- •Arrays
- •Functions
- •Operators
- •Breaking Long Lines of Code
- •Conditional Logic
- •Loops
- •Object Oriented Programming Concepts
- •Objects and Classes
- •Properties
- •Methods
- •Classes
- •Constructors
- •Scope
- •Events
- •Understanding Inheritance
- •Objects In .NET
- •Namespaces
- •Using Code-behind Files
- •Summary
- •Constructing ASP.NET Web Pages
- •Web Forms
- •HTML Server Controls
- •Using the HTML Server Controls
- •Web Server Controls
- •Standard Web Server Controls
- •Label
- •Literal
- •TextBox
- •HiddenField
- •Button
- •ImageButton
- •LinkButton
- •HyperLink
- •CheckBox
- •RadioButton
- •Image
- •ImageMap
- •PlaceHolder
- •Panel
- •List Controls
- •DropDownList
- •ListBox
- •RadioButtonList
- •CheckBoxList
- •BulletedList
- •Advanced Controls
- •Calendar
- •AdRotator
- •TreeView
- •SiteMapPath
- •Menu
- •MultiView
- •Wizard
- •FileUpload
- •Web User Controls
- •Creating a Web User Control
- •Using the Web User Control
- •Master Pages
- •Using Cascading Style Sheets (CSS)
- •Types of Styles and Style Sheets
- •Style Properties
- •The CssClass Property
- •Summary
- •Building Web Applications
- •Introducing the Dorknozzle Project
- •Using Visual Web Developer
- •Meeting the Features
- •The Solution Explorer
- •The Web Forms Designer
- •The Code Editor
- •IntelliSense
- •The Toolbox
- •The Properties Window
- •Executing your Project
- •Using Visual Web Developer’s Built-in Web Server
- •Using IIS
- •Using IIS with Visual Web Developer
- •Core Web Application Features
- •Web.config
- •Global.asax
- •Using Application State
- •Working with User Sessions
- •Using the Cache Object
- •Using Cookies
- •Starting the Dorknozzle Project
- •Preparing the Sitemap
- •Using Themes, Skins, and Styles
- •Creating a New Theme Folder
- •Creating a New Style Sheet
- •Styling Web Server Controls
- •Adding a Skin
- •Applying the Theme
- •Building the Master Page
- •Using the Master Page
- •Extending Dorknozzle
- •Debugging and Error Handling
- •Debugging with Visual Web Developer
- •Other Kinds of Errors
- •Custom Errors
- •Handling Exceptions Locally
- •Summary
- •Using the Validation Controls
- •Enforcing Validation on the Server
- •Using Validation Controls
- •RequiredFieldValidator
- •CompareValidator
- •RangeValidator
- •ValidationSummary
- •RegularExpressionValidator
- •Some Useful Regular Expressions
- •CustomValidator
- •Validation Groups
- •Updating Dorknozzle
- •Summary
- •What is a Database?
- •Creating your First Database
- •Creating a New Database Using Visual Web Developer
- •Creating Database Tables
- •Data Types
- •Column Properties
- •Primary Keys
- •Creating the Employees Table
- •Creating the Remaining Tables
- •Executing SQL Scripts
- •Populating the Data Tables
- •Relational Database Design Concepts
- •Foreign Keys
- •Using Database Diagrams
- •Diagrams and Table Relationships
- •One-to-one Relationships
- •One-to-many Relationships
- •Many-to-many Relationships
- •Summary
- •Speaking SQL
- •Reading Data from a Single Table
- •Using the SELECT Statement
- •Selecting Certain Fields
- •Selecting Unique Data with DISTINCT
- •Row Filtering with WHERE
- •Selecting Ranges of Values with BETWEEN
- •Matching Patterns with LIKE
- •Using the IN Operator
- •Sorting Results Using ORDER BY
- •Limiting the Number of Results with TOP
- •Reading Data from Multiple Tables
- •Subqueries
- •Table Joins
- •Expressions and Operators
- •Transact-SQL Functions
- •Arithmetic Functions
- •String Functions
- •Date and Time Functions
- •Working with Groups of Values
- •The COUNT Function
- •Grouping Records Using GROUP BY
- •Filtering Groups Using HAVING
- •The SUM, AVG, MIN, and MAX Functions
- •Updating Existing Data
- •The INSERT Statement
- •The UPDATE Statement
- •The DELETE Statement
- •Stored Procedures
- •Summary
- •Introducing ADO.NET
- •Importing the SqlClient Namespace
- •Defining the Database Connection
- •Preparing the Command
- •Executing the Command
- •Setting up Database Authentication
- •Reading the Data
- •Using Parameters with Queries
- •Bulletproofing Data Access Code
- •Using the Repeater Control
- •More Data Binding
- •Inserting Records
- •Updating Records
- •Deleting Records
- •Using Stored Procedures
- •Summary
- •DataList Basics
- •Handling DataList Events
- •Editing DataList Items and Using Templates
- •DataList and Visual Web Developer
- •Styling the DataList
- •Summary
- •Using the GridView Control
- •Customizing the GridView Columns
- •Styling the GridView with Templates, Skins, and CSS
- •Selecting Grid Records
- •Using the DetailsView Control
- •Styling the DetailsView
- •GridView and DetailsView Events
- •Entering Edit Mode
- •Using Templates
- •Updating DetailsView Records
- •Summary
- •Advanced Data Access
- •Using Data Source Controls
- •Binding the GridView to a SqlDataSource
- •Binding the DetailsView to a SqlDataSource
- •Displaying Lists in DetailsView
- •More on SqlDataSource
- •Working with Data Sets and Data Tables
- •What is a Data Set Made From?
- •Binding DataSets to Controls
- •Implementing Paging
- •Storing Data Sets in View State
- •Implementing Sorting
- •Filtering Data
- •Updating a Database from a Modified DataSet
- •Summary
- •Security and User Authentication
- •Basic Security Guidelines
- •Securing ASP.NET 2.0 Applications
- •Working with Forms Authentication
- •Authenticating Users
- •Working with Hard-coded User Accounts
- •Configuring Forms Authentication
- •Configuring Forms Authorization
- •Storing Users in Web.config
- •Hashing Passwords
- •Logging Users Out
- •ASP.NET 2.0 Memberships and Roles
- •Creating the Membership Data Structures
- •Using your Database to Store Membership Data
- •Using the ASP.NET Web Site Configuration Tool
- •Creating Users and Roles
- •Changing Password Strength Requirements
- •Securing your Web Application
- •Using the ASP.NET Login Controls
- •Authenticating Users
- •Customizing User Display
- •Summary
- •Working with Files and Email
- •Writing and Reading Text Files
- •Setting Up Security
- •Writing Content to a Text File
- •Reading Content from a Text File
- •Accessing Directories and Directory Information
- •Working with Directory and File Paths
- •Uploading Files
- •Sending Email with ASP.NET
- •Configuring the SMTP Server
- •Sending a Test Email
- •Creating the Company Newsletter Page
- •Summary
- •The WebControl Class
- •Properties
- •Methods
- •Standard Web Controls
- •AdRotator
- •Properties
- •Events
- •BulletedList
- •Properties
- •Events
- •Button
- •Properties
- •Events
- •Calendar
- •Properties
- •Events
- •CheckBox
- •Properties
- •Events
- •CheckBoxList
- •Properties
- •Events
- •DropDownList
- •Properties
- •Events
- •FileUpload
- •Properties
- •Methods
- •HiddenField
- •Properties
- •HyperLink
- •Properties
- •Image
- •Properties
- •ImageButton
- •Properties
- •Events
- •ImageMap
- •Properties
- •Events
- •Label
- •Properties
- •LinkButton
- •Properties
- •Events
- •ListBox
- •Properties
- •Events
- •Literal
- •Properties
- •MultiView
- •Properties
- •Methods
- •Events
- •Panel
- •Properties
- •PlaceHolder
- •Properties
- •RadioButton
- •Properties
- •Events
- •RadioButtonList
- •Properties
- •Events
- •TextBox
- •Properties
- •Events
- •Properties
- •Validation Controls
- •CompareValidator
- •Properties
- •Methods
- •CustomValidator
- •Methods
- •Events
- •RangeValidator
- •Properties
- •Methods
- •RegularExpressionValidator
- •Properties
- •Methods
- •RequiredFieldValidator
- •Properties
- •Methods
- •ValidationSummary
- •Properties
- •Navigation Web Controls
- •SiteMapPath
- •Properties
- •Methods
- •Events
- •Menu
- •Properties
- •Methods
- •Events
- •TreeView
- •Properties
- •Methods
- •Events
- •HTML Server Controls
- •HtmlAnchor Control
- •Properties
- •Events
- •HtmlButton Control
- •Properties
- •Events
- •HtmlForm Control
- •Properties
- •HtmlGeneric Control
- •Properties
- •HtmlImage Control
- •Properties
- •HtmlInputButton Control
- •Properties
- •Events
- •HtmlInputCheckBox Control
- •Properties
- •Events
- •HtmlInputFile Control
- •Properties
- •HtmlInputHidden Control
- •Properties
- •HtmlInputImage Control
- •Properties
- •Events
- •HtmlInputRadioButton Control
- •Properties
- •Events
- •HtmlInputText Control
- •Properties
- •Events
- •HtmlSelect Control
- •Properties
- •Events
- •HtmlTable Control
- •Properties
- •HtmlTableCell Control
- •Properties
- •HtmlTableRow Control
- •Properties
- •HtmlTextArea Control
- •Properties
- •Events
- •Index
Chapter 9: ADO.NET
C#
Response.Redirect("SomeForm.aspx");
In our example, we redirect the user back to the same web form. Why on earth would we want to do that? It’s because of view state—if we didn’t end our event handler this way, the same page would display in the browser, but ASP.NET would preserve all of the values that the user had typed into the form fields. The user might not realize the form had even been submitted, and might submit the form repeatedly in confusion. Redirecting the user in the way outlined above causes the browser to reload the page from scratch, clearing the form fields to indicate the completed submission.
Save your work and run it in a browser. Now, we can enter help desk information, as shown in Figure 9.12, and click Submit Request.
Once we click Submit Request, the Click event is raised, the submitButton_Click method is called, all the parameters from the form are passed into the SQL statement, and the data is inserted into the HelpDesk table. We can see this if we open the table in SQL Server Management Studio or Visual Web Developer, which displays the view shown in Figure 9.13.
Figure 9.13. The new request appearing in the HelpDesk table
Updating Records
The major difference between inserting new database records and updating existing ones is that if a user wants to update a record, you’ll usually want to display the information that already exists in the database table before allowing the user to update it. This gives the user a chance to review the data, make the necessary changes, and, finally, submit the updated values. Before we get ahead of ourselves, though, let’s take a look at the code we’ll use to update records within the database table:
Visual Basic
comm = New SqlCommand("UPDATE Table " & _
"SET Field1=@Parameter1, Field2=@Parameter2, … " & _
378
Updating Records
"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
C#
comm = new SqlCommand ("UPDATE Table " +
"SET Field1=@Parameter1, Field2=@Parameter2, … " + "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 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!
When updating a table with some new data, if you don’t specify a WHERE clause, 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. 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.
File: AdminTools.aspx (excerpt)
<%@ Page Language="VB" MasterPageFile="~/Dorknozzle.master" AutoEventWireup="true" CodeFile="AdminTools.aspx.vb" Inherits="AdminTools" title="Dorknozzle Admin Tools" %>
379
Chapter 9: ADO.NET
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" runat="Server">
<h1>Admin Tools</h1>
<asp:Label ID="dbErrorLabel" ForeColor="Red" runat="server" /> <p>
Select an employee to update:<br />
<asp:DropDownList ID="employeesList" runat="server" /> <asp:Button ID="selectButton" Text="Select" runat="server" />
</p>
<p>
<asp:Label ID="nameLabel" runat="server" Text="Name:" Width="100" />
<asp:TextBox ID="nameTextBox" runat="server" /> </p>
<p>
<asp:Label ID="usernameLabel" runat="server" Text="Username:" Width="100" />
<asp:TextBox ID="usernameTextBox" runat="server" /> </p>
<p>
<asp:Label ID="addressLabel" runat="server" Text="Address:" Width="100" />
<asp:TextBox ID="addressTextBox" runat="server" /> </p>
<p>
<asp:Label ID="cityLabel" runat="server" Text="City:" Width="100" />
<asp:TextBox ID="cityTextBox" runat="server" /> </p>
<p>
<asp:Label ID="stateLabel" runat="server" Text="State:" Width="100" />
<asp:TextBox ID="stateTextBox" runat="server" /> </p>
<p>
<asp:Label ID="zipLabel" runat="server" Text="Zip:" Width="100" />
<asp:TextBox ID="zipTextBox" runat="server" /> </p>
<p>
<asp:Label ID="homePhoneLabel" runat="server" Text="Home Phone:" Width="100" />
<asp:TextBox ID="homePhoneTextBox" runat="server" /> </p>
<p>
<asp:Label ID="extensionLabel" runat="server"
380
Updating Records
Text="Extension:" Width="100" />
<asp:TextBox ID="extensionTextBox" runat="server" /> </p>
<p>
<asp:Label ID="mobilePhoneLabel" runat="server" Text="Mobile Phone:" Width="100" />
<asp:TextBox ID="mobilePhoneTextBox" runat="server" /> </p>
<p>
<asp:Button ID="updateButton" Text="Update Employee" Enabled="False" runat="server" />
</p>
</asp:Content>
You can switch to Design View to ensure 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:
381
Chapter 9: ADO.NET
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.
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 selected from employeesList will be passed to the web form—this will be used in an SqlCommand to retrieve the details for this employee.
nameLabel, usernameLabel, addressLabel, cityLabel, stateLabel, zipLabel, homePhoneLabel, extensionLabel, mobilePhoneLabel
These are the labels for the following TextBox controls.
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 Load event handler, then add this code:
Visual Basic |
File: AdminTools.aspx.vb (excerpt) |
Imports System.Data.SqlClient
Imports System.Configuration
Partial Class AdminTools
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles Me.Load
' Read the employees list when initially loading the page If Not IsPostBack Then
382
Updating Records
LoadEmployeesList()
End If
End Sub
Private Sub LoadEmployeesList()
'Define data objects Dim conn As SqlConnection Dim comm As SqlCommand
Dim reader As SqlDataReader
'Read the connection string from Web.config Dim connectionString As String = _
ConfigurationManager.ConnectionStrings( _ "Dorknozzle").ConnectionString
'Initialize connection
conn = New SqlConnection(connectionString) ' Create command
comm = New SqlCommand( _
"SELECT EmployeeID, Name FROM Employees", conn) ' Enclose database code in Try-Catch-Finally
Try
'Open the connection conn.Open()
'Execute the command
reader = comm.ExecuteReader()
'Populate the list of employees employeesList.DataSource = reader employeesList.DataValueField = "EmployeeID" employeesList.DataTextField = "Name" employeesList.DataBind()
'Close the reader
reader.Close() Catch
' Display error message dbErrorLabel.Text = _
"Error loading the list of employees!<br />" Finally
' Close the connection conn.Close()
End Try
'Disable the update button updateButton.Enabled = False
'Clear any values in the TextBox controls nameTextBox.Text = ""
usernameTextBox.Text = "" addressTextBox.Text = "" cityTextBox.Text = ""
383
Chapter 9: ADO.NET
stateTextBox.Text = "" zipTextBox.Text = "" homePhoneTextBox.Text = "" extensionTextBox.Text = "" mobilePhoneTextBox.Text = ""
End |
Sub |
End Class |
|
|
|
C# |
File: AdminTools.aspx.cs (excerpt) |
|
|
using |
System; |
using |
System.Data; |
using |
System.Configuration; |
using |
System.Collections; |
using |
System.Web; |
using |
System.Web.Security; |
using |
System.Web.UI; |
using |
System.Web.UI.WebControls; |
using |
System.Web.UI.WebControls.WebParts; |
using |
System.Web.UI.HtmlControls; |
using |
System.Data.SqlClient; |
public partial class AdminTools : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
// Read the employees list when initially loading the page if (!IsPostBack)
{
LoadEmployeesList();
}
}
private void LoadEmployeesList()
{
//Declare objects SqlConnection conn; SqlCommand comm; SqlDataReader reader;
//Read the connection string from Web.config string connectionString =
ConfigurationManager.ConnectionStrings[
"Dorknozzle"].ConnectionString;
//Initialize connection
conn = new SqlConnection(connectionString); // Create command
comm = new SqlCommand(
384
Updating Records
"SELECT EmployeeID, Name FROM Employees", conn); // Enclose database code in Try-Catch-Finally
try
{
//Open the connection conn.Open();
//Execute the command
reader = comm.ExecuteReader();
//Populate the list of categories employeesList.DataSource = reader; employeesList.DataValueField = "EmployeeID"; employeesList.DataTextField = "Name"; employeesList.DataBind();
//Close the reader
reader.Close();
}
catch
{
// Display error message dbErrorLabel.Text =
"Error loading the list of employees!<br />";
}
finally
{
// Close the connection conn.Close();
}
//Disable the update button updateButton.Enabled = false;
//Clear any values in the TextBox controls nameTextBox.Text = "";
usernameTextBox.Text = ""; addressTextBox.Text = ""; cityTextBox.Text = ""; stateTextBox.Text = ""; zipTextBox.Text = ""; homePhoneTextBox.Text = ""; extensionTextBox.Text = ""; mobilePhoneTextBox.Text = "";
}
}
Note that we’ve put the code to populate the employeesList in a separate subroutine called LoadEmployeeList. Later on, we’ll need to reload the names in this list in case any of the names have been edited; we put this code into its own subroutine so that we don’t need to repeat it.
385
Chapter 9: ADO.NET
Load the page now, and test that the list of employees is bound to employeeList, and that the page displays as shown in Figure 9.15.
Figure 9.15. Displaying the list of employees in a drop-down
As you can see, all the employees are listed within the employee drop-down menu. Again, the employees’ names are shown because the Name field is bound to the
DataTextField property of the DropDownList control. Similarly, the EmployeeID field is bound to the DataValueField property of the DropDownList control, ensuring that a selected employee’s ID will be submitted as the value of the field.
We need to undertake two more tasks to complete this page’s functionality. First, we need to handle the Click event of the Select button so that it will load the form with data about the selected employee. Then, we’ll need to handle the Click event of the Update button, to update the information for the selected employee. Let’s start with the Select button. Double-click the button in Design View to have the Click event handler generated for you, then complete the code like this:
Visual Basic |
File: AdminTools.aspx.vb (excerpt) |
Protected Sub selectButton_Click(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles selectButton.Click
386
Updating Records
Figure 9.16. Displaying employee details in the update form
' Define data objects
Dim conn As SqlConnection Dim comm As SqlCommand
Dim reader As SqlDataReader
'Read the connection string from Web.config Dim connectionString As String = _
ConfigurationManager.ConnectionStrings( _ "Dorknozzle").ConnectionString
'Initialize connection
conn = New SqlConnection(connectionString) ' Create command
comm = New SqlCommand( _
"SELECT Name, Username, Address, City, State, Zip, " & _ "HomePhone, Extension, MobilePhone FROM Employees " & _ "WHERE EmployeeID = @EmployeeID", conn)
387
Chapter 9: ADO.NET
'Add command parameters comm.Parameters.Add("@EmployeeID", Data.SqlDbType.Int) comm.Parameters.Item("@EmployeeID").Value = _
employeesList.SelectedItem.Value
'Enclose database code in Try-Catch-Finally
Try
'Open the connection conn.Open()
'Execute the command
reader = comm.ExecuteReader()
'Display the data on the form If reader.Read() Then
nameTextBox.Text = reader.Item("Name").ToString() usernameTextBox.Text = reader.Item("Username").ToString() addressTextBox.Text = reader.Item("Address").ToString() cityTextBox.Text = reader.Item("City").ToString() stateTextBox.Text = reader.Item("State").ToString() zipTextBox.Text = reader.Item("Zip").ToString() homePhoneTextBox.Text = reader.Item("HomePhone").ToString() extensionTextBox.Text = reader.Item("Extension").ToString() mobilePhoneTextBox.Text = _
reader.Item("MobilePhone").ToString()
End If
'Close the reader
reader.Close()
' |
Enable the Update button |
updateButton.Enabled = True |
|
Catch |
|
' |
Display error message |
dbErrorLabel.Text = _ |
|
|
"Error loading the employee details!<br />" |
Finally |
|
' |
Close the connection |
|
conn.Close() |
End |
Try |
End Sub |
|
|
|
C# |
File: AdminTools.aspx.cs (excerpt) |
protected void selectButton_Click(object sender, EventArgs e)
{
//Declare objects SqlConnection conn; SqlCommand comm; SqlDataReader reader;
//Read the connection string from Web.config string connectionString =
388
Updating Records
ConfigurationManager.ConnectionStrings[
"Dorknozzle"].ConnectionString; // Initialize connection
conn = new SqlConnection(connectionString); // Create command
comm = new SqlCommand(
"SELECT Name, Username, Address, City, State, Zip, " + "HomePhone, Extension, MobilePhone FROM Employees " + "WHERE EmployeeID = @EmployeeID", conn);
//Add command parameters comm.Parameters.Add("@EmployeeID", SqlDbType.Int); comm.Parameters["@EmployeeID"].Value =
employeesList.SelectedItem.Value;
//Enclose database code in Try-Catch-Finally
try
{
//Open the connection conn.Open();
//Execute the command
reader = comm.ExecuteReader();
//Display the data on the form if (reader.Read())
{
nameTextBox.Text = reader["Name"].ToString(); usernameTextBox.Text = reader["Username"].ToString(); addressTextBox.Text = reader["Address"].ToString(); cityTextBox.Text = reader["City"].ToString(); stateTextBox.Text = reader["State"].ToString(); zipTextBox.Text = reader["Zip"].ToString(); homePhoneTextBox.Text = reader["HomePhone"].ToString(); extensionTextBox.Text = reader["Extension"].ToString(); mobilePhoneTextBox.Text = reader["MobilePhone"].ToString();
}
//Close the reader
reader.Close();
// Enable the Update button updateButton.Enabled = true;
}
catch
{
// Display error message dbErrorLabel.Text =
"Error loading the employee details!<br />";
}
finally
{
389
Chapter 9: ADO.NET
// Close the connection conn.Close();
}
}
If you load the page, select an employee, and click the Select button, the form will be populated with the employee’s details.
The last thing we need to do is add code to handle the update interaction. You may have noticed that the Button control has an Enabled property, which is initially set to False. The reason for this is simple: you don’t want your users updating information before they’ve selected an employee. You want them to use the Update Employee button only when data for an existing employee has been loaded into the TextBox controls. If you look at the selectButton_Click method just before the Catch statement, you’ll notice that we enable this button after binding the user data to the fields.
Now that these TextBox controls are populated and the Update Employee button is enabled, let’s add some code to update an employee’s details. Open AdminTools.aspx in Design View, and double-click the Update Employee button. Visual Web Developer will generate the signature of the event handler subroutine (updateButton_Click) for you. Finally, let’s add the necessary code to handle updating the employee data:
Visual Basic |
|
File: AdminTools.aspx.vb (excerpt) |
|
|
|
Protected Sub |
updateButton_Click(ByVal sender As Object, _ |
|
ByVal e As System.EventArgs) Handles updateButton.Click |
||
' Define |
data objects |
|
Dim conn |
As |
SqlConnection |
Dim comm |
As |
SqlCommand |
'Read the connection string from Web.config Dim connectionString As String = _
ConfigurationManager.ConnectionStrings( _ "Dorknozzle").ConnectionString
'Initialize connection
conn = New SqlConnection(connectionString) ' Create command
comm = New SqlCommand( _
"UPDATE Employees SET Name=@Name, Username=@Username, " & _ "Address=@Address, City=@City, State=@State, Zip=@Zip," & _ "HomePhone=@HomePhone, Extension=@Extension, " & _ "MobilePhone=@MobilePhone " & _
"WHERE EmployeeID=@EmployeeID", conn) ' Add command parameters
390
Updating Records
comm.Parameters.Add("@Name", System.Data.SqlDbType.NVarChar, 50) comm.Parameters("@Name").Value = nameTextBox.Text comm.Parameters.Add("@Username", _
System.Data.SqlDbType.NVarChar, 50) comm.Parameters("@Username").Value = usernameTextBox.Text comm.Parameters.Add("@Address", _
System.Data.SqlDbType.NVarChar, 50) comm.Parameters("@Address").Value = addressTextBox.Text comm.Parameters.Add("@City", _
System.Data.SqlDbType.NVarChar, 50) comm.Parameters("@City").Value = cityTextBox.Text comm.Parameters.Add("@State", _
System.Data.SqlDbType.NVarChar, 50) comm.Parameters("@State").Value = stateTextBox.Text comm.Parameters.Add("@Zip", System.Data.SqlDbType.NVarChar, 50) comm.Parameters("@Zip").Value = zipTextBox.Text comm.Parameters.Add("@HomePhone", _
System.Data.SqlDbType.NVarChar, 50) comm.Parameters("@HomePhone").Value = homePhoneTextBox.Text comm.Parameters.Add("@Extension", _
System.Data.SqlDbType.NVarChar, 50) comm.Parameters("@Extension").Value = extensionTextBox.Text comm.Parameters.Add("@MobilePhone", _
System.Data.SqlDbType.NVarChar, 50) comm.Parameters("@MobilePhone").Value = mobilePhoneTextBox.Text comm.Parameters.Add("@EmployeeID", System.Data.SqlDbType.Int) comm.Parameters("@EmployeeID").Value = _
employeesList.SelectedItem.Value
' Enclose database code in Try-Catch-Finally Try
'Open the connection conn.Open()
'Execute the command comm.ExecuteNonQuery()
Catch
' Display error message dbErrorLabel.Text = _
"Error updating the employee details!<br />" Finally
' Close the connection conn.Close()
End Try
' Refresh the employees list LoadEmployeesList()
End Sub
391
Chapter 9: ADO.NET
C# |
File: AdminTools.aspx.cs (excerpt) |
protected void updateButton_Click(object sender, EventArgs e)
{
//Declare objects SqlConnection conn; SqlCommand comm;
//Read the connection string from Web.config string connectionString =
ConfigurationManager.ConnectionStrings[
"Dorknozzle"].ConnectionString;
//Initialize connection
conn = new SqlConnection(connectionString); // Create command
comm = new SqlCommand(
"UPDATE Employees SET Name=@Name, Username=@Username, " + "Address=@Address, City=@City, State=@State, Zip=@Zip, " + "HomePhone=@HomePhone, Extension=@Extension, " + "MobilePhone=@MobilePhone " +
"WHERE EmployeeID=@EmployeeID", conn); // Add command parameters comm.Parameters.Add("@Name",
System.Data.SqlDbType.NVarChar,50); comm.Parameters["@Name"].Value = nameTextBox.Text; comm.Parameters.Add("@Username",
System.Data.SqlDbType.NVarChar, 50); comm.Parameters["@Username"].Value = usernameTextBox.Text; comm.Parameters.Add("@Address",
System.Data.SqlDbType.NVarChar, 50); comm.Parameters["@Address"].Value = addressTextBox.Text; comm.Parameters.Add("@City",
System.Data.SqlDbType.NVarChar, 50); comm.Parameters["@City"].Value = cityTextBox.Text; comm.Parameters.Add("@State",
System.Data.SqlDbType.NVarChar, 50); comm.Parameters["@State"].Value = stateTextBox.Text; comm.Parameters.Add("@Zip",
System.Data.SqlDbType.NVarChar, 50); comm.Parameters["@Zip"].Value = zipTextBox.Text; comm.Parameters.Add("@HomePhone",
System.Data.SqlDbType.NVarChar, 50); comm.Parameters["@HomePhone"].Value = homePhoneTextBox.Text; comm.Parameters.Add("@Extension",
System.Data.SqlDbType.NVarChar, 50); comm.Parameters["@Extension"].Value = extensionTextBox.Text; comm.Parameters.Add("@MobilePhone",
System.Data.SqlDbType.NVarChar, 50);
392
Updating Records
comm.Parameters["@MobilePhone"].Value = mobilePhoneTextBox.Text; comm.Parameters.Add("@EmployeeID", System.Data.SqlDbType.Int); comm.Parameters["@EmployeeID"].Value =
employeesList.SelectedItem.Value;
// Enclose database code in Try-Catch-Finally try
{
//Open the connection conn.Open();
//Execute the command comm.ExecuteNonQuery();
}
catch
{
// Display error message dbErrorLabel.Text =
"Error updating the employee details!<br />";
}
finally
{
// Close the connection conn.Close();
}
// Refresh the employees list LoadEmployeesList();
}
As you can see, the only real differences between this and the help desk page are that we’re using an UPDATE query instead of an INSERT query, and we’ve had to let the user choose an entry from the database to update. We use that selection not only to populate the form fields with the existing database values, but to restrict our UPDATE query so that it only affects that one record.
You’ll also notice that at the very end of this method, we call LoadEmployeesList to reload the list of employees, as the user may have changed the name of one of the employees. LoadEmployeesList also disables the Update Employee button and clears the contents of the page’s TextBox controls. Once LoadEmployeesList has executed, the page is ready for the user to select another employee for updating.
As with all examples in this book, you can get this page’s completed code from the code archive.
393