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 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 user input and then use it to query an SQL Server database, with the results then 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 button is clicked, the web app shows the selected auto parts data.

Starting the Web App

  • As usual, make a new project of type Visual Basic Project and having template ASP.NET Web Application. In the location box put in a reasonable location such as:
  • We will assume from here on that the folder name used is StoreInput.
  • Change the name of the web form, WebForm1.aspx, to home.aspx.
  • From the Web Forms section of the Toolbox, add the 5 labels for the top of your web form as shown in the initial view of the application.
  • Save your work so far by using File, Save All.

User Input

  • Add a text box, with ID MinTextBox, just below the label for the minimum. Resize it as needed.
  • To quickly add a second text box, just click on the first one. Then do Edit, Copy and Edit, Paste. Drag the new one just below the maximum label and set the ID for the text box to MaxTextBox.
  • 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 some automatic checks against these script attacks, but it is still best to explicitly check to see that the data is reasonable yourself.
  • 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).
  • From the Web Forms 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.
  • Here is an image of what we have thus far.
  • 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. If you wish to try this, here are the directions:
    • 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.
  • Add a button from the Web Forms section of the Toolbox. Place this button to the right of the text boxes, changes its ID to MatchesButton, and change its Text to "List Matches".
  • 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

  • Drag a label onto the form just below everything we have so far and rename it as PartsLabel. The text for it should be "List of Selected Parts". Set its visible property to false. We will make it visible when we display the data on the form.
  • The data will be looked up in the same Parts database that we used in the previous web app.
  • Drag a DataGrid from the WebForms section of the Toolbox to the form. Place it just below the above label and resize it to fill most of the bottom half of the screen. We will use this data grid to hold the data that we look up.
  • While the data grid is selected use AutoFormat under Properties. Select Colorful1.
  • Also drag another label onto the form, between the button and the DataGrid, and change its ID to ErrorLabel. Set the Text to "Error in accessing database", change the foreground color to red, and put False in the Visible field. We will make this label visible in the code if an exception is detected.
  • Check your work with this image of the web form. (This image also contains a few items at the bottom that we add in the next few steps, so do not worry about these.)
  • Drag a DataView from the Data section of the Toolbox and place it at the bottom of your web form.
  • Then click on the data grid on your form and change the DataSource field to DataView1. That is, the data grid will get its data from the data view, just as in the previous app.
  • Next, drag an SqlDataAdapter from the Data section of the Toolbox to the form. This will start up a wizard.
  • Select the same data connection that you used in the previous web app. This is the one that connects to your Parts database. If for some reason that connection does not appear, create a new one by following the New Connection directions for the previous web app.
  • Click Next.
  • Select Create New Stored Procedures. Remember that we use stored procedures, not regular SQL, for security reasons.
  • Click Next.
  • Select Query Builder.
  • Select the Parts table. Then click on Add and Close. This is the step where you select what table(s) to use in your database query.
  • As in the previous app, check all 4 database fields (not the all columns entry) in the top half of the window.
  • Click on the field in the Sort Type column and the Price row. Use the pulldown to select Ascending. This specifies that the results will be sorted in ascending order by price.
  • Next we need to select the data that fits the correct range. Go to the field in the Criteria column, Price row and enter <=@Max. The @ sign indicates a parameter to the stored procedure. You will see later in the Visual Basic code how a value is given to a parameter in a stored procedure.
  • Click in the Column column, one row beneath that Price row. Use the pulldown to select Price. Go to the field in the Criteria column, same row and enter >=@Min. Notice that we have a second parameter here.
  • Double check that all the rows except the last Price row have a check in the Output column. This specifies what fields of the table will be returned as output. This should be OK but check to be sure.
  • You can see the final settings in this picture of Query Builder. If you are familiar with SQL, you can check that the SQL in the bottom part of Query Builder looks to be correct.
  • Select OK.
  • Then select Advanced Options.
  • Uncheck the first box about generating insert, update, and delete statements.
  • Click OK and Next.
  • In the Select box on the Configuration Wizard's dialog, fill in SelectByRange as the name of your stored procedure.
  • Also be sure that the radio button is selected to have the stored procedure created in the database for you.
  • Click on Next and then on Finish.
  • You also need to give the SQL or IWAM user execute permission on your stored procedure.
    • As in our first database-related web app, which database user to give execute access to the stored procedure depends on how your server is configured. Consult your database administrator if need be.
    • In Enterprise Manager, click on the Stored Procedures icon under your particular database.
    • Find your SelectByRange stored procedure in the right pane and right click on it.
    • Select Properties, then Permissions.
    • Check the Exec box for the SQL or IWAM user as needed.
  • In Visual Studio we probably need to adjust the Select Command.
    • Click on the DataAdapter1 icon at the bottom of your form.
    • In the Properties window for this data adapter, expand the SelectCommand item.
    • Note that the CommandType is listed as StoredProcedure.
    • Change the CommandText entry to yourid.SelectByRange where you replace yourid by studentc or whatever your user ID is, the user ID that you were logged in as when you created your stored procedure.
    • Visual Studio will change the entry to look like yourid.[SelectByRange], but do not type it in that way yourself. Let it handle this change.
    • Respond Yes when asked if you want to regenerate the parameters collection for this command.
    • Say Yes to the next question that warns that information may be lost and asks whether to apply the new parameter configuration.
  • You should now check to see if you can indeed get your data from the database.
    • Click DataAdapter1 at the bottom of your form.
    • Then in the Property window, click on the Preview Data link.
    • Note that information about the 2 parameters is shown at the top right. In the Value column fill in 10 for the @Min parameter and 50 for @Max.
    • Click on the Fill Dataset button.
    • You should then see your data as shown in this picture of the preview.
    • If this does not work, recheck your work on the stored procedure. You can even delete the data adapter from your form and create a new one.
  • Do File, Save All in Visual Studio before going on.
  • An alternate way to check your stored procedure is available with SQL Server Enterprise Manager.
    • In Enterprise Manager, select Tools and then SQL Query Analyzer.
    • In the Object Browser, find your database and your particular stored procedure.
    • Right click on your stored procedure, SelectByRange, and select Debug.
    • In the Debug Procedure dialog box, fill in reasonable values for the input parameters (such as 100 for @Max and 12 for @Min.)
    • Here is a picture of Debug Procedure dialog box.
    • Click on Execute.
    • Resize the output window for this debugger and resize the components within it as need be so that everything is visible.
    • Examine this picture of the results.
    • This debugger has additional features not covered here.

