CIS Logo SVC Logo

   Computing & Information Systems


Schoology Facebook        Search CIS Site      Tutorials

User Input to Get Matching Data From 2 Related Tables

The Goals for this Example

The overall goal is to again use some user input as in our previous web app to get data from a database, but this time the data is in 2 related tables, not 1 table. Our revised application has the user enter a minimum and maximum price, and allows the user to check a box if only those items that are in stock should be displayed. The app can tell what is in stock by looking for a positive number in the NumInStock column of the correct row of the new table, the Inventory table. When the button is then clicked, all the data that fits this input is displayed in a grid view. This version again uses validation controls, but adds the ValidationSummary control, in this case to pop up a window listing all of the errors in one handy place. This application uses the Entity Framework (EF) and LINQ.


Details on the App

  • We wish to create a web form that gets a minimum and maximum price from the user and that allows the user to check a check box to limit the results to bicycle parts that are in stock.
  • After the postback (which happens automatically when the user clicks the button) all matching data is displayed in a grid view.
  • Besides using the Parts table as in the previous web app, this one also uses an Inventory table, which you will set up below. The number of items in stock will be displayed for each part on the results page, and if the check box is checked, only those parts with a "number in stock" greater than zero will be displayed.

First Steps

  • Create a new web site in Visual Studio, placing it on your mapped network drive for your live web server if possible. Add the App_Code folder to your web site by right clicking the site in Solution Explorer, etc.
  • Use Computer to find your Web.config file from the previous web app. Copy the section for the webappConnection into your Web.config file for the new web site. This connection string was originally 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. Also copy 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. Leave out any entity connections.

Creating the Inventory Table

  • In this example, create the Inventory table using SQL Server Management Studio. This is because Visual Studio's Server Explorer does not appear to allow you to make a database diagram. However, you could use Server Explorer to set up the structure (the columns) for the Inventory table and to fill in the data. Then you would need to go to SQL Server Management Studio to make the database diagram.
  • 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 Inventory within your database. Give it the following fields:
Name Data Type Allow Nulls
Id int not checked
PartNumber int not checked
NumInStock int not checked

  • For this list of steps, the explanation is geared toward how things would be done in SQL Server Management Studio. If you are using Server Explorer in Visual Studio, you will have to adapt this a bit or refer back to the directions for this prior example as a general outline.
  • In the Designer for this table, right click at the front of the Id field and make the selection to designate this field to be the primary key.
  • Right click again at the front of the Id field and look at Properties. In the Properties window, find the Identity Column and set its value to be Id.
  • Next, look at the Properties for this table. Find the (Name) entry and change it to Inventory.
  • Now do a File, Save or click the save button.
  • You can now close the window where we designed the columns for this table.
  • Next we add data to this table. Begin by right clicking on the table in SQL Server Management Studio and then select Edit Top 200 Rows.
  • In the resulting window you can fill in data for this table. The Id field is an identity field, so its values will automatically be filled in. For the other fields, enter the data that is shown below:
Id PartNumber NumInStock
1 3 11
2 1 4
3 5 0
4 4 26
5 2 1
6 6 17
7 7 33
8 13 0
9 10 1
10 8 5
11 9 8
12 11 3
13 12 6

  • Be sure that for every row in your Parts table, the PartNumber for that row occurs in a row of the Inventory table. Adjust your data if need be so that this condition is satisfied. Each part needs to have inventory data.
  • You may now close the window where you entered the data. The data for each row is saved automatically when you move to the next row.
  • The next step is to add a database drawing that establishes the relationship between these two tables that is based on the PartNumber field that they both have. In the Parts file, PartNumber is the primary key. In the Inventory file, it is called a foreign key. In SQL Server Management Studio, in Object Explorer, find your database folder. Inside of that folder, right click on the Database Diagram folder. Select New Database Diagram. Put checks next to the Parts and Inventory tables to add them to the digram. Then click in the left margin of the PartNumber field of the Parts table in the drawing and drag it to the PartNumber field of the Inventory table. A line should be drawn indicating the relationship. If you click on that line, you can then examine the properties of this relationship.
  • Note that the property named Enforce Foreign Key Constraint will keep you (and your web apps) from deleting a part from the Parts table if that part number is in use for some entry in the Inventory table. If you do the deletion from the Inventory table and then delete the part in the Parts table that has the same PartNumber, it should work.
  • Also note that you can right click on a table and select Edit Top 200 Rows at any time that you want to adjust the data.
  • You can now close SQL Server Management Studio.

