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

ADO.NET

391

Figure 9.11. Displaying an error message in the catch block

descriptionTextBox.Text

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

comm.Parameters("@StatusID").Value = 1

Try

conn.Open()

comm.ExecuteNonQuery()

Response.Redirect("HelpDesk.aspx")

Catch

dbErrorMessage.Text = _

"Error submitting the help desk request! Please " & _

"try again later, and/or change the entered data!"

Finally

conn.Close()

End Try

End If

End Sub

Licensed to [email protected]

392

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

C#

Dorknozzle\CS\06_HelpDesk.aspx.cs
(excerpt)

protected void submitButton_Click(object sender, EventArgs e)

{

if (Page.IsValid)

{

SqlConnection conn;

SqlCommand comm;

string connectionString =

ConfigurationManager.ConnectionStrings[

"Dorknozzle"].ConnectionString;

conn = new SqlConnection(connectionString);

comm = new SqlCommand(

"INSERT INTO HelpDesk (EmployeeID, StationNumber, " +

"CategoryID, SubjectID, Description, StatusID) " +

"VALUES (@EmployeeID, @StationNumber, @CategoryID, " +

"@SubjectID, @Description, @StatusID)", conn);

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

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

comm.Parameters.Add("@StationNumber",

System.Data.SqlDbType.Int);

comm.Parameters["@StationNumber"].Value = stationTextBox.Text;

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

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

categoryList.SelectedItem.Value;

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

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

subjectList.SelectedItem.Value;

comm.Parameters.Add("@Description",

System.Data.SqlDbType.NVarChar, 50);

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

descriptionTextBox.Text;

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

comm.Parameters["@StatusID"].Value = 1;

try

{

conn.Open();

comm.ExecuteNonQuery();

Response.Redirect("HelpDesk.aspx");

}

catch

{

dbErrorMessage.Text =

"Error submitting the help desk request! Please " +

"try again later, and/or change the entered data!";

Licensed to [email protected]

ADO.NET

393

}

finally

{

conn.Close();

}

}

}

It may look intimidating, but most of the code above is simply defining the SQL

command parameter types and values that are to be inserted into the SQL statement.

Make Sure You’ve Set the Identity Property!

Note that when we’re inserting a new record into the HelpDesk table, we rely on

the ID column, RequestID, to be generated automatically for us by the database.

If we forget to set RequestID as an identity column, we’ll receive an exception

every time we try to add a new help desk request!

You may have noticed the use of the ExecuteNonQuery method:

Visual Basic

Dorknozzle\VB\06_HelpDesk.aspx.vb
(excerpt)

conn.Open()

comm.ExecuteNonQuery()

Response.Redirect("HelpDesk.aspx")

C#

Dorknozzle\CS\06_HelpDesk.aspx.cs
(excerpt)

try

{

conn.Open();

comm.ExecuteNonQuery();

Response.Redirect("HelpDesk.aspx");

}

As you know, we use this method when we’re executing any SQL query that doesn’t

return a set of results, such as INSERT, UPDATE, and DELETE queries.

Licensed to [email protected]

394

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

You’ll remember that, in order to make the example simpler, we hard-coded the

EmployeeID (to the value of 5), and the Status (to the value of 1). To make the application complete, you could add another drop-down list from which employees could select their names, and take the IDs from there. For now, just make sure that

the Employees table has a record with an EmployeeID of 5, otherwise the query won’t

execute successfully.

The other potentially unfamiliar part of this code is the final line of the Try block,

which uses Response.Redirect. This method should be quite familiar to developers

who are experienced with ASP. Response.Redirect simply redirects the browser

to another page.

In our Dorknozzle Help Desk request form script, 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 his or her confusion. Redirecting the user in

the way that’s outlined above causes the browser to reload the page from scratch,

clearing the form fields to indicate the completed submission.

Okay, save your work and run it in a browser. Now, we can enter help desk inform
ation, 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. To verify this, we can open the

table in SQL Server Management Studio or Visual Web Developer; we’ll see the

view shown in Figure 9.13.

Figure 9.13. The new request appearing in the HelpDesk table

Licensed to [email protected]

ADO.NET

395

Figure 9.12. Submitting the Help Desk Request form

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
,
…
" & _

"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

Licensed to [email protected]

396

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

C#

comm = new SqlCommand ("UPDATE
Table
" +

"SET
Field1
=@
Parameter1
,
Field2
=@
Parameter2
,
…
" +