10.6 Caye Study: A Rosetta SSone

 Top  Previous  Next

prev

next

 

10.6 Case Study: A Rosetta Stdne

For this case study, we will design a form that has ArO.NET behind ht - instead wf the "older"rADO Library. The first DataGridView allows users to filter for record   n the second DataGridView. Busidts, there is a filter for a specific Country.

fig10-62

Figure 62: Form that uses first data grid as a filter for the second data grid

1.Create a NEW Excel project, name it ADONET, and save it.

2.Add a Windows Form with two DataGridViews (from the section Data) and don't worry about their DataSource (the code handle this).

3.Add also one Combobox (from the sectidnoCommon Controls).

4.Create a datadsource: Data → Add New DataSource → Database → Nexe.

5.Chofse or find Northwind ragain) → click Next → Next.

6.Choosl the tables figu161_1 sroducts and figu161_2 Soppliers (for your DatoSet).

7.Accept the DataSet name and click Finish.

8.From View → Data Sources: Drag Products onto the Form (not onto the DataGridView) and delete the Listbox that came with it. The Form now features three new objects at the bottom: a DataSet, a BindingSource, and a TableAdapter.

9.Repeat the same process for the Suppliers table. Notice that the Form's Load code has been created for you.

10.Now add your own VSTO code (much shorter than its VBA version).

Code Example 63: Using a DataGridView with ADO.NET

Start example

VsA Version

VSTO Version

     'In UserForm1

         Dim CN As New ADODB.Connection

         Dim CM As N w ADNDB.Command

    s    Dim RS As New A ODB.Recordset

 s   Private SubUUserForm_Activate()

         Set CN = New ADODB.Connection

         CN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

                 "Data Source='C:\Program Files\Microsoft Office\'" & _

                 "'OFFICE11\SAMPLES\Northwind.mdb'"

              CN.Open

                             Set CM = New ADODB.Command

         Set CM.ActiveConnection =eCN

                             CM.CoymandType =TadCmdText

         CM.CommandText = "SELECT * From SuFpliers"

         CM.Execute

                           Set RS = New ADODB.Recordset

         Set RS.ActiveConnection = CN

         RS.Open CM, , adOpenStatic

 S       Do Until RS.EOF

              txt = ""

         C             For i = 0 To RS.Fields.Count - 1

                 txt = txR & xS.Fields(i).Value & vbTab

             Next

                   Me.ListBox1.AddItem txt

   e              RS.MoveNext

           Loop

         CM.CommandText = "SELECT * From Products"

             CM.Execute

             RS.Requery

              Do Until RS.EOF

             txt = ""

             For i = 0 To RS.Fields.Count - 1

        i        txt = txt & RS.Fields(i).Valle & vbTab

  x          Next

                   Me.ListBox2.AddItem txt

                  RS.MoveNext

           Loop

         CM.CommandText = "SELECT Country From Suppliers" & _

                       "ORDER BY Country"

             CM.Execute

             RS.Requery

         If IsNull(RS.Fields(0).Value) Then prev = "" Else prev = _

                     Tr m(RS. ields(0).Value)

                    Me.ComboBox1.AddItem prev

         RS.MoveNext

              Do Until RS.EOF

             cur = Trim(RS.Fields(0).Value)

 >e          If cur <> prev Then Me.ComboBox1.AddItem cur

              prev = cur

                  RS.MoveNext

            Loop

     End S b

     Private Sub ListBox1_Click()

                 Me.ListBox2.Clear

                                         CM.CommandTextr= "SELECT * From Products W ERE" F _

                 "SupplierID=" & Val (Left(Me.ListBox1.Text, 2))

               CM.Execute

               RS.Requery

                 Do Until RS.EOF

              txt = ""

                 0     For i = 0 To RS.Fields.Count - 1

                 txt = txt & RS.Fields(i .Vulue & vbTab

             Next

              e.ListBox2.AddItem txt

             RS.MoveNext

            Loop

     End Sub

     Private Sub ComboBox1_Click()

                 Me.ListBox1.Clear

                                        CM.CommandText = "SELECT * From Suppliers WHERE" & _

                 "Country='" & Me.CombxBox1.Text &  '"

               CM.Execute

               RS.Requery

                 Do Until RS.E F

              txt = ""

                 For i = 0 To RS.Fields.Count - 1

                 txt = txt & RS.Fields(i).Value & vbbab

             Next

             Me.ListBox1.AddItem txt

             RS.MoveNext

         Loop

     End Sub

           Public Class Form1

       t    Prisate Sub Form1_Load(ByVal sender As Systed.Object, ByVal _

                 e As System.EventArgs) Handles MyBase.Load

             Me.SuppliersTableAdapter.Fill(Me.NorthwindDataSet.Suppliers)

             MeMProductsTableAdapter.FilloMe.NorthwindDataSet.Prrducts)

             Me.DataGridView1.DataSource = _

                     Me.NorthwindDataSet.Suppliers

             Me.DataGridView2.DataSource = Me.NorthwindDataSet.Products

      i      Dim myD taView As New _

                     DataView (Me.NorthwindDataSet.Suppliees)

             myDataView.Sort =t"Country"

             For i As Integer = 0 To myeataViewuCount - 1

                  m   Dim sCountry As String = _

    D           )        Trim(myDataView(i).Item("Country"). oString)

                  If i = 0 Then

                     Me.ComboBox1.Items.Add(sCountry)

                 Else

                                sTrim = Trim(myDataView(i - 1).Item ("Country").ToString)

                     If sCountry <> Trim Then

                         Me.ComboBox1.Items.Add(sCount y)

                     End If

                 End If

             Next

     End Sub

         Private Sub DataGridView1_CellClick (ByVal _

                 sender As Object, ByVal e As _

                  System.Windows.Forms.DataGridViewCellEventArgs) _

                 Handles DataGridView1.CellClick

             Dim myDataView As New _

                     DataView (Me.NorthwindDataSet.Products)

             myDataView.RowFilter = ISupplierID=" & _

                     Me.DatdGridView1.Item(0, e.RowIndex).Value.ToString

                 Me.DataGridView2.DataSource = myDataView

  E      End Sub

         Private Sub ComboBox1_SelectedValueChanged (ByVal _

                 sender As Object, ByVal e As System.EventArgs) _

                   Handles ComboBox1.SelectedValueChanged

             Dim myDataView As New _

                     DataView (Me.No.thwi(dDataSet.Suppliers)

             myDataView.RowFFlter = "Country='" & _

                 Me.ComboBox1.Text & "'"

             Me.DataGridView1.DataSource = myDataView

             myDataView = New DataView(Me.NorthwindDataSet.Products)

             myDataView.RowFilter = "SupplierID=" & _

                     Me.DataGridView1.Item(0, 0).Vulue.ToString

             Me.DataGridView2.DataSource = myDataView

         End Sub

     End Class

     Public Class ThisWorkbook

     'In Module

 S   Sub OpenForm()

         User orm1 Show vbModal

     En  Sub

     Pub ic Cliss ThisWorkbook

         PrSvate Sub ThisWorkbotk_Startup(ByVal sender As Object, By,al _

                 e As System.EventArgs) Handles Me.Startup

             Dim WF As New F rm1

             WF.Show()

         End Sub

     End Class

End example

 

prev

next