将日期传递给MySQL查询时的VBA运行时错误 [英] VBA run-time error when passing dates to MySQL query
问题描述
我从Excel VBA连接到MySQL以获取my_stored_procedure.正常运行,除了当我使用日期过滤器作为参数传递给MySQL SP时,我收到运行时错误.
用户在两个单元格中输入日期过滤器作为开始日期和结束日期.单元格定义为dateFrom和dateTo范围.
所以我正在使用这些范围来传递给mySql rs,但是VBA会返回:
运行时错误'-2147217900(80040e14)'
您的MySQL语法有误;检查相应的手册到您的MYSQL服务器版本...'my_stored_procedure'2015-10-01',第1行的"2015-10-30".
下面是我的VBA代码:
Const ConnStr As String ="Driver = {MySQL ODBC 5.3 ANSI Driver};服务器= 0.0.0.0;数据库= db;用户=用户;密码= pw;选项= 3;端口= 3306;连接超时= 20;函数MySqlCommand(strSql As String)As Variant昏暗的conMySQL设置conMySQL = CreateObject("ADODB.Connection")设置cmd = CreateObject("ADODB.Command")conMySQL.ConnectionString = ConnStrconMySQL.Open设置cmd.ActiveConnection = conMySQLcmd.CommandText = strSqlReturnValue = cmd.ExecuteconMySQL.关闭结束功能函数MySqlRS(strSql作为字符串)作为ADODB.Recordset昏暗的conMySQL设置conMySQL = CreateObject("ADODB.Connection")设置rs = CreateObject("ADODB.Recordset")conMySQL.ConnectionString = ConnStrconMySQL.Open设置rs.ActiveConnection = conMySQLrs.Open strSql设置MySqlRS = rs结束功能_____公共子fetchReport()作为ADODB.Recordset的昏暗设置rs = MySqlRS("my_stored_procedure'"& Format(Range("dateFrom"),"yyyy-mm-dd")&','"& Format(Range("dateTo"),"yyyy-mm-dd)&"')昏暗的高清整数如果不是rs.EOF,则对于hd = 0到rs.Fields.Count-1Worksheets("data").Cells(1,hd + 1).Value = rs.Fields(hd).Name'插入列标题下一个Sheets("data").Range("A2").CopyFromRecordset rs'插入数据Sheets("data").Range("A1").CurrentRegion.Name ="rsRange"'设置数据透视表的命名范围万一ActiveWorkbook.RefreshAll结束子
这是我的SP语法:
CREATE DEFINER =`user` @`%`程序my_stored_procedure`(在fromDate日期,截止日期)开始选择 ...dateColumn> = fromDate和dateColumn< = toDate结尾
感谢您的帮助.
乔尔
我希望这会有所帮助.自从我这样做已经很长时间了.
架构
创建表myTable(id int auto_increment主键,dateColumn日期不为null,资料varchar(100)不为null);插入myTable(dateColumn,stuff)值('2014-12-21','dogs'),('2015-02-21','frogs'),('2015-07-21','snakes'),('2015-12-21','猫');
存储过程
删除过程(如果存在)"my_stored_procedure";分隔符$$创建过程`my_stored_procedure`(在fromDate日期,截止日期)开始选择 *从myTable其中dateColumn> = fromDate和dateColumn< = toDate;结尾$$定界符;
VBA
函数MySqlStoredProcRS(字符串形式的strSql)作为ADODB.Recordset昏暗的conMySQL设置conMySQL = CreateObject("ADODB.Connection")设置rs = CreateObject("ADODB.Recordset")conMySQL.ConnectionString = ConnStr'未显示,但与您在Op问题中的显示类似conMySQL.Open设置rs.ActiveConnection = conMySQLrs.CursorLocation = adUseClientrs.Open strSql,conMySQL,``adCmdText设置MySqlStoredProcRS = rs结束功能公共子fetchReport()作为ADODB.Recordset的昏暗昏暗的SQL作为字符串sql ="call my_stored_procedure('"& Format(Range("dateFrom"),"yyyy-mm-dd")&','"& Format(Range("dateTo"),"yyyy-mm-dd)&"')设置rs = MySqlStoredProcRS(sql)昏暗的高清整数如果不是rs.EOF,则对于hd = 0到rs.Fields.Count-1Worksheets("data").Cells(1,hd + 1).Value = rs.Fields(hd).Name'插入列标题下一个Sheets("data").Range("A2").CopyFromRecordset rs'插入数据Sheets("data").Range("A1").CurrentRegion.Name ="rsRange"'设置数据透视表的命名范围万一ActiveWorkbook.RefreshAll结束子
输出图片(点击蓝色小东西后)
外卖店
我想探索对新函数 MySqlStoredProcRS
的更改,使用 call
的ADODB.RecordSet的处理以及将参数包装在调用字符串中的括号./p>
祝你好运.
From Excel VBA i connect to MySQL to fetch my_stored_procedure. Working fine except that when I use date filter as parameters to pass to MySQL SP i receive a run-time error.
The date filters are entered by the user in two cells as start-date and end-date. The cells are defined defined as ranges dateFrom and dateTo.
So I'm using these ranges to pass on to mySql rs, but VBA throws back:
Run-time error '-2147217900 (80040e14)'
You have an error in MySQL syntax; check the manual that corresponds to your MYSQL server version... 'my_stored_procedure '2015-10-01', '2015-10-30'' at line 1.
Below is my VBA code:
Const ConnStr As String = "Driver={MySQL ODBC 5.3 ANSI Driver};Server=0.0.0.0;Database=db;User=user;Password=pw;Option=3;PORT=3306;Connect Timeout=20;"
Function MySqlCommand(strSql As String) As Variant
Dim conMySQL
Set conMySQL = CreateObject("ADODB.Connection")
Set cmd = CreateObject("ADODB.Command")
conMySQL.ConnectionString = ConnStr
conMySQL.Open
Set cmd.ActiveConnection = conMySQL
cmd.CommandText = strSql
ReturnValue = cmd.Execute
conMySQL.Close
End Function
Function MySqlRS(strSql As String) As ADODB.Recordset
Dim conMySQL
Set conMySQL = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
conMySQL.ConnectionString = ConnStr
conMySQL.Open
Set rs.ActiveConnection = conMySQL
rs.Open strSql
Set MySqlRS = rs
End Function
_____
Public Sub fetchReport()
Dim rs As ADODB.Recordset
Set rs = MySqlRS("my_stored_procedure '" & Format(Range("dateFrom"), "yyyy-mm-dd") & "' ,'" & Format(Range("dateTo"), "yyyy-mm-dd") & "' ")
Dim hd As Integer
If Not rs.EOF Then
For hd = 0 To rs.Fields.Count - 1
Worksheets("data").Cells(1, hd + 1).Value = rs.Fields(hd).Name 'insert column headers
Next
Sheets("data").Range("A2").CopyFromRecordset rs 'insert data
Sheets("data").Range("A1").CurrentRegion.Name = "rsRange" 'set named range for pivot
End If
ActiveWorkbook.RefreshAll
End Sub
And here is my SP syntax:
CREATE DEFINER=`user`@`%` PROCEDURE `my_stored_procedure`
(
in fromDate date,
in toDate date
)
BEGIN
SELECT ...
WHERE dateColumn >= fromDate AND dateColumn <= toDate
END
Any help is appreciated.
Joel
I hope this is helpful. Been a long time since I have done this.
Schema
create table myTable
( id int auto_increment primary key,
dateColumn date not null,
stuff varchar(100) not null
);
insert myTable(dateColumn,stuff) values
('2014-12-21','dogs'),('2015-02-21','frogs'),('2015-07-21','snakes'),('2015-12-21','cats');
Stored Proc
drop procedure if exists `my_stored_procedure`;
DELIMITER $$
CREATE PROCEDURE `my_stored_procedure`
(
in fromDate date,
in toDate date
)
BEGIN
SELECT *
from myTable
WHERE dateColumn >= fromDate AND dateColumn <= toDate;
END
$$
DELIMITER ;
VBA
Function MySqlStoredProcRS(strSql As String) As ADODB.Recordset
Dim conMySQL
Set conMySQL = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
conMySQL.ConnectionString = ConnStr ' not shown but like yours in Op Question
conMySQL.Open
Set rs.ActiveConnection = conMySQL
rs.CursorLocation = adUseClient
rs.Open strSql, conMySQL, , , adCmdText
Set MySqlStoredProcRS = rs
End Function
Public Sub fetchReport()
Dim rs As ADODB.Recordset
Dim sql As String
sql = "call my_stored_procedure ('" & Format(Range("dateFrom"), "yyyy-mm-dd") & "' ,'" & Format(Range("dateTo"), "yyyy-mm-dd") & "')"
Set rs = MySqlStoredProcRS(sql)
Dim hd As Integer
If Not rs.EOF Then
For hd = 0 To rs.Fields.Count - 1
Worksheets("data").Cells(1, hd + 1).Value = rs.Fields(hd).Name 'insert column headers
Next
Sheets("data").Range("A2").CopyFromRecordset rs 'insert data
Sheets("data").Range("A1").CurrentRegion.Name = "rsRange" 'set named range for pivot
End If
ActiveWorkbook.RefreshAll
End Sub
Picture of Output (after clicked Blue thingie)
The Takeaway
I guess explore the changes to the new function MySqlStoredProcRS
, the handling of the ADODB.RecordSet, using call
, and parentheses wrapping the parameters in the call string.
Good luck.
这篇关于将日期传递给MySQL查询时的VBA运行时错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!