预期的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
WHERE Ngay_HD<> NULL AND TS_HD =? - 参数占位符只是一个问号。
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
我很确定该声明可以正常工作。 >
我不太确定参数如何传递:
设置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 ofCreateParameter
: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屋!