Setting up the 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.
  • You may want to copy in the controls that you placed on the form in the previous web app.
  • Then add the label whose text says "Only report parts that are in stock" along with the checkbox that is next to it.
  • The textboxes, validators, and grid view are the same as before, although you may want to move the validators closer together so that the grid view is closer to the textboxes and button.
  • However, we do add one new control that is related to the validators: a validation summary control. Set it to use absolute positioning and move it to the bottom of the main div on your form, below all the validations and grid view. Resize it so that it fits our image of the form in Design View. In the properties for this control, set the foreground color to #CC3300 or whatever red color you used for the validators. The header text should be "Correct the following before clicking List Matches:". Finally set the ShowMessageBox property to True and the ShowMessageSummary to False. The former has the app pop up a message box if there are errors reported by one or more of your validations. That message box will list these errors. If ShowMessageSummary is True, the same list of errors is placed on the form itself.
  • Save all of your files before going on.

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" 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 and Inventory tables. Put a check in front of each of those tables. 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. 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 two classes that were created based on the Parts and Inventory tables, along with a line between them representing the relationship based on the PartNumber. Note that we have a 1-to-many relationship here. The asterisk on the Inventory table end means that several inventory records could possibly correspond to the same PartNumber. Our data does not set it up that way, since it was 1-to-1, but someone could put in extra records with the same PartNumber. The Navigation Properties in the drawing are also helpful. In the Inventory table, referring to a PartNumber gives you one part. Thus the Navigation Property here is Part. However, in the Part table, a PartNumber might give you several rows of the Inventory table, which is why the Navigation Property here is Inventories. (Inventory records would be a better term for this.)
  • If you click on that line between the two tables, under Properties you should see much of the same information that you saw about the relationship in SQL Server Management Studio. Note that one end of this relationship is one single part, whereas the other end is for a collection of inventory records. This is again because someone could set the Inventory table up with several inventory records having the same PartNumber, even though our data does not do this.
  • Be sure to save and close this diagram.


  • Next, we write some LINQ (language integrated query) code in our VB code-behind file, along with some other event-handler code for the Load event for our page.
  • 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. Double click this spot at the bottom of the page. (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

         Using pEntities As New carlsondEntities()
            If CheckBox1.Checked Then
               Dim parts = From Part In pEntities.Parts.Include("Inventories")
                  Where (Part.Price >= CDbl(Min)) And (Part.Price <= CDbl(Max)) _
                     And (Part.Inventories.FirstOrDefault.NumInStock > 0)
                  Order By Part.Price Ascending
                  Select New With {Part.Description, Part.Color, Part.Price, Part.Inventories.FirstOrDefault.NumInStock}
               GridView1.DataSource = parts.ToList()
               Dim parts = From Part In pEntities.Parts.Include("Inventories")
                  Where (Part.Price >= CDbl(Min)) And (Part.Price <= CDbl(Max))
                  Order By Part.Price Ascending
                  Select New With {Part.Description, Part.Color, Part.Price, Part.Inventories.FirstOrDefault.NumInStock}
               GridView1.DataSource = parts.ToList()
            End If
         End Using

         GridView1.Visible = True
      Catch ex As Exception
         LabelError.Visible = True
         'LabelError.Text = LabelError.Text + ex.Message   These 2 lines are for debugging only.
         'LabelError.Text = LabelError.Text + ex.StackTrace
      End Try
   End If
End If

  • Most of this code is the same as in the previous web app and does not need to be explained further here.
  • 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 tables. 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. However, it often has worked if I close Visual Studio and start it up on the project later. All of a sudden it can find carlsondEntities.
  • The parts variable is used to hold the data returned by our LINQ query. Note that it is getting data from the Parts object (that maps to the Parts table), but that we have told it to include the Inventories (inventory records). If we did not do that, the Inventories navigation item would still be there in the object, but would be null. This is for efficiency. There is no sense in loading the data from the second table unless we need to. In this case, we need to, and so use the include to get that data ahead of time (rather that ask for one inventory record after another). Notice that we use a Select to pick only only those Part items whose Price fits the desired range.
  • The CDbl function converts the Min and Max strings (taken from the textboxes) into doubles.
  • If the check box has been checked to say that we only want to see the data for parts that are in stock, we look at Part.Inventories.FirstOrDefault.NumInStock to see if that is greater than 0. This code starts with the particular part and then uses the Inventories navigation item to get to the matching inventory records. Since we know there is only one inventory record per part, it is safe for us to use FirstOrDefault to pick out the first (only) inventory record. In that record we look at the NumInStock field.
  • It is not used here, but if you would ever need to get the Last or LastOrDefault (which the Entity Framework supposedly does not provide), you can specify Descending order and then use the First operator or the FirstOrDefault operator.
  • The list of fields in the With clause of the Select is used to indicate the fields that we want to have returned. This list includes Part.Inventories.FirstOrDefault.NumInStock, which we analyzed above. It's type is called an anonymous type, since we did not directly say what it is. Instead, the compiler figures it out from how we selected the data, that is, from Part.Inventories.FirstOrDefault.NumInStock.
  • 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.
  • Note that the catch clause for the try statement has commented off two lines for displaying the details of any error message and/or a stack trace. This is a useful debugging technique, but not something that you want to leave active in your final code. When running your web app locally, you might also be able to use MsgBox to pop up a window that displays the same two messages.

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.
  • Like the previous app, this one will probably not run correctly at first. If you get an UnobtrusiveValidationMode error, the easiest fix is to turn off unobtrusive validation. (Unobtrusive validation produces cleaner validation code on the client end, but is not necessary.) Just 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.

   <add key="ValidationSettings:UnobtrusiveValidationMode" value="None"/>

  • Then run your app by going to the correct URL with a browser. For example, the URL I am using is:
  • This too might not work at first. It depends on having the project folder (Test10 here) somewhere under the webroot on your webserver. (In my URL, the carlsond folder is the mapped network drive on the web server 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.
  • In addition, 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 this for you, as in the previous web app.
  • Now check to see if the web form, accessed on the live web server, works correctly. Hopefully the answer is yes.
  • In checking your app, be sure to try invalid input. For example, for this input the resulting page after the postback should show two validators with error messages as well as a popup window summarizing the errors. The latter is due to the validation summary control that you put on the page.
  • If you turned on error reporting or debugging in your Web.config file, turn them off now. To return your Web.config file to normal (no debugging and no displaying of error messages in the browser), 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!

      <compilation debug="false" strict="false" explicit="true" targetFramework="4.5"/>
      <customErrors mode="On"/>

Back to the main page for ASP.NET 4.5 Web Apps

Author: Br. David Carlson
Last updated: October 04, 2017