How do i populate say combobox2 depending on a *selected* ms access database table in combobox1 without writing If statements for each and every table in the database in vb.net windows forms

Kudzanai Victor

I would want to prompt user to select a tool name from combobox1, then out automatically, combobox2 gets filled with tool description and size, which is a column in the selected table. Currently i have been using the following code, however the problem comes when i would like to update the database and add another table. This would mean adding the code in the application as well. I would like the application to be able to detect that a new tool table has been added and show that automatically without having to code again

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

' ADDING TABLE NAMES TO COMBOBOX 1 (TOOL NAME)

con.Open()

Dim schemaTable As DataTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})

For Each dr As DataRow In schemaTable.Rows

        Combobox1.Items.Add(dr.Item("TABLE\_NAME"))

Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles Combobox1.SelectedIndexChanged

'FILLING COMBOBOX 2 (TOOL DESCRIPTION AND SIZE) DEPENDING ON SELECTION IN COMBOBOX 1 (TOOL NAME)

If Combobox1.SelectedIndex = 1 Then

Combobox2.Text = ""

con.Open()

Combobox2.Items.Clear()

Dim dr As OleDbDataReader

Dim cmd As New OleDbCommand

cmd.CommandText = "Select * from 14_POUND_HAMMER"

cmd.Connection = con

dr = cmd.ExecuteReader

While dr.Read

Combobox2.Items.Add(dr.GetString(1))

End While

dr.Close()

con.Close()

ElseIf Combobox1.SelectedIndex = 2 Then

Combobox2.Text = ""

con.Open()

Combobox2.Items.Clear()

Dim dr As OleDbDataReader

Dim cmd As New OleDbCommand

cmd.CommandText = "Select * from 3_QUARTER_IMPACT_WRENCH"
cmd.Connection = con

dr = cmd.ExecuteReader

While dr.Read

Combobox2.Items.Add(dr.GetString(0))

End While

dr.Close()

con.Close()

ElseIf Combobox1.SelectedIndex = 3 Then

Combobox2.Text = ""

con.Open()

Combobox2.Items.Clear()

Dim dr As OleDbDataReader

Dim cmd As New OleDbCommand

cmd.

Lost User

Whay are you using manual code to fill the comboboxes? Use DataAdapters and DataBinding and the framework will do it for you. You can also set triggers on the database so you get notified when a new product is added.

jschell

Lots of problems here.

Kudzanai Victor wrote:

Select * from ABRASIVE_CUT_OFF_SAW

Problem 1 (most serious): The name of the tool should be kept IN a database column. It should NOT be a table. Design you are looking for is likely like this Table: Tool Columns: Id, Name, Description Table: Tool_Attribute Columns: Tool_Id, Name, Description If you need more detail for Attribute you might add one or two more columns. If more detail than that is added then you might need an additional third table. Once you do the above then getting a list of all tools consists of querying the first table. This includes name, description AND the id. Then detail for a single tool is gotten by using the id from the above list. ------------------------------------------

Kudzanai Victor wrote:

Select * from ABRASIVE_CUT_OFF_SAW

Problem 2: Don't use the asterisk. Rather explicitly name the columns that you want to retrieve.