CIS Department > Tutorials > ASP .NET Web Apps > VB 2005

Type and Range Example

The Goals for this Example

This example allows the user to select products based both on a price range and a product type (category). The types are displayed in the initial page in a drop-down list from which the user can select. We also learn how to add an "all items" entry to this list.

Making the Modified Web App

What the App Should Do

We take the web app that used HttpSessionState, and rewrite it so that it allows the user to select auto parts based on the type (category) of part in addition to the price range as used before.
The various types of parts are read from the SQL Server database by using a DataReader.
These types are then displayed in a drop-down list from which the user can choose.
In addition, this example shows how to add an item to the list for "All Types" in case the user does not wish to narrow the search to a particular type.
The user selects the desired price range and type of product as shown in this picture of the home page.
Clicking the button shows the matches on the results page.

Copying the Old Web App

Use MyComputer to copy the project folder for the web app that used HttpSessionState and name the copied folder TypeAndRange. Store it in a convenient spot (such as your M drive).
Then open the new project.

Making the First Round of Changes

Move the "List Matches" button so that it is below the other items on the Home.aspx web form.
Drag a drop-down list onto this form. Place it to the right of the boxes for the minimum price and maximum price.
Change the ID of this drop-down list to TypeList.
Place a label above this drop-down list and set its Text field to "Type:".
Add a label to the right of the button and make its font color red.
Change the text and ID of this label to "Error in accessing database" and ErrorTypeLabel, respectively. Set its Visible property to False.
Check that the EnableViewState property for the 2 text boxes and the drop down list are True.
That way, after the postback happens when the user clicks the button, the data will still be available. In other words, this allows data in our controls (the text boxes and drop-down list, including the item selected by the user) to remain after the postback happens.
As you will see below in the code that goes with this web form, we need this data to still be present so that we can save it in a session state object that will be available for use in the Results.aspx web form.
If you do not need data to persist like this, you can set EnableViewState to False and avoid the extra overhead associated with maintaining view state.
Do File, Save All to save what you have thus far.

Database Changes

In Server Explorer, right click on the Parts table and select Show Table Data.
We wish to add some new data to this Parts table so that we can have more interesting queries of the database.
Add rows of data until you get the following 9 rows:

PartNumber	Description	Type	Price
105	From 1975 Oldsmobile 88.	engine	599.98
120	From 1977 Chevy Nova. Like new!	muffler	29.99
128	From 1966 Volkswagon Beetle.	muffler	9.98
129	From 1975 Oldsmobile 88.	steering wheel	14.95
133	From 1966 Volkswagon Beetle. Chipped.	windshield	98.98
155	From 1966 Volkswagon Beetle.	engine	450
222	From 1975 Oldsmobile 88.	muffler	22.95
226	From 1977 Chevy Nova. Collector's item!	steering wheel	12.55
333	From 1977 Chevy Nova. Slightly cracked.	fan belt	8.88

Simply close the window showing the rows of data when you are finished entering data.

A Stored Procedure to Find the Types

In order to place the parts categories into the drop down list, we need a stored procedure that returns these types.
In Server Explorer, under the data connection that we have been using, right click on Stored Procedures and select Add New Stored Procedure.
Change the name of the stored procedure to SelectByType.
Delete the parameter list as we do not need it.
Uncomment the SET NOCOUNT ON line.
Put a blank line between this and the RETURN. Then right click on this new blank line and select Insert SQL.
Add the Parts table to the QueryBuilder grid.
Select the check box for the Type field. This indicates that we want the values for the Type field returned by our stored procedure.
Click in this row under Sort Type and choose Ascending. We might as well have our list of types in alphabetical order!
Right click at the front of this field, select Properties, and set Distinct Values to Yes. See this picture showing the Properties window. This is needed since the database has multiple rows with the same parts type. Using Distinct omits the duplicates.
Click Close and OK.
Your stored procedure should now look like this:


CREATE PROCEDURE db_datareader.SelectByType 
AS
    SET NOCOUNT ON
    SELECT DISTINCT Type
    FROM         Parts
    ORDER BY Type
    RETURN

Click on the save button to save your stored procedure.
Right click on your stored procedure, either on the editor where the procedure code is displayed or on the icon for the procedure in Server Explorer. Select the Execute option. You should see 5 rows of data returned.
You may now close the editor that is displaying the stored procedure code.
In Visual Studio do File, Save All.

Adjusting the Code in Home.aspx.vb

