CODEWITHSUNDEEP
sqlvbaexcelstored-procedures
blahblahblah
blahblahblah

Reputation: 2447

VBA passing date in stored procedure problems

I'm having difficulty passing a Date value to a datetime value using a SQL Stored Procedure. The @DateTime parameter is in datetime format in MyDb in a Microsoft SQL Server. When I run the below subroutine, I get an error: Parameter type not supported. I believe the error has to do with with the adDBTimeStamp parameter type. Any idea how to resolve this problem?

Public Sub AddDateTime

Dim DateTime as Date
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim param as ADODB.Parameter
Dim sConnString As String

/*Create the connection string*/

DateTime = Now()

sConnString = "Driver={SQL Server};Server=TheServer3;Database=MyDb;Trusted_Connection=Yes;"

Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command
cmd.ActiveConnection = sConnString
cmd.CommandText = "usp_AddNewDateTime"
cmd.CommandType = adCmdStoredProc

Set param = cmd.CreateParameter("@DateTime", adDBTimeStamp, adParamInput, , DateTime)
cmd.Parameters.Append param

Set rs = cmd.Execute

/*Clean up*/

Set conn = Nothing
Set rs = Nothing

End Sub

Upvotes: 2

Views: 3986

Answers (2)

blahblahblah
blahblahblah

Reputation: 2447

Simple solution -- The DateTime variable has to be converted to a Double: CDbl(DateTime)

Upvotes: 0

D Stanley
D Stanley

Reputation: 152624

Since you are providing a Date value, just use adDBDate:

Set param = cmd.CreateParameter("@DateTime", adDBDate, adParamInput, , DateTime)

Upvotes: 1

Related Questions