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 isnt too
different from what weve 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, youd need
to use the ExecuteNonQuery method of the SqlCommand object, as INSERT queries
dont return results.
When inserting user-entered data into the database, you need to be extra careful
about validating that data in case the users dont 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, lets finish the
help desk page.
When employees visit the help desk page, theyll 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 pages 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. Were 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 cant guarantee that the database query
will run flawlessly. For example, if the database server is rebooted, and we try to
run a database query, well 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.aspxwe 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

Get Build Your Own ASP.NET 2.0 Web Site Using C# & VB, Second Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.