tow comboboxes filling another one

[george8]

Hi everybody >>

Thanks to this forum >>
I need your help please .. My problem in the Combo Box and SQL query ..

I have three comboboxes linked to an Access 2007 database

first one "Department"
second one "Year"
third one "ClassRoom"

in third combobox I need display the classroom of department and year which select in "first comobobox" and "second comobobox"
for example :
I have 3 department
math first year and have classroom1 and classroom 2
Physics second year and have classroom1
History third year and have classroom3 and classroom 4
when user select math from first combobox
and firt year from second combobox then the third combobox must fill classroom1 , classroom2

I know my English is poor but I hope you understand me ... thank you

my code

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
ComboBox1.DataSource = GetTable("select DepID,DepName from Department")
ComboBox1.DisplayMember = "DepName"
ComboBox1.ValueMember = "DepID"
ComboBox1.DataSource = GetTable("select YearID,YearName from Year")
ComboBox1.DisplayMember = "YearName"
ComboBox1.ValueMember = "YearID"
End Sub

Public Function GetTable(ByVal query As String) As System.Data.DataTable
        Dim adp As New OleDb.OleDbDataAdapter(query, "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\HamakDatabase.mdb")
        Dim dt As New Data.DataTable
        adp.Fill(dt)
        Return dt
    End Function
Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
        Dim r As DataRowView = ComboBox1.SelectedItem
        Dim rr As DataRowView = ComboBox2.SelectedItem
        If Not IsNothing(r) Then
            ComboBox3.DataSource = GetTable("select CRID , CRname From ClassRoom Where YearID = " & r("YearID")&" AND DepID ="&rr(DepID))
        End If
    End Sub
Private Sub ComboBox2_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox2.SelectedIndexChanged

End Sub

my code work but result of query fault
the third combobox display all classroom in the department which selected in first combobox and don't care to year which select in second combobox

I use Visual Basic 2010

[hericles]

That is because you are firing the code in the SelectedIndexChanged event of the first combo box and included the selectedIndex of the second combo box before it has been changed.

Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
        Dim r As DataRowView = ComboBox1.SelectedItem  // has been set (fired this code section)
        Dim rr As DataRowView = ComboBox2.SelectedItem // hasn't been set yet, is still default value
        If Not IsNothing(r) Then
            ComboBox3.DataSource = GetTable("select CRID , CRname From ClassRoom Where YearID = " &     r("YearID")&" AND DepID ="&rr(DepID))
        End If
    End Sub

You need to address the error in the logic there. You can either make the second combo populate when the first item is selected (forcing the user to select them in order) and then fire the code in the second combo boxes selectedIndexChanged event or include a button which uses the set values of the two combo boxes.
Trying to assume that both combo boxes have been selected is a mistake.

[george8]

Thank you my friend for your advice .. can you suggest new code for one of you suggest

[george8]

hericles >>> thank you man
I try one of your suggest
I made Button3 which enable second combobox after user select first one
but it's doesn't work

there is my code

Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
        
        ComboBox2.Enabled = False
       
    End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        ComboBox2.Enabled = True
        
    End Sub

Private Sub ComboBox2_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox2.SelectedIndexChanged
        Dim rr As DataRowView = ComboBox1.SelectedItem
        Dim r As DataRowView = ComboBox2.SelectedItem

        If Not IsNothing(r) Then
            ComboBox3.DataSource = GetTable("select CRID , CRname From ClassRoom Where YearID = " & r("YearID") & "DepID = " & rr("DepID"))
        End If
End Sub
Private Sub ComboBox3_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox3.SelectedIndexChanged

    End Sub

[M.Waqas Aslam]

well i am working in mssql , so i am writing this code for mssql , but u can change the sqldataadapter with oledbdataadapter etc , and if this code not solve your prob then it will surly give you an idea .

'code for department combo
dim con as new sqlconnection("connection string")
con.open()
dim da as new sqldataadapter("select departmentID,departmentname from dept",con)
dim dt as new datatable
da.fill(dt)
comboDepartment.datasource= dt
combodepartment.displaymember="departmentname"
combodepartment.Valuemember="departmentID"
con.close()

'code for year combo
dim con as new sqlconnection("connection string")
con.open()
dim da as new sqldataadapter("select yearID,year from YearTable",con)
dim dt as new datatable
da.fill(dt)
comboYear.datasource= dt
comboYear.displaymember="year"
comboYear.Valuemember="yearID"
con.close()

'code for class combo
dim con as new sqlconnection("connection string")
con.open()
dim da as new sqldataadapter("select ClassID,ClassName from Classes where yearID="&val( comboyear.selectedvalue) & " and departmentID="& val(combodepartment.selectedvalue),con)
dim dt as new datatable
da.fill(dt)
comboClass.datasource= dt
comboClass.displaymember="ClassName"
comboClass.valuemember="ClassID"
con.close
'NOTE:as i type all this here so may be there are some errors , so please dont mind if there is. 2nd thing if val(comboyear.selectedvalue) is not working properly then
'please use val(comboyear.selectedvalue.tostring()) and same with combodepartment.

Best Regards

[george8]

Mr.waqasaslammmeo
thanks very much for your support
but your solve doesn't work with me
can you suggest another one please

[george8]

Thank you all >>>>
My problem solved >>>>
I have used the button pressed for enable combo class ... In other words, I took the value of the other ComboBoxes and then press the button to activate the Combo Box which do queries ... This idea has worked with me and succeded ....

I'm really grateful for your support
Thank you very much >>>>

code which I used ...

Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
        ComboBox3.Enabled = False
    End Sub

Private Sub ComboBox2_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox2.SelectedIndexChanged
        ComboBox3.Enabled = False
    End Sub

Private Sub ComboBox3_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox3.SelectedIndexChanged

    End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        ComboBox3.Enabled = True
        Dim r As DataRowView = ComboBox1.SelectedItem
        Dim rc As DataRowView = ComboBox2.SelectedItem
        If Not IsNothing(r) Then
            ComboBox3.DataSource = GetTable("select CRname , CRID From ClassRoom Where (DepID = " & r("DepID") & ") AND YearID = " & rc("YearID"))
        End If
    End Sub