Data Display and Navigation With Tables

Practical Learning: Creating an ODBC Data Source

1. On the taskbar, click Start -> Programs -> Administrative Tools -> Data Sources (ODBC)
2. In the ODBC Data Source Administrator property sheet, in the list, click the Add button:
    
   
3. In the Create New Data Source wizard, scroll down in the list box and click SQL Server:
    
   
4. Click Finish
5. In the Name edit box of the Create A New Data Source To SQL Server wizard, type NorthWnd and press Tab
6. In the Description edit box, type Data Navigation With Northwind
7. Click the arrow of the Server combo box and select the server where the Northwind database resides:
    
   
8. Click Next
9. Select the desired authentication. For this example, I accept the Windows NT Authentication. I also accept to connect to SQL Server with Default Settings by leaving the check box untouched:
    
   
10. Click Next
11. In the new page of the Create A New Data Source To SQL Server wizard, click the Change The Default Database To check box.
12. Once the combo box is enabled, click its arrow and select Northwind:
     
    
13. For this example, leave the Attach Database FileName check box unchecked and accept the other defaults. Click Next
14. Unless you have any other reason, accept the default (language, encryption, etc) settings of the new page:
     
    
15. Click Finish
16. On the ODBC Microsoft SQL Server Setup dialog box, click the Test Data Source button:
     
    
17. When the SQL Server ODBC Data Source Test dialog box confirms that the TESTS COMPLETED SUCCESSFULLY!, click OK:
     
    
18. On the ODBC Microsoft SQL Server Setup dialog box, click OK
19. In the User DSN property page of the ODBC Data Source Administrator dialog box, make sure that the NorthWnd data store is listed in the User Data Sources list box
20. Click OK

Data Navigation With a Microsoft Access Table 

Besides the MMC, if you have Microsoft Access, you can use it to display data from a SQL Server table. To use a SQL Server table in Microsoft Access, you must create a project (in Microsoft Access). This would require you to connect to the database.

Microsoft Access makes it easy to display data from a SQL Server table because it automatically imports the tables of the database you select. 

Practical Learning: Navigating Data With Microsoft Access

1. Start Microsoft Access
2. On the opening Microsoft Access dialog box, click the Access Database Wizards, Pages, And Projects radio button and click OK
3. In the New dialog box, click the General property page and click the Project (Existing Database) icon
    
   
4. Click OK
5. In the File New Database dialog box, specify the name as NWind
    
   
6. Click Create
7. In the Data Link Properties dialog box, make sure the Connection property page is selected. In the first combo box, select the server that holds the database
8. Specify your preferred authentication mode in the 2 section
9. In the second combo box, select Northwind
10. Click Test Connection
     
    
11. Click OK and OK
12. To display a SQL Server table, in the Tables section of the Database window, double-click Customers
     
    
13. To navigate through records, press the right arrow key twice
14. Press the down arrow key five times
15. Press Ctrl + End to move to the last record
16. Press Page Up
17. Press Ctrl + Home
18. After viewing the data, close the table by clicking its System Close button 

Data Navigation With a Microsoft Visual Studio Data Grid

Unlike Microsoft Access, Microsoft Visual Studio doesn't have a formal definition of database table. It provides only means of getting a table, through classes such as DataTable or Windows Forms controls. The most usual control used to display data in a table setting is called DataGrid. The Microsoft .NET Framework's DataGrid control resembles a table as it organizes its information in series of columns and rows.

Like Microsoft Access, to use a Microsoft SQL Server table in Visual Studio, you must create a project.

Practical Learning: Navigating Data in Visual Studio .NET With a Data Grid

