Build Your Own ASP.NET 2.0 Web Site Using C# & VB, Second Edition

a DropDownList, because the database operations are always carried out using

the items’ IDs.

The DataTextField property of the DropDownList needs to be set to the name

of the column that provides the text to be displayed, and the DataValueField

must be set to the name of the column that contains the ID. This allows us to

pass the ID of the category or subject along to any part of the application when

a user makes a selection from the drop-down lists.

When the page loads, all the categories and subjects will be loaded into their re-

spective DropDownList controls, as shown in Figure 9.10.

Inserting Records

The code that inserts records from your application into a database isn’t too

different from what we’ve already seen. The main difference is that now we need

to retrieve data from the user input controls in the page, and use this data as the

parameters to our INSERT query, rather than simply firing off a simple SELECT

query. As we discussed earlier in this chapter, to execute such a query, you’d need

to use the ExecuteNonQuery method of the SqlCommand object, as INSERT queries

don’t return results.

When inserting user-entered data into the database, you need to be extra careful

about validating that data in case the users don’t type whatever you expect them

to (those users always seem to find unimaginable ways to do things!).

A typical INSERT statement is coded as follows:

Visual Basic

comm = New SqlCommand( _

"INSERT INTO HelpDesk (Field1, Field2, …) " & _

"VALUES (@Parameter1, @Parameter2, …)", conn)

Once the SqlCommand object has been created with a parameterized INSERT query,

we simply pass in the necessary parameters, similarly to the process we followed

for SELECT queries:

Visual Basic

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

371

Inserting Records

Keep in mind that in C#, the syntax for accessing the parameters collection is

slightly different:

C#

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;

To demonstrate the process of inserting records into the database, let’s finish the

help desk page.

When employees visit the help desk page, they’ll fill out the necessary information,

click Submit Request, and the information will be saved within the HelpDesk

table. The HelpDesk table acts as a queue for IT personnel to review and respond

to reported issues.

First, open HelpDesk.aspx, and add a label just below the page’s heading.

File: HelpDesk.aspx (excerpt)

<h1>Employee Help Desk Request</h1>

<asp:Label ID="dbErrorMessage" ForeColor="Red" runat="server" />

<p>

Station Number:<br />

<asp:TextBox id="stationTextBox" runat="server"

CssClass="textbox" />

The form already contains numerous validation controls that display error mes-

sages if they find problems with the entered data. We’re adding this Label control

to display errors that arise when an exception is caught while the database query

is executing. This is necessary because, although the validation controls prevent

most of the errors that could occur, they can’t guarantee that the database query

will run flawlessly. For example, if the database server is rebooted, and we try to

run a database query, we’ll receive an error until the database is up and running

again. There could be other kinds of errors, too. An example of an error message

is shown in Figure 9.11.

You already have a Click event handler for the Submit Request button in Help-

Desk.aspx—we added it in Chapter 6, when we added validation controls to the

page. The event handler should look like this:

Visual Basic File: HelpDesk.aspx.vb (excerpt)

Protected Sub submitButton_Click(ByVal sender As Object, _

ByVal e As System.EventArgs) Handles submitButton.Click

372

Chapter 9: ADO.NET

Figure 9.11. Displaying an error message in the catch block

If Page.IsValid Then

' Code that uses the data entered by the user

End If

End Sub

C# File: HelpDesk.aspx.cs (excerpt)

protected void submitButton_Click(object sender, EventArgs e)

{

if (Page.IsValid)

{

// Code that uses the data entered by the user

}

}

373

Inserting Records

Modify this method by adding code that inserts the user-submitted help desk

request into the database, as shown below:

Visual Basic File: HelpDesk.aspx.vb (excerpt)

Protected Sub submitButton_Click(ByVal sender As Object, _

ByVal e As System.EventArgs) Handles submitButton.Click

If Page.IsValid Then

' 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( _

"INSERT INTO HelpDesk (EmployeeID, StationNumber, " & _

"CategoryID, SubjectID, Description, StatusID) " & _

"VALUES (@EmployeeID, @StationNumber, @CategoryID, " & _

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

' Add command parameters

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

' Enclose database code in Try-Catch-Finally

Try

' Open the connection

conn.Open()

' Execute the command

comm.ExecuteNonQuery()

374

Chapter 9: ADO.NET