SQL with 4 "And" values

Databases
1

Hi all, I am not very sharp in SQL, and have been trying to figure out the a sql statement for using “And” statements.

The problem is based on four values passing from containers.

example
sql = “select item1, item2, item3, item4 fromt table_1 where” item1" = “+ Cbmbobox1.text +” and “item2” = “+ cmbbox2.text +”, and “+ item3 +”= “1”, and " + item4 + = “2”’"

any help would be appreciated.

2

first off… you have “FROMT” not “FROM” and your quotes are ALL screwed up

SQL= _
"SELECT item1,item2,item3,item4" +_
" FROM table_1 "+_
" WHERE item1='"+cbmbobox1.text+"'"+_
"   and item2='"+cmbbox2.text+"'"+_
" and item3=1 and item4=2"

Look REALL carefully at the places with ’ are used and where " is used and particularly with the are used side by side

I use the above format as it makes it MUCH easier to read and find errors like this

3

[quote=47028:@Cory Hefner]Hi all, I am not very sharp in SQL, and have been trying to figure out the a sql statement for using “And” statements.

The problem is based on four values passing from containers.

example

sql = “select item1, item2, item3, item4 fromt table_1 where” item1" = “+ Cbmbobox1.text +” and “item2” = “+ cmbbox2.text +”, and “+ item3 +”= “1”, and " + item4 + = “2”’"

any help would be appreciated.[/quote]

Dim rs As Recordset
Dim sql As String = "select item1, item2, item3, item4 fromt table_1 " _

  • “where item1 = '” + Cbmbobox1.text + "’ " _
  • “and item2 = '” + cmbbox2.text + "’ " _
  • "and item3 = " + Str(1) + " " _
  • "and item4 = " + Str(2)

rs = Database.SQLSelect(sql)

  • No commas before ‘and’
  • Don’t forget spaces between keywords, at the end of a line
  • Look at your sql statement in the debugger
  • copy it to a sqleditor like SQLiteManager and test it
4

Zut … replace fromt with from !
(why I no longer can edit posts!?)

5

You can add a TextField at the bottom of your window and place there a copy of your SQL statement / error messages returned from SQLite (when one is reported).
Do not forget to remove it / the code to write data there before shipping the product / when you know you do not need it anymore.

I use Courier (a mono proportional font) as the IDE default font, so I can watch and see multiple spaces…
When I write statements like Dave does above, I always place the space at the “end of the line” (before " +_)
I try to write SQL Statements as strings in a variable and use that variable with SQLSelect / SQLExecute.
I also add spaces to set a vertical ‘line’ of <…/…" + "…/…>

SQLcmd = "SELECT item1, item2, item3, item4 " +_ "FROM table_1 " +_ "WHERE item1 ='" + ComboBox1.Text + " '" +_ "AND item2 = '" + ComboBox2.Text + " '" +_ "AND item3 = 1 AND item4 = 2"
I hope the above will display some vertical aligments (+ for example).

At last, also like Dave, I use the SQLite commands / whatever (FROM, WHERE, AND, …) CAPITALYZED.

6

[quote=47057:@Oliver Osswald]Dim rs As Recordset
Dim sql As String = "select item1, item2, item3, item4 fromt table_1 " _

  • “where item1 = '” + Cbmbobox1.text + "’ " _
  • “and item2 = '” + cmbbox2.text + "’ " _
  • "and item3 = " + Str(1) + " " _
  • "and item4 = " + Str(2)

rs = Database.SQLSelect(sql)[/quote]

remember to use Prepared Statements so your not open to SQL Injection… :slight_smile: