ListBox Control

[Saam_cse]

I am working with access database and vb.net. I like to make two listBox in a form. first for product list and second for suppliers list of those corresponding products. When I click product list item corresponding suppliers of that product should be show in supplier list.Data retrive from products and suppliers table from database. I am a begainer of programming. Please help me to design the code.

[Khatri Mitesh]

Hiii I send a sample of code that use the Access Database (Test.mdb from C:) and that contain Table1 ( "Prodct" and "Supplier") fields.

Dim myConnection As New OleDbConnection()
dim oDataAdapter as dataAdapter
Dim oDataTable as DataTable

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
myconnection=new oledbconnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Test.Mdb")
oDataAdapter = New OleDbDataAdapter("Select * From Table1", myConnection)
oDataTable=New DataTable
oDataAdapter.Fill(oDataTable)
ListBox1.DataSource = oDataTable
ListBox1.ValueMember = "product"
end sub

Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged
dim sProduct as string=ListBox1.SelectedItem.ToString()
if not isNothing (sproduct) then
oDataAdapter = New OleDbDataAdapter("Select Supplier From Table1 where Product=" & sProduct & ", myConnection)
oDataTable=New DataTable
oDataAdapter.Fill(oDataTable)
ListBox2.DataSource = oDataTable
ListBox2.ValueMember = "Supplier"

end if

End Sub

may be it helps you. Thanx ~Khatri Mitesh

[John_Adams]

Hi, You need to implment the parent-child relationship between the two tables to get the desired results. You may try the following code snippet: --------------------------------------------------------------------------------------------------- BEGIN CODE Dim dc As OleDbConnection Dim connstr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Nwind.mdb;" Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim da As OleDbDataAdapter Dim dt As DataTable Dim dv As DataView dc = New OleDbConnection(connstr) da = New OleDbDataAdapter("Select * from Products", dc) dt = New DataTable da.Fill(dt) dv = New DataView(dt) Me.ListBox1.DataSource = dv End Sub Private Sub ListBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged Dim SupplierIDString = Me.C1Combo1.Columns("SupplierID").Text.ToString() Dim da As OleDbDataAdapter Dim dt As DataTable Dim dv As DataView dc = New OleDbConnection(connstr) da = New OleDbDataAdapter("Select * from Supplier where SupplierID = '" + SupplierID+ "'", dc) dt = New DataTable da.Fill(dt) dv = New DataView(dt) Me.ListBox2.DataSource = dv End Sub END CODE --------------------------------------------------------------------------------------------------- Hope this helps :).

Regards, John Adams ComponentOne LLC