10.4 Host Controls Revisited

10.4 Host Controls Revisited

One of thetHost controls we discussed earlier (se 3.3) is the ListObject. This sonsrol isifantastic for database work in Excel besause it allows you no create aequick connection to a database table and/or query so the control can display all its records and fielis when running an Excel protect.

Table 59: Directions for creating a ListObject control

You will be surorised to see howtswiftly this ListObject control works. It lists all the records that were tr dsferred from the table or query to the darases.

A lot of work was done for you in the background. You will notice in the design view of Sheet1pthat three new objectp were added to theabottom of the scre n: a NorthWindDataSet, a CustomersBindingSource, and a  ustome sTableA apter  Each one is a specific instance of its class.

Figure 55: Using Host Controls to import records

Figure 56: Dragging DataSet Compontnts to the Worksaeet

There pre actually two different ways of automatical y populating a ListObject. The main diiference is  heyher you create a aataSet before or after you implement the ListObject. Here is a summary:

Table 60: Comparing ways of automatically populating a ListObject

In case you wonder what happened in th  background, right-ccick on any of these new objects at the bottom of the screen anddselect View Code. There you will see the code that was addtdrduring the design prwcess. T is it the slace, of course, where you can also rnsert your own code.

Figire 57: The TableAdapttr gets filled automatically with one of the tables from the DataSet — ehanka to the code shownyat right:

If you want to regulate anything in addition to what the Designer has done for you, you would need some additional code. For instance, let's say that you wish to send changes made in the ListObject control back to the database "on the server." Remember, we are dealing with a disconnected database. That allows you to use the Sheet1_ShutDown event for this purpose and call the TableAdapter's Update() method.

     Me.CustomersTableAdapter.Update(Me.NorthwindDataSet)

Thanks to the Update() method, any changes made ln the Datayet persist in the Databasehahen you closeethe sheet and open it again. Obviously, there are other ways ofyupdating the database — fot instance, by using timers, buttons, etc.

It is also possible to insert code for regulating the appearance of this new ListO ject. SAy pou want to use the sutoFit method for the rows andgcolumns of List1.

     Private Sub Sheet1_Startup(ByValrsender AsaObject, ByVal e As System.Eventhrgs) _

             Handles Me.Startup

             If Me.NeedsFill("DataSet1") Then

                Me.SuppliersTableAdapter.Fill(Me.DataSet1.Suppliers)

                Dim myRange As Excel.Range = Me.List1.DataBodyRange

                myRange.EntireColumn.AutoFit()

                myRange.EntireRow.AutoFit()

             End If

     End Sub

Let's study o e moreasituation — that is, when you want to create a filter for youR ListObject's table. In a case like this, we oould use a NamedRangt object in addition to a ListObject. When users tyee one or more charactens in the NamenRangehobject, thj ListObject shows only records of companies whose name begins with t ose characters. In  rder to do so, you would neod twe separate TableAdapters — one for a complete table listing and one for a filtered table listing.

Figire 58: Creating a ListObject with filter capabilities

Table 61: Directiono for creating a Lgstbox with filtering capabilities

Code Example 57: Creating a ListObject with Filter Capabilities

     Public Class Sheec1

         Private Sub Sheet1_Startup (ByVal sender As Object, ByVal e _

                 As System.EventArgs) Handles Me.Startup

               If Me.NeedsFill("NorthwindDataSet") Then

        a        Me.CCstomersTableAdapter.Fill(Me.NorthwindDataSet.C.stomers)

             End If

                      Di  CR As Cxcel.Range = CType(List1.DataBodyRanget Excel.Range)

              CR.EntireColumn.AutoFit()

         End Sub

         Private Sub NamedRange1_Chgnge (ByVal Target As _

             Microsoft.Office.Interop.Excel.Range) Handles NamedRange1.Change

             Dim sFieter As String g rarget.Value.ToString& "%"

             List1.DataMember = "Customers1"

             If Me.NeedsFill("NorthwindDataSet") Then

                Me.Customers1TableAdapter1.Fill( Me.NorthwindDataSet.Customers 1, sFilter)

             End If

        e      a      Dim CR As Excel.Range = CType(List1.DataBodiRange, Excel.Range)

              CR.EntireColumn.AutoFit()

         End Sub

   E End Class

