|
Imports System.Data.OleDb
Public Class Main
Dim connObj As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\Car_Showroom.mdb")
Dim commObj As New OleDbCommand
Dim myDataReader As OleDbDataReader
Dim myDataSet, myDataSet1 As New DataSet
Dim query(3) As String
Dim myAdapter As OleDbDataAdapter
Private Sub loadcombos()
myDataSet1.Clear()
connObj.Open()
''''''' Load Steering
myDataSet1.Clear()
cmbSteering.Items.Clear()
commObj = New OleDbCommand("SELECT DISTINCT Steering FROM CarDetails", connObj)
myAdapter = New OleDbDataAdapter(commObj)
myAdapter.Fill(myDataSet1, "cDetails")
cmbSteering.Text = "Select Company"
For Each ds As DataRow In myDataSet1.Tables("cDetails").Rows
cmbSteering.Items.Add(ds("Steering").ToString())
Next
''''''''Load Engine
myDataSet1.Clear()
cmbEngine.Items.Clear()
commObj = New OleDbCommand("SELECT DISTINCT Engine FROM CarDetails", connObj)
myAdapter = New OleDbDataAdapter(commObj)
myAdapter.Fill(myDataSet1, "cDetails")
cmbEngine.Text = "Select Company"
For Each ds As DataRow In myDataSet1.Tables("cDetails").Rows
cmbEngine.Items.Add(ds("Engine").ToString())
Next
'''''''' Laod Fuel
myDataSet1.Clear()
cmbFuel.Items.Clear()
commObj = New OleDbCommand("SELECT DISTINCT Fuel FROM CarDetails", connObj)
myAdapter = New OleDbDataAdapter(commObj)
myAdapter.Fill(myDataSet1, "cDetails")
cmbFuel.Text = "Select Company"
For Each ds As DataRow In myDataSet1.Tables("cDetails").Rows
cmbFuel.Items.Add(ds("Fuel").ToString())
Next
'''''''' Load Lock
myDataSet1.Clear()
cmbLock.Items.Clear()
commObj = New OleDbCommand("SELECT DISTINCT Lock FROM CarDetails", connObj)
myAdapter = New OleDbDataAdapter(commObj)
myAdapter.Fill(myDataSet1, "cDetails")
cmbLock.Text = "Select Company"
For Each ds As DataRow In myDataSet1.Tables("cDetails").Rows
cmbLock.Items.Add(ds("Lock").ToString())
Next
''''' Load Brakes
myDataSet1.Clear()
cmbBrakes.Items.Clear()
commObj = New OleDbCommand("SELECT DISTINCT Brakes FROM CarDetails", connObj)
myAdapter = New OleDbDataAdapter(commObj)
myAdapter.Fill(myDataSet1, "cDetails")
cmbBrakes.Text = "Select Company"
For Each ds As DataRow In myDataSet1.Tables("cDetails").Rows
cmbBrakes.Items.Add(ds("Brakes").ToString())
Next
connObj.Close()
End Sub
Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
myDataSet1.Tables.Clear()
Dim steer As String = cmbSteering.SelectedItem
Dim engine As String = cmbEngine.SelectedItem
Dim fuel As String = cmbFuel.SelectedItem
Dim lock As String = cmbLock.SelectedItem
Dim brakes As String = cmbBrakes.SelectedItem
myDataSet.Clear()
connObj.Open()
query(1) = "SELECT PRODID,Occurence FROM (SELECT ProdId,count(*) AS Occurence FROM (SELECT ProdId FROM CarDetails WHERE Steering = '{0}' UNION ALL SELECT ProdId FROM CarDetails WHERE Engine ='{1}' UNION ALL SELECT ProdId FROM CarDetails WHERE Fuel ='{2}' UNION ALL SELECT ProdId FROM CarDetails WHERE Brakes ='{3}' UNION ALL SELECT ProdId FROM CarDetails WHERE Lock ='{4}' ) Group By ProdID )ORDER BY Occurence DESC"
query(2) = String.Format(query(1), cmbSteering.SelectedItem, cmbEngine.SelectedItem, cmbFuel.SelectedItem, cmbBrakes.SelectedItem, cmbLock.SelectedItem)
commObj = New OleDbCommand(query(2), connObj)
myAdapter = New OleDbDataAdapter(commObj)
myAdapter.Fill(myDataSet1, "cDetails")
connObj.Close()
DataGridView1.DataSource = myDataSet1.Tables("cDetails")
End Sub
Private Sub Main_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
loadcombos()
End Sub
End Class
|
By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.
If a file you wish to view isn't highlighted, and is a text file (not binary), please
let us know and we'll add colourisation support for it.