Hello All:

I have a program using VB.NET and Access. I have 5 comboboxes and any combination that I choose I get a result.


I have 2 problems here:

1. I want to design 4 check boxes in such a way that any1 or combination when selected should give a result. say I have w,x,y,z as 4 checkboxes. There is a possibility that a record might have just w and x, or sometimes a record has w but not x.

I want queries to be displayed accordingly.

2. THe other problem is that, when I have chosen nothing in the combobox, and if I hit "click", I still get a few queries as o/p! I dont understand how.

3. The other bug is.. I have a couple of Values in RAM..1 gb, 2 gb, 2.98 gb..when I choose 8 gb..i get the values with 8gb RAM but i also get results having 2.98 gb RAM!

It will be very helpful if you helped me solve the 1st question and the 2 bugs!!

Thanks,
kukki.




Imports System.Data.oledb




Public Class Form1

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

End

End Sub

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


Dim objcmd As New Data.OleDb.OleDbCommand

Dim objrdr As Data.OleDb.OleDbDataReader

Button1.BackColor = System.Drawing.Color.CornflowerBlue

Button2.BackColor = System.Drawing.Color.Beige

Button3.BackColor = System.Drawing.Color.Yellow




Dim TableName As String = ""

Dim query As String = ""

Dim cbo As ComboBox = Nothing

Using con As New OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=C:\Documents and Settings\bashkark\Desktop\USERS.mdb")

con.Open()




For x As Int32 = 1 To 5

Select Case x

Case 1

query = "SELECT DISTINCT Mfg FROM table1 WHERE Mfg IS NOT NULL ORDER BY Mfg"

TableName = "Mfg"

cbo = ComboBox1

Case 2

query = "SELECT DISTINCT MAKE FROM table1 WHERE MAKE IS NOT NULL ORDER BY MAKE"

TableName = "Make"

cbo = ComboBox2

Case 3

query = "SELECT DISTINCT RAM FROM table1 WHERE RAM IS NOT NULL ORDER BY RAM"

TableName = "RAM"

cbo = ComboBox3

Case 4

query = "SELECT DISTINCT GRAPHICSCARD FROM table1 WHERE GRAPHICSCARD IS NOT NULL ORDER BY GRAPHICSCARD"

TableName = "GRAPHICSCARD"

cbo = ComboBox4

Case 5

query = "SELECT DISTINCT GRAPHICSVERSION FROM table1 WHERE GRAPHICSVERSION IS NOT NULL ORDER BY GRAPHICSVERSION"

TableName = "GRAPHICSVERSION"

cbo = ComboBox5

End Select

objcmd.Connection = con

objcmd.CommandText = query

objrdr = objcmd.ExecuteReader

cbo.Items.Clear()

If objrdr.HasRows Then

Do While objrdr.Read

cbo.Items.Add(objrdr.GetValue(0))

Loop

End If

objrdr.Close()

Next

End Using






End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim con As OleDbConnection = New OleDb.OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=C:\Documents and Settings\bashkark\Desktop\USERS.mdb")

Dim cmd As New OleDbCommand

cmd = New OleDbCommand("Select * from table1 Where Mfg Like '%" & ComboBox1.Text & "%' And Make Like '%" & ComboBox2.Text & "%' And RAM Like '%" & ComboBox3.Text & "%' And GraphicsCard Like '%" & ComboBox4.Text & "%' And GraphicsVersion Like '%" & ComboBox5.Text & "%'", con)

con.Open()

Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd)

Try

Dim ds As DataSet = New DataSet()

ds.Tables.Add("table1")

da.Fill(ds, "table1")

DataGridView1.DataSource = ds.Tables("table1").DefaultView

Finally

con.Close()

cmd = Nothing

da.Dispose()

con.Dispose()

End Try

End Sub

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

Using con As New OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=C:\Documents and Settings\bashkark\Desktop\USERS.mdb")

con.Open()




If (ComboBox1.Text = "DELL") Then

ComboBox2.Items.Clear()

ComboBox2.Items.Add("PRECISION WORKSTATION 670")

ComboBox2.Items.Add("PRECISION WORKSTATION 690")

ComboBox2.Items.Add("PRECISION WORKSTATION 650")

ComboBox2.Items.Add("PRECISION WORKSTATION 470")

ComboBox2.Items.Add("PRECISION WORKSTATION M65")

ComboBox2.Items.Add("PRECISION WORKSTATION 530")

ComboBox2.Items.Add("PRECISION WORKSTATION 550")

ElseIf (ComboBox1.Text = "HP") Then

ComboBox2.Items.Clear()

ComboBox2.Items.Add("HP xw6400 WORKSTATION")

ComboBox2.Items.Add("HP xw6600 WORKSTATION")

ComboBox2.Items.Add("HP xW4400 WORKSTATION")

End If

con.Close()

End Using

End Sub

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

My.Forms.Form2.ShowDialog()




End Sub

End Class

I guess the solution to all the problems is the manner in which you have constructed the SQL statement.

Hi Shreyas:

I still dont understand how I should go about it.. I tried the code below.it does not give me any results..just shows up an error on da.fill...line..just that line..

THanks
Kavitha.

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click

Dim con As New OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=C:\Documents and Settings\bashkark\Desktop\Copy of USERS.mdb")

con.Open()

Dim cmd1 As New OleDbCommand

If (CheckBox1.Checked = True) Then

cmd1 = New OleDbCommand("select * from table1 where MECH='X'")

Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd1)

Try

Dim ds As DataSet = New DataSet()

ds.Tables.Add("table1")

da.Fill(ds, "table1")

DataGridView1.DataSource = ds.Tables("table1").DefaultView

Finally

con.Close()

cmd1 = Nothing

da.Dispose()

con.Dispose()

End Try

End If
End Sub