2.1. Adding Data to the Customer Page


Return to the first tab of frmCustomerDetails and give each text box a reasonable name (e.g. txtCustomerID, txtCity, and so on).

This chapter picks up on the code from the previous chapter. If you download the source, however, you'll find that we've created folders with snapshots that represent the state of the code at the end of each chapter.


2.1.1. Create a Data Connection

You need a connection to the database. Before you begin, open the Data Sources Window (Data Show Data Sources). From this floating, dockable window there are a number of ways to create a new data source. The two simplest are either to click the hyperlink Add New Data Source ... or to click the Add New Data Source button , as shown in Figure 2-1.

Figure 2-1. Add New Data Source button


Clicking this button opens the Data Source Configuration wizard. On the first tab, you can indicate the type of DataSource you wish to use; in this case, you'll pick Database. The next step is to create a Connection to do that data source.

You can use an existing connection (if you have one) or click New Connection... to create a new connection object. This opens the connection Properties modal dialog box. The first step is to select the server, the second step is to choose between Windows Integrated Security (trusted connection) or a specific database user ID. The third and final step is to choose the database. Be sure to click the Test Connection button to ensure that the connection is working, as shown in Figure 2-2.

Figure 2-2. Configure and test the connection


After you click OK and click the Next button, you will have the option to save the connection information to the application configuration file. Click the check box and name the connection NorthwindConnection, as shown in Figure 2-3.

Figure 2-3. Save NorthwindConnection


2.1.2. Create a Data Set Declaratively

The next step in the wizard asks you to choose the database objects you'll connect to through this Database connection object. You can connect to any number of tables , stored procedures, views, and functions of the Northwind dataset. For this project, expand the tables category and select the Customers table, as shown in Figure 2-4 (you can expand the table to select just certain fields, but in this case you want them all).

Figure 2-4. Choose the Customers table


Click Finish and the Wizard will create a selection statement for selecting all the fields from the Customers table within the Northwind database. Notice that the Data Sources window now reflects your NorthwindDataSet, with the Customers table beneath the ADO.NET Object Model.

The NorthwindDataSet is an instance of a DataSet object, the heart of the ADO.NET object model. While you don't need to understand the ADO.NET object model in detail to work with data (the controls will hide a lot of the internal plumbing from you), it can be useful to have an idea of what these different objects are and what they are for. This is shown in Table 2-1.

Table 2-1. Principal ADO.NET objects

ADO.NET object

Description

DataSet

A disconnected subset of the entire database. In most environments, there are a limited number of connections to the database, such connections are said to be "expensive" and there is strong incentive to use connections to the database as briefly as possible. By making the dataset "disconnected" you are able to work on it at length without tying up a connection to the database. (Periodically, you'll reconnect the DataSet to its parent database, update the database with changes you've made to the DataSet, and update the DataSet with changes in the database made by other processes.)

A DataSet is composed of DataTable objects as well as DataRelation objects.

DataTable

The DataTable, which represents a database table, can be created programmatically or as a result of a query against the database. The DataTable has a number of public properties, including the Columns collection, which returns the ColumnsCollection object, which in turn consists of DataColumn objects. Each DataColumn object represents a column in a table.

DataRelation

Represents the relation between two columns (typically of different tables).

DataView

Enables you to create different views of the data in a table, allowing for sorting and filtering.

DataConnector

Acts as a data source for controls and mediates between the control and its own data source (typically a dataset). Simplifies binding to a single table within a dataset that may have many tables.

DataNavigator

Provides services for navigating through data bound to a control.

TableAdapter

Designer-generated components that connect a DataSet object to the underlying data source. Similar to DataAdapters (below) but strongly typed (a unique class defined to work only with the fields selected for a specific database object), and can contain multiple queries to support multiple tables from a data source. Typically generated by the Data Source Configuration Wizard.

DataAdapter

Decouples the DataSet from the underlying structure of the physical database.

Command

Command objects are used for Selection, Update, Deletion, and Insertion.

Connection

Connection objects represent a connection from your application to the database.


2.1.3. Binding Data Controls with Drag and Drop

Return to frmCustomerDetails. The first step is to associate the text boxes on the form with the data in the database. You can do this with the DataBindings property, but it is much easier to drag columns from the Customers table onto the appropriate text box. To do so, expand the Customers table in the NorthWindDataSet (within the Data Sources window) and drag the CustomerID column onto the CustomerID text box. Then do the same with the remaining text boxes.

