Run Stored Procedure From Access VBA

问题:

I keep getting an error of

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

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

回答1:

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)

回答2:

Any reason your parameters can't be date/time data type? What is the data type of your hiredate column? This has always worked for me:

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