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

386

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

}

finally

{

conn.Close();

}

}

}

You’ll notice that the guts of Page_Load are enclosed in an If statement, which

tests to see if IsPostBack is not True. But just what
is
this IsPostBack?

Earlier, in
Chapter 2, we explored the view state
mechanism that ASP.NET uses to remember the data in its controls. View state allows your user controls to remember

their states across page loads. Every time an event that needs to be handled on the

server is raised, the form in the page is submitted to the server—a process known

as a
post back
. For example, when a button with a server-side Click event handler

is clicked, a post back occurs so that the server-side code can respond to the Click

event.

After such an event occurs, all the controls in the web form retain their values, but

the Page_Load method is executed again regardless. In consequence, if you click

the
Submit Request
button ten times, Page_Load will be executed ten times. If the

data access code that fills the form with values is in Page_Load, the database will

be queried ten times, even though the data that needs to be displayed on the page

won’t change!

It’s here that IsPostBack comes into play. IsPostBack returns False if the web

form is being loaded for the first time; it returns True if the page is being loaded,

because the form has been posted back to the server.

Referring to IsPostBack

IsPostBack is actually a property of the Page class, but since our web form is

a class that inherits from Page, we can refer to IsPostBack directly. If we wanted

to, we could refer to this property as Me.IsPostBack in VB, or this.IsPostBack

in C#.

Licensed to [email protected]

ADO.NET

387

Using the IsPostBack Property Appropriately

It’s not always appropriate to use IsPostBack as we’re using it here. We’re

loading the form with data only the first time the page is loaded, because we know

that the data in the drop-down lists won’t change in response to other changes in

the form. In cases in which the data in the drop-down lists may change, it may

be appropriate to access the database and re-fill the form with data every time the

form is loaded. For example, we might want to take such action in a car search

form in which, when users select a car manufacturer, their selection triggers a request to the server to load a list of all models of car made by that manufacturer. Once it has been established that this is the first time the page has been loaded, the

code continues in a pattern similar to the previous code samples. We retrieve the

connection string from
Web.config
, create a new connection to the database, and set

up our SqlCommand objects. In this page, we retrieve two lists—a list of help desk

request categories and a list of subjects—so we’ll need to execute two queries. These

queries are stored in two SqlCommand objects: categoryComm and subjectComm.

Next, inside a Try block, we execute the commands and bind the data in our

SqlDataReader to the existing controls. First, we execute categoryComm to retrieve

a list of categories; then, we bind that list to categoryList:

Visual Basic

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

reader = categoryComm.ExecuteReader()

categoryList.DataSource = reader

categoryList.DataValueField = "CategoryID"

categoryList.DataTextField = "Category"

categoryList.DataBind()

reader.Close()

C#

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

reader = categoryComm.ExecuteReader();

categoryList.DataSource = reader;

categoryList.DataValueField = "CategoryID";

categoryList.DataTextField = "Category";

categoryList.DataBind();

reader.Close();

Licensed to [email protected]

388

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

Note that not all controls handle their bindings in the same way. In this case, we

want the DropDownList control to display the data from the Category column of

the HelpDeskCategories table. The DropDownList control is cleverly designed, and

it can also store an ID associated with each item in the list. This can be very helpful

when we’re performing database operations using the items selected from 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 respective 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 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 an INSERT query, you’d need to use the

ExecuteNonQuery method of the SqlCommand object, as INSERT queries don’t return

results.

When you’re 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 pesky users always seem to find unimaginable ways to do things!).

A typical INSERT query is coded as follows:

Visual Basic

comm = New SqlCommand( _

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

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

Licensed to [email protected]

ADO.NET

389

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

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

and click
Submit Request
to cause the information to 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:

Dorknozzle\VB\05_HelpDesk.aspx
(excerpt)

⋮

Employee Help Desk Request

⋮

The form already contains numerous validation controls that display error messages

if problems are found within the entered data. We’re adding this Label control to

Licensed to [email protected]

390

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

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; this situation will persist 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
HelpDesk.as-
px
—we added it in
the section called “Updating Dorknozzle” in Chapter 6, when

we added validation controls to the page. Modify this method by adding code that

inserts the user-submitted help desk request into the database, as shown below:

Visual Basic

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

Protected Sub submitButton_Click(ByVal sender As Object,

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

If Page.IsValid Then

Dim conn As SqlConnection

Dim comm As SqlCommand

Dim connectionString As String = _

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 = _

Licensed to [email protected]