Using DataGridView to Display Database Records | Microsoft Visual Basic 2005 Step by Step (Step by Step (Microsoft))

Using DataGridView to Display Database Records

The DataGridView control presents information by establishing a grid of rows and columns on a form to display data as you might see it in a program such as Microsoft Excel or Microsoft Access. A DataGridView control can be used to display any type of tabular data: text, numbers, dates, or the contents of an array.

In this chapter, however, you'll focus on the ability of the DataGridView control to display the colums (fields) and rows (records) of the Students.mdb database, the file of structured student information that you started working with in Chapter 18. You'll start by filling a simple data grid view object with text records from the database, and then you'll set a few formatting options. Next you'll move on to sorting records in grid objects and learning how to manage multiple grids and navigation bars on a form. Finally, you'll learn how to adjust DataGridView properties, including the ReadOnly property that allows or prevents a user from saving changes back to the original database.

The DataGridView control is connected, or bound, to underlying data access components through the its BindingSource property. This property contains useful information only after your program has established a connection to a valid data source by using the Data Source Configuration Wizard and the Data Sources window. (The steps involved in establishing this connection will be reviewed quickly here but are described in greater detail in Chapter 18; if you want more information , read the section entitled “Working with an Access Database” in that chapter.) After a data grid view object is bound to a valid data source, Visual Studio fills, or populates, the grid automatically by using the Fill method when the form is loaded into memory.

Establish a connection to a database table

Start Visual Studio, and create a new Visual Basic Windows Application project named My DataGridView Sample.

A new project appears in the IDE.
Click the Add New Data Source command on the Data menu.

The Data Source Configuration Wizard opens in the development environment. You used this tool Chapter 18 to link the Students.mdb database to your project and fill the Data Sources window with tables and columns from the database. This time you'll select a broader range of information from the sample Access database.
Click the Database icon, and then click Next.

The wizard prompts you to build a connection string, but if you completed the exercises in Chapter 18, the Students.mdb database is automatically offered to you, as shown in the following screen:

If you don't see the Students database connection, click the New Connection button, and browse to the Students.mdb file, located in the c:\vb05sbs\chap18 folder. (Detailed steps for establishing this connection are given in Chapter 18, if you'd like additional information.)
With the Students.mdb connection string highlighted, click the Next button.

The wizard asks whether you want to save your connection string.
Click Next to save the string in the default location (your project's configuration file).

You are now prompted to select the database objects you want to use for this particular project. Remember that the Data Source Configuration Wizard allows you to pick and choose database tables and columns at this point—you can select all the objects in the database or just a subset.
Expand the Tables node to see the names of the seven tables in the database and an additional entry called Switchboard Items.
Click the plus sign (+) next to the Tables node to select all eight items.

You'll add a broader range of database information to this project, because the goal of this chapter is to view large amounts of data by using the DataGridView control. Your wizard page looks as shown on the next page.
Click Finish to close the Data Source Configuration Wizard.

Visual Studio creates a dataset named StudentsDataSet to represent the eight database objects you selected. Visual Studio also adds an XML schema file named StudentsDataSet.xsd to your project and the Solution Explorer window. You have now established a connection to the Students.mdb database that you can use for the remainder of this chapter.
Click the Save All button on the Standard toolbar to save the project. Specify the c:\vb05sbs\chap19 folder as the location.
Click the Data Source tab below Solution Explorer to open the Data Sources window. (If the Data Sources tab is not visible, click the Show Data Sources command on the Data menu.)

The Data Sources window displays the objects in StudentsDataSet, as shown in the following illustration:

In Chapter 18, you dragged individual fields from the Data Sources window to a Windows form to bind data objects to controls in the user interface. In the next exercise, you'll follow a similar procedure, but this time you'll drag an entire table to the form, and you'll bind the table to a DataGridView control so that all the fields in the table can be displayed at once.

Create a data grid view object

Resize the form so that it covers most of the screen.

Before this chapter is complete, you'll place two data grid view objects side by side on the form, each with four columns and about ten rows of data. Remember that the form can be larger than the room allotted for it within the IDE, and you can close programming tools or use the scroll bars to see portions of the form that are hidden. (However, you'll want to keep the Data Sources window open for the next step.)
In the Data Sources window, click the Instructors table, and then click the arrow to its right to display the list of controls that can be bound to the Instructors table on the form.

The Data Sources window looks like this:

Because you have selected an entire table, you do not see individual bound controls in this list box. Instead you see the following options:
- DataGridView, the default selection, which displays a grid of columns and rows representing the fields and records in the Instructors table.
- Details, which configures Visual Basic to automatically create individual controls (with associated labels) for each field in a table that you drag to the form. Although I won't demonstrate Details now, it is a useful option if you want to present tabular data in a slightly more approachable format.
- None, which removes any association between the table and a user interface element or control. (If you select None for a table, you will not be able to drag the table from the Data Sources window to the form, and a Null icon will appear next to the table name.)
- Customize, which lets you select a different control that might be suitable for displaying multiple database fields (such as the ListBox control).
Click the DataGridView option, and then drag the Instructors table to the left side of your form.

Visual Studio creates a navigation bar at the top of the form, adds dataset, binding source, table adapter, and binding navigator components to the component tray, and creates a data grid view object named InstructorsDataGridView on the form. Your screen looks similar to the following:

