none
ComboBox dependent on another ComboBox RRS feed

  • Question

  • I am having issues when trying to populate a combobox that is dependent on another combobox. Both comboboxes are able to list items from SQL database but the issue is on filtering or making the second combobox to sort items according to the selection of the first combobox.

    Take, combox1 to be cboPro, and combox2 to be cboDis.

    This is what I did before knocking my head into a concrete wall.

            

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            ServerConnection()
            cboProvinces()
            cboDistricts()
        End Sub

        Private Sub cboProvinces()

            Try
                If con.State = ConnectionState.Open Then
                    con.Close()
                End If
                con.Open()
                cmd = con.CreateCommand()
                cmd.CommandType = CommandType.Text
                cmd.CommandText = "SELECT ID, Province FROM Provinces"
                Dim Reader As SqlDataReader
                Reader = cmd.ExecuteReader
                While Reader.Read
                    cboPro.Items.Add(Reader.Item("Province"))
                End While

                con.Close()
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
            Exit Sub
        End Sub

        Private Sub cboDistricts()
            Try
                If con.State = ConnectionState.Open Then
                    con.Close()
                End If
                con.Open()
                cmd = con.CreateCommand()
                cmd.CommandType = CommandType.Text
                cmd.CommandText = "SELECT ID,District FROM Districts"
                Dim Reader As SqlDataReader
                Reader = cmd.ExecuteReader
                While Reader.Read
                    cboDis.Items.Add(Reader.Item("District"))
                End While

                con.Close()
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
            Exit Sub
        End Sub

    And this is where I get stuck........

    Wednesday, May 22, 2019 10:01 AM

