Home > Articles > Programming > Windows Programming

This chapter is from the book

This chapter is from the book

Displaying Your Data

Everything you've done so far is invisible at runtime. You've set up a DataAdapter object, a Connection object, and a DataSet object, but you can't see any of these things. In order to actually see the data you're working with, you'll need to add controls to the page. ASP.NET makes this simple.

Adding the Grid

Now that you've set up all the data-handling components, it's time to add the DataGrid control to the page and hook it up. Follow these steps to display the data retrieved by the DataAdapter in a grid:

  1. In the Solution Explorer window, select Products.aspx and select View, Designer (or simply double-click) to view the page in the designer.

  2. From the Web Forms tag of the Toolbox window, drag a DataGrid control from the Toolbox window onto the page.

  3. Set properties for the grid, as shown in Table 1.

    Table 1 Set These Properties for the Sample Grid

    Property

    Value

    Description

    ID

    grdProducts

    You're going to need to interact with this control programmatically, so you should set its name to something that indicates its purpose.

    DataSource

    dsProducts1

    The DataSource property of the grid indicates where it should look in order to retrieve its data.

    DataMember

    Products

    The DataMember property of the grid allows you to specify which table within the data source to use to fill the grid. In this case, there's only one table in there, so you don't really have to specify this value. There may be multiple tables in the DataSet, however, and in that case, you'd need to indicate the specific table you want to use. (Note that the programmer filling the DataSet specifies the table name—this normally isn't tied directly to the name of the table from which the data was retrieved. In this case, the wizard set the name for you. You simply choose the name from the list provided in the Properties window.)


  4. In the Solution Explorer, right-click the Products.aspx page, select Build and Browse from the context menu, and load the page. (You expect to see data, don't you?)

As you can see, setting the DataGrid control's properties isn't all you need to do. It takes a few lines of code to fill the DataSet and bind the grid. The next section walks you through the final steps necessary to display data in the grid.

NOTE

The DataGrid control, as you're using it here, isn't terribly attractive. If you want to make it look "prettier," right-click the control and select Auto Format from the context menu. You're on your own, for now.

Populating the DataGrid with Data

Although you've set properties that would appear to bind the DataGrid control to a DataSet, the page doesn't automatically populate the DataSet when you load the page. You'll need to use the page's Page_Load event procedure to populate the DataGrid control with data as the page is loaded.

To display the data in the grid, follow these steps:

  1. If the page is still loaded in a browser window, close the browser window.

  2. Back in the page designer, double-click the page (not on a specific control) to load the code-behind file, which is ready for you to enter the contents of the Page_Load procedure.

  3. Modify the Page_Load procedure so that it looks like this:

    Private Sub Page_Load( _
     ByVal sender As System.Object, _
     ByVal e As System.EventArgs) _
     Handles MyBase.Load
    
     daProducts.Fill(DsProducts1)
     grdProducts.DataBind()
    End Sub

    TIP

    The Fill method of the DataAdapter object accepts a DataSet or DataTable object as its parameter, and it fills its parameter with data. The DataBind method of the DataGrid object tells the control to bind itself to its data source, effectively filling the grid from the DataSet you set as its data source.

  4. In the Solution Explorer window, right-click the sample page, select Build and Browse from the context menu, and verify that the page looks like Figure 1. You've now managed to load the data from the SQL Server table using only two lines of code!

Filling a DropDownList Control with Data

Using the design-time components that create Connection, DataAdapter, and DataSet objects is fine, but it is hard to see everything that is going on behind the scenes. In fact, the wizards actually build a complete class that handles adding, editing, deleting, and retrieving data. This is generally overkill when all you need to do is load a list full of data. In these cases, it may make more sense to write the code yourself.

Extending the example you built in the first half of this article, in this second part, you'll add a drop-down list of available categories and filter the displayed products based on the category you select. In this case, you'll write all the code required to bind the DropDownList control to a DataSet, instead of using the design-time components.

TIP

Once you've worked through this section, you may find it useful to go back and revise the previous example so that it uses a similar technique—That is, modify the example so that you fill the DataGrid control by hand, as well.

Follow these steps to set up the DropDownList control:

  1. With the Products.aspx page open in the designer, add a Label control and a DropDownList control to the page. Modify the properties of the controls, as shown in Table 2. When you're done, the page should look like Figure 9.

    Table 2 Set Properties of the Controls Using These Values

    Control

    Property

    Value

    Label1

    Text

    Categories

    DropDownList1

    ID

    ddlCategories

     

    AutoPostBack

    True


    Figure 9 How the finished page should look in Design view.

    TIP

    Setting the AutoPostBack property to True for the DropDownList control causes the page to post back to the server each time you select an item from the list. Using this technique, you can provide immediate feedback, after the user selects a new category. Without using AutoPostBack, you would need some way to trigger a roundtrip, so you could run the code necessary to refill the grid.

  2. Select View, Code to load the code-behind file in the code editor and then find the Page_Load procedure. Modify the Page_Load procedure so that it looks like this:

    Private Sub Page_Load( _
     ByVal sender As System.Object, _
     ByVal e As System.EventArgs) _
     Handles MyBase.Load
    
     If Not Page.IsPostBack Then
      CategoryLoad()
     End If
    End Sub

    Handling Postback

    Clearly, the CategoryLoad procedure call fills a list of categories, but what's with Page.IsPostBack? This property of the page makes sure you don't reload the list of categories each time the form is posted back to itself. This list can't change, so why refill it? Selecting an item from the list will raise the SelectedIndexChanged event procedure but will also cause the Page_Load event procedure to run, because the page will be reloaded. Page.IsPostBack is False the first time the page loads, and the page framework sets the Page.IsPostBack property to True after the first loading of this page. You'll use this technique often to ensure that you only calculate things you must calculate when you post back to a page.

  3. Add the following statement at the very top of the code-behind file (adding this allows you to refer to classes within the OleDb namespace without having to explicitly include the OleDb name each time):

    Imports System.Data.OleDb
  4. Add the procedure in Listing 1 below the Page_Load procedure (but above the End Class statement).

    Listing 1—Load the Category Drop-Down List with Category Information

    Private Sub CategoryLoad()
     Dim ds As DataSet
     Dim da As OleDbDataAdapter
    
     Dim strSQL As String = _
      "SELECT CategoryID, CategoryName " & _
      "FROM Categories"
    
     ds = New DataSet()
     da = New OleDbDataAdapter(strSQL, cnNorthwind)
     da.Fill(ds)
    
     With ddlCategories
      .DataTextField = "CategoryName"
      .DataValueField = "CategoryID"
      .DataSource = ds
      .DataBind()
     End With
    End Sub
  5. In the Solution Explorer window, right-click Products.aspx and select Build and Browse from the context menu. The page should contain a drop-down list containing all the available categories. (Of course, you're no longer filling the grid—that comes back in the next section.)

Before going further, you should investigate what happened in the CategoryLoad procedure. This procedure first declares the two ADO.NET variables it will need—DataSet and OleDbDataAdapter:

Dim ds As DataSet
Dim da As OleDbDataAdapter

The code then creates the SQL string it will need in order to retrieve just the columns it requires for the drop-down list:

Dim strSQL As String = _
 "SELECT CategoryID, CategoryName " & _
 "FROM Categories"

TIP

Although Visual Basic .NET allows you to initialize variables on the same line of code on which they're declared, we generally shy away from this technique. There is a good reason for not doing this: error handling. If an error could occur as you're declaring the variable, you'll need to place the declaration in a Try/End Try block. But placing the declaration in a Try/End Try block scopes the variable so that it's only available within that block—it's not even available in the Catch block! This makes it impossible to use the variable throughout the procedure, so we tend to declare the variable outside the error-handling block and supply its value within the error handling. Our rule of thumb: We only take advantage of this new feature for assigning constant values, where it's not possible that a runtime error could occur. This is just a personal preference, and you may do what you like. However, that's the style you'll see throughout this article.

The code then instantiates the DataAdapter object, passing in a SQL string (which supplies the SELECT command for this DataAdapter object) and an OleDbConnection object, supplied at design time on the form. Next, the code calls the Fill method of the DataAdapter object, filling the data in the DataSet:

ds = New DataSet()
da = New OleDbDataAdapter(strSQL, cnNorthwind)
da.Fill(ds)

We had a number of options in the preceding code fragment. We could have created a new OleDbConnection object and supplied the connection information in the code. Because we had the available connection object already created, there didn't seem much sense in doing that. We had no command information (that is, information on retrieving the data) already prepared, so this example sets up its own SQL string and its own OleDbDataAdapter object.

Finally, the code sets up the DropDownList control so that it fills itself with data. This requires setting three properties and calling a method:

With ddlCategories
 .DataTextField = "CategoryName"
 .DataValueField = "CategoryID"
 .DataSource = ds
 .DataBind()
End With

The DataTextField property contains the name of the field from the data source that provides the content to be displayed within the drop-down portion of the control. This is the information you see on the screen. The DataValueField property contains the name of the field from the data source that provides the value of each list item. Although this could be the same field as the DataTextField property, most of time it won't be. Most often, you want to display one item but have the option of retrieving a different value when the user makes a choice. In this case, you're displaying the category name and retrieving the category ID when the user selects a category. You must set the DataSource property, telling the control which DataSet or DataTable it should use to provide its data. Finally, when you're ready to display the data, call the DataBind method, which binds the control to its data source.

It's interesting to see the HTML created by the .NET page framework. If you view the page in a browser; then right-click and select the View Source option, you'll see code like Listing 2 for the drop-down list control (we've removed extraneous attributes).

Listing 2—In the Browser, You'll see HTML Like This, Rendered by ASP.NET

<select name="ddlCategories">
 <option value="1">Beverages</option>
 <option value="2">Condiments</option>
 <option value="3">Confections</option>
 <option value="4">Dairy Products</option>
 <option value="5">Grains/Cereals</option>
 <option value="6">Meat/Poultry</option>
 <option value="7">Produce</option>
 <option value="8">Seafood</option>
</select>

As you can see, the page framework retrieved the CategoryName field and set it as the displayed text of each list item and set the CategoryID field as the value for each item.

Using the DropDownList Control to Filter by Categories

As the final step in this demonstration, you need to hook up the code that will display a list of products within the selected category. To do that, follow these steps:

  1. Make sure you've closed any browser windows displaying Products.aspx.

  2. In the page designer, double-click the DropDownList control, loading the code editor with the ddlCategories_SelectedIndexChanged procedure selected.

  3. Modify the procedure so that it looks like this:

    Private Sub ddlCategories_SelectedIndexChanged( _
     ByVal sender As System.Object, _
     ByVal e As System.EventArgs) _
     Handles ddlCategories.SelectedIndexChanged
     ProductsLoad
    End Sub
  4. Add the code in Listing 3 to the page's class:

    Listing 3—Filter the DataGrid Control Based on the Selected Category

    Private Sub ProductsLoad()
     Dim ds As DataSet
     Dim strSQL As String
    
     strSQL = daProducts.SelectCommand.CommandText & _
      " WHERE CategoryID = " & _
      ddlCategories.SelectedItem.Value
    
     ds = New DataSet()
    
     With daProducts
      .SelectCommand.CommandText = strSQL
      .Fill(ds)
     End With
    
     With grdProducts
      .DataSource = ds
      .DataBind()
     End With
    End Sub
  5. Modify the Page_Load procedure once again, adding a call to the ProductsLoad procedure:

    Private Sub Page_Load( _
     ByVal sender As System.Object, _
     ByVal e As System.EventArgs) _
     Handles MyBase.Load
    
     If Not Page.IsPostBack Then
      CategoryLoad()
      ProductsLoad()
     End If
    End Sub
  6. In the page designer, select the DataGrid control. In the Properties window, remove the text from the DataSource and DataMember properties—because you're setting the DataSource property in code, you don't need these values.

  7. Build and browse the page once again. This time, you should be able to select a category from the drop-down list and see the associated products displayed in the grid.

  8. When you're done, close the browser window and save your project.

The code in the ProductsLoad procedure does the work of loading the grid with products from the selected category. It starts by retrieving the SQL string from the DataAdapter object you set up on the page, in the first half of the article. The code uses the CommandText property of the SelectCommand property and then concatenates a WHERE clause to the SQL:

strSQL = daProducts.SelectCommand.CommandText & _
 " WHERE CategoryID = " & _
 ddlCategories.SelectedItem.Value

This new SQL expression pulls in all the fields you set up originally but limits the rows to only those whose CategoryID field matches the selected item in the drop-down list.

The code then instantiates a new DataSet object, resets the CommandText property of the DataAdapter's SelectCommand object, and fills the DataSet with the newly filtered rows:

ds = New DataSet()

With daProducts
 .SelectCommand.CommandText = strSQL
 .Fill(ds)
End With

The code finishes up by setting the DataGrid control's DataSource property to be the new DataSet (instead of dsProducts1, which it used in the earlier example) and then binds the grid to its new data source.

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Overview


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information


To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information


Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children


This site is not directed to children under the age of 13.

Marketing


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information


If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information


Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents


California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure


Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact


Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice


We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020