none
RunnTime DataBinding RRS feed

  • Question

  • 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

    Belowis the code I am using so far.  I have used Select Case statments since this is something that makes sence to me.  

    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.  

    So the only DataBinding I need is being abel to show a list of Recipe names in a listbox  and showing the ingrediants etc. in textboxes as mentiond above.  No tabel names or so have to be shown in the listbox.  

     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 where the needed code will go.
        End Sub

    Sunday, February 9, 2020 6:26 PM

Answers

  • Hi VB Novice Hendri,

    I note that you create a new thread and ask the same question, so I will give my reply in that thread.

    Best Regards,

    Xingyu Zhao

      

    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.

    Wednesday, February 12, 2020 3:14 AM
    Moderator

All replies

  • Hi VB Novice Hendri,

    Thank you for posting here.

    I use DataGridView to show data in tables and then show the result in textboxes.

    Tables in my databases:

    School1.accdb:

    School2.accdb:

    My code:

        Private ConnectionString As String = Nothing
        Private dt As DataTable = New DataTable()
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim lst As List(Of String) = New List(Of String)() From {
                "School1",
                "School2"
            }
            CategoryComboBox.DataSource = lst
            dt.Columns.Add("ClassInfo")
            dt.Columns.Add("StudentId")
            dt.Columns.Add("StudentName")
        End Sub
        Private Sub CategoryComboBox_SelectedIndexChanged(sender As Object, e As EventArgs) Handles CategoryComboBox.SelectedIndexChanged
            TitleListBox.Items.Clear()
    
            Select Case CategoryComboBox.Text
                Case "School1"
                    ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\TestFile\\School1.accdb;"
                    ShowRecipeNames()
                    Exit Select
                Case "School2"
                    ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\TestFile\\School2.accdb;"
                    ShowRecipeNames()
                    Exit Select
            End Select
        End Sub
        Private Sub TitleListBox_SelectedIndexChanged(sender As Object, e As EventArgs) Handles TitleListBox.SelectedIndexChanged
            dt.Clear()
    
            Try
                Dim tableName As String = TitleListBox.SelectedItem.ToString()
    
                Using cn As OleDbConnection = New OleDbConnection(ConnectionString)
                    cn.Open()
                    Dim cmdText As String = "SELECT ClassInfo, StudentId, StudentName FROM " & tableName & ""
    
                    Using cmd As OleDbCommand = New OleDbCommand(cmdText, cn)
                        Dim reader = cmd.ExecuteReader()
                        dt.Load(reader)
                    End Using
    
                    DataGridView1.DataSource = dt
                End Using
    
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        End Sub
        Private Sub DataGridView1_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellClick
            If e.RowIndex >= 0 Then
                Dim row As DataGridViewRow = Me.DataGridView1.Rows(e.RowIndex)
                TextBox1.Text = row.Cells(0).Value.ToString()
                TextBox2.Text = row.Cells(1).Value.ToString()
                TextBox3.Text = row.Cells(2).Value.ToString()
            End If
        End Sub
        Public Sub ShowRecipeNames()
            Try
    
                Using myConn As OleDbConnection = New OleDbConnection(ConnectionString)
                    myConn.Open()
                    Dim dtNames As DataTable = myConn.GetSchema("Tables", New String() {Nothing, Nothing, Nothing, "TABLE"})
                    TitleListBox.Items.Clear()
    
                    For Each row As DataRow In dtNames.Rows
                        Dim tablename As String = System.Convert.ToString(row(2))
                        TitleListBox.Items.Add(tablename)
                    Next
                End Using
    
            Catch ex As Exception
                MessageBox.Show(ex.Message, "Problem Detected", MessageBoxButtons.OK, MessageBoxIcon.[Error])
            End Try
        End Sub

    Result:

    Hope it could be helpful.

    Best Regards,

    Xingyu Zhao


    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.

    Monday, February 10, 2020 10:34 AM
    Moderator
  • Is ther realy no simple way of doing this?
    Monday, February 10, 2020 2:35 PM
  • Hi VB Novice Hendri,

    I note that you create a new thread and ask the same question, so I will give my reply in that thread.

    Best Regards,

    Xingyu Zhao

      

    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.

    Wednesday, February 12, 2020 3:14 AM
    Moderator