1. Start Microsoft Visual Studio .NET
2. To create a new application, on the main menu, click File -> New -> Project...
3. In the Project Types section, click the language of your choice
4. In the Templates section, click Windows (Forms) Application
5. In the Name text box, type DataNavigation
    
   
6. Click OK
7. Position the mouse in the Server Explorer then expand the Servers node, the name of your Domain Controller, the SQL Servers node, the name of the server that holds the Northwind database, the Northwind node, and the Tables node
8. From the Tables node, drag Customers and drop it on the form
    
   
9. On the main menu, click Data -> Generate Dataset...
10. In the Generate Dataset dialog box, make sure the New radio button is selected. Change the name of the dataset to dsCustomers
     
    
11. Click OK
12. From the Data section of the Toolbox, click DataGrid and click the form
13. Change the control's Location to 8, 8 and set its Anchor to Top, Bottom, Left, Right
14. Change its DataSource to dsCustomers1.Customers
15. Resize it to occupy most of the client area of the form
     
    
16. Double-click an unoccupied area of the form and implement its Load event as follows:
     

    Visual C++

    Visual J#

    Visual Basic

    Visual C#

    private void Form1_Load(object sender, System.EventArgs e) { this.dataGrid1.CaptionText = "Customers Records"; this.sqlDataAdapter1.Fill(this.dsCustomers1); }

17. Execute the application to test it
     
    
18. To navigate through records, press the right arrow key twice
19. Press the down arrow key five times
20. Press Ctrl + End to move to the last record
21. Press Page Up
22. Press Ctrl + Home
23. After using the form, close it by clicking its system Close button

Data Navigation With a Borland's Database Grid

Borland provides various programming environments, two of which are C++ Builder and Delphi. We provide tutorials for Borland C++ Builder and Delphi on this site. When it comes to databases, both environments share most characteristics. Let's call it VCL the combination that allows both Borland C++ Builder and Delphi to share so many characteristics.

The level of database support provided by C++ Builder and Delphi is almost unequalled. They come with their own means of creating tables through Paradox that ships with the compilers. The version of Paradox that ships with C++ Builder and Delphi is mostly used to create tables. These tables have all the necessary characteristics and requirements of a database table. Tables created in Paradox are extremely easy to use. This means that Paradox is as close to C++ Builder and Delphi as SQL Server is to Microsoft Visual Studio .NET.

If you don't want to use Paradox (Paradox is usually considered a "desktop" database environment, like Microsoft Access), you can create a database that supports Microsoft SQL server. To do this, you would need (to have created) a data source, which we reviewed above.

To display data in a table format, the VCL (Visual Component Library) provides the DBGrid control.

Practical Learning: Data Navigation in Borland C++ Builder and Delphi

1. Start Borland C++ Builder or Delphi with the default form
2. Save the project in a new folder named DataNavigation
3. Save Unit1 as Customers and save the project as DataNavigation
4. On the Object Inspector, click Caption, type Data Navigation - Customers Records and press Enter
5. From the ADO tab of the Component Palette, click the ADOTable button and click the form
6. While the ADOTable1 component is still selected on the form, on the Object Inspector, click ConnectionString and click its ellipsis button
7. In the Form1->ADOTable1 ConnectionString dialog box, make sure the Use Connection String radio button is selected and click the Build button:
    
   
8. In the Data Link Properties dialog box, make sure the Provider property page is selected and make sure the Microsoft OLE DB Provider For ODBC Drivers is selected. Click Next
9. In the Connection property page, make sure the Use Data Source Name radio button is selected. Click the arrow of its combo box and select NorthWnd
10. Accept all defaults on the other controls and click Test Connection
11. After receiving a message stating that the Test Connection Succeeded, click OK. Then click OK on the Data Link Properties dialog box
12. Click OK on the Form1->ADOTable1 ConnectionString dialog box.
13. While the ADOTable1 component is still selected on the form, on the Object Inspector, change the following properties:
    Name: tblCustomers
    TableName: Customers
14. On the Component Palette, click the Data Access tab. Click DataSource and click the form
15. While the DataSource1 component is still selected on the form, on the Object Inspector, change the following properties:
    DataSet: tblCustomers
    Name: dsCustomers
16. From the Data Controls tab of the Component Palette, click DBGrid and click the form
17. Change its properties as follows:
    Align: alClient
    DataSource: dsCustomers
18. On the form, click the ADOTable control and, in the Object Inspector, set its Active property to true 
19. Execute the application. You may receive a warning of "Could Not Convert Variant Of Type...". In that case, click OK and press F9 to display the form:
     
    
20. To navigate through records, press the right arrow key twice
21. Press the down arrow key five times
22. Press Ctrl + End to move to the last record
23. Press Page Up
24. Press Ctrl + Home
25. After viewing the data, close the form by clicking its System Close button