运行存储过程从访问VBA
从字符串转换日期和/或时间时,我不断收到运行存储过程从访问VBA
转换的错误而失败。
这是我的vba我正在使用以及我的sql服务器存储过程的语法。为了让这个运行成功,我应该改变什么?
Private Sub btnRunStoredProc_Click()
Dim cmd As ADODB.Command, startdate As String, enddate As String
Set cmd = New ADODB.Command
startdate = "'" & Me.txtStartDate & "'"
enddate = "'" & Me.txtEndDate & "'"
cmd.ActiveConnection = "Provider=sqloledb;Server=Server;Database=DB;Trusted_Connection=yes;"
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "runstoredproc"
cmd.Parameters.Append cmd.CreateParameter("@startdate", adVarChar, adParamInput, 255, startdate)
cmd.Parameters.Append cmd.CreateParameter("@enddate", adVarChar, adParamInput, 255, enddate)
cmd.Execute
End Sub
ALTER Procedure [dbo].[runstoredproc]
(
@startdate varchar(100)
,@enddate varchar(100)
)
As
Select * from helper where hiredate between @startdate And @enddate
考虑使用Format()
将MS Access日期转换为字符串,因为连接引号不起作用。此外,使用CONVERT()
将SQL Server中的varchar字符串转换为日期。格式YYYY-MM-DD
用于不依赖于文化设置与月份和日期的位置。
VBA
Private Sub btnRunStoredProc_Click()
Dim cmd As ADODB.Command, startdate As String, enddate As String
Set cmd = New ADODB.Command
startdate = Format(Me.txtStartDate, "YYYY-MM-DD")
enddate = Format(Me.txtEndDate, "YYYY-MM-DD")
With cmd
.ActiveConnection = "Provider=sqloledb;Server=Server;Database=DB;Trusted_Connection=yes;"
.CommandType = adCmdStoredProc
.CommandText = "runstoredproc"
.Parameters.Append .CreateParameter("@startdate", adVarChar, adParamInput, 255, startdate)
.Parameters.Append .CreateParameter("@enddate", adVarChar, adParamInput, 255, enddate)
.Execute
End With
Set cmd = Nothing
End Sub
TSQL
ALTER Procedure [dbo].[runstoredproc]
(
@startdate varchar(100)
,@enddate varchar(100)
)
As
SELECT * FROM [helper]
WHERE hiredate BETWEEN CONVERT(DATE, @startdate) AND CONVERT(DATE, @enddate)
任何原因,你的参数不能是日期/时间数据类型?你的hiredate
专栏的数据类型是什么?这一直为我工作:
Private Sub btnRunStoredProc_Click()
Dim cmd As ADODB.Command, startdate As Date, enddate As Date
Set cmd = New ADODB.Command
startdate = CVDate(Me.txtStartDate)
enddate = CVDate(Me.txtEndDate)
cmd.ActiveConnection = "Provider=sqloledb;Server=Server;Database=DB;Trusted_Connection=yes;"
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "runstoredproc"
cmd.Parameters.Append cmd.CreateParameter("@startdate", adDBTimeStamp, adParamInput, , startdate)
cmd.Parameters.Append cmd.CreateParameter("@enddate", adDBTimeStamp, adParamInput, , enddate)
cmd.Execute
End Sub
ALTER Procedure [dbo].[runstoredproc]
(
@startdate datetime
,@enddate datetime
)
As
Select * from helper where hiredate between @startdate And @enddate
即使我更改为日期时间数据类型,我仍然收到错误查询超时 – BellHopByDayAmetuerCoderByNigh
如果您在SQL企业管理器中手动运行此sp,该怎么办?多久时间?你可以在你运行Access的同一台机器上使用相同的凭据吗? – Skippy
这让我查询超时的VBA错误。我试图添加'.ActiveConnection.CommandTimeout = 0',但仍然显示错误。 – BellHopByDayAmetuerCoderByNigh
为什么把它设置为零秒?您的查询可能运行时间超过默认的30秒。尝试在“With”块中添加'.commandTimeout = 60' 60秒。 – Parfait
@Parfait - 将超时设置为零意味着“无限超时”,而不是“默认超时”。 – Skippy