VBA 如何将 DATE 作为参数传递给存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20404605/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA How do you pass DATE as parameter to stored procedure
提问by user3022875
Currently I pass the date parameter as varchar and convert because I got errors with passing a date parameter. I still get errors but its a conversion parameter.
目前我将日期参数作为 varchar 传递并进行转换，因为我在传递日期参数时出错。我仍然收到错误，但它是一个转换参数。
Sub GetPositions() 'xdate As Date
Dim sSQL As String
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Set cmd = New ADODB.Command
Set prm = New ADODB.Parameter
Sheets("Positions").Select
Range("a2:bb999999").ClearContents
Set cn = New ADODB.Connection
cn.CommandTimeout = 300000
cn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=GlobalR;Data Source=SWP"
Dim d As Date
d = "2013/12/03"
cmd.ActiveConnection = cn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "Get9599Delta"
cmd.NamedParameters = True
Set prm = cmd.CreateParameter("@date", adVarChar, adParamInput, 12)
cmd.Parameters.Append prm
cmd.Parameters("@date").Value = "'12/3/2013'"
Set rs = New ADODB.Recordset
rs.Open cmd.Execute ''''I AM GETTING AN ERROR ON THIS LINE THAT READS
''''''''''' CONVERSION FAILED WHEN CONVERTING DATETIME TO CHARACTER STRING
Cells(2, 1).CopyFromRecordset rs
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
here is my stored proc
这是我的存储过程
alter PROC Get9599Delta (@date varchar(12))
AS
DECLARE @d DATETIME
SET @d = CONVERT(DateTime, @date, 104) --convert the varchar to a date
Any idea why I am having this conversion issue?
知道为什么我会遇到这个转换问题吗？
回答by Kenny Evitt
I'm using Excel 2016 MSO (16.0.8730.2175) 64-bit and my "VBAProject" is referencing "Microsoft ActiveX Data Objects 6.1 Library". I'm running SQL Server Express (64-bit) version 11.0.3156.0 (2012).
我使用的是 Excel 2016 MSO (16.0.8730.2175) 64 位，我的“VBAProject”引用了“Microsoft ActiveX Data Objects 6.1 Library”。我正在运行 SQL Server Express（64 位）版本 11.0.3156.0 (2012)。
My SQL Server database stored procedure has a date parameter with a datetimedata type. The stored procedure does notcontain SET NOCOUNT ON.
我的 SQL Server 数据库存储过程有一个datetime数据类型的日期参数。该存储过程并没有包含SET NOCOUNT ON。
Here's (an edited version of) my code:
这是我的代码（的编辑版本）：
Sub ExecuteStoredProcedure(date_ As Date)
Dim connection_ As New ADODB.Connection
connection_.Open "Provider=SQLOLEDB;Data Source=.\SQLEXPRESS;Initial Catalog=not_my_real_database_name;Integrated Security=SSPI"
Dim command_ As New ADODB.Command
With command_
.ActiveConnection = connection_
.CommandType = adCmdStoredProc
.CommandText = "dbo.NotMyRealProcedureName"
.Parameters.Append .CreateParameter("@Date", adDBDate, adParamInput, , date_)
.Execute
End With
End Sub
If you're having similar problems, try commenting or removing all of the code after the connection_.Open ...call. I definitely had problems because of the connection string passed to that call and the error info shown by VBA is so minimal that it was not immediately obvious that my problem wasn't related to the date parameter in my VBA code.
如果您遇到类似问题，请尝试在connection_.Open ...调用后注释或删除所有代码。由于传递给该调用的连接字符串，我确实遇到了问题，而且 VBA 显示的错误信息非常少，以至于我的问题与 VBA 代码中的日期参数无关，这并不是很明显。
回答by user3022875
The problem was solved by putting SET NOCOUNT ONat the top of my stored procedure!
问题通过放在SET NOCOUNT ON我的存储过程的顶部解决了！
回答by greko
try this:
尝试这个：
.Parameters.Append .CreateParameter("@date", adDate, adParamInput, , )
@ is optional msdn ( command.CreateParameter (Name, Type, Direction, Size, Value) )
@ 是可选的 msdn ( command.CreateParameter (Name, Type, Direction, Size, Value) )
and type of parameter adDBTimeStamp
和参数类型 adDBTimeStamp
