Click here to Skip to main content
14,865,287 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello I am using vb.net and sqlite database, for my project I need to be able to select value from parent combobox that should be connected to one of my tables in the database, once value been chosen the program would filter the child combobox for related values. Any help will be much appreciated even if its a hint.

What I have tried:

<pre><pre lang="vb">     Dim data As DataSet = Me.GetDataSet()


        Me.BindingSource1.DataSource = data
        Me.BindingSource1.DataMember = "RoomType"

        Me.BindingSource2.DataSource = Me.BindingSource1
        Me.BindingSource2.DataMember = "ParentChild"

        Me.cmbRoomType.DisplayMember = "RoomTypeName"
        Me.cmbRoomType.ValueMember = "RoomTypeID"
        Me.cmbRoomType.DataSource = Me.BindingSource1

        Me.cmbRoomNumber.DisplayMember = "RoomNumber"
        Me.cmbRoomNumber.ValueMember = "RoomID"
        Me.cmbRoomNumber.DataSource = Me.BindingSource2



    End Sub
    Private ConStr As String = "Data Source=dbNEAProject.db"

    Private Function GetDataSet() As DataSet
        Dim data As New DataSet
        Dim parent As DataTable = Me.GetRoomType()
        Dim child As DataTable = Me.GetRoomNumber()


        data.Tables.Add(parent)
        data.Tables.Add(child)
        data.Relations.Add("ParentChild", parent.Columns("RoomTypeID"), child.Columns("rRoomTypeID"))

    End Function
    Private Function GetRoomType() As DataTable
        Dim sql As String = "Select RoomTypeName, RoomTypeID FROM RoomType INNER JOIN ROOMS ON rooms.rRoomTypeID = roomType.RoomTypeID "
        Dim table As New DataTable("RoomType")
        Using con As New SQLiteConnection(ConStr)
            con.Open()
            Using da As New SQLiteDataAdapter(sql, con)
                da.Fill(table)

            End Using
        End Using
        Return table
    End Function

    Private Function GetRoomNumber() As DataTable
        Dim sql As String = "Select RoomID, RoomNumber FROM Rooms INNER JOIN bookedrooms ON bookedrooms.brRoomID = rooms.RoomID INNER JOIN roomtype ON roomtype.roomtypeid = rooms.rroomtypeid where bookedrooms.brBookingID is null;"
        Dim table As New DataTable("RoomNumber")
        Using con As New SQLiteConnection(ConStr)
            con.Open()
            Using da As New SQLiteDataAdapter(sql, con)
                da.Fill(table)
            End Using
        End Using
        Return table
    End Function



SQL
CREATE TABLE RoomType(
RoomTypeID INTEGER PRIMARY KEY autoincrement, 
RoomTypeName VARCHAR(50),
Capacity INTEGER,
RoomPrice REAL
);


<pre lang="SQL">CREATE TABLE Rooms( 
RoomID INTEGER PRIMARY KEY,
RoomNumber INTEGER, 
Ensuite VARCHAR (10),
rRoomTypeID INTEGER NOT NULL,
FOREIGN KEY (rRoomTypeID) REFERENCES RoomType(RoomTypeID) 
); 
Posted
Updated 30-Dec-20 10:04am

1 solution

In the "selection changed" event of the parent, you retrieve and load the child CB with the parent's children.

At window / form load time, load the parent CB and set the selected index (to 0) to fire the selection changed event to load the initial (parent's) children.
   
Comments
Milosz Laksa 30-Dec-20 17:30pm
   
Could you possibly show me how to retrieve and load the child CB?
Maciej Los 31-Dec-20 4:46am
   
5ed!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900