Open the Home.aspx.vb code-behind file.
Rewrite the Page_Load procedure so that it looks as follows:


Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    Dim Min, Max As String

    If Not IsPostBack Then
        Dim myDatabaseConnection As SqlClient.SqlConnection = Nothing
        Dim myCommand As SqlClient.SqlCommand = Nothing
        Dim myReader As SqlClient.SqlDataReader = Nothing

        Try
            Dim connectionString As String = _
                ConfigurationManager.ConnectionStrings("PartsConnectionString").ConnectionString
            myDatabaseConnection = New SqlClient.SqlConnection(connectionString)
            myCommand = New SqlClient.SqlCommand()
            TypeList.Items.Add("*** All Types ***")

            'Set up to use a stored procedure:
            myCommand.CommandType = CommandType.StoredProcedure
            myCommand.Connection = myDatabaseConnection
            myCommand.CommandText = "SelectByType"

            'Use an SqlDataReader to execute the stored procedure and
            'get the results into the drop down list:
            myDatabaseConnection.Open()
            myReader = myCommand.ExecuteReader()
            While (myReader.Read())
                TypeList.Items.Add(myReader.GetString(0))
            End While

            myDatabaseConnection.Close()
            myDatabaseConnection.Dispose()
        Catch exception As System.Data.SqlClient.SqlException
            ErrorTypeLabel.Visible = True
        Catch exception As Exception
            ErrorTypeLabel.Visible = True
        Finally    'Do cleanup tasks here:
            myCommand = Nothing

            If Not myReader Is Nothing AndAlso _
                Not myReader.IsClosed Then
                myReader.Close()
            End If

            myReader = Nothing

            If Not myDatabaseConnection Is Nothing AndAlso _
                myDatabaseConnection.State = ConnectionState.Open Then
                myDatabaseConnection.Close()
                myDatabaseConnection.Dispose()
            End If

            myDatabaseConnection = Nothing
        End Try
    Else   ' We have a postback.
        Page.Validate()
        If Page.IsValid Then
            Min = MinTextBox.Text.ToString()
            Max = MaxTextBox.Text.ToString()
            Session.Add("MinKey", Min)
            Session.Add("MaxKey", Max)
            Session.Add("TypeSelected", TypeList.SelectedItem.Text)
            Response.Redirect("Results.aspx")
        End If
    End If
End Sub

In the code, note that if there is no postback, we add "*** All Types ***" as an item in the drop-down list. You will see later how we handle the case when the user selects this item.
Then we open our connection to the database and execute our new stored procedure (via the ExecuteReader command).
The while loop shows how to read through the data returned by the stored procedure and add each string to the drop-down list of types.
Note that GetString(0) gives us the first field from the row of data returned by our stored procedure. In our case there was only 1 column, 1 field, namely the type field, but in other situations you may have a stored procedure that returns several fields. You choose the one(s) you want by using an index of 0, 1, 2, etc.
The rest of the code in the case where there is no postback is pretty much what we have used before.
In the else section we handle the case when we do have a postback. Recall that a postback happens if the user clicks the button on the form.
It is here that we would not have any data in our text boxes and drop-down list if we had failed above to set EnableViewState to True for the text boxes and drop down list. The data would have been gone after the postback happened.
The code here is much like what we have used before. After validation, we extract the values that the user placed in the text boxes, store these values (really as key/value pairs) in an HttpSessionState object, and then redirect to the results page. Of course, we have to store one more item: the type chosen from the drop down list.
Save all of your files before going on.

Another Stored Procedure

