预期的SQL语句无效; “删除”，”插入”， [英] Invalid SQL statement expected; ‘DELETE’,’INSERT’,

问题描述

我有一个代码

  Dim Cn As New ADODB.Connection 
 
 Dim cmd As ADODB .Command 
设置cmd =新建ADODB.Command 
 Dim i As Long 
 
 Cn.ConnectionString =Provider = Microsoft.Jet.OLEDB.4.0; &安培; _ 
Data Source =& ThisWorkbook.FullName& ; &安培; _ 
扩展属性=Excel 8.0;
 Cn.Open 
 cmd.ActiveConnection = Cn 
 cmd.CommandText =DECLARE @TS_HD DOUBLE &安培; _ 
SET @TS_HD =？TS_HD; &安培; _ 
SELECT So_HD，Ngay_HD，Ten_Khach_Hang，Ma_So_Thue，Sum（Doanh_So_KT）as DoanhSo，Sum（Thue_VAT）as VAT from [BR $]& _ 
其中Ngay_HD<> null和TS_HD = @TS_HD Group by So_HD，Ma_So_Thue，Ngay_HD，Ten_Khach_Hang Order by Ngay_HD
 
设置TS_HD = cmd.CreateParameter（？TS_HD ，adDouble，adParamInput）
 cmd.Parameters.Append TS_HD 
 TS_HD.Value = 0.1'在excel中这个值是10％'
 
 Dim rst As ADODB.Recordset 
设置rst = cmd.Execute（）
 
 BRToTalKhongChiuThue（）= rst.GetRows 
 
 rst.Close 
 Cn.Close 
  

当我运行此代码VBA显示错误无效的SQL语句预期;DELETE，INSERT，
谢谢你的问题！

解决方案

您的参数化查询的语法看起来不正确。 b
$ b

  DECLARE @TS_HD DOUBLE; 
 SET @TS_HD =？TS_HD; 
 SELECT 
 So_HD 
，Ngay_HD 
，Ten_Khach_Hang 
，Ma_So_Thue 
，SUM（Doanh_So_KT）AS DoanhSo 
，SUM（Thue_VAT）AS VAT 
 FROM [BR $] 
 WHERE Ngay_HD<> NULL AND TS_HD = @TS_HD 
 GROUP BY 
 So_HD 
，Ma_So_Thue 
，Ngay_HD 
，Ten_Khach_Hang 
 ORDER BY 
 Ngay_HD 
  

我不知道提供商是否支持 DECLARE 语句。如果没有，那就是为什么你收到这个消息。我们尝试删除它：

  SELECT 
 So_HD 
，Ngay_HD 
，Ten_Khach_Hang 
，Ma_So_Thue 
，SUM（Doanh_So_KT）AS DoanhSo 
，SUM（Thue_VAT）AS VAT 
 FROM [BR $] 
 WHERE Ngay_HD& NULL AND TS_HD =？ 
 GROUP BY 
 So_HD 
，Ma_So_Thue 
，Ngay_HD 
，Ten_Khach_Hang 
 ORDER BY 
 Ngay_HD 
  WHERE Ngay_HD<> NULL AND TS_HD =？  - 参数占位符只是一个问号。
 
 
 
我很确定该声明可以正常工作。 > 
 
 
我不太确定参数如何传递：
 设置TS_HD = cmd.CreateParameter（？TS_HD，adDouble，adParamInput）
  
我想你可以放弃名称参数，它是可选的 - 一些提供者不支持命名参数。因为现在您需要命名参数，因为 Name 参数首先在 CreateParameter  
 
 
 设置TS_HD = cmd.CreateParameter（Type：= adDouble，Direction：= adParamInput）
  
另一种可能是新建并初始化它手动：
  Dim param As New ADODB.Parameter 
 With param 
 .Type = adDouble 
 .Direction = adParamInput 
 .Value = 0.01'在excel中这个值是10％真？这是1％！ 
结束
 
 cmd.Parameters.Append param 
  
 
I have a code 
Dim Cn As New ADODB.Connection

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
Dim i As Long

Cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & ThisWorkbook.FullName & ";" & _
    "Extended Properties=""Excel 8.0;"""
Cn.Open
cmd.ActiveConnection = Cn
cmd.CommandText = "DECLARE @TS_HD DOUBLE;" & _
"SET @TS_HD = ?TS_HD;" & _
"SELECT So_HD, Ngay_HD, Ten_Khach_Hang, Ma_So_Thue, Sum(Doanh_So_KT) as DoanhSo, Sum(Thue_VAT) as VAT from [BR$] " & _
"Where Ngay_HD <> null And TS_HD = @TS_HD Group by So_HD, Ma_So_Thue,Ngay_HD, Ten_Khach_Hang Order by Ngay_HD"

Set TS_HD = cmd.CreateParameter("?TS_HD", adDouble, adParamInput)
cmd.Parameters.Append TS_HD
TS_HD.Value = 0.1          'in excel this value is 10%'

Dim rst As ADODB.Recordset
Set rst = cmd.Execute()

BRToTalKhongChiuThue() = rst.GetRows

rst.Close
Cn.Close
Is that something worng when i ran this code VBA show Error "Invalid SQL statement expected; ‘DELETE’,’INSERT’,"
Thanks for see my question !
解决方案
The syntax for your parametered query doesn't look right.

DECLARE @TS_HD DOUBLE;
SET @TS_HD = ?TS_HD;
SELECT 
     So_HD
    ,Ngay_HD
    ,Ten_Khach_Hang
    ,Ma_So_Thue
    ,SUM(Doanh_So_KT) AS DoanhSo
    ,SUM(Thue_VAT) AS VAT 
FROM [BR$]
WHERE Ngay_HD <> NULL AND TS_HD = @TS_HD 
GROUP BY 
     So_HD
    ,Ma_So_Thue
    ,Ngay_HD
    ,Ten_Khach_Hang
ORDER BY 
     Ngay_HD

I'm not sure the provider supports DECLARE statements. If it doesn't, that's why you're getting this message. Let's try to remove it:
SELECT 
     So_HD
    ,Ngay_HD
    ,Ten_Khach_Hang
    ,Ma_So_Thue
    ,SUM(Doanh_So_KT) AS DoanhSo
    ,SUM(Thue_VAT) AS VAT 
FROM [BR$]
WHERE Ngay_HD <> NULL AND TS_HD = ?
GROUP BY 
     So_HD
    ,Ma_So_Thue
    ,Ngay_HD
    ,Ten_Khach_Hang
ORDER BY 
     Ngay_HD
Notice the WHERE clause, WHERE Ngay_HD <> NULL AND TS_HD = ? - the parameter placeholder is just a question mark.

I'm pretty sure that statement would work.

I'm less sure about how the parameter is passed in though:
Set TS_HD = cmd.CreateParameter("?TS_HD", adDouble, adParamInput)
I think you can drop the name parameter, it's optional - and some providers don't support named parameters. That sucks though, because now you need to name the arguments since the Name parameter is first in the signature of CreateParameter:
Set TS_HD = cmd.CreateParameter(Type:=adDouble, Direction:=adParamInput)
An alternative could be to New it up and initialize it "manually":
Dim param As New ADODB.Parameter
With param
    .Type = adDouble
    .Direction = adParamInput
    .Value = 0.01    'in excel this value is 10% '?? really? it's 1% here!
End With

cmd.Parameters.Append param


                        
这篇关于预期的SQL语句无效; “删除”，”插入”，的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！