Data Display and Navigation With Tables


 

The Data Source

To connect to a SQL Server database from an external application, you must use an intermediary object that would perform the link from your application to the tables of the database. The database you need to connect to is called the source.

To connect to a database, you must create a data source. Microsoft Windows (all versions) comes with an engine or wizard used to create this data source. Although there are various types of data sources you can use, one of the options provided by Microsoft is called ODBC, which stands for Open Database Connectivity. It is simply a driver or program you use to "translate" a database in a format that the needing programming environments can use.

Creating a data source is particularly easily, you just have to remember to do it. In most cases, if you are using some Microsoft development environments such as Microsoft Access or Visual Studio .NET as we will see shortly, you can skip creating an ODBC data source. Many other environments require it, including Visual Studio 6 and Borland environments, etc.

It is important to know that an ODBC data source can be created for any other database such as a Microsoft Excel workbook or a Microsoft Access database. Here are the steps to create a data source.

 

Practical Learning 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 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 Close

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 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 Close

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 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 Close
 

Copyright © 2005-2010 FunctionX, Inc.