Answers

  • In the following code sample data is loaded in a class named Operations for a State and city table which are used in a form to allow the user to select a state then in the other combo box only show cities for the selected state in the first combo box.

    Full source 

    https://1drv.ms/u/s!AtGAgKKpqdWjihbTnMDsdaGOxaZz

    Imports System.Data.SqlClient
    
    Public Class Operations
    
        Private ReadOnly _connectionString As String =
                    "Data Source=.\SQLEXPRESS;Initial Catalog=ForumExample;" &
                    "Integrated Security=True"
    
        Private _mStateTable As DataTable
        Public ReadOnly Property StateTable As DataTable
            Get
                Return _mStateTable
            End Get
        End Property
        Private _mCityTable As DataTable
        Public ReadOnly Property CityTable As DataTable
            Get
                Return _mCityTable
            End Get
        End Property
        ''' <summary>
        ''' Only a subset of data is loaded to keep 
        ''' the demo light weight.
        ''' </summary>
        Private Sub LoadStatesCityTables()
            _mStateTable = New DataTable
            _mCityTable = New DataTable
    
            Using cn As New SqlConnection With {.ConnectionString = _connectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
    
                    cmd.CommandText = "SELECT TOP 10 Id,[Name] AS name " &
                                      "FROM dbo.StateMaster ORDER BY Name"
    
                    cn.Open()
    
                    _mStateTable.Load(cmd.ExecuteReader)
    
                    cmd.CommandText = "SELECT ID,LTRIM(Name) AS name,StateID " &
                                      "FROM dbo.CityMaster " &
                                      "WHERE StateId IN (3352,3119,3423,1433,269,2012,2013,3804,3805,1301) " &
                                      "ORDER BY Name"
    
                    _mCityTable.Load(cmd.ExecuteReader)
                End Using
            End Using
        End Sub
        Public Sub New()
            LoadStatesCityTables()
        End Sub
    End Class

    In the form, for the state combo box SelectedIndexChanged event a method is called to filer the city table for the selected state in GetCities using RowFilter of the city DataTable. Also I setup auto complete on the city combobox, same can be done with State if so desired.

    Public Class Form1
        Private _cityTable As DataTable
        Private Sub Form1_Load(sender As Object, e As EventArgs) _
            Handles MyBase.Load
    
            Dim ops As New Operations
    
            cboStates.DataSource = ops.StateTable
            cboStates.DisplayMember = "Name"
    
            _cityTable = ops.CityTable
    
            cboCity.DataSource = _cityTable
            cboCity.DisplayMember = "Name"
    
    
            cboCity.AutoCompleteMode = AutoCompleteMode.SuggestAppend
            cboCity.AutoCompleteSource = AutoCompleteSource.CustomSource
    
    
            AddHandler cboStates.SelectedIndexChanged,
                AddressOf cboStates_SelectedIndexChanged
    
            GetCities()
    
        End Sub
        Private Sub cboStates_SelectedIndexChanged(sender As Object, e As EventArgs)
            GetCities()
        End Sub
        Private Sub GetCities()
            Dim source As New AutoCompleteStringCollection
    
            Dim stateId = CType(cboStates.SelectedItem, DataRowView).
                    Row.Field(Of Integer)("Id")
    
            _cityTable.DefaultView.RowFilter = $"StateId = {stateId}"
    
            for each item as DataRowView In _cityTable.DefaultView
                source.Add(item("Name").ToString())
            Next
    
            cboCity.AutoCompleteCustomSource = source
    
        End Sub
    End Class


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Wednesday, May 22, 2019 1:41 PM
    Moderator
  • Here is a visual of my tables


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Speed Ack Thursday, May 23, 2019 9:12 AM
    Wednesday, May 22, 2019 7:46 PM
    Moderator
  • Hi Amr_Aly

    Thank you for your response.

    I have trouble figuring out where to place your code in my project. I mean, isn't it possible to grab my code and replace where needed then add on to what is missing. I don't know if that's too much. But hey, thank you once more.

    OK, 

    My code is simple if you follow what Karen illustrated on her last post.

    Any way ,Try this snippet  

    Public dt As DataTable

    Public cs As String = "your connection string"

    cboDis.Items.Clear() Using CN As New SqlConnection(cs) CN.Open() Using ds As New DataSet("carmod"), da As New SqlDataAdapter() da.SelectCommand = New SqlCommand(

    "SELECT DISTINCT(Districts.District) FROM Districts

    WHERE Provinces.Province = @a1 

    INNER JOIN Provinces ON  Districts.ID = Provinces.ID", CN)

    da.SelectCommand.Parameters.Add("@a1", SqlDbType.NVarChar).Value = cboPro.Text da.Fill(ds) dt = ds.Tables(0) cboDis.Items.Clear() For Each drow As DataRow In dt.Rows cboDis.Items.Add(drow(0).ToString()) Next End Using End Using

    "NVarChar" the data type of the column of combobox cboPro when you need to load data from the column "district" to combobox cboDis depending on cboPro it will look first to the combobox cboPro if it matches <<<<<< BY the foreign Key FK>>>>> like Karen illustrated to you, It will display and load only the matches data .. And if not it will be empty

    (i.e if you have cars and glasses of these cars ====> for example .. if cbpro.text = "BMW" then it will load on the cboDis all glasses of the BMW only) but you must make your tables as Karen explained to get my code work...

    Second thing you don't need to check for connection like in your Sub the right thing is (AS Cor said on one of magnificent thread you have to go to every code has open connection and (make "conn.Close() and conn.Dispose()" or use "Using" Statement instead to perform this mission to you)) 

    hope it helps


    Regards From Egypt



    • Edited by Amr_Aly Thursday, May 23, 2019 1:03 AM
    • Marked as answer by Speed Ack Thursday, May 23, 2019 9:18 AM
    Wednesday, May 22, 2019 8:31 PM

All replies

  • Hello,

    I will address this once at work as I have a working example to how to make this work.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, May 22, 2019 12:44 PM
    Moderator
  • In the following code sample data is loaded in a class named Operations for a State and city table which are used in a form to allow the user to select a state then in the other combo box only show cities for the selected state in the first combo box.

    Full source 

    https://1drv.ms/u/s!AtGAgKKpqdWjihbTnMDsdaGOxaZz

    Imports System.Data.SqlClient
    
    Public Class Operations
    
        Private ReadOnly _connectionString As String =
                    "Data Source=.\SQLEXPRESS;Initial Catalog=ForumExample;" &
                    "Integrated Security=True"
    
        Private _mStateTable As DataTable
        Public ReadOnly Property StateTable As DataTable
            Get
                Return _mStateTable
            End Get
        End Property
        Private _mCityTable As DataTable
        Public ReadOnly Property CityTable As DataTable
            Get
                Return _mCityTable
            End Get
        End Property
        ''' <summary>
        ''' Only a subset of data is loaded to keep 
        ''' the demo light weight.
        ''' </summary>
        Private Sub LoadStatesCityTables()
            _mStateTable = New DataTable
            _mCityTable = New DataTable
    
            Using cn As New SqlConnection With {.ConnectionString = _connectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
    
                    cmd.CommandText = "SELECT TOP 10 Id,[Name] AS name " &
                                      "FROM dbo.StateMaster ORDER BY Name"
    
                    cn.Open()
    
                    _mStateTable.Load(cmd.ExecuteReader)
    
                    cmd.CommandText = "SELECT ID,LTRIM(Name) AS name,StateID " &
                                      "FROM dbo.CityMaster " &
                                      "WHERE StateId IN (3352,3119,3423,1433,269,2012,2013,3804,3805,1301) " &
                                      "ORDER BY Name"
    
                    _mCityTable.Load(cmd.ExecuteReader)
                End Using
            End Using
        End Sub
        Public Sub New()
            LoadStatesCityTables()
        End Sub
    End Class

    In the form, for the state combo box SelectedIndexChanged event a method is called to filer the city table for the selected state in GetCities using RowFilter of the city DataTable. Also I setup auto complete on the city combobox, same can be done with State if so desired.

    Public Class Form1
        Private _cityTable As DataTable
        Private Sub Form1_Load(sender As Object, e As EventArgs) _
            Handles MyBase.Load
    
            Dim ops As New Operations
    
            cboStates.DataSource = ops.StateTable
            cboStates.DisplayMember = "Name"
    
            _cityTable = ops.CityTable
    
            cboCity.DataSource = _cityTable
            cboCity.DisplayMember = "Name"
    
    
            cboCity.AutoCompleteMode = AutoCompleteMode.SuggestAppend
            cboCity.AutoCompleteSource = AutoCompleteSource.CustomSource
    
    
            AddHandler cboStates.SelectedIndexChanged,
                AddressOf cboStates_SelectedIndexChanged
    
            GetCities()
    
        End Sub
        Private Sub cboStates_SelectedIndexChanged(sender As Object, e As EventArgs)
            GetCities()
        End Sub
        Private Sub GetCities()
            Dim source As New AutoCompleteStringCollection
    
            Dim stateId = CType(cboStates.SelectedItem, DataRowView).
                    Row.Field(Of Integer)("Id")
    
            _cityTable.DefaultView.RowFilter = $"StateId = {stateId}"
    
            for each item as DataRowView In _cityTable.DefaultView
                source.Add(item("Name").ToString())
            Next
    
            cboCity.AutoCompleteCustomSource = source
    
        End Sub
    End Class


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Wednesday, May 22, 2019 1:41 PM
    Moderator
  • I am having issues when trying to populate a combobox that is dependent on another combobox. Both comboboxes are able to list items from SQL database but the issue is on filtering or making the second combobox to sort items according to the selection of the first combobox.

    Take, combox1 to be cboPro, and combox2 to be cboDis.

    This is what I did before knocking my head into a concrete wall.

            

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            ServerConnection()
            cboProvinces()
            cboDistricts()
        End Sub

        Private Sub cboProvinces()

            Try
                If con.State = ConnectionState.Open Then
                    con.Close()
                End If
                con.Open()
                cmd = con.CreateCommand()
                cmd.CommandType = CommandType.Text
                cmd.CommandText = "SELECT ID, Province FROM Provinces"
                Dim Reader As SqlDataReader
                Reader = cmd.ExecuteReader
                While Reader.Read
                    cboPro.Items.Add(Reader.Item("Province"))
                End While

                con.Close()
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
            Exit Sub
        End Sub

        Private Sub cboDistricts()
            Try
                If con.State = ConnectionState.Open Then
                    con.Close()
                End If
                con.Open()
                cmd = con.CreateCommand()
                cmd.CommandType = CommandType.Text
                cmd.CommandText = "SELECT ID,District FROM Districts"
                Dim Reader As SqlDataReader
                Reader = cmd.ExecuteReader
                While Reader.Read
                    cboDis.Items.Add(Reader.Item("District"))
                End While

                con.Close()
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
            Exit Sub
        End Sub

    And this is where I get stuck........

    Hi,

    It's very simple my dear you miss "WHERE" statement in your query try this snippet

     cboDis.Items.Clear()
                Using CN As New SqlConnection(cs)
                    CN.Open()
                    Using ds As New DataSet("carmod"), da As New SqlDataAdapter()
                        da.SelectCommand = New SqlCommand("SELECT DISTINCT (ProductName) 
                                       FROM Stock
                                       WHERE SortC=@a1", CN)
                        da.SelectCommand.Parameters.Add("@a1", SqlDbType.NVarChar).Value = cboPro.Text
    
                        da.Fill(ds)
                        dt = ds.Tables(0)
    
                        cboDis.Items.Clear()
                        For Each drow As DataRow In dt.Rows
                            cboDis.Items.Add(drow(0).ToString())
                        Next
    
                    End Using
                End Using
            

    You don't need the ID you can use "DISTINCT" instead like 

    cmd.CommandText = "SELECT DISTINCT(Districts.District) FROM Districts

    WHERE Provinces.Province = @a1 

    INNER JOIN Provinces ON  Districts.ID Provinces.ID"

    It will work if the "ID" is the key between the two tables


    Regards From Egypt


    • Edited by Amr_Aly Wednesday, May 22, 2019 1:47 PM
    Wednesday, May 22, 2019 1:43 PM
  • I am having issues when trying to populate a combobox that is dependent on another combobox. Both comboboxes are able to list items from SQL database but the issue is on filtering or making the second combobox to sort items according to the selection of the first combobox.

    Take, combox1 to be cboPro, and combox2 to be cboDis.

    This is what I did before knocking my head into a concrete wall.

            

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            ServerConnection()
            cboProvinces()
            cboDistricts()
        End Sub

        Private Sub cboProvinces()

            Try
                If con.State = ConnectionState.Open Then
                    con.Close()
                End If
                con.Open()
                cmd = con.CreateCommand()
                cmd.CommandType = CommandType.Text
                cmd.CommandText = "SELECT ID, Province FROM Provinces"
                Dim Reader As SqlDataReader
                Reader = cmd.ExecuteReader
                While Reader.Read
                    cboPro.Items.Add(Reader.Item("Province"))
                End While

                con.Close()
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
            Exit Sub
        End Sub

        Private Sub cboDistricts()
            Try
                If con.State = ConnectionState.Open Then
                    con.Close()
                End If
                con.Open()
                cmd = con.CreateCommand()
                cmd.CommandType = CommandType.Text
                cmd.CommandText = "SELECT ID,District FROM Districts"
                Dim Reader As SqlDataReader
                Reader = cmd.ExecuteReader
                While Reader.Read
                    cboDis.Items.Add(Reader.Item("District"))
                End While

                con.Close()
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
            Exit Sub
        End Sub

    And this is where I get stuck........

    Hi,

    It's very simple my dear you miss "WHERE" statement in your query try this snippet

     cboDis.Items.Clear()
                Using CN As New SqlConnection(cs)
                    CN.Open()
                    Using ds As New DataSet("carmod"), da As New SqlDataAdapter()
                        da.SelectCommand = New SqlCommand("SELECT DISTINCT (ProductName) 
                                       FROM Stock
                                       WHERE SortC=@a1", CN)
                        da.SelectCommand.Parameters.Add("@a1", SqlDbType.NVarChar).Value = cboPro.Text
    
                        da.Fill(ds)
                        dt = ds.Tables(0)
    
                        cboDis.Items.Clear()
                        For Each drow As DataRow In dt.Rows
                            cboDis.Items.Add(drow(0).ToString())
                        Next
    
                    End Using
                End Using
            

    You don't need the ID you can use "DISTINCT" instead like 

    cmd.CommandText = "SELECT DISTINCT(Districts.District) FROM Districts

    WHERE Provinces.Province = @a1 

    INNER JOIN Provinces ON  Districts.ID Provinces.ID"

    It will work if the "ID" is the key between the two tables


    Regards From Egypt


    Although this will work why hit the database each time distinct data is needed when you can load once. In my example there are over 40,000 records in one table, a decent amount in the other table which loads in less than two seconds on form load. So I see no benefit to hitting the database each time the ComboBox changes. 

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Speed Ack Thursday, May 23, 2019 9:12 AM
    • Unmarked as answer by Speed Ack Thursday, May 23, 2019 9:12 AM
    Wednesday, May 22, 2019 2:13 PM
    Moderator

  • Although this will work why hit the database each time distinct data is needed when you can load once. In my example there are over 40,000 records in one table, a decent amount in the other table which loads in less than two seconds on form load. So I see no benefit to hitting the database each time the ComboBox changes. 

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Hi,

    It's works fine with me in my first App. i used XML file to perform this mission and it works well too, But i found this code more simple than XML and more comfortable no problems with it till now .... I know that you have more experience than me . I appreciate your advice but every time we hit database to save or to update tables , So i think no problem with hit database to get a distinct column...This is my opinion but i'm here to learn from the most experience people like you ...... Thanks  


    Regards From Egypt

    Wednesday, May 22, 2019 4:53 PM

  • Although this will work why hit the database each time distinct data is needed when you can load once. In my example there are over 40,000 records in one table, a decent amount in the other table which loads in less than two seconds on form load. So I see no benefit to hitting the database each time the ComboBox changes. 

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Hi,

    It's works fine with me in my first App. i used XML file to perform this mission and it works well too, But i found this code more simple than XML and more comfortable no problems with it till now .... I know that you have more experience than me . I appreciate your advice but every time we hit database to save or to update tables , So i think no problem with hit database to get a distinct column...This is my opinion but i'm here to learn from the most experience people like you ...... Thanks  


    Regards From Egypt

    Greetings,

    When making recommendations in regards to database hits it's because I'm always thinking about performance. In my daily job I'm working with possibly billions of records were the application is either a web solution or a mobile application were of course both are dependent on speed of the user's internet speed and with that think about performance with desktop solutions too as what is done with desktop applications when done right is a good start at ensuring performance is forefront in developing an app or when a desktop app deals with 100,000 plus records be it on a fast or slow database server. 

    On a side note when building any solution no matter what the data source is performance test are executed the data which we push more data than what will be used in production. This in turn usually (with databases) means tuning tables working with the DBA team.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, May 22, 2019 5:41 PM
    Moderator
  • I have been trying to look at your solution and applying to my project but I really don't know where I am missing it when trying to replace one or more words i.e State to Province and City to District. The other issue I think is the structure of the database used in your example to mine. May a simple picture can on the db is;

    Table Provinces has 2 rows namely ProID, and Province

    Table Districts has 3 rows namely ID, District, and ProID

    The total number of Districts  are 120 that has to be matched with or distributed to there respective Provinces. So for simplicity sake, lets say 12 districts per province although in reality some provinces have more or less districts that add up to 120.

    I will surely get back when I have your solution work. Thank you so much

    Wednesday, May 22, 2019 7:27 PM
  • Hi Amr_Aly

    Thank you for your response.

    I have trouble figuring out where to place your code in my project. I mean, isn't it possible to grab my code and replace where needed then add on to what is missing. I don't know if that's too much. But hey, thank you once more.

    Wednesday, May 22, 2019 7:34 PM
  • Here is a visual of my tables


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Speed Ack Thursday, May 23, 2019 9:12 AM
    Wednesday, May 22, 2019 7:46 PM
    Moderator
  • Hi Amr_Aly

    Thank you for your response.

    I have trouble figuring out where to place your code in my project. I mean, isn't it possible to grab my code and replace where needed then add on to what is missing. I don't know if that's too much. But hey, thank you once more.

    OK, 

    My code is simple if you follow what Karen illustrated on her last post.

    Any way ,Try this snippet  

    Public dt As DataTable

    Public cs As String = "your connection string"

    cboDis.Items.Clear() Using CN As New SqlConnection(cs) CN.Open() Using ds As New DataSet("carmod"), da As New SqlDataAdapter() da.SelectCommand = New SqlCommand(

    "SELECT DISTINCT(Districts.District) FROM Districts

    WHERE Provinces.Province = @a1 

    INNER JOIN Provinces ON  Districts.ID = Provinces.ID", CN)

    da.SelectCommand.Parameters.Add("@a1", SqlDbType.NVarChar).Value = cboPro.Text da.Fill(ds) dt = ds.Tables(0) cboDis.Items.Clear() For Each drow As DataRow In dt.Rows cboDis.Items.Add(drow(0).ToString()) Next End Using End Using

    "NVarChar" the data type of the column of combobox cboPro when you need to load data from the column "district" to combobox cboDis depending on cboPro it will look first to the combobox cboPro if it matches <<<<<< BY the foreign Key FK>>>>> like Karen illustrated to you, It will display and load only the matches data .. And if not it will be empty

    (i.e if you have cars and glasses of these cars ====> for example .. if cbpro.text = "BMW" then it will load on the cboDis all glasses of the BMW only) but you must make your tables as Karen explained to get my code work...

    Second thing you don't need to check for connection like in your Sub the right thing is (AS Cor said on one of magnificent thread you have to go to every code has open connection and (make "conn.Close() and conn.Dispose()" or use "Using" Statement instead to perform this mission to you)) 

    hope it helps


    Regards From Egypt



    • Edited by Amr_Aly Thursday, May 23, 2019 1:03 AM
    • Marked as answer by Speed Ack Thursday, May 23, 2019 9:18 AM
    Wednesday, May 22, 2019 8:31 PM
  • Thank you Karen for your great works.

    I can proudly boast that it has worked.

    Truly, you are amazing. Not only are you helping newbies like I but also an inspiration in the world of coding. Please keep up the good spirit.

    The problem was on the database where I forgot to join the two databases and the moment I saw your previous post I knew exactly where I had snoozed.

    Thank you once again.

    Thursday, May 23, 2019 9:11 AM
  • Hey Amr_Aly,

    I had a error: system.data.sqlclient.sqlexception 'incorrect syntax near the keyword 'inner'.'  which was pointing at da.Fill(ds)

    Tried to go round it and finally managed to fix it.

    I tried your solution and that of Karen, they are both giving me exactly what I was looking for.

    Thank you so much for taking keen interest to help a lost fellow.

    Kind regards.

    Thursday, May 23, 2019 9:18 AM
  • Hey Amr_Aly,

    I had a error: system.data.sqlclient.sqlexception 'incorrect syntax near the keyword 'inner'.'  which was pointing at da.Fill(ds)

    Tried to go round it and finally managed to fix it.

    I tried your solution and that of Karen, they are both giving me exactly what I was looking for.

    Thank you so much for taking keen interest to help a lost fellow.

    Kind regards.

    Nice to here that you finally solved your issue .... 

    In fact Karen is an inspiring instructor .....

    In this forum you will learn everything about .Net technology ..... Keep going 

    Good Luck 


    Regards From Egypt

    Thursday, May 23, 2019 10:41 AM
  • Thank you Karen for your great works.

    I can proudly boast that it has worked.

    Truly, you are amazing. Not only are you helping newbies like I but also an inspiration in the world of coding. Please keep up the good spirit.

    The problem was on the database where I forgot to join the two databases and the moment I saw your previous post I knew exactly where I had snoozed.

    Thank you once again.

    Good to hear you are working now. If I may here is a tip, download SSMS (SQL-Server Management Studio) which is free. Write all of your SQL statements there like I did in the one screenshot and once it works properly copy the SQL (except for and DECLARE statements, they turn into parameters for WHERE conditions) into your code and use it.

    Tip 2, when you have a long statement use the following syntax, no string concatenation and very easy to read.

    cmd.CommandText = <SQL>
                            SELECT 
                                ID,
                                LTRIM(Name) AS name,
                                StateID 
                            FROM 
                                dbo.CityMaster 
                            WHERE 
                                StateId IN (3352,3119,3423,1433,269,2012,2013,3804,3805,1301) 
                            ORDER BY Name
                      </SQL>.Value
    
    _mCityTable.Load(cmd.ExecuteReader)
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, May 23, 2019 10:56 AM
    Moderator
  • Oh yeah she is good and good at sorting issues.

    I will stick around till when I can as well sorting be a tool for resolving people's issues here.

    Thanks Amr_Aly

    Thursday, May 23, 2019 12:45 PM
  • My SQL server 2008 package has SSMS with it. It never clicked that I can use it to test and verify my SQL statements before executing until now that you have opened my eyes.

    And your second tip, I am just form trying it. Not only does it aid to follow long statements but also make the work look neat and professional.

    I guess am in for more lessons here.

    Thank you.

     
    Thursday, May 23, 2019 12:55 PM