3rd combo does not add data in progressive search 2

[bamauto]

Need some help,
I'm using vb2008
oledb connection
I have one table in an access database with 4 important fields to find the result of a search through combobox selection.
EX.
combobox1 query on form load fills combobox1 with YEAR selection from database.
VEHICLEDATA database has fields of YEAR, MAKE, MODEL, ENGINE, FILTERNUMBER, ENGINECAPACITY.
as you can see we are looking up oil filter numbers by
vehicle application.
User selects the YEAR first from combobox1, the next step I can't quite figure the query....
Using combobox1 click event need to query for combobox2 to load its values based on a progressive type query.
i'm not real familiar with select statement, could someone help.
Eventually i want to fill remaining comboboxes for selection
progressively.

select year, select make, select model, select engine.
this order will eventually retreive the oil filter number and engine oil capacity from the database
This is the current code:

Imports System.Data.OleDb
Public Class Form1
Inherits System.Windows.Forms.Form
Dim cn As OleDbConnection
Dim cmd As OleDbCommand
Dim dr As OleDbDataReader



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

'Load combobox1 with year data for initial start
Try
cn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\VehicleData.accdb")
cn.Open()
cmd = New OleDbCommand("SELECT DISTINCT [Year] FROM(VehicleData) ORDER BY [Year] DESC", cn)
dr = cmd.ExecuteReader
While dr.Read
ComboBox1.Items.Add(dr(0))
End While
Catch
End Try
dr.Close()
cn.Close()
'End combobox1 initial load


End Sub


End Class

 

[randy700]

Something like this?

"SELECT DISTINCT Make FROM VehicleData WHERE Year = '" & cboYear & "'"

Randy

 

[bamauto]

I did get to the second combobox by the code below, but
the third I cannot load, any ideas?

this is current code.

Imports System.Data.OleDb
Public Class Form1
Inherits System.Windows.Forms.Form
Dim cn As OleDbConnection
Dim cmd As OleDbCommand
Dim dr As OleDbDataReader



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


'Load combobox1 with year data for initial start
Try
cn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\VehicleData.accdb")
cn.Open()
cmd = New OleDbCommand("SELECT DISTINCT [Year] FROM(VehicleData) ORDER BY [Year] DESC", cn)
dr = cmd.ExecuteReader

While dr.Read
ComboBox1.Items.Add(dr(0))
End While
Catch
End Try
dr.Close()
cn.Close()
'End combobox1 initial load

TextBox1.Text = ComboBox1.Text



End Sub




Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged

'Load combobox2 with make data


Try
cn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\VehicleData.accdb")
cn.Open()
cmd = New OleDbCommand("SELECT DISTINCT [Make] FROM (VehicleData) " & " WHERE [Year] = " & ComboBox1.Text & " ORDER BY [Make] ", cn)
dr = cmd.ExecuteReader
ComboBox2.Items.Clear()
While dr.Read
ComboBox2.Items.Add(dr(0))
End While
Catch
End Try
dr.Close()
cn.Close()
TextBox1.Text = ComboBox1.Text


End Sub

Private Sub ComboBox2_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox2.SelectedIndexChanged
'Load combobox3 with model data
Try
cn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\VehicleData.accdb")
cn.Open()
cmd = New OleDbCommand("SELECT DISTINCT [Model] FROM (VehicleData) " & " WHERE [Year] = " & ComboBox1.Text & " WHERE [Make] = " & ComboBox2.Text & " ORDER BY [Model] ", cn)
dr = cmd.ExecuteReader
ComboBox3.Items.Clear()
While dr.Read
ComboBox3.Items.Add(dr(0))
End While
Catch
End Try
dr.Close()
cn.Close()


End Sub


End Class

 

[PHV]

I'd try something like this:

cmd = New OleDbCommand("SELECT DISTINCT [Model] FROM VehicleData WHERE [Year]=" & ComboBox1.Text & " AND [Make]='" & ComboBox2.Text & "' ORDER BY [Model]", cn)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

select year, select make, select model, select engine.

first use YEAR

then use YEAR & MAKE

then use YEAR, MAKE, MODEL

then...

get the pic?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[bamauto]

To PHV, that is awesome it works great,
been working on that one for a week.
THANKYOU!
I'll try to figure out the last one for engine.
But feel free to post it, Heh Heh
I owe you guys.
Thanks SkipVought.