SQL Server Home

Data Display and Navigation With Forms


Data Navigation With Forms



Although data of a database is stored in tables, these objects don't provide a friendly environment of data navigation for casual users. While tables are perfectly normal and easier to use for you as the database developer, most users find them boring and somewhat not very rich in terms of Windows controls. For this reason, to deliver your database, you will most likely create forms that present data to users.

Because Microsoft SQL Server is (only) a database list-oriented environment, you would need an external application if you want to provide Windows controls to your database. To do this, you would need an environment that allows you to create graphical applications. There are many of them on the market. Those we are familiar with, as shown in functionx.com are Microsoft Visual Studio (Visual C++, Visual Basic, Visual J#, Visual C#), Borland C++ Builder, Borland Delphi, Microsoft Access, etc. As different as these applications are, the process of creating a Windows or database application is different by their groups. Therefore, if you plan to provide GUI support for your database, you will need to be familiar with the way your chosen environment works.

Based on my limitations (I am sure you didn't expect me to know everything or to own all possible programming environments), I will cover only those I have.

Data navigation on a form has the advantage of using a better graphical environment. While a table always displays all or most its records on one screen, a form allows you to display only one record at a time or all of them, depending on the objects you use when designing the form. This means that you can use:

  • A table-like grid to display all or most records. If you decide to use a grid, its dimensions, your screen resolution and the number of records would control the number of records you can display at a time.
  • Alternatively, you can use text-based controls that would each hold and display only one field of a particular record. In this case, you may also need to provide a way for the user to navigate from one record to another

As we will see, each environment has its advantage and disadvantage.

Data Navigation With Microsoft Access Forms

Microsoft Access appears to provide the easiest and fastest means of displaying data from a SQL Server database. Although all environments require that you create the necessary forms, Microsoft Access is equipped to generate forms "on-demand" with an easy-to-use wizard. You don't have to use the wizard; you can design your form easily anyway you like. Also, you can create a form with one click by selecting a table and then using AutoForm.

If you use the wizard and don't like part of the design made by Microsoft Access, you can switch the form to Design View and customize it. On this site, we cover all aspects of the form design with Microsoft Access.

One of the biggest advantages of using Microsoft Access is its high level of support for reports. Although the other development environments we will review support and provide reports (Borland C++ Builder and Delphi also have very good support of reports), Microsoft Access provides various options in this area.

One of the disadvantages of Microsoft Access as compared to other developments is its somewhat limited number of Windows controls, although you can access additional controls using ActiveX but these other controls are not as easy to use as those available from the Toolbox.

Practical Learning Practical Learning: Navigating Data With Microsoft Access

  1. Continuing with Microsoft Access,
  2. To generate a new form, in the Tables section of the Database window, click Customers
  3. In the Database toolbar, click the arrow of the New Object button and click AutoForm
  4. Save the form as Customers
  5. After using the form, close it by clicking its System Close button Close

Data Navigation With a Visual Studio .NET Form

Another friendly environment you can use to create a graphical database application is the Microsoft Visual Studio .NET. In reality, Visual Studio is close to SQL Server and you can think that Visual Studio .NET can easily "talk to" a SQL Server database. In fact, the ability to create a SQL Server database is completely possible inside of Visual Studio .NET, so much that you may not need to open either Enterprise Manager or the Query Analyzer to create a database.

As compared to Microsoft Access, Microsoft Visual Studio is richer in terms of Windows controls you can use easily. In fact any type of Windows Forms Controls can be used, some relatively easier, in your database. Another advantage of Visual Studio is that it performs an incredibly good job behind the scenes with its wizards, reducing the amount of code you need to write.

Practical Learning Practical Learning: Navigating Data With a Visual Studio .NET Form

  1. Continuing with your Microsoft Visual Studio .NET application, on the form, click the DataGrid control and press Delete to remove it
  2. Design the form as follows:
    Control Name Text
    Label   Customer ID:
    TextBox txtCustomerID  
    Label   Company Name:
    TextBox txtCompanyName  
    Label   Contact Name:
    TextBox txtContactName  
    Label   Contact Title:
    TextBox txtContactTitle  
    Label   Address:
    TextBox txtAddress  
    Label   City:
    TextBox txtCity  
    Label   Region:
    TextBox txtRegion  
    Label   Postal Code:
    TextBox txtPostalCode  
    Label   Country:
    TextBox txtCountry  
    Label   Phone:
    TextBox txtPhone  
    Label   Fax:
    TextBox txtFax  
    Button btnClose Close
    Button btnFirst | <
    Button btnPrevious <<
    Button btnNext >>
    Button btnLast > |
  3. Click each control then, in the Properties window, click the + button of DataBindings
  4. Click the arrow of the Text combo box and set the values as follows:
    Control Name DataBindings -> Text -> dsCustomers -> Customers
    txtCustomerID CustomerID
    txtCompanyName CompanyName
    txtContactName ContactName
    txtContactTitle ContactTitle
    txtAddress Address
    txtCity City
    txtRegion Region
    txtPostalCode PostalCode
    txtCountry Country
    txtPhone Phone
    txtFax Fax
  5. Double-click an empty area on the form to access its Load event
  6. Double-click the bottom buttons on the form from left to right and implement them as follows:
    Visual C++
    private: System::Void Form1_Load(System::Object *  sender, System::EventArgs *  e)
    private: System::Void btnClose_Click(System::Object *  sender, System::EventArgs *  e)
    private: System::Void btnFirst_Click(System::Object *  sender, System::EventArgs *  e)
     this->BindingContext->get_Item(this->dsCustomers1, S"Customers")->Position = 0;
    private: System::Void btnPrevious_Click(System::Object *  sender, System::EventArgs *  e)
     this->BindingContext->get_Item(this->dsCustomers1, S"Customers")->Position = 
     this->BindingContext->get_Item(this->dsCustomers1, S"Customers")->Position - 1;
    private: System::Void btnNext_Click(System::Object *  sender, System::EventArgs *  e)
     this->BindingContext->get_Item(this->dsCustomers1, S"Customers")->Position = 
     this->BindingContext->get_Item(this->dsCustomers1, S"Customers")->Position + 1;
    private: System::Void btnLast_Click(System::Object *  sender, System::EventArgs *  e)
     this->BindingContext->get_Item(this->dsCustomers1, S"Customers")->Position = 
     this->BindingContext->get_Item(this->dsCustomers1, S"Customers")->Count - 1;
    Visual J#
    Visual Basic
    Visual C#
  7. Execute the application to test it
  8. After using the form, close it by clicking its system Close button Close

Data Navigation With Borland C++ Builder and Delphi

Like any other programming environment, to display a SQL Server table on a VCL form, you can design it. The particularity of the VCL as compared to the Microsoft .NET Framework is that the VCL provides a set of Windows controls especially made for database support. This also allows you to use a combination of regular controls and those made for databases.

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

  1. Continuing with your Borland C++ Builder or Delphi application, on the form, click the DBGrid control and press Delete to remove it
  2. Design the form as follows (all controls, except for the BitBtn use the same DataSource: dsCustomers):
Control Caption DataField Additional Properties
Label Customer ID:    
DBEdit   CustomerID  
Label Company Name:    
DBEdit   CompanyName  
Label Contact Name:    
DBEdit   ContactName  
Label Address:    
DBEdit   Address  
Label City:    
DBEdit   City  
Label Region:    
DBEdit   Region  
Label Postal Code:    
DBEdit   PostalCode  
Label Country:    
DBEdit   Country  
Label Phone:    
DBEdit   Phone  
Label Fax:    
DBEdit   Fax  
BitBtn     Kind: bkClose
  1. On the form, click the ADOTable control and, in the Object Inspector, set its Active property to true 
  2. 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:
  3. After using the form, close it

Overview of Data Entry With Forms



Data entry on tables is usually done only by you the database developer. Users can find it boring or even irritating. An alternative is to use an external application that can provide a friendlier environment. The most regular object used to perform data entry is the form.

Forms and External Applications

Any GUI development environment you would use to create forms for data entry presents its own set of advantages and disadvantages. Nevertheless, nowadays, in this fast-paced world, the more wizards you have, the faster you can be. As it happens, some environments presents you with everything you need for the fastest development possible. That's the case for Microsoft Access.

Microsoft Access is filled with wizards to create forms and reports. Another strength of Microsoft Access' is that, if you use a wizard to create a form, you can still redesign or customize it however you want.

Borland's VCL is another very good database development environment. Without providing the powerful wizards of Microsoft Access, it can easily provide with means of performing data entry when you simply add a DBNavigation control. In fact, if you check the application we created in Lesson 3, you would see that, after we had added this control, we were able to enter records without writing a single line of code.

Microsoft Visual Studio .NET provides a very powerful programming environment for database development, as we saw also in Lesson 3. It provides good wizards you can use to specify what you want to do on a database. To perform data entry, in most cases you would have to write some code. If you are using Visual Basic, Visual C# or Visual J#, they are equipped with a wizard that allows you to create a form ready for data entry without yourself writing code. Once again, in the 2002 and 2003 release, Visual C++ .NET side was left without this wizard. Fortunately, once you know what is necessary, performing data entry can be easily done.


Copyright © 2004-2010 FunctionX, Inc.