locked
Simple DataBinding RRS feed

  • Question

  • Please help.

    I have multipal Access DataBases, eatch with multipal Tabels.  This is for an Virtual Recipebook in Afrikaans.

    Here is what I need,  I need one of the Tabels lets say Eiergeregte in the DataBases Hoofgeregte to show all the Recipe Names contained in a Listbox.  When one of that Recipes is selected all of its ingredients must show in a series of TextBoxes.  

    I'm using Visual Studeo 2013


    Thursday, February 6, 2020 12:58 PM

All replies

  • Hi,
    set your parameters in following demo. 

    Imports System.Data.OleDb
    Imports System.IO
    
    Public Class Form1
    
      Private lbl1 As New Label With {.Dock = DockStyle.Top, .Text = "Select Database"}
      Private WithEvents cb1 As New ComboBox With {.Dock = DockStyle.Top}
      Private lbl2 As New Label With {.Dock = DockStyle.Top, .Text = "Select Table"}
      Private WithEvents cb2 As New ComboBox With {.Dock = DockStyle.Top}
      Private lbl3 As New Label With {.Dock = DockStyle.Top, .Text = "Select Recipe"}
      Private WithEvents cb3 As New ComboBox With {.Dock = DockStyle.Top}
      Private lbl4 As New Label With {.Dock = DockStyle.Top, .Text = "Recipe data"}
      Private tb4 As New TextBox With {.Dock = DockStyle.Top}
    
      Private Sub Form72_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Me.Controls.AddRange(New Control() {tb4, lbl4, cb3, lbl3, cb2, lbl2, cb1, lbl1})
        LoadDataBaseList()
      End Sub
    
      Private Sub LoadDataBaseList()
        For Each f In My.Computer.FileSystem.GetFiles("c:\temp")
          If Path.GetExtension(f) = ".accdb" Then
            cb1.Items.Add(f)
          End If
        Next
      End Sub
    
      Private Sub cb1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cb1.SelectedIndexChanged
        Try
          Dim cn = GetConnection(cb1.SelectedItem.ToString)
          Dim dt = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
          cb2.Items.Clear()
          For Each row As DataRow In dt.Rows
            cb2.Items.Add(row("TABLE_NAME").ToString)
          Next
        Catch ex As Exception
          MsgBox(ex.Message)
        End Try
      End Sub
    
      Private Sub cb2_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cb2.SelectedIndexChanged
        Try
          Using da As New OleDbDataAdapter($"SELECT * FROM {cb2.SelectedItem}", Me._cn)
            Dim dt As New DataTable
            da.Fill(dt)
            cb3.Items.Clear()
            For Each row As DataRow In dt.Rows
              cb3.Items.Add(row("RecipeName").ToString)
            Next
          End Using
        Catch ex As Exception
          MsgBox(ex.Message)
        End Try
      End Sub
    
      Private Sub cb3_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cb3.SelectedIndexChanged
        Try
          Using da As New OleDbDataAdapter($"SELECT * FROM {cb2.SelectedItem} WHERE ID = {cb3.SelectedItem}", Me._cn)
            Dim dt As New DataTable
            da.Fill(dt)
            Dim row = dt.Rows(0)
            tb4.Text = row("RecipeField").ToString
          End Using
        Catch ex As Exception
          MsgBox(ex.Message)
        End Try
      End Sub
    
      Private _filename As String
      Private _cn As OleDbConnection
      Private Function GetConnection(filename As String) As OleDbConnection
        If filename <> Me._filename Then
          If _cn IsNot Nothing Then _cn.Close()
          _cn = New OleDbConnection($"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={filename};Persist Security Info=True")
          _cn.Open()
        End If
        Return _cn
      End Function
    
    End Class

    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks


    Friday, February 7, 2020 5:45 AM
  • Hi,

    What did you accomplish about this?

    Could you share us a screen shot about your current result or code?

    When providing the code please indicate the methods and events also.

    Thanks.

    Best Regards,

    Julie


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, February 7, 2020 6:37 AM
  • Here is the code in question. I have used Select Case statments so I do not have to use Casses witsh I dont understand and dont have the time to learn. I am sick, loosing my memory fast, I will not be coding after this Virtual Recipebook is finish as a gift to my wife.  Hope I'm not sounding rood or unthankfull.  

    I was previosly told that Tabel and Colum Names must not contain "&" or spaces, I made the changes this leaves me with Tabel Names that does not display well, the Select Case statments solves that problem for me.  

     Private Sub CategoryComboBox_SelectedIndexChanged(sender As Object, e As EventArgs) Handles CategoryComboBox.SelectedIndexChanged
            'The ComboBox contain an unbound list of witch the user can make a selection. 
            'The selection made is then matched with a corospondeg DataBase via this Select Case code block.
            TitleListBox.Items.Clear()
            Select Case CategoryComboBox.Text
                Case Is = "Smaaklik Tuisgemaakte Drankies"
                    ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=M:\My Documents\My Resepte\Drankies.accdb"
                    TitleListBox.Items.Add("Koue Drankies")
                    TitleListBox.Items.Add("Melk Drankies")
                    TitleListBox.Items.Add("Warm Drankies")
    
                Case Is = "Voorgeregte & Ander Ligte Eetes"
                    ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=M:\My Documents\My Resepte\Voorgeregte.accdb"
                    'No need to display Tabel names since this DataBase has only one Tabel.
                    ActiveTabel = "VoorgeregteLigteEetes"
                    Call ShowRecipeNames()
    
                Case Is = "Hoofgeregte"
                    ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=M:\My Documents\My Resepte\Hoofgeregte.accdb"
                    TitleListBox.Items.Add("Eiergeregte")
                    TitleListBox.Items.Add("Heerlike Sop Resepte")
                    TitleListBox.Items.Add("Pasta, Bobotie, Breyani, Tamatie en Kerie")
                    TitleListBox.Items.Add("Viegitariese Eetes & Groente Geregte")
                    TitleListBox.Items.Add("Vleis Geregte")
                    TitleListBox.Items.Add("Pluimvee Geregte - Hoender, Eend ens.")
                    TitleListBox.Items.Add("Vis & ander Seekos Geregte")
    
                Case Is = "Watertand Nageregte & Poedings"
                    ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=M:\My Documents\My Resepte\Nageregte.accdb"
                    TitleListBox.Items.Add("Koud voorberyde Nageregte (Poedings)")
                    TitleListBox.Items.Add("Warm voorberyde Nageregte")
    
                Case Is = "Kraakvars Slaai Resepte"
                    ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=M:\My Documents\My Resepte\Slaaie.accdb"
                    'No need to display Tabel names since this DataBase has only one Tabel.
                    ActiveTabel = "SlaaiResepte"
                    Call ShowRecipeNames()
    
                Case Is = "Souse & Marinades"
                    ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=M:\My Documents\My Resepte\Souse.accdb"
                    'No need to display Tabel names since this DataBase has only one Tabel.
                    ActiveTabel = "SouseMarinades"
                    Call ShowRecipeNames()
    
                Case Is = "Geurige Tuisgebak"
                    ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=M:\My Documents\My Resepte\Gebak.accdb"
                    TitleListBox.Items.Add("Beskuit & Mosbolietjies")
                    TitleListBox.Items.Add("Brood & Broodrolietjies")
                    TitleListBox.Items.Add("Koek, Kolwyntjies & Ander Lekerneie")
                    TitleListBox.Items.Add("Muffens & Scones")
                    TitleListBox.Items.Add("Pannekoeke, Plaatkoekies & Tortillas")
                    TitleListBox.Items.Add("Southappies & Peeselkossies")
                    TitleListBox.Items.Add("Terte & Pasteie")
    
                Case Is = "Gebottelde & Ingelegde Lekerneie"
                    ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=M:\My Documents\My Resepte\Gebottelde.accdb"
                    TitleListBox.Items.Add("Gepeekelde Lekerneie")
                    TitleListBox.Items.Add("Ingelegde Vrugte & Konfyte")
                    TitleListBox.Items.Add("Smeere & Bladjang")
    
                Case Is = "Wenke & Boererate vir in en om die Huis"
                    ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=M:\My Documents\My Resepte\Wenke.accdb"
                    TitleListBox.Items.Add("Kook & Bak Wenke")
                    TitleListBox.Items.Add("Huishoudelike Wenke")
    
            End Select
        End Sub
    
    
      Private Sub TitleListBox_SelectedIndexChanged(sender As Object, e As EventArgs) Handles TitleListBox.SelectedIndexChanged
    
            Select Case TitleListBox.SelectedItem
                'Smaaklik Tuisgemaakte Drankies
                Case Is = "Koue Drankies"
                    ActiveTabel = "KoueDrankies"
                Case Is = "Melk Drankies"
                    ActiveTabel = "MelkDrankies"
                Case Is = "Warm Drankies"
                    ActiveTabel = "WarmDrankies"
                    'Hoofgeregte
                Case Is = "Eiergeregte"
                    ActiveTabel = "Eiergeregte"
                Case Is = "Heerlike Sop Resepte"
                    ActiveTabel = "SopResepte"
                Case Is = "Pasta, Bobotie, Breyani, Tamatie en Kerie"
                    ActiveTabel = "PastaBobotieBreyaniTamatieKerie"
                Case Is = "Viegitariese Eetes & Groente Geregte"
                    ActiveTabel = "ViegitarieseEetesGroenteGeregte"
                Case Is = "Vleis Geregte"
                    ActiveTabel = "VleisGeregte"
                Case Is = "Pluimvee Geregte - Hoender, Eend ens."
                    ActiveTabel = "PluimveeGeregte"
                Case Is = "Vis & ander Seekos Geregte"
                    ActiveTabel = "VisSeekosGeregte"
                    'Watertand Nageregte & Poedings
                Case Is = "Koud voorberyde Nageregte (Poedings)"
                    ActiveTabel = "KoueNageregte"
                Case Is = "Warm voorberyde Nageregte"
                    ActiveTabel = "WarmNageregte"
                    'Geurige Tuisgebak
                Case Is = "Beskuit & Mosbolietjies"
                    ActiveTabel = "BeskuitMosbolietjies"
                Case Is = "Brood & Broodrolietjies"
                    ActiveTabel = "BroodBroodrolietjies"
                Case Is = "Koek, Kolwyntjies & Ander Lekerneie"
                    ActiveTabel = "KoekAnderLekerneie"
                Case Is = "Muffens & Scones"
                    ActiveTabel = "MuffensScones"
                Case Is = "Pannekoeke, Plaatkoekies & Tortillas"
                    ActiveTabel = "PannekoekePlaatkoekiesTortillas"
                Case Is = "Southappies & Peeselkossies"
                    ActiveTabel = "SouthappiesPeeselkosies"
                Case Is = "Terte & Pasteie"
                    ActiveTabel = "TertePasteie"
                    'Gebottelde & Ingelegde Lekerneie
                Case Is = "Gepeekelde Lekerneie"
                    ActiveTabel = "GepeekeldeLekerneie"
                Case Is = "Ingelegde Vrugte & Konfyte"
                    ActiveTabel = "IngelegdeVrugteKonfyte"
                Case Is = "Smeere & Bladjang"
                    ActiveTabel = "SmeereBladjang"
                    'Wenke & Boererate vir in en om die Huis
                Case Is = "Kook & Bak Wenke"
                    ActiveTabel = "KookBakWenke"
                Case Is = "Huishoudelike Wenke"
                    ActiveTabel = "HuishoudelikeWenke"
            End Select
    
            Call ShowRecipeNames()
    
     End Sub
    
    
        Sub ShowRecipeNames()
            'This is the kind of code I am looking for short and simple, no Casses I dont understand,  
            'I know this code dont work, that is where I need help.  
            TitleListBox.DataBindings.Add("Text", HoofgeregteDataSetBindingSource, ("Eiergeregte"))
        End Sub


    Friday, February 7, 2020 8:41 AM