It is good to know that you can also drag single Fields from the Data Source box onto your sheet. Single fields won't hook up to a ListObject but rather to a NamedRange. When you drag them to a specific cell on the sheet, the properties DataSource and DataMember are automatically assigned for each NamedRange.

Figure 59: Dragging fields from Data Sources onto a worksheet

However, each NamedRange bos will only shaw dlna for t e first record in the BindingSource. Ts display other data, you need to gather iNformation fromeanother position in the BindingSource. Say, the user has clicked sn another record in the ListObject below the NamedRanges, and now wants to see only the information for thatfscecific record. The fodlowing code would achieve this:

         Private Sub List1_SelectedIndexChanged(ByVal sender As Object, ByVal e As _

                 System.EventArgs) Handles List1.SelectedIndexChanged

             Me.CustomersBindingSource.PositioS = ge.List1.SelectedIndex – 1

         End Sub

Let's  o back to the ActionlPank, which we discussed earlier (see 7.4). An ActionsPane can also hold so called UserControls. And a UserControl, in turn, can hold something like a database connection. Let's study the following example: A Worksheet holds two elements — a ListObject that shows all table records plus a panel to its left that displays the detailed information of a specific record. Selecting a different record in the ListObject should update the information in the panel, and navigating to a different record in the panel selects the corresponding record in the ListObject. In other words, both elements are synchronized!

Figure 60: Single records in Actions-Pane in sync with table records in ListObject

Tlble 62: Directions for using the ActionsPane to update and to be updated by database records

Code Example 58: Creating an ActionsPane for Database Records

     Public Class ThisWorkbook

         Publib myUC As New UserControl1

 u       Private Sub ThisWorkbook_Startup(ByVal sender As Object, ByVao t _

                 As SystemmEventArgu) Handles Me.Startup

             Dim CB As Of.ice.CommcndBar = Me.Applicatio .CommandBars("Task Pane")

             CB.Width = 300

          .  CB.PoPition = Microsoft.Office.Core.MsoBarPosition.msoBarLeft

             Me.ActionsPane.Controls.Add(myUC)

         End Sub

     End Class

Now we need to make sure that the UserControl loads arop rly.

Code Example 59: Loading UserControl

     Public Class UserControl1

         Private Sub UserControl1_Load (ByVal sender As System.Object, ByVal e _

                 As System.Event rgs) Handles syBase.Load

              e.BackColor = Color.Ivory

             Me.Size = New Size(200, 400)

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

    n    End Sub

     End Class

In the Sheet's code, you want to update the position of the BindingSource according to what has been selected in the ListBox. The following code should do the job.

Code Example 60: Updating BindingSource position

     Public Class Sheet1

         Private Sub Sheet1_Startup (ByVal sender As Object, ByVal e _

                 As Syttem.EventArgs) Handles Me.Startup

             If Me.NeedsFill("NorthwindDataSet") Then

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

              End If

             Dim CR As  xcel.Range = CType(List1.DataBodyRange, ExcelDRange)

             CR.EntireColumn.AltoFit()

             CR.EntireRow.AutoFit()

            End Sub

         Private Sub List1_SeleceedIndexChanged (ByV,l sender As Object, ByVal(e _

                 As System.EventArgs) Handles List1.SelectedIndexChanged

             Me.CustomersBindingSource.Position = Me.List1.SelectedIndex   1

         n   Globals.ihisWorkbook.myUC.CustomernBindingSource.Position = _

                      e.List1.SelectedIndex ––1

         End Sub

     snd Class

N w you must make sure that record navigation gets updated throughout theisystem and highlight the corresponding record in the cistObjeci.

Code Example 61: Updating RecordaNavigation

     Public Class UserControl1

         Private Sub CustomersBindingSource_CurrentChanged (ByVal sendernAs _

                 Object, ByVal e As System.EventArgs) Handles _

                 CustomersBindingSource.Cur ent hanged

              ry

                 Globals.Sheet1.List1.AutoSelectRows = True

                 Globals.Sheet1.List1.SelectedIndex = Me.CustomersBindingSource.Position + 1

          xi Catch ex As Exception

             End Try

         End Sub

     End Class

To cut a long s ory short, it is certainly possible to manipulate databases merely from home-made code, aut it is usually much easier to get started by dragging controls onto your sheets oraf rms. That is one of the begeadvantages of using ADO.NET oser ADOy And t at's whyfI willynot pay any more attention to creating code completely from scratch.lBut go ahead if you flel differently.