As you can see, the grid does not contain any information at this point, and it is probably not the right size either. (My data grid view object is not wide enough to display all four columns, for example.) However, you can clearly see that Visual Studio has organized the Instructors table in the grid so that its fields appear as columns and its rows represent individual records. A blank row is reserved for the first record in the table, and additional rows will be added as soon as the program is run and the grid is filled with data.
Move and resize the data grid view object so that all four of its columns are clearly visible and there is ample room for at least ten rows of data.
Use the Properties window to set the form's Text property to “The Instructors Table”.

Your form looks similar to the following:

You have completed the basic steps necessary to create a data grid view object on a form and size it appropriately. Next you'll preview the data and customize your table. The ability to preview grid data and adjust basic settings is made easy by the new Visual Studio shortcut arrow feature.

Preview the data bound to a data grid view object

Select the data grid view object on the form, and then click the shortcut arrow in the upper-right corner of the object.

Visual Studio displays DataGridView Tasks, a list of common property settings and commands related to the data grid view object. The DataGridView Tasks list looks like this:

You can use the settings and commands in this list to change the table that is bound to the data grid view object and to enable or disable editing within the grid. (The default setting is to give the user limited abilities to edit information in the table, although you can still control whether the changes he or she makes are written to the underlying database.) You can also adjust the columns shown, dock (attach) the grid to the parent container (in this case, the form), filter records with a query (SQL statement), and preview the data in the table.
Click Preview Data to open the Preview Data dialog box.

You display this dialog box when you want to examine the data in a table before you actually run the program—a handy feature.
Click the Preview button.

Visual Studio loads the Instructors table from StudentsDataSet, as shown on the next page.

You should be familiar with some of this data from Chapter 18, but you now have an opportunity to see all four columns in the table. Seeing all the columns at once is interesting but also disconcerting—the Extension column contains no data at all, something that might confuse or annoy your users. (This column was designed to hold office phone extensions, but no data has been entered in this column in the database.) Visual Studio makes it easy to detect such a shortcoming and tailor the grid's output so that the unused column is not shown.
Click the Close button to close the Preview Data dialog box.

Now you'll remove the empty Extension column from the grid.

Remove a column from a data grid view object

With the DataGridView Tasks list still open, click the Edit Columns command.

You see the following dialog box:

You can use the Edit Columns dialog box to add or remove columns from those displayed by the data grid view object. (As you'll learn later in the chapter, you also use this dialog box to change the properties of the InstructorsDataGridView object.) Right now you want to delete the unused Extension column.

NOTE
Although you are removing the Extension column from the data grid, it still exists in the underlying Students.mdb database.
Click the Extension column in the Selected Columns list box.
Click the Remove button.

Visual Studio removes the column from the list.
Click OK to confirm your change.

The InstructorsDataGridView object appears again, but without the Extension column. You now have more room on the form to display database information.
Resize the InstructorsDataGridView object so that it takes up less space.

Your form looks similar to the following:
Click the Save All button to save your changes.

You've previewed and customized your table using database tools. Now you'll run the program to see what the grid looks like at run time. You'll also learn how to sort records in a data grid view object.

Manage a data grid view object at run time

Click the Start Debugging button.

Visual Studio runs your project in the IDE. The Instructors database table appears within the data grid view object, just as you configured it. Your form looks something like this:

The program statement in the Form1_Load event procedure that populated the grid with information from the Instructors table looks like this:
```
Me.InstructorsTableAdapter.Fill(Me.StudentsDataSet.Instructors)
```
This line was added to your program by Visual Studio when you dragged the Instructors table to the form from the Data Sources window.

Each row in the grid represents a record of data from the Instructors table in the database. Scroll bars are provided so that you can view any records or columns that aren't immediately visible. This is a handy ease-of-use feature that comes automatically with the DataGridView control.
Scroll down the list of records to view all nine rows, which represent instructor data for a college or university.
Reduce the size of the InstructorID column by placing the pointer between the InstructorID and Instructor column headings and dragging the column border to the left.

When you place the pointer between the column headings, it changes to a resizing handle. You can resize columns at run time because the data grid view object's Allow-UserToResizeColumns property is by default set to True. If you want to prevent resizing, you can set this property to False.
Return the InstructorID column to its original width.

When a data grid view object is filled with data, you can also take advantage of the DataGridView control's sorting feature.
Click the Instructor column heading.

The grid is sorted alphabetically by instructor name. (Barr, Adam is now first.) Your form looks something like this:

When database records are sorted, a sorting column, or key, is required—you establish this key by clicking the heading of the column on which you want to base the sort. The DataGridView control provides visual identification for the current sort key—a tiny arrow to the right of the column header. If the sort order is currently an ascending alphabetical (A–Z) list, the arrow points up. Clicking the column heading will reverse the sort order to create a descending alphabetical (Z–A) list. The arrow acts like a toggle, so you can switch back and forth between sorting directions.
Click the Instructor column several times to see how the sort order can be switched back and forth.
Click other column headings such as InstructorID and PhoneNumber to sort the database based on those keys.
When you're finished experimenting with the scrolling, resizing, and sorting features of the DataGridView control, click the Close button on the form to stop the program.

The program closes, and the development environment returns.