从Access VBA运行存储过程 [英] Run Stored Procedure From Access VBA

问题描述

我不断收到

从字符串转换日期和/或时间时转换失败.

Conversion failed when converting date and/or time from character string.

这是我正在使用的vba以及sql server存储过程语法.为了使此程序成功运行，我应该更改什么?

This is my vba I am using as well as my sql server stored procedure syntax. What should I alter in order to have this run succesfully?

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()将varchar字符串转换为SQL Server中的日期.格式YYYY-MM-DD过去不依赖于文化环境(放置数月和数天).

Consider using Format() to convert MS Access dates to string as concatenating quotes will not work. Also, use CONVERT() to convert varchar strings to date in SQL Server. The format YYYY-MM-DD is used to not be dependent on cultural settings with placement of months and days.

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)

这篇关于从Access VBA运行存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！