Using User Input to Get Matching Data From a Database
The Goals for this Example
The overall goal is 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.
Our revised application has the user enter a minimum and maximum price. When the button is then clicked,
all the data that fits this price range is displayed in a grid view.
Another goal is to use validation controls to validate this user input, both to keep this input to reasonable data and to
try to prevent hacking of our web site by means of malicious input. This application will again use the Entity Framework and LINQ.
Getting Started
Preliminaries
- Thus we wish to create a web form with user input so that when the user clicks the button
all items matching the specified price range are shown on the same page after the postback as seen in
this picture of the running app.
The data will come from a new table (of bicycle parts) that we will create.
- Open your usual solution in Visual Studio, the solution that contains the web site that you have
been working on in these examples. However, if you wish, you may create a new web site/solution instead.
If you make a new one, be sure to add the App_Code folder to your web site by right clicking the site in Solution Explorer, etc.
The directions here assume that you use your existing web site/solution.
- In Server Explorer, open your web.config file and be sure that you have a webapp connection string, as created in
this prior example.
This is the connection string that allows you to connect to the database using SQL Server authentication. That is,
it passes the username and password to log into SQL Server over the network so as to make the connection.
If need be, copy in that connection string. Also copy, if not already present,
the connection string for logging into SQL Server using Windows authentication with your Windows account.
You would need that if you want to use Server Explorer in Visual Studio to access your database.
If you created a new web site, you definitely need to copy your connection strings from the old web.config file
and paste them into the correct spot in your new web.config file.
|
Making the Parts Table
- You can create the Parts table of bicycle parts using SQL Server Management Studio or Visual Studio's Server Explorer.
The way to connect to the database and work with it are the same as discussed in
this section of the quick database app which we did earlier.
- Like in that earlier case, create a new table called Parts within your database. Give it the following fields:
|
| Name |
Data Type |
Allow Nulls |
| PartNumber |
int |
not checked |
| Description |
nchar(40) |
not checked |
| Color |
nchar(12) |
checked |
| Price |
money |
not checked |
- Right click at the front of the PartNumber field and make the selection to designate this field
to be the primary key.
- Right click again at the front of the PartNumber field and select Properties. In the Properties window,
expand Identity Specification. Change (IsIdentity) to True.
- Below the design view should be a T-SQL view showing the SQL code that will be run to create your table. It
probably begins with CREATE TABLE [dbo].[Table], but manually change Table to Parts so that
the name of the table gives you an idea of what it contains.
- Click Update in the upper left corner of design view. Then click Update Database.
You should now have a new, but empty table.
- You can now close the window where we set up the fields for this table.
- Next we add data to this table. Begin by right clicking on the table in Server
Explorer. Select Show Table Data.
- In the resulting window you can fill in data for this table. The PartNumber field is an identity field, so its values
will automatically be filled in. For the other fields, enter the data that is shown below:
|
| PartNumber |
Description |
Color |
Price |
| 1 |
seat |
black |
45.98 |
| 2 |
seat |
silver |
45.98 |
| 3 |
tire |
NULL |
38.99 |
| 4 |
tire tube |
NULL |
9.98 |
| 5 |
chain |
NULL |
52.25 |
| 6 |
reflector |
red |
7.98 |
| 7 |
reflector |
clear |
7.98 |
| 8 |
handgrips |
black |
12.67 |
| 9 |
handgrips |
white |
12.67 |
| 10 |
kickstand |
NULL |
20.95 |
| 11 |
rear shifter 7 position |
black |
18.98 |
| 12 |
front shifter 3 position |
black |
13.33 |
| 13 |
seat extra wide |
black |
57.88 |
- You may now close the window where you entered the data. The data is saved automatically.
- You can right click on your table and select Show Table Data at any time that you want to adjust the data.
- Similarly, you can right click on your table and select Open Table Definition if you wish to change
the design of the table. This could be used to add another field, for example.
|
Setting up a New Form
- In Solution Explorer, add a new web form called PartsLookup to your web site, adjusting the title (in Source view) to
"Super Bicycle Parts" or similar.
- On your PartsLookup form, you want to make it look like this image of the form in Design View.
- Every control on the form is set to use absolute positioning.
- There are 4 labels near the top. Make the font blue and adjust the sizes as shown. The topmost label is bold
and extra large. The one below it is bold and medium size, while the next 2 labels are small size and not bold.
- There are 2 textboxes to hold the minimum and maximum price. Change their IDs to TextBoxMin and TextBoxMax so that
you can later tell which is which in the VB code.
- The one button displays the text List Matches in bold and blue.
- Below this are several items in red. These are all validation controls except for the rightmost, which is a label
that says "Error in connecting to database".
- The first validation control is a RequiredFieldValidator. You can drag this onto the form from the Toolbox.
Under the Properties window, change its ID to RequiredFieldValidatorMin.
Similarly, set ControlToValidate to TextBoxMin, so that this control validates that text box.
Set the ErrorMessage property to "Min needed". Set ErrorMessage to be "Min needed".
Also make the foreground color of this (and all the other validation controls) to be red.
- Moving to the right, just under the first text box, the next validation control is a RangeValidator.
Set its ID to RangeValidatorMin, the ControlToValidate to TextBoxMin, the ErrorMessage to "Must be 0 to 800",
MaximumValue to 800, MinimumValue to 0, and Type to Double. The reason to choose Double, not Currency, is because
a double will accept almost any number that the user types. That way the number can be accepted and then compared
to 0 and 800, even if it is something like 12.345, which doesn't fit Currency format. A different validator will
check to see if the user enters something of Currency type.
- Moving further to the right, just under the other text box, is another RangeValidator. Set its ID to
RangeValidatorMax, the ControlToValidate to TextBoxMax, and for the rest, use the same property values as
with the first RangeValidator.
- Moving further down the page, we first find a CompareValidator. We will use it simply to see if the value in the
first text box fits the Currency type. Set the ID of this validator to CompareValidatorMin, ControlToValidate to
TextBoxMin, ErrorMessage to "Must use currency", Operator to DataTypeCheck, and Type to Currency. The last
2 items are what specify that TextBoxMin must hold something that fits Currency type.
- Moving to the right, underneath TextBoxMax and its label and other validators, put another CompareValidator.
Set the ID of this validator to CompareValidatorMax, ControlToValidate to TextBoxMax, and then use the same values
as in the other CompareValidator for the other properties.
- To the right of the last validator is a label. Since you will need to access it in your VB code, set its ID
to LabelError. Make the font medium, bold, and red. The Text should be "Error in connecting to databsae".
Also change the Visible property to False. Your VB code will make this label visible if something keeps the app
from connecting to the database.
- Below all of this is one more label. Since this one will only be made visible when a lookup is carried out,
give it an ID of LabelParts, and set its Visible property to False. Make the font medium, bold, and blue. The
Text should be "Selected parts:". This label is used as heading above the data grid of results.
- Finally, below everything else is a GridView. You can leave its ID as GridView1 since it will be the only
GridView. Drag the right edge of this GridView further to the right so that the GridView extends to be underneath
at least the start of the error label. This is so that there will be plenty of room in the GridView to display
the parts descriptions. Click the smart tag at the upper right corner of the GridView, choose Auto Format,
Classic, and Apply. Do not choose the data source for this GridView as that will be set up in the VB code.
Be sure to set the Visible property of this GridView to False.
- If you have trouble, you can compare your PartsLookup (in Source View) to the following, which shows just the head
and body sections. (Recall that there are a few lines before the head section and the closing html tag after the body.)
In fact, if you are in a hurry, you can copy in the following code so that it replaces the head and body section on your form,
but it is recommended that you take the longer approach shown above so that you learn how to set up the validation controls.
|
<head id="Head1" runat="server">
<title>Super Bicycle Parts</title>
</head>
<body style="height: 544px">
<form id="form1" runat="server">
<div style="height: 532px">
<asp:Label ID="Label1" runat="server" Font-Bold="True" Font-Size="X-Large" ForeColor="#3333FF"
style="z-index: 1; left: 38px; top: 49px; position: absolute" Text="Super Bicycle Parts"></asp:Label>
<asp:Label ID="Label2" runat="server" Font-Bold="False" Font-Size="Medium" ForeColor="#3333FF"
style="z-index: 1; left: 39px; top: 105px; position: absolute"
Text="Specify the price range you wish to consider:"></asp:Label>
<asp:Label ID="Label3" runat="server" Font-Bold="False" Font-Size="Small" ForeColor="#3333FF"
style="z-index: 1; left: 40px; top: 159px; position: absolute" Text="Minimum price:"></asp:Label>
<asp:Label ID="Label4" runat="server" Font-Bold="False" Font-Size="Small" ForeColor="#3333FF"
style="z-index: 1; left: 331px; top: 162px; position: absolute" Text="Maximum price:"></asp:Label>
<asp:TextBox ID="TextBoxMin" runat="server" ForeColor="#3333FF"
style="z-index: 1; left: 140px; top: 158px; position: absolute"></asp:TextBox>
<asp:TextBox ID="TextBoxMax" runat="server" ForeColor="#3333FF"
style="z-index: 1; left: 430px; top: 159px; position: absolute"></asp:TextBox>
<asp:Button ID="Button1" runat="server" Font-Bold="True" ForeColor="Blue"
style="z-index: 1; left: 637px; top: 159px; position: absolute" Text="List Matches" />
<asp:Label ID="LabelParts" runat="server" Font-Bold="False" Font-Size="Medium" ForeColor="#3333FF"
style="z-index: 1; left: 43px; top: 263px; position: absolute" Text="Selected parts:"
Visible="False"></asp:Label>
<asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None"
style="z-index: 1; left: 41px; top: 296px; position: absolute; height: 133px; width: 611px"
Visible="False">
<AlternatingRowStyle BackColor="White" />
<EditRowStyle BackColor="#2461BF" />
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<RowStyle BackColor="#EFF3FB" />
<SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
<SortedAscendingCellStyle BackColor="#F5F7FB" />
<SortedAscendingHeaderStyle BackColor="#6D95E1" />
<SortedDescendingCellStyle BackColor="#E9EBEF" />
<SortedDescendingHeaderStyle BackColor="#4870BE" />
</asp:GridView>
<asp:RequiredFieldValidator ID="RequiredFieldValidatorMin" runat="server" ControlToValidate="TextBoxMin"
ErrorMessage="Min needed" ForeColor="#CC3300" style="z-index: 1; left: 42px; top: 199px;
position: absolute"></asp:RequiredFieldValidator>
<asp:RequiredFieldValidator ID="RequiredFieldValidatorMax" runat="server" ControlToValidate="TextBoxMax"
ErrorMessage="Max needed" ForeColor="#CC3300" style="z-index: 1; left: 335px; top: 199px;
position: absolute">
</asp:RequiredFieldValidator>
<asp:RangeValidator ID="RangeValidatorMin" runat="server" ControlToValidate="TextBoxMin"
ErrorMessage="Must be 0 to 800" ForeColor="#CC3300" MaximumValue="800" MinimumValue="0"
style="z-index: 1; left: 141px; top: 199px; position: absolute; right: 701px" Type="Double">
</asp:RangeValidator>
<asp:RangeValidator ID="RangeValidatorMax" runat="server" ControlToValidate="TextBoxMax"
ErrorMessage="Must be 0 to 800" ForeColor="#CC3300" MaximumValue="800" MinimumValue="0"
style="z-index: 1; left: 434px; top: 199px; position: absolute; right: 408px" Type="Double">
</asp:RangeValidator>
<asp:CompareValidator ID="CompareValidatorMin" runat="server" ControlToValidate="TextBoxMin"
ErrorMessage="Must use currency" ForeColor="#CC3300"
style="z-index: 1; left: 40px; top: 231px; position: absolute"
Operator="DataTypeCheck" Type="Currency"></asp:CompareValidator>
<asp:CompareValidator ID="CompareValidatorMax" runat="server" ControlToValidate="TextBoxMax"
ErrorMessage="Must use currency" ForeColor="#CC3300"
style="z-index: 1; top: 231px; position: absolute; left: 336px"
Operator="DataTypeCheck" Type="Currency"></asp:CompareValidator>
<asp:Label ID="LabelError" runat="server" Font-Bold="False" Font-Size="Medium" ForeColor="#CC3300"
style="z-index: 1; left: 566px; top: 231px; position: absolute" Text="Error in connecting to database"
Visible="False"></asp:Label>
</div>
</form>
</body>
|
Getting the Data from the Database
Entity Framework
- IMPORTANT: If you have your web.config file open in Visual Studio, close it. In the following steps Visual Studio
will try to store another type of connection string, referred to as "entity connection settings" in
your web.config file. You must not have your web.config file open for this to succeed.
- Right click the App_Code folder in Solution Explorer. Select Add, ADO.NET Entity Data
Model. When asked to name the model, call it PartsModel and click OK.
- At the next screen you are asked what the model should contain. Select "Generate from database"
or "EF Designer from Database" (depending on your version of Visual Studio) and click Next.
- The next window has you choose your connection to the database. In the pull-down box, choose your webappConnectionString (or
whatever you named the connnection that uses SQL Server authentication in order to connect to your database.
Select the radio button labeled "Yes, include the sensitive data in the connection string".
Also choose to save the entity connection settings. The default name of carlsondEntities (or whatever username appended
to Entitites) should be fine. Then click Next.
- The next window is for choosing your database objects and settings. Keep expanding the tree starting at the Tables node
until you find your Parts table (or whatever you named it). Put a check in front of that table.
Leave all 3 check boxes at the bottom checked. In the
box at the bottom, use carlsondModel (your username appended with Model) for this model namespace. Then click Finish.
- You will now probably be asked whether it is OK to run certain code, maybe with a mention that this could be dangerous.
However, this is exactly what you need to do. Check the box to tell it not to show this message again and then select OK.
- You might also be asked whether to reload certain files that got modified outside of the environment. Select Yes to All.
- Wait until Visual Studio completes its work. You should see the Entity Designer displaying
a drawing of the class that was created based on the Parts table that you selected in your database.
- Be sure to save and close this diagram.
|
LINQ
- Next, we turn to writing some LINQ (language integrated query) code in our VB code-behind file,
along with some other event-handler code.
- Open your PartsLookup.aspx form in Design view if it is not already opened.
Click toward the bottom of your form until you see the little tag saying body at the top of the form.
If your verion of Visual Studio shows the body tag and others on tabs along a horizontal row at the bottom,
you should be able to click on the tab labelled body.
Now that the right portion of the page (the body) is selected, double click this highlighted area.
Do NOT double click the button on the form as we are not going to use a button click handler in this project.
You will get an outline of the handler for the Page_Load event in the PartsLookup.aspx.vb
code-behind file. Place the following code within this function, adjusting the names if you choose different names.
See this picture of the approximate end result.
|
'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
LabelError.Visible = False
Button1.Visible = False
LabelParts.Visible = True
Page.Validate() ' Check all validators to see if user input is valid.
If Page.IsValid Then
Min = TextBoxMin.Text
Max = TextBoxMax.Text
Try
Using pEntities As New carlsondEntities()
Dim parts = From Part In pEntities.Parts
Select Part Where (Part.Price >= Min) And (Part.Price <= Max)
GridView1.DataSource = parts.ToList()
GridView1.DataBind()
End Using
GridView1.Visible = True
Catch exception As Exception
LabelError.Visible = True
End Try
End If
End If
|
- Turning the visibility of items off and on should be easy to follow in the above code.
- Similarly, using Page.Validate() to check if all of the validators accepted their data is simple,
as is extrating the strings that the user placed in the 2 text boxes.
- A Try...Catch construct is then used so that if anything goes wrong in getting the data from the database and into the
GridView, LabelError is made visible, giving a generic error about connecting to the database.
- The Using statement sets up a variable (an instance) named pEntities that uses carlsondEntities
(or whatever name you chose above for the connection settings for the Entity Framework to access the
database via the webappConnectionString). This pEntities instance takes care of the connection to your
database and sends the database the LINQ queries that you use on it. For you, as you write your VB code,
pEntities gives you an object-oriented view of the database table(s).
When "End Using" is reached this pEntities variable is disposed of.
- I have often gotten errors at this point. Specifically, when I type carlsondEntities in that Using line, Intellisense
often can't find any such thing. What has typically worked for me is to close Visual Studio and start it up again, as
that has so far resolved the issue for me. Other ideas include deleteing all of the Model files and starting again to
create a model, based on the theory that something went wrong in the production of the Model. Another idea is to pull up
your model by double clicking the .edmx file and look at its properties in the
Properties window and check that you have
the correct Entity Container Name. We have been assuming that this name is carlsondEntities.
You can also check that there is a reasonable name for the Namespace property. Above we attempted to name it carlsondModel.
Saving all of your files both before and after creating the model may also help.
- The first line inside of the Using construct uses a LINQ command to place into a variable called parts,
all the information on each Part that it can extract from pEntities.Parts, a collection of Part items
(ultimately obtained from the database). Notice that we use a Select to pick only only those Part items whose Price
fits the desired range.
- Finally, the data in the parts variable is converted to a list that is used as the data source for your GridView and then
the data is bound to the GridView so that it shows up in the user's browser.
|
Building and Running Your App
- Save all of your files in Visual Studio.
- Then use Build, Build Page.
- If the build succeeded, the Output window will give you a message to that effect.
- If it does not build, fix the errors.
- Then run your app first locally, by clicking the green triangle button labeled as Internet Explorer or by selecting
Debug, Start Without Debugging.
- Most likely it will not run correctly.
The error message I got was "WebForms UnobtrusiveValidationMode requires
a ScriptResourceMapping for 'jquery'. Please add a ScriptResourceMapping named jquery(case-sensitive)".
The error message does not really tell you how to go about making this fix.
- This error is due to the Validators we put on the form. The easiest fix is to turn off unobtrusive validation.
Unobtrusive validation produces cleaner validation code on the client end, but is not necessary.
- Thus, edit your web.config file, placing the following after the end of the configSections region so that
your web.config looks much like this web.config file.
|
<appSettings>
<add key="ValidationSettings:UnobtrusiveValidationMode" value="None"/>
</appSettings>
|
- Then run your app by going to the correct URL with a browser. For example, the URL I am using is:
http://cis2.stvincent.edu/carlsond/Wrox7/UserInput.aspx.
- Do not be surprised if this does not work. First, this depends on having the project folder (Wrox7 here) somewhere under
the webroot on your webserver. (In my URL, the carlsond folder is the mapped network drive on the web server cis2.stvincent.edu
where I created the project in Visual Studio in the first place. If you have no network drive for this, you can use Website,
Copy Web Site to copy your website files. You then click Connect, select an appropriate folder for the copied files, highlight
in the left panel the files to be copied, and click the right arrow to copy those files to the folder shown in the right panel.
You can later transfer these files to your web server, perhaps by ftp.
- Even now, your web form probably does not work correctly on the live web server. This is because the IIS web server
needs to be told to use a certain application pool, one probably named ASP.NET v4.0, for your app.
Have your instructor or system administrator do the following,
unless you are the system administrator, in which case you should do these steps yourself.
- Log into the web server and start IIS Manager.
- In IIS Manager, under the overall website on this web server, find the Application Pools folder. Either find an existing
application pool that meets the following coditions, or right click in the empty space and add an application pool that
fulfills these conditions. The conditions are: Under Basic settings, it should use the .NET Framework v4.0 or later,
Managed Pipeline Mode needs to be set to Integrated, and check the box for "Start application pool" immediately.
Under Advanced Settings, Identity should be set either to NetworkService or ApplicationPoolIdentity. You may have to
figure out which one to use by trial and error. Also, Load User Profile should be set to true.
- In IIS Manager, find the person's website folder for this particular web application. For me, this folder was Wrox7.
- Right click this folder and select Convert to Application.
- Click the Select button next to the "Application pool" text box. Choose the application pool noted above.
This is the one I named ASP.NET 4.0. Click OK and OK.
- If you get the wrong application pool, you can right click the application and select Manage Application, Advanced Settings.
Click at the end of the application pool line and use the drop down to select the correct application pool.
- For further help on geting web apps to run under IIS on a live web server, see chapter 19, Deploying Your Web Site, in
Imar Spaanjaars's book Beginning ASP.NET 4 in C# and VB.
- Now check to see if the web form, accessed on the live web server, works correctly. Hopefully the answer is yes.
- If you turned on error reporting or debugging in your web.config file, and were ready to put this web application into
production, you would want to turn these off. To return your web.config file to normal
(no debugging and no displaying of error messages in the browser), you would edit the file so that the customErrors and compilation
sections look like the following. Note that either On or RemoteOnly for customErrors will protect users not on the same machine from
the technical details of errors and protect your website from hackers who would like to see those detailed error messages!
- For the purposes of this course, however, leave customErrors Off as that allows your instructor to see any errors that your
site generates.
|
<compilation debug="false" strict="false" explicit="true" targetFramework="4.5"/>
<customErrors mode="On"/>
|
|
Search CIS Site
Tutorials
