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

ADO.NET

403

homePhoneTextBox.Text = "";

extensionTextBox.Text = "";

mobilePhoneTextBox.Text = "";

}

}

In our LoadEmployeeList method, we use data binding to create the values in the

drop-down list as we did in
the section called “More Data Binding”, and we clear

all the form fields by setting their values to an empty string. You may also have

noticed that we set the Enabled property of the updateButton to False. We have a

good reason for doing this, as we’ll explain shortly, when we come to write the code

that updates the employee record in the database.

Load the page now, 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 list

Licensed to [email protected]

404

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

As you can see, all the employees are listed within the 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, and then insert the following code:

Visual Basic

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

Protected Sub selectButton_Click(ByVal sender As Object,

➥ ByVal e As System.EventArgs) Handles selectButton.Click

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 Name, Username, Address, City, State, Zip, " & _

"HomePhone, Extension, MobilePhone FROM Employees " & _

"WHERE EmployeeID = @EmployeeID", conn)

comm.Parameters.Add("@EmployeeID", Data.SqlDbType.Int)

comm.Parameters.Item("@EmployeeID").Value = _

employeesList.SelectedItem.Value

Try

conn.Open()

reader = comm.ExecuteReader()

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()

Licensed to [email protected]

ADO.NET

405

mobilePhoneTextBox.Text = _

reader.Item("MobilePhone").ToString()

End If

reader.Close()

updateButton.Enabled = True

Catch

dbErrorLabel.Text = _

"Error loading the employee details!
"

Finally

conn.Close()

End Try

End Sub

C#

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

protected void selectButton_Click(object sender, EventArgs e)

{

SqlConnection conn;

SqlCommand comm;

SqlDataReader reader;

string connectionString =

ConfigurationManager.ConnectionStrings[

"Dorknozzle"].ConnectionString;

conn = new SqlConnection(connectionString);

comm = new SqlCommand(

"SELECT Name, Username, Address, City, State, Zip, " +

"HomePhone, Extension, MobilePhone FROM Employees " +

"WHERE EmployeeID = @EmployeeID", conn);

comm.Parameters.Add("@EmployeeID", System.Data.SqlDbType.Int);

comm.Parameters["@EmployeeID"].Value =

employeesList.SelectedItem.Value;

try

{

conn.Open();

reader = comm.ExecuteReader();

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();

Licensed to [email protected]

406

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

extensionTextBox.Text = reader["Extension"].ToString();

mobilePhoneTextBox.Text = reader["MobilePhone"].ToString();

}

reader.Close();

updateButton.Enabled = true;

}

catch

{

dbErrorLabel.Text =

"Error loading the employee details!
";

}

finally

{

conn.Close();

}

}

In our
Select
button Click event code above, we start by setting up our database

connection and command objects, as well as the command parameter for the employee ID. Then, within the Try block we read the data from the SqlDataReader object to fill in the form fields. If you load the page, select an employee, and click

the
Select
button, the form will be populated with the employee’s details, as depicted in
Figure 9.16.

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 again at the selectButton_Click method above, just before

the Catch statement, you’ll notice that we enable this button by setting its Enabled

property to True, 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 for the updateButton_Click event handler automatically.

Finally, let’s add the code that handles the updating of the employee data:

Licensed to [email protected]

ADO.NET

407

Figure 9.16. Displaying employee details in the update form

Visual Basic

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

Protected Sub updateButton_Click(ByVal sender As Object,

➥ ByVal e As System.EventArgs) Handles updateButton.Click

Dim conn As SqlConnection

Dim comm As SqlCommand

Dim connectionString As String = _

ConfigurationManager.ConnectionStrings( _

"Dorknozzle").ConnectionString

conn = New SqlConnection(connectionString)

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)

comm.Parameters.Add("@Name", System.Data.SqlDbType.NVarChar, 50)

comm.Parameters("@Name").Value = nameTextBox.Text

comm.Parameters.Add("@Username", _

Licensed to [email protected]

408

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

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

Try

conn.Open()

comm.ExecuteNonQuery()

Catch

dbErrorLabel.Text = _

"Error updating the employee details!
"

Finally

conn.Close()

End Try

LoadEmployeesList()

End Sub

C#

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

protected void updateButton_Click(object sender, EventArgs e)

{

SqlConnection conn;

SqlCommand comm;

string connectionString =

Licensed to [email protected]

ADO.NET

409

ConfigurationManager.ConnectionStrings[

"Dorknozzle"].ConnectionString;

conn = new SqlConnection(connectionString);

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);

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;