The Code-Behind File

  • In Solution Explorer double click on home.aspx.vb, the "code-behind" file for your web form, to bring it up in the editor. This is where we add the code for your web page.
  • Unlike in the previous app, we are going to try omitting the various Imports commands. This may make a few lines of code longer, as we will have to specify where SqlException is found. In fact, that is probably the only place it would make a difference, so it seems reasonable to leave out the Imports commands.
  • Click on the + to show the code inside of the "Web Form Designer Generated Code" region.
  • Find the section of code that sets up your labels and other items as protected fields.
  • Add to the bottom of this list the following line of code so as to create a data table to use:
    Protected dTable As New DataTable
  • Recall that a DataTable can hold within your program the data from an actual database table.
  • 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 this button.
  • Thus, we just write code to handle the postback in the Page_Load procedure.
  • Make your Page_Load procedure match the following:

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles MyBase.Load
    'Put user code to initialize the page here
    'If there is no postback (e.g. the first time the page loads), nothing
    'special needs to be done.  But...
    Dim Min, Max As String

    If IsPostBack Then
        'Page was reloaded, for example, when user clicked the button.
        Page.Validate()  ' Check all validators to see if user input is valid.
        If Page.IsValid Then
            MatchesButton.Visible = False
            PartsLabel.Visible = True
            DataGrid1.Visible = True
            Min = MinTextBox.Text.ToString()
            Max = MaxTextBox.Text.ToString()
                SqlDataAdapter1.SelectCommand.Parameters("@Min").Value = Min
                SqlDataAdapter1.SelectCommand.Parameters("@Max").Value = Max
                DataView1 = New DataView(dTable)
            Catch exception As System.Data.SqlClient.SqlException
                ErrorLabel.Visible = True
            Catch exception As Exception
                ErrorLabel.Visible = True
            End Try
        End If
    End If
End Sub

  • Next, let's analyze the code. It begins by declaring 2 local string variables Min and Max.
  • If a postback has occurred, we first 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 Text field (error message) 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.
    • As a first small step, we make the button invisible since the user should not be clicking on it if we have already displayed the data.
    • However, we do make the PartsLabel and DataGrid1 to be visible as we do want the user to see these.
    • Since this web app has user input, we next get that user data out of the text boxes and into the variable 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 open the connection to the database.
    • Before using the data adapter to retrieve data from the database, we must fill in the parameter values for the data adapter to use when it calls upon the SelectByRange stored procedure that we set up in the database. Note the use of the @Min and @Max names that we used when we created the stored procedure.
    • Then we use the data adapter (and the stored procedure) to get the data from the database and place it into the data table here inside our program.
    • The data view then gets its data from the data table.
    • Then the data grid is bound with the data from the data view.
    • If all works OK, the last step in the procedure is to close the connection to the database.
    • 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.
  • 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.

Build Your App

  • Save all of your files in Visual Studio.
  • Then use Build, Build StoreInput (or whatever you named your application).
  • If the build succeeded, the Output window will give you a message to that effect.
  • If not, fix any problems.

Testing and Debugging The App

Finding Errors

  • Look at your app in a browser by going to the appropriate URL. For example, the URL might be something like one of the following:
  • Fill in reasonable minimum and maximum values (such as 5 and 50) and click on the List Matches button to see what you get.
  • If it does not work, you might try setting 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 edit the line that says <customErrors mode="RemoteOnly" /> so that it instead reads as <customErrors mode="Off" />
  • Save everything, rebuild your application, and refresh the initial page in your browser.
  • See if you get an error message now when you click on the button.
  • Often it is a matter of getting the permissions right on the stored procedure if you get an error at this point. Note that the stored procedure may work fine when you preview the data in Visual Studio, yet not work when called from your web app. This is because the stored procedure is run by one user (such as SQL or IWAM) when the web app calls it, but is no doubt run by a different user when called from Visual Studio. Consult your system admin or another .NET web developer if you cannot get rid of an error at this point.
  • When finished with debugging, undo all of your changes to Web.config, 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.

Back to the main page for ASP .NET Web Apps

Author: Br. David Carlson
Last updated: November 02, 2015