CIS Logo SVC Logo

   Computing & Information Systems


Schoology Facebook        Search CIS Site      Tutorials

A Database/Web App with User Input

The Goals for this Example

It would be nice to add user input to our previous car parts web app. Many web applications let the user supply input that is used to select what data to display. Thus, we need to learn how to get this input and then use it to query an SQL Server database, and have the results displayed on the web form.

Creating the Example

What the App Should Do

  • Like the previous web app, this one will display auto parts data on a web form for a fictitious store.
  • However, this time the app has the user enter a minimum and maximum price for the range of prices that the person wishes to have displayed. Thus this application displays selected data items, instead of always giving all of the data.
  • We will also have the web app validate the user input.
  • The initial view of the application shows a web page with a few labels and a button.
  • Once the minimum and maximum price are supplied and the button is clicked, the web app shows the desired auto parts data.

Starting the Web App

  • Use MyComputer to copy the folder containing your previous web app. Save the copy in a folder name StoreInput, in a convenient location (such as your M drive).
  • If you wish, change the name of the web form, Default.aspx, to home.aspx.
  • Save your work so far by using File, Save All.

User Input

  • First, we adjust the controls on this form so that they fit this screen shot.
  • Note that the label above the GridView has been adjusted to say "List of Selected Parts". Check that its ID is PartsLabel and that its Visible property is set to False.
  • The button now says "List Matches". Make sure that it's ID is MatchesButton.
  • Add a text box, with ID MinTextBox, just below the label with text "Minimum price:".
  • Copy this text box and label, placing the new ones to the right of the first ones. Give the text box the ID MaxTextBox and use "Maximum price:" for the label's text.
  • Whenever we have user input in a web app, it is very important to validate that input and to do so at the server end. That's because anyone on the Internet can attack your web app by supplying malicious input. If you don't verify that the input is reasonable, an attacker might be able to supply input that gets data from your database that the attacker should not see. Worse yet, the attacker might be able to change the data in your database. Even in applications, such as guestbooks, where you want users to post data, you need to validate the data to be sure that an attacker is not able to save some nasty script to your database, a script that would get run for some other poor user who views what is in the guestbook. (This is called a cross-site scripting attack.) Visual Studio says that it provides automatic checks against these script attacks, but it is still best to explicitly check to see that the data is reasonable yourself as new attacks are found all the time.
  • Although checking user data at the client end (at the user's browser) is often done to assist the user in filling in reasonable data, it is no good as a protection against an attacker. That is because the attacker has complete control over his/her computer and can totally bypass any JavaScript of similar code user to check the data at the browser. Thus we will use validators, as we did in an earlier app, since they always run on the server end (though they can in addition check at the client end if the EnableClientScript property is set to True for each validator -- which they are by default).
  • We now add validators to our web form so that it looks like this screen shot.
  • From the Validation section of the Toolbox, drag a RequiredFieldValidator onto your form, just below the text box for the minimum. Change the ErrorMessage field to "Min needed" and the ControlToValidate to MinTextBox. We thus force the user to enter a minimum, even if it is only 0.
  • Copy this validator and place it underneath the text box for the maximum. Change the ErrorMessage to "Max needed" and the ControlToValidate to MaxTextBox.
  • These RequiredFieldValidators probably work best if the user's browser has JavaScript enabled when viewing your web app. That way they can quickly check at the client end and warn the user if one of the fields is empty. If JavaScript is turned off, then the empty strings in these boxes will be sent back to the server. The round-trip to the server and back slows down the display of the validator error messages.
  • Drag a RangeValidator onto your form just underneath the "MinNeeded" validator. Change the ErrorMessage to "Use a number from 0 to 50000" and set the ControlToValidate to MinTextBox. Set the MinimumValue to 0.0 and the MaximumValue to 50000.0 so as to define the acceptable range of numbers. Set the Type field to Currency. (If you do not do this, the type will be String. Since we want a range of numbers, not strings, you will get unexpected results if you leave the Type field at String.) The user input will be converted (if possible) into a currency value and then only values that fit the specified range will be accepted. The range validator is our main way of filtering out malicious input in this application.
  • Copy the last validator and drag the copy to just underneath the "MaxNeeded" validator. Change the ControlToValidate to MaxTextBox but keep everything else the same.
  • More is needed to do complete filtering of bad input as explained in this quote from the Visual Studio help system: If the input control is empty, no validation functions are called and validation succeeds. Use a RequiredFieldValidator control to prevent the user from skipping an input control. Similarly, if the value of the input control cannot be converted to the data type specified by the Type property, validation also succeeds. It is strongly recommended that an additional CompareValidator control, with its Operator property set to ValidationCompareOperator.DataTypeCheck, be used to verify the data type of the input value. Thus, do the following:
    • Drag a CompareValidator onto your form and place it near the MinTextBox.
    • In this validator's properties, set the ControlToValidate (not the ControlToCompare) field to MinTextBox.
    • Change the ErrorMessage field to "Must use currency".
    • In the Operator field select DataTypeCheck.
    • In the Type field select Currency.
    • Copy this CompareValidator, place it near MaxTextBox, and change the ControlToValidate to MaxTextBox.
  • It would be best to do a File, Save All at this point.


  • Since security was mentioned in the section above on user input, let's expand on some of the security issues in web applications here.
  • Many of the typical attacks directed against web apps involve malicious input (as mentioned above). This could be a script, enclosed in script tags and entered into a text box, or perhaps it could be an SQL command appended to expected input in a text box. (The former is a cross-site scripting attack, while the latter would be an attempt at an SQL injection attack.)
  • The usual solution to the above problems is to filter user input at the server.
  • Here are some relevant quotes from the Visual Studio help system:
    • By default, the Web Forms page automatically validates that malicious users are not attempting to send script to your application. So, there is built-in protection from the script attacks. However, one should still further filter user input.
    • Validation controls perform input checking in server code. When the user submits a form to the server, the validation controls are invoked to review the user's input, control by control.
    • If the user is working with a browser that supports DHTML, the validation controls can also perform validation using client script. This can substantially improve response time in the page; errors are detected immediately and error messages are displayed as soon as the user leaves the control containing the error. Check that the EnableClientScript property on your validators is true if you want client-side script to run (in addition to the server-side code for the validators).
    • The page framework performs validation on the server even if the validation controls have already performed it on the client, so that you can test for validity within your server-based event handlers. In addition, it helps prevent users from being able to bypass validation by disabling or changing the client script.
    • Validation occurs after page initialization (that is, after view-state and postback data have been processed) but before Change or Click event handlers are called. You can perform the validation procedure earlier by calling the control's Validate method, for example during page load.
  • For further information on web app security, use Help, Search to look for the following topics in Visual Studio:
    • Scripting Exploits
    • Protecting Against Script Exploits in a Web Application
    • Overview of Web Application Security Threats
    • Introduction to Web Application Security
    • ASP.NET Web Application Security
    • How ASP.NET Security Works
    • Basic Security Practices for Web Applications

Getting Data from the Database

  • The data will be looked up in the same Parts database that we used in the previous web app.
  • Check that the GridView has ID PartsGridView, is formatted as you wish, and has its Visible property set to False. We will make it visible when we are ready to display the data.
  • Also check that the error label has an ID of ErrorLabel and is not visible.
  • We need to make a new stored procedure. Begin by going to Server Explorer and expanding the data connection used in the last couple of web apps. If you cannot find this data connection, make a new one as in this old web app.
  • Right click on Stored Procedures under this data connection. Select Add New Stored Procedure.
  • This will give you an outline of a stored procedure. This time, do not delete the parameters.
  • Change the parameters section so that it reads as follows:

@Min money,
@Max money

  • Note that type money is the SQL Server equivalent of currency in VB .NET and that the @ sign is used to indicate a parameter to a stored procedure.
  • We do not use the term OUTPUT on either parameter, so these parameters are input parameters. That is, they are used to send data into the stored procedure. In particular, we plan to send in the minimum price and maximum price that the user supplies when running our web app.
  • Place the mouse at the start of the line that says RETURN. Press Enter to get some room to add code. Right click at this spot and select Add SQL. This will bring up a query builder where you can create the SQL that should get compiled into your stored procedure.
  • Add the Parts table to the query builder.
  • Check each of the 4 fields so that they will be selected by the SQL.
  • Also click in the Sort Type column for the row (field) named Price. In the drop-down choose Ascending so that the data will be presented in ascending order by Price.
  • In this same row, the one for Price, find the entry for the Filter column and click there. Type in <=@Max. This is used to say that we only want to select those rows of the Parts table with price less or equal to this maximum value.
  • The next row should be empty. Click in the first location, the one for the column labelled Column. Use the pull-down to select Price. This gives a second row for Price.
  • Do NOT check the entry in the Output column for this row. Doing so would cause the price to be display a second time. However, do click in the entry for this row and column Filter. Type in >=@Min. Now our query is designed to retrieve those rows of the table that have a price greater than the minimum value and at the same time less than the maximum.
  • Click on Execute Query to test the SQL. You will be asked to supply numbers for @Max and @Min. The resulting data set will be shown at the bottom of the window.
  • Click OK. Change the name StoredProcedure1 to SelectByRange.
  • Make sure that the code now reads as in the following listing. If not, edit it manually to make it match this. Be very sure that the RETURN is the last line of code. If you do RETURN earlier on, the last part of the stored procedure will never be executed.

CREATE PROCEDURE db_datareader.SelectByRange 
    @Min money,
    @Max money
    SELECT     PartNumber, Description, Type, Price
    FROM         Parts
    WHERE     (Price <= @Max) AND (Price >= @Min)
    ORDER BY Price

  • Now try out your stored procedure:
    • This stored procedure should now show up under Stored Procedures for this data connection in Server Explorer. Right click it and select Execute. You will be asked to supply values for the minimum and maximum price. Using reasonable numbers will show you the data returned by the stored procedure.
    • To be more accurate, the code that we wrote above is the code to create a stored procedure that contains the desired SQL. After you execute it once, note that the word CREATE changes to the word ALTER. That allows you to adjust the SQL, select Execute again, and get the SelectByRange stored procedure altered.
  • Do File, Save All in Visual Studio before going on.

The Code-Behind File

  • In Solution Explorer double click on your form's aspx.vb file, the "code-behind" file for your web form, to bring it up in the editor. This is where we add the VB code for your web page.
  • We will not add a click handler for the button on your form. Instead, we will again use the fact that a postback happens whenever the user click on the button.
  • Thus, we just write code to handle the postback in the Page_Load procedure.
  • Make your Page_Load procedure match the following:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    'If there is no postback (e.g. the first time the page loads), nothing
    'special needs to be done.  But...
    If IsPostBack Then
        Dim Min, Max As String
        Dim myDatabaseConnection As SqlClient.SqlConnection = Nothing
        Dim myCommand As SqlClient.SqlCommand = Nothing
        Dim myReader As SqlClient.SqlDataReader = Nothing

        ' Check all validators to see if user input is valid.
        If Page.IsValid Then
            Min = MinTextBox.Text.ToString()
            Max = MaxTextBox.Text.ToString()

                ErrorLabel.Visible = False
                MatchesButton.Visible = False
                PartsLabel.Visible = True
                Dim connectionString As String = _
                myDatabaseConnection = New SqlClient.SqlConnection(connectionString)
                myCommand = New SqlClient.SqlCommand()

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

                'Use an SqlDataReader to execute the stored procedure and
                'get the results into the GridView:
                myReader = myCommand.ExecuteReader()
                PartsGridView.DataSource = myReader
                PartsGridView.Visible = True
            Catch exception As System.Data.SqlClient.SqlException
                ErrorLabel.Visible = True
            Catch exception As Exception
                ErrorLabel.Visible = True
            Finally    'Do cleanup tasks here:
                myCommand = Nothing

                If Not myReader Is Nothing AndAlso _
                    Not myReader.IsClosed Then
                End If

                myReader = Nothing

                If Not myDatabaseConnection Is Nothing AndAlso _
                    myDatabaseConnection.State = ConnectionState.Open Then
                End If

                myDatabaseConnection = Nothing
            End Try
        End If
    End If
End Sub

  • Next, let's talk our way through the code.
  • The Dim section sets up 2 local string variables Min and Max, as well as empty SqlConnection, SqlCommand, and SqlDataReader objects.
  • We use Page.Validate() to cause all of the validators on this page to run (at the server end). This is in addition to any validation already done at the client end. The explicit call to Validate() is needed because the validators normally only run at the server after Page_Load has been executed and before any click handlers are run. We want to validate the user input here before we try to use it as parameters in a query of the database. If Page.IsValid is True, we go on to process the user input. If not, we do nothing, since whatever validator(s) found a problem with user input will display their error message(s) on the page.
  • Assuming that the user input validated fine, we want to read the desired data from the database and display it in the data grid on the web form.
    • Since this web app has user input, we get that user data out of the text boxes and into the variables Min and Max. The code just uses the Text property of each box and then converts the number to a string by using the ToString function.
    • As in the previous web app, a try..catch construct is used when accessing the database so that we can catch an exception if one occurs.
    • Inside this construct we first adjust the visibility of items on the form so that the button and error label cannot be seen, but the PartsLabel can.
    • Next, we set up the connection string and a new database connection object based on that string. If your connection string in the web.config file is not named PartsConnectionString, then change the VB code to use whatever name it has in web.config.
    • Then we create a new SqlCommand object to hold the information about the stored procedure that we wish to run.
    • Its CommandType is set to StoredProcedure, it uses the connection object we just created, and CommandText holds the name of the particular stored procedure we wish to use.
    • However, since this stored procedure requires 2 input parameters, we also need to supply those in the SqlCommand object. You can see how AddWithValue is used to give the value of the variable Max to the parameter @Max. Similarly, the value of Min is given to the @Min parameter.
    • Unlike in the previous web app, we will not use a DataAdapter to read the data (sent back from the stored procedure) into a DataSet. This DataSet would be an in-memory copy of the returned data and could, in many applications, be rather large. Thus a DataSet could waste a lot of space and give slower than necessary access to data.
    • The preferred method of getting at the data is to use a DataReader. The reader can read through the data one item at a time, without necessarily saving it all in memory at once. Recall that we set up an empty DataReader object named myReader in the Dim section.
    • Thus we open the database connection and call ExecuteReader on our SqlConnection object.
    • We then use myReader as the data source for our GridView and bind the data to the GridView.
    • We are now finished with the database connection and so close it right away. In addition, we displose of the connection object and make the GridView visible on the form.
    • If an SqlException occurs, we make the error label visible in order to warn the user. This is the most likely exception and typically occurs when the connection to the database cannot be opened for some reason.
    • If there is no SqlException, we also catch any other kind of exception (since type Exception matches any type of exception) and make the error label visible in this case as well. It seems unlikely that any exception but an SqlException would occur in this code, but we have included the extra Catch just in case. Of course, since we check for type Exception, we could remove the Catch for SqlException. However, we leave it here in case we might want to have different code executed for the different types of exceptions. For the SqlException we could display an error message saying that there was a database error, whereas for any other exception we could display a more generic message saying just that there was some kind of error.
    • The cleanup code is put in the Finally section of the try..catch construct. This is similar to what was used in the last web app. The new part is the section that makes sure that the DataReader is closed and that it is an empty object.
  • Note that the code is essentially the same as the code in the previous web application, except that we now have to deal with the parameters for the stored procedure and we have a little more cleanup to do in the Finally section.

Build Your App

  • Save all of your files in Visual Studio.
  • Then build your application.
  • If the build succeeded, run your application locally by using Debug, Start Without Debugging.
  • If not, fix any problems.

Testing and Debugging The App

Finding Errors

  • Fill in reasonable minimum and maximum values (such as 10 and 500) and click on the List Matches button to see what you get. Here is a picture of the expected results.
  • If it does not work, you might need to set the Web.config file so that you can see any error messages, as we have done in previous web apps. Double click the Web.config file in Solution Explorer and add the line <customErrors mode="Off" />
  • It would be best to test what your web app does with bad data:
    • Try clicking on the button without putting anything into the 2 text boxes. The required field validators should then show their error messages.
    • Try putting bad data such as a string into one of the text boxes. The other validators should handle this case.
    • Also try a number bigger than the largest we allowed, which was 50,000. The range validators should catch this.
    • Now, since our validators have the EnableClientScript property set to True, the above bad input is caught by JavaScript running in the user's browser. If you want to check that these errors also get caught, if need be, on the server end (which is important for security), you would need to temporarily disable JavaScript in your browser. If you then give bad input like above, you should see that the error messages only show after you click on the button (so that the data gets sent to the server) and that the messages show up more slowly due to the round trip to the server and back.
  • You might want to use the Debugger. Here are the basics of using it:
    • Find a reasonable spot to set a breakpoint. For example, in the above VB code, you could click on the line that opens the database connection. Then use Debug, Toggle Breakpoint to set a breakpoint there. You can set several breakpoints if you wish.
    • Use Debug, Start Debugging to start running your web app locally with the debugger.
    • Then use Debug, Windows to see what windows of information are available to you. Some of the most useful ones include Locals, Autos, Call Stack, and Watch. Perhaps turn all of these on to see what you get. Note that when you create a new watch window, it lets you enter the names of the variables that you wish to examine.
    • In the current web app, you would next have to supply the minimum and maximum price and click on the List Matches button in the browser window that Start Debugging started up.
    • The application will then run, if possible, to the first breakpoint. You can then examine the values of the variables in the various Debug windows and check the call stack to see what called what.
    • Use Debug, Step Over to have the debugger move to the next instruction. If you want to follow a procedure call into the procedure code, use Debug, Step Into. There is also a Step Out to get out of a procedure and a Continue to run to the next breakpoint (or the end of the program). There are also buttons on the toolbar for each of these operations.
    • You can always abort running the debugger with Debug, Stop Debugging.
  • When finished with debugging, undo the customErrors change to Web.config, if you used this, save everything, and rebuild your app. This will leave your app in a more secure state. If a runtime error crops up later, you don't want everyone on the Internet to be able to read the detailed error messages.
  • If desired, publish your web app to a live web server and then try that version in a browser. Remember that the web server administrator needs to convert your published app into an official IIS web app for it to work. Then you will get results like this.

Back to the main page for ASP .NET Web Apps

Author: Br. David Carlson
Last updated: November 01, 2008