选择记录时SQL语句中的语法错误 [英] Syntax error in SQL statement when selecting records

问题描述

我正在尝试在excel中运行以下语句:

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

但是在rs.Open行上，我得到一个错误:

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

incorrect syntax near the keyword 'select' 

我在做什么错了?

这是sql语句的样子:

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

推荐答案

我认为您的最后一个选择adCmdTable是错误的.您可能需要adCmdText代替.

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

adCmdTable用于当传递的文本只是一个表名时.由于您要提供SQL语句，因此adCmdText更合适.

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

这篇关于选择记录时SQL语句中的语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！