Let's also add text boxes for the Phone and Fax. Click on Phone in the Data Sources window. Notice that there is a drop-down menu available. Click on the drop-down menu and notice that you may choose a TextBox, ComboBox, Label, and so on, to display this data. Set it to TextBox and drag it into place.

Two controls are placed onto your form: a label and a text box. Reposition the label (to align it) and rename the text box to txtPhone. Do the same with the Fax.

Three controls are added to your tray: a NorthWindDataSet control, a CustomersBindingSource, and a CustomersTableAdapter. These are used to facilitate binding the data from the data source to the actual controls on your form.


You do not want the users setting the Customer ID. You can disable the text box by setting the Enabled property to False.

2.1.4. Querying with Parameters

Now return to the Welcome page (see Figure 1-1). The Find button works great if you want to find the first customer. However, you want slightly more complex behavior. When the user clicks on the Find button, the value in the Company Name field will be examined and used as a search criterion against all the Company Names in the database. Here is the behavior we want to achieve with our code:

  • If there is exactly one match, the customer data is displayed.

  • If there is more than one match, however, you want to display a list of all the matching customer names, so the user can pick the desired company.

  • If no names match, you want to inform the user that no matches were found.

To begin your implementation make the Customers table available to the Welcome page. Drag the Customers table onto the form. Five controls are created:

  • NorthWindDataSet

  • CustomersBindingSource

  • CustomersTableAdapter

  • CustomersBindingNavigator

  • CustomersDataGridView (visible on form)

Delete the CustomersDataGridView from the form; we won't be using it.

The next task is to write a complete handler for the Customers Find button. The code we'll use is shown in Example 2-1.

Example 2-1. Customers Group Find button
 Private Sub btnCustomersFind_Click( _     ByVal sender As System.Object, _     ByVal e As System.EventArgs) _     Handles btnCustomersFind.Click     Dim filteredView As Data.DataView = _         New Data.DataView(NorthwindDataSet.Customers)     filteredView.RowFilter = "CompanyName Like '%" + txtCustomerName.Text + "%'"     Dim rowsFound As Int32 = filteredView.Count     Select Case rowsFound         Case 0  ' no records found              MessageBox.Show( _              "No matching records found", _              "No records found", _              MessageBoxButtons.OK, _              MessageBoxIcon.Exclamation)         Case 1             frmCustomerDetails.CompanyNameParameter = _                filteredView.Item(0)("CompanyName")             frmCustomerDetails.Show(  )         Case Else             dlgPickMatchingCompany.FilteredView = filteredView             Dim result As DialogResult             result = dlgPickMatchingCompany.ShowDialog(  )             If result = DialogResult.OK Then                 Dim rowView As Data.DataRowView                 rowView = dlgPickMatchingCompany.lbMatching.SelectedItem                 Dim companyName As String = rowView.Row.Item("CompanyName")                 frmCustomerDetails.CompanyNameParameter = companyName                 frmCustomerDetails.Show(  )             End If     End Select End Sub 

Let's take this step by step.

