Syntax error in SQL statement when selecting records

Question

i am trying to run the following statement in excel:

       Dim myquery As String
   myquery = "select * from batchinfo where " + "datapath='" + dpath + "' and analystname='" + aname + "' and reportname='" + rname + "' and batchstate='" + bstate + "'"

   rs.Open myquery, cn, adOpenKeyset, adLockOptimistic, adCmdTable

' deleting batchinfo and from other tables with rowid if duplicate exists

If Not rs.EOF Then
    RowId_batchinfo = rs.Fields("rowid")
    cn.Execute "delete from batchinfo where rowid=" + RowId_batchinfo
    cn.Execute "delete from calibration where rowid='" + RowId_batchinfo + "'"
    cn.Execute "delete from qvalues where rowid='" + RowId_batchinfo + "'"
End If


With rs
    .AddNew ' create a new record
    ' add values to each field in the record
    .Fields("datapath") = dpath
    .Fields("analysistime") = atime
    .Fields("reporttime") = rtime
    .Fields("lastcalib") = lcalib
    .Fields("analystname") = aname
    .Fields("reportname") = rname
    .Fields("batchstate") = bstate
    .Fields("instrument") = Instrument
    .Fields("macrowriter") = Environ$("computername")
    .Update ' stores the new record
    capture_id = .Fields(0)
End With
' get the last id

'MsgBox capture_id
rs.Close

but on the rs.Open line i am getting an error:

incorrect syntax near the keyword 'select'

what am i doing wrong?

here is what the sql statement looks like:

"select * from batchinfo where datapath='F:\MassHunter\DATA\44612_PAIN\QuantResults\44612.batch.bin' and analystname='MLABS\nalidag' and reportname='MLABS\nalidag' and batchstate='Processed'"

Small point, and not the answer to your problem, but you don't need the first + concatenation in your assignment to myquery: myquery = "select * from batchinfo where datapath='" + dpath + "' and analystname='" + aname + "' and reportname='" + rname + "' and batchstate='" + bstate + "'" — phoog
– phoog, Commented Nov 29, 2011 at 23:59
In situations like this, I find it helpful to emit the dynamically built statement. Use something like a message box, print statement, etc to output the query and then paste it into a query tool. Helps to verify whether it's a bum statement vs parameter value vs a connection issue. — billinkc
– billinkc, Commented Nov 30, 2011 at 0:03
@bill thank you, ive pasted the statement: "select * from batchinfo where datapath='F:\MassHunter\DATA\44612_PAIN\QuantResults\44612.batch.bin' and analystname='MLABS\nalidag' and reportname='MLABS\nalidag' and batchstate='Processed'" — Alex Gordon
– Alex Gordon, Commented Nov 30, 2011 at 0:04
maybe im opening it incorreclty rs.Open myquery, cn, adOpenKeyset, adLockOptimistic, adCmdTable — Alex Gordon
– Alex Gordon, Commented Nov 30, 2011 at 0:09

PaulStock · Accepted Answer · 2011-11-30 00:12:35Z

4

I think your last option, adCmdTable, is incorrect. You probably want adCmdText instead.

adCmdTable is for when the passed text is just a table name. Since you are giving a SQL statement, adCmdText is more appropriate.

answered Nov 30, 2011 at 0:12

PaulStock

11.3k9 gold badges51 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Syntax error in SQL statement when selecting records

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related