Return to the first tab of frmCustomerDetails and give each text box a reasonable name (e.g. txtCustomerID, txtCity, and so on).
2.1.1. Create a Data ConnectionYou 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 buttonClicking 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 connectionAfter 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 NorthwindConnection2.1.2. Create a Data Set DeclarativelyThe 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 tableClick 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.
2.1.3. Binding Data Controls with Drag and DropReturn 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.
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 ParametersNow 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:
To begin your implementation make the Customers table available to the Welcome page. Drag the Customers table onto the form. Five controls are created:
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 [%]).
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 searchOnce 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.
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 CustomersTableAdapterThis 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 dialogClick 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.xsdIt 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
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 nameFill 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)
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( )
2.1.5. Finding More Than One MatchIf 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 recordsSelect 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
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 displayedRow, 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 |