Open the handler for the customers Find button in the Customers group box and replace it with the following code (you'll need to add the statement Imports System.Data to the top of the code file):

 Private Sub btnCustomersFind_Click( _     ByVal sender As System.Object, _     ByVal e As System.EventArgs) _     Handles btnCustomersFind.Click     Dim filteredView as DataView = new DataView(NorthwindDataSet.Customers)     filteredView.RowFilter = "CompanyName Like '%" + txtCustomerName.Text + "%'" 

The first line creates a DataView object based on the Customers table within your data set. As noted in Table 2-1, this view can be used to filter which data is presented.

The second line sets the filter on the DataView to match the partial customer name entered by the user (surrounded by wildcard characters [%]).

This search will find any CustomerName that contains the text the user enters. If you want to match any name that starts with the text the user enters, remove the first wildcard (%).


Thus, if the user enters "BO" and clicks find, four companies will be found: Bon app', Bottom-Dollar Markets, Lacorne d'abondance, and The Cracker Box. If you change the query to starts with (by removing the first wildcard), you will match only the first two of these.

2.1.4.1. One row found in search

Once you have a filtered view, you can find out how many matches have been returned:

 Dim rowsFound As Int32 = filteredView.Count 

If rowsFound has a value of exactly one, only one company matches your search criterion, so you want to open the frmCustomerDetails page and display the information for that one company.

By inspecting the database you will find that if you query for the name Around, only one company matches (Around the Horn). That makes a good test case.


To implement the case of finding a single company, the first step is to return to the code page of the frmCustomerDetails.vb page and notice that the frmCustomerDetails_Load method fills the CustomersTableAdapter with the entire Customers table within the NorthwindDataSet:

 Private Sub frmCustomerDetails_Load(ByVal sender As System.Object, ByVal e As  System.EventArgs) Handles MyBase.Load     Me.CustomersTableAdapter.Fill(Me.NorthwindDataSet.Customers) End Sub 

You want to fill only the subset of that table that matches the criterion (Company name). So you'll create a parameterized query, which you'll use to pass in the name of the company.

To do so, return to the Designer view of frmCustomerDetails. Click on the smart tab for CustomersTableAdapter and click Add Query, as shown in Figure 2-5.

Figure 2-5. Add Query to CustomersTableAdapter


This will open the Search Criteria Builder. Select the data source, and create a new query named FillByCompanyName. Modify the query to add a where clause, as shown in the sample on the dialog, and as illustrated in Figure 2-6.

Figure 2-6. Search Criteria Builder dialog


Click OK. A FillByCompanyNameToolStrip is created for you. Delete it; you will not be filling in the company name interactively on this form.

Double-click on NorthWindDataSet.xsd in the Solution explorer. This will open the NorthWindDataSet.xsd designer and reveal that you now have two methods to fill the CustomersTableAdapter, as shown in Figure 2-7.

Figure 2-7. NorthWindDataSet.xsd


It is this new method (FillByCompanyName) that you'll want to call, with the name of the company as the second parameter (the first parameter is the table).

To do so, you need a way for the Welcome page to pass the name of the Company to the Customer Details page. The easiest way is to create a public property in the Customer Details form:

 Private m_CompanyNameParameter As String Public WriteOnly Property CompanyNameParameter(  ) As String     Set(ByVal value As String)         m_CompanyNameParameter = value     End Set End Property 

Properties typically have a get and a set accessor. Since there is never a reason to get the m_CompanyNameParameter value from outside this class, this property has been marked WriteOnly and provides only a Set accessor.


You can now modify the frmCustomersDetails_Load event to call the new parameterized query, which has four overloads. The one you want takes a DataTable (Customers) and the string representation of the parameter (the company name), as shown in Figure 2-8.

Figure 2-8. Fill by company name


Fill this in with the name of the table and with the name of the company (set by the Welcome form).

 Me.CustomersTableAdapter.FillByCompanyName( _    Me.NorthwindDataSet.Customers, _    Me.m_CompanyNameParameter) 

Don't forget to comment out or delete the original call to the unparameterized fill method.

 'Me.CustomersTableAdapter.Fill( _   'Me.NorthwindDataSet.Customers) 


Return to Welcome.vb. Still working on the assumption that you found exactly one matching record, you can now set the CompanyNameParameter in the frmCustomerDetails page, and call Show on frmCustomerDetails, which will fire the Load event. This will in turn load the parameterized query and display your company.

 frmCustomerDetails.CompanyNameParameter = filteredView.Item(0)("CompanyName") frmCustomerDetails.Show(  ) 

Let's unpack the line. The database returned, you'll remember, a filteredView with just one row. That row is stored in the Item property, which is a collection. You want the first (and only) entry, which is at offset 0. That returns a Table row. Within that table row you want the column whose name is CompanyName. It is that value that you are setting to the CompanyNameParameter property of the frmCustomerDetails page.


2.1.5. Finding More Than One Match

If the find returns zero records, you'll post a MessageBox and return the user to the Welcome form. If you match one record, you'll invoke frmCustomerDetails, as shown earlier. If you find more than one record, you'll need to open a modal dialog box that will display all the matching records and let the user pick, as shown in Example 2-2.

Example 2-2. Matching records
 Select Case rowsFound     Case 0  ' no records found         MessageBox.Show( _         "No matching records found", _         "No records found", _         MessageBoxButtons.OK, _         MessageBoxIcon.Exclamation)     Case 1         frmCustomerDetails.CompanyNameParameter = _            filteredView.Item(0)("CompanyName")         frmCustomerDetails.Show(  )     Case Else         dlgPickMatchingCompany.FilteredView = filteredView         Dim result As DialogResult          result = dlgPickMatchingCompany.ShowDialog(  )         If result = DialogResult.OK Then             Dim rowView As Data.DataRowView             rowView = dlgPickMatchingCompany.lbMatching.SelectedItem             Dim companyName As String = rowView.Row.Item("CompanyName")            frmCustomerDetails.CompanyNameParameter = _              filteredView.Item(0)("CompanyName")            frmCustomerDetails.Show(  ) 

To do this, create a form, dlgPickMatchingCompany.vb, with a list box and two buttons: OK and Cancel, as shown in Figure 2-9.

Change the name of the first button to btnOK and the second to btnCancel. Set the DialogResult property for the first to OK and for the second to Cancel. The form itself has a DialogResult property that can be queried after the dialog is closed. By setting the button's DialogResult property, you instruct that button to set the form's DialogResult property when the button is clicked. The net effect is that when the dialog is closed you can test the DialogResult to see if the OK button was clicked.

Figure 2-9. dlgPickMatchingCompany dialog


VB6 NOTE: In Visual Basic 6.0 and earlier versions, it was often difficult to determine whether the Cancel button was pressed to close a window or terminate a dialog. In Visual Basic 2005, however, the DialogResult property makes this easy, since, if the DialogResult property of buttons contained on the form is properly set, it reflects the button used to cancel the dialog.


You'll want to pass the value of the DataView to this dialog box so that you can bind the list box to the filtered view, and thus display all the companies that match the user's input. To do so, create a property in the dlgPickMatchingCompany class:

 Private my_filteredView As Data.DataView Public WriteOnly Property FilteredView(  ) As Data.DataView     Set(ByVal value As Data.DataView)         my_filteredView = value     End Set End Property 

When you load the form, you'll bind the list box to this view by setting its DataSource property. You'll also set the DisplayMember of the list box to the column you want to display.

 Private Sub dlgPickMatchingCompany_Load(ByVal sender As System.Object, _    ByVal e As System.EventArgs) Handles MyBase.Load     Me.lbMatching.DataSource = Me.my_filteredView     Me.lbMatching.DisplayMember = "CompanyName" End Sub 

If you match two or more companies (e.g., you enter bo into the find control), the Case Else (shown soon) is triggered and the dlgPickMatchingCompany dialog is displayed, as shown in Figure 2-10.

If the user picks a company (e.g., The Cracker Box) and then presses the OK button, the dialog is closed (the OK button does this automatically). The result returned to the calling code (Welcome.btnCustomersFind_Click) is DialogResult.OK

At that point, you can ask the list box for the selected item (which will be of type DataRowView). You may then ask the DataRowView for its Row property, and within the

Figure 2-10. dlgPickMatchingCompany dialog displayed


Row, you may ask for the Item property, indexing it by column name. What you get back is a string that you can use to set the CompanyNameParameter property of the frmCustomerDetails form, which you then display. The complete code for the Welcome Page's Customer Find button (in the customer's group) is shown once again in Example 2-3.

Example 2-3. CustomersFind button Click event handler
 Private Sub btnCustomersFind_Click( _     ByVal sender As System.Object, _     ByVal e As System.EventArgs) _     Handles btnCustomersFind.Click     Dim filteredView As Data.DataView = _         New Data.DataView(NorthwindDataSet.Customers)     filteredView.RowFilter = "CompanyName Like '%" + txtCustomerName.Text + "%'"     Dim rowsFound As Int32 = filteredView.Count     Select Case rowsFound         Case 0  ' no records found              MessageBox.Show( _              "No matching records found", _              "No records found", _              MessageBoxButtons.OK, _              MessageBoxIcon.Exclamation)         Case 1             frmCustomerDetails.CompanyNameParameter = _                filteredView.Item(0)("CompanyName")             frmCustomerDetails.Show(  )         Case Else             dlgPickMatchingCompany.FilteredView = filteredView             Dim result As DialogResult             result = dlgPickMatchingCompany.ShowDialog(  )             If result = DialogResult.OK Then                 Dim rowView As Data.DataRowView                 rowView = dlgPickMatchingCompany.lbMatching.SelectedItem                 Dim companyName As String = rowView.Row.Item("CompanyName")                 frmCustomerDetails.CompanyNameParameter = companyName                 frmCustomerDetails.Show(  )             End If     End Select End Sub 



Programming Visual Basic 2005
Programming Visual Basic 2005
ISBN: 0596009496
EAN: 2147483647
Year: 2006
Pages: 162
Authors: Jesse Liberty

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net