Need help in framing SQL statements

[kavithabhaskar]

I am pretty sure that the VB.NET code is right but I am sure my SQL statements are completely wrong ..can anyone help me figure out my mistakes and as to how the SQL statements need to be framed to get the results correctly from the Access Database ?

Thansk,

Kukki

Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click

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

Dim cmd1 As New OleDbCommand

con.Open()

If (CheckBox1.Checked = True) Then

cmd1 = New OleDbCommand("select * from table1 where RAM ='< 2 GB'", con)

End If

If (CheckBox2.Checked = True) Then

cmd1 = New OleDbCommand("select * from table1 where RAM is between '2 GB' and RAM is between '3 GB'", con)

End If

If (CheckBox3.Checked = True) Then

cmd1 = New OleDbCommand("select * from table1 where RAM is between '2 GB' and RAM is between '3.25 GB'", con)

End If

If (CheckBox4.Checked) Then

cmd1 = New OleDbCommand("select * from table1 where RAM is between '1 GB' and RAM is between '3 GB'", con)

End If

If (CheckBox5.Checked = True) Then

cmd1 = New OleDbCommand("select * from table1 where RAM is between '3 GB' and RAM is between '8 GB'", con)

End If

If (CheckBox6.Checked = True) Then

cmd1 = New OleDbCommand("select * from table1 where RAM is between '3.25 GB' and RAM is between '8 GB'", con)

End If

If (CheckBox7.Checked = True) Then

cmd1 = New OleDbCommand("Select * from table1 where RAM is between '2 GB' and RAM is between '8 GB'", con)

End If

If (CheckBox8.Checked = True) Then

cmd1 = New OleDbCommand("select * from table1 where RAM is between '2.98 GB' and RAM is between '8 GB'", con)

End If

Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd1)

Try

Dim ds As DataSet = New DataSet()

da.Fill(ds, "table1")

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

Finally

con.Close()

cmd1 = Nothing

da.Dispose()

con.Dispose()

End Try

End Sub

[ratri]

When you use the BETWEEN phrase, there needs to be 2 values to chek the value between. Change your statements to :

select * from table1 where RAM is between '3.25 GB' and '8 GB'
---Notice that I have removed "RAM is between " before '8 GB'

[kavithabhaskar]

hi ratri thanks for ur reply..but i didnt really understand my mistake..sorry..can u please explain..
thanks
kavitha.

[alkeshnayak]

In This Statement ("select * from table1 where RAM ='< 2 GB'"
i think <= , >=, > ,and < only use with numeric containt not for string

[SheSaidImaPregy]

alkeshnayak is wrong on that point, as long as you have "< 2 GB" in your database, it will work. However, if you meant: =< "2 GB" then this statement will fail as you cannot use an operator (=<) with a string (text).

This statement needs to be changed:

"select * from table1 where RAM is between '2 GB' and RAM is between '3 GB'"

You cannot use "between" unless it's with integers or dates. The SQL engine cannot find the difference between GB and GB, so the statement will fail.