Syntax error in SQL statement when selecting records

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'"

Answers


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.


Need Your Help

How to install a dependency from a submodule in Python?

python git-submodules distutils

I have a Python project with the following structure (irrelevant source files omitted for simplicity):

How do I read an arraylist from C# and write it to java script objects

c# javascript sql-server

I currently have an arraylist in C# which holds the my data base table (having 4 columns with 2 being dateTime objects in C#),I want to read the values of the arrayList into javascript object ,here...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.