Error[ReSolved]

Printable View

Error[ReSolved]

I am trying to query a records from a SQL Server Dbase and i get a syntax error. Where am i goin wrong
Thanks

VB Code:

Private Sub Command1_Click()
    rsrequests.Open "SELECT * FROM Requests WHERE((DateReq) Between '" & Format(DTPicker1.Value, "dd mmm yyyy") & "' AND '" & Format(DTPicker2.Value, "dd mmm yyyy") & "'AND '" & (Combo1.Text) & "')", Cn, adOpenStatic, adLockReadOnly

Nov 11th, 2004, 11:19 AM
Mike Hildner

That's pretty tough to look at. Why don't you turn on the profiler and check out the sql that's getting sent. Profiler is a great debugging tool.
Nov 11th, 2004, 11:22 AM
brucevde

The last criteria needs to be part of an equation. You currently have

& "'AND '" & (Combo1.Text) & "')", cn

Add the database field you are checking against the combobox.

Also, insert a space between the single quote and the last And
Nov 11th, 2004, 11:39 AM
Paradox

I'm Kinda newbie to SQL ope u don mind writin da code.

The err msg still remains.

Incorrect syntax near ')'

Thanks
Nov 11th, 2004, 11:58 AM
Mike Hildner

brucevde did a lot more than me, but still I suggest you check out the sql that is actually getting sent to the DB. Best way is to use the profiler, if you know how. If not, well, you'll want to learn how to do it (it'll save you tons of time), but you could also set a string to the sql you're going to send, then display the string that will be sent.
Nov 11th, 2004, 12:19 PM
Paradox

Thanks for your help. let me try using the profiler and see how it goes
Nov 11th, 2004, 12:44 PM
brucevde

Does Combo1.Text contain a single quote?

Post your modified query.
Nov 11th, 2004, 12:59 PM
Paradox

Yeah it has a single quote

Code:

Private Sub Command1_Click() rsrequests.Open "SELECT * FROM Requests WHERE((DateReq) Between '" & Format(DTPicker1.Value, "dd mmm yyyy") & "' AND '" & Format(DTPicker2.Value, "dd mmm yyyy") & "'AND ' " & (Combo1.Text) & " ' )", Cn, adOpenStatic, adLockReadOnly
Nov 11th, 2004, 01:06 PM
brucevde
You need to replace the single quote in Combo1.Text with two single quotes. I prefer to use a variable to hold the sql statement but thats up to you
VB Code:
```
Dim strSQL as String
 
strSQL = "SELECT * FROM Requests WHERE " _ 
& "((DateReq) Between '" & Format(DTPicker1.Value, "dd mmm yyyy")  _ 
& "' AND '" & Format(DTPicker2.Value, "dd mmm yyyy")  _ 
& "' AND SomeDatabaseField = '" & Replace(Combo1.Text,"'","''") & "')", 
 
rsrequests.Open strSQL, Cn, adOpenStatic, adLockReadOnly
```
The statement sent to SQL Server should look something like

SELECT * FROM Requests WHERE ((DateReq) Between '11 Nov 2004' AND '11 Nov 2004' AND SomeDatabaseField = 'O''Brien')
Nov 12th, 2004, 12:44 AM
Paradox

brucevde and Mike Hildner Thanks alot.
Dont know how much you guys helped me out. You are appreceiated