We need a new stored procedure to select rows of the Parts table based on both a price range and the type of part.
Right click on Stored Procedures and select Add New Stored Procedure.
Change the name of the stored procedure to SelectByTypeAndRange.
We use the same Min and Max parameters as before, but now we need an additional parameter for the type of part. Its type should be nchar(20). (If you have forgotten the types for any of your fields, you can find them under Parts in Server Explorer. Once you click on the desired field, properties should show the type and other information.
We want the stored procedure to look like this:


CREATE PROCEDURE db_datareader.SelectByTypeAndRange
    (
    @Min money,
    @Max money,
    @PartType nchar(20)
    )
AS
    SET NOCOUNT ON
    SELECT     PartNumber, Description, Type, Price
    FROM         Parts
    WHERE     (Type = @PartType) AND (Price <= @Max) AND (Price >= @Min)
    ORDER BY Price
    RETURN

You can either adjust the code in the editor or use Query Builder, as before, to get the SQL part.
Note that the results are sorted in ascending order by price.
If you use Query Builder, type in =@PartType as the condition for the Type field. That is, place this condition in the box for the Type row, column Filter.
See this picture of QueryBuilder to see what you would have at this point.
Right click on your stored procedure and choose Execute. You will be asked to supply values for each of the 3 parameters to the stored procedure. With 20 for the minimum price, 100 for the maximum price, and windshield as the Type, you should get just one row of data returned.
You may now close the editor that is displaying the stored procedure code.
Save all of your files before continuing with the next section.

Adjusting the Code in Results.aspx.vb

Open the Results.aspx.vb code-behind file.
Rewrite the Page_Load procedure so that it looks as follows:


Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    Dim Min, Max, Type As String
    Dim myDatabaseConnection As SqlClient.SqlConnection = Nothing
    Dim myCommand As SqlClient.SqlCommand = Nothing
    Dim myReader As SqlClient.SqlDataReader = Nothing

    NoDataLabel.Visible = False

    If Session.Count = 0 Then   ' No session items were supplied.
        NoDataLabel.Visible = True
    Else
        Min = Session("MinKey")
        Max = Session("MaxKey")
        Type = Session("TypeSelected")
        Try
            Dim connectionString As String = _
                ConfigurationManager.ConnectionStrings("PartsConnectionString").ConnectionString
            myDatabaseConnection = New SqlClient.SqlConnection(connectionString)
            myCommand = New SqlClient.SqlCommand()

            'Set up to use a stored procedure:
            myCommand.CommandType = CommandType.StoredProcedure
            myCommand.Connection = myDatabaseConnection
            myCommand.Parameters.AddWithValue("@Min", Min)
            myCommand.Parameters.AddWithValue("@Max", Max)

            If (Type.Equals("*** All Types ***")) Then
                myCommand.CommandText = "SelectByRange"
            Else
                myCommand.CommandText = "SelectByTypeAndRange"
                myCommand.Parameters.AddWithValue("@PartType", Type)
            End If

            'Use an SqlDataReader to execute the stored procedure and
            'get the results into the GridView:
            myDatabaseConnection.Open()
            myReader = myCommand.ExecuteReader()
            If myReader.HasRows Then
                PartsGridView.Visible = True
                PartsGridView.DataSource = myReader
                PartsGridView.DataBind()
            Else
                NoDataLabel.Visible = True
            End If
            myDatabaseConnection.Close()
            myDatabaseConnection.Dispose()
        Catch exception As System.Data.SqlClient.SqlException
            DatabaseErrorLabel.Visible = True
        Catch exception As Exception
            DatabaseErrorLabel.Visible = True
        Finally    'Do cleanup tasks here:
            myCommand = Nothing

            If Not myReader Is Nothing AndAlso _
                Not myReader.IsClosed Then
                myReader.Close()
            End If

            myReader = Nothing

            If Not myDatabaseConnection Is Nothing AndAlso _
                myDatabaseConnection.State = ConnectionState.Open Then
                myDatabaseConnection.Close()
                myDatabaseConnection.Dispose()
            End If

            myDatabaseConnection = Nothing
        End Try
    End If
End Sub

If there is no session data (which is where we placed the price range and parts type information) then we cannot possibly do a lookup. We just display an error message.
Otherwise, we proceed much like before.
What is new is that we check to see if the parts type that was selected was "*** All Types ***" or not.
In the first case, we use the old SelectByRange stored procedure.
In the second case, we use the SelectByTypeAndRange stored procedure since a specific type was given.
If ExecuteReader results in no rows of data, then we simply display an error message.
Otherwise, we display our data in the GridView as we have done in several of the previous web apps.
Checking for exceptions and closing the database connection are also done as before.

Testing Your Web Application

Save all of your files and build the project as usual.
Use Debug, Start Without Debugging to try your web app locally.
Test the various ways in which the user can select data to be sure that the application works in all cases. In particular, try both selecting a particular type of part and selecting all parts, as those cases use different stored procedures.
This screen shot shows muffler being selected as the product type.

corresponding output is incorrect

For all other part types, the app worked. Note that a Temp label was placed on the Results page and used to display the type of part selected. This was for debugging purposes. For some reason, when muffler was selected, the Results page incorrectly got "*** All Types ***" instead.
After publishing the web app and trying it on a live web server, it was discovered that the app worked fine in all cases. Here is a picture of the screen with the user selecting type muffler and here is the resulting products page, with the correct data.
Perhaps there is a bug in how the local web server handles items selected from the drop down list.

Back to the main page for ASP .NET Web Apps

Author: Br. David Carlson
Last updated: October 15, 2008
Disclaimer


Computing & Information Systems Department		Search CIS Site Tutorials