Displaying Database Records on a Web Page

Displaying Database Records on a Web Page

For many users, one of the most exciting aspects of the World Wide Web is the ability to access large amounts of information rapidly through a Web browser. Often, of course, the quantity of information that needs to be displayed on a commercial Web site far exceeds what a developer can realistically prepare using simple text documents. In these cases, Web programmers add database objects to their Web sites to display tables, fields, and records of database information on Web pages, and they connect the objects to a secure database residing on the Web server or another location.

Visual Studio 2005 makes it easy to display simple database tables on a Web site, so as your computing needs grow, you can use Visual Studio to process orders, handle security, manage complex customer information profiles, and create new database records—all from the Web. Importantly, Visual Web Designer delivers this power very effectively. For example, by using the GridView control, you can display a database table containing dozens or thousands of records on a Web page without any program code. You'll see how this works by completing the following exercise, which adds a Web page containing loan contact data to the Car Loan Calculator project. If you completed the database programming exercises in Chapter 18, “Getting Started with ADO.NET,” and Chapter 19, “Data Presentation Using the DataGridView Control,” be sure to notice the similarities (and a few differences) between database programming in a Windows environment and database programming on the Web.

Add a new Web page for database information

1. Click the Add New Item command on the Website menu.

   Visual Web Developer displays a list of components that you can add to your Web site.

2. Click the Web Form icon, type InstructorLoans.aspx in the Name text box, and then click Add.

   Visual Web Developer adds a new Web page your Web site. Unlike the HTML page you added earlier, this Web page component is capable of displaying server controls.

3. If necessary, click the Design tab to switch to Design mode.

4. Enter the following text at the top of the Web page:

   The following grid shows instructors who want loans and their contact phone numbers:

5. Press the Enter key twice to add two blank lines below the text.

   Remember that Web page controls are added to Web pages at the insertion point, so it is always important to create a few blank lines when you are preparing to add a control.

Next you'll display two fields from the Instructors table of the Students.mdb database by adding a GridView control to the Web page. GridView is similar to the GridDataView control you used in Chapter 19, but GridView has been optimized for use on the Web. (There are also a few other differences, which you can explore by using the Properties window and Visual Studio online Help.) Note that I'm using the same Access database table I used in Chapters 18 and 19, so you can see how similar database programming is in Visual Web Developer. Many programmers also use SQL databases on their Web sites, and Visual Web Developer also handles that format very well.

Add a GridView control

1. With the new Web page open and the insertion point in the desired location, double-click the GridView control on the Data tab of the Visual Web Developer Toolbox.

   Visual Web Developer adds a grid view object named GridView1 to the Web page. The grid view object currently contains placeholder information and a tiny green icon, indicating that the object is a server control.

2. If the GridView Tasks list box is not already displayed, click the GridView1 object's shortcut arrow to display the list box.

3. Click the Choose Data Source arrow, and then click the New Data Source option.

   Visual Web Developer displays the Data Source Configuration Wizard, a tool that you used in Chapters 18 and 19 to establish a connection to a database and select the tables and fields that will make up a dataset. Your screen looks like this:

   

4. Click the Access Database icon, type Students in the Specify An ID For The Source box, and click OK.

   You are now prompted to specify the location of the Access database on your system. (This dialog box is slightly different than the one you used in Chapter 18.)

5. Type c:\vb05sbs\chap18\students.mdb, and click Next.

   You are now asked to configure your data source; that is, to select the table and fields that you want to display on your Web page. Here you'll use two fields from the Instructors table. (Remember that in Visual Studio, database fields are often referred to as columns, so you'll see the word “columns” used in the IDE and the instructions below.)

6. Click the Name arrow, and then click Instructors in the list box.

7. Select the Instructor and PhoneNumber check boxes in the Columns list box.

   Your screen looks like this:

   

   Through your actions here, you are creating a SQL SELECT statement that configures a dataset representing a portion of the Students.mdb database. You can see the SELECT statement at the bottom of this dialog box.

8. Click Next to see the Test Query dialog box.

9. Click the Test Query button to see a preview of your data.

   You see the following information in the dialog box:

   

   This data looks as expected, although if we were preparing this Web site for wider distribution, we would take the extra step of formatting the PhoneNumber column so that it contains standard spacing and phone number formatting.

10. Click Finish.

    Visual Web Developer closes the wizard and adjusts the number of columns and column headers in the grid view object to match the selections that you have made. However, it continues to display placeholder information (“abc”) in the grid cells.

11. With the GridView Tasks list box still open, click the Auto Format command.

12. Click the Professional scheme.

    The Auto Format dialog box looks like this:

    

    The ability to quickly format, adjust, and preview formatting options is a great feature of the GridView control.

13. Click OK and then close the GridView Tasks list.

    The InstructorLoans.aspx Web page is complete now, and looks like this:

    

Now you'll add a hyperlink on the first Web page (or home page) that will display this Web page when the user wants to see the database table. You'll create the hyperlink with the HyperLink control.

Add a hyperlink to the home page

1. Click the Default.aspx tab at the top of the Designer.

   The home page for your Web site appears in the Designer.

2. Click to the right of the Display Help (lnkHelp) object to place the insertion point after that object.

3. Press Enter twice to create space for a second hyperlink.

4. Double-click the HyperLink control on the Standard tab of the Toolbox to create a hyperlink object at the insertion point.

5. Set the Text property of the hyperlink object to “Display Loan Prospects”.

   We'll pretend that your users are bank loan officers (or well-informed car salespeople) looking to sell auto loans to university professors. Display Loan Prospects will be the link they click to view the selected database records.

6. Set the ID property of the hyperlink object to “lnkProspects”.

7. Click the NavigateUrl property, and then click the ellipsis button.

   Visual Studio opens the Select URL dialog box.

8. Click the InstructorLoans.aspx file in the Contents Of Folder list box, and then click OK.

Your link is finished, and you're ready to test the Web site and GridView control in your browser.

Test the final Car Loan Calculator Web site

TIP
The complete Car Loan Calculator Web site is located in the c:\vb05sbs\chap20 folder. Use the Open Web Site command on the File menu to open an existing Web site.

1. Click the Start Debugging button.

   Visual Studio builds the Web site and displays it in Internet Explorer.

2. Enter 8000 for the loan amount and 0.08 for the interest rate, then click Calculate.

   The result is $250.69. Whenever you add to a project, it is always good to go back and test the original features to verify that they have not been modified inadvertently. Your screen looks like this:

   

   The new hyperlink (Display Loan Prospects) is visible at the bottom of the Web page.

3. Click Display Loan Prospects to load the database table.

   Internet Explorer loads the Instructor and PhoneNumber fields from the Students.mdb database into the grid view object. Your Web page looks something like this:

   

   The information is nicely formatted and appears useful. By default, you'll find that the data in this table cannot be sorted, but you can change this option by selecting the Enable Sorting check box in GridView Tasks. If your database contains many rows (records) of information, you can select the Enable Paging check box in GridView Tasks to display a list of page numbers at the bottom of the Web page (like a list you might see in Microsoft Document Explorer or a search engine that displays many pages of “hits” for your search).

4. Click the Back and Forward buttons in Internet Explorer.

   As you learned earlier, you can jump back and forth between Web pages in your Web site, just as you would in any professional Web site.

5. When you're finished experimenting, close Internet Explorer to close the Web site.

You've added a table of custom database information without adding any program code!