The conversion of a char data type to a datetime data type resulted in an out-of-range datetime valu

[M. Bruil]

Hi,

This error occurs when I call a stored procedure (SQL Server 2005) from MS
Access (2007) VBA, using ADODB.

I've tried to execute the stored procedure directly from the management
studio:

up_WerknemerInsert 10551,666, 'test', 'te', '', 'M', 't', '', '',
'2008-04-19 2:05:06 PM', '', '',''

this works just fine. When I run it from VBA, then the error occurs. I've
tried a lot of variations of the date format:

dd-MM-yyyy
yyyy-MM-dd
MM-dd-yyyy
MM/dd/yyyy

and so on...

The weird thing is that when I run this VBA in older versions of MS Access,
it seems to run error-free. I changed the @GebDate parameter in the stored
procedure from DateTime to nvarchar and tried to solve it by converting the
string to date in the INSERT INTO, but no success...

This is my VBA snippet:
==================================================================================
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "up_WerknemerInsert"

cmd.parameters.Append cmd.CreateParameter("@Relatie_Ref", adInteger,
adParamInput, , .Relatie_ID)
cmd.parameters.Append cmd.CreateParameter("@SOFINr", adInteger,
adParamInput, , .SOFINummer)
cmd.parameters.Append cmd.CreateParameter("@WnNaam", adVarChar,
adParamInput, 50, .Naam)
cmd.parameters.Append cmd.CreateParameter("@Voorletters", adVarChar,
adParamInput, 50, .Voorletters)
cmd.parameters.Append cmd.CreateParameter("@TussenVgsl", adVarChar,
adParamInput, 50, .Tussenvoegsel)
cmd.parameters.Append cmd.CreateParameter("@Geslacht", adVarChar,
adParamInput, 1, .Geslacht)
cmd.parameters.Append cmd.CreateParameter("@PriveAdres", adVarChar,
adParamInput, 50, .Adres)
cmd.parameters.Append cmd.CreateParameter("@PrivePC", adVarChar,
adParamInput, 7, .Postcode)
cmd.parameters.Append cmd.CreateParameter("@PriveWnPlaats", adVarChar,
adParamInput, 30, .Woonplaats)
cmd.parameters.Append cmd.CreateParameter("@GebDat", adDate, adParamInput, ,
"2008-04-19 2:05:06 PM")
cmd.parameters.Append cmd.CreateParameter("@Email", adVarChar, adParamInput,
50, .Email)
cmd.parameters.Append cmd.CreateParameter("@Rekening_nr", adVarChar,
adParamInput, 11, .Rekeningnummer)

Dim p As New ADODB.Parameter
Set p = cmd.CreateParameter("@WerknemerID", adVarChar, adParamOutput, 30)
cmd.parameters.Append p

cmd.Execute

..Werknemer_ID = p.value



==================================================================================

And this is the stored procedure I've written:

==================================================================================

GO

/****** Object: StoredProcedure [dbo].[up_WerknemerInsert] Script Date:
03/25/2009 20:10:33 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO



ALTER PROCEDURE [dbo].[up_WerknemerInsert]
@Relatie_Ref int,
@SOFINr int,
@WnNaam nvarchar(50),
@Voorletters nvarchar(50),
@TussenVgsl nvarchar(50),
@Geslacht nvarchar(1),
@PriveAdres nvarchar(50),
@PrivePC nvarchar(7),
@PriveWnPlaats nvarchar(30),
@GebDat nvarchar(30),
@Email nvarchar(50),
@Rekening_nr nvarchar(11),
@WerknemerID nvarchar(30) output

AS

BEGIN

SET NOCOUNT ON;

INSERT INTO tblWerknemer
(
Relatie_Ref,
SOFINr,
WnNaam,
Voorletters,
TussenVgsl,
Geslacht,
PriveAdres,
PrivePC,
PriveWnPlaats,
GebDat,
Email,
Rekening_nr
)
VALUES
(
@Relatie_Ref,
@SOFINr,
@WnNaam,
@Voorletters,
@TussenVgsl,
@Geslacht,
@PriveAdres,
@PrivePC,
@PriveWnPlaats,
CONVERT(DATETIME,@GebDat,104), --'18/02/2009
11:24:34',104),
@Email,
@Rekening_nr
)

SET @WerknemerID=@GebDat



END

==================================================================================