Error converting data type varchar to datetime.

[BennyLinton]

I'm getting an error: "Error converting data type varchar to datetime." with the following VBA tha calls a Stored Procedure on my SQL Server backend. Any clues greated appreciated:

Here is My VBA:

Private Sub cmdSave_Click()

Dim strCUser As String
strCUser = CurrentUserName

Dim con As adodb.Connection
Dim cmd As adodb.Command
Dim rs As adodb.Recordset
Set con = New adodb.Connection
Set cmd = New adodb.Command
con.ConnectionString = CONN_STRING
con.Open
cmd.ActiveConnection = con
cmd.CommandType = adCmdStoredProc


If IsNull(FirstName) Or IsNull(LastName) Then
    MsgBox "This record must contain a first and last name in order to be saved."
    Exit Sub
End If


DoCmd.SetWarnings Warningsoff

cmd.CommandText = "insertPeople"
    cmd.Parameters.Append cmd.CreateParameter("@peopleID", adInteger, adParamInput, 50, Me.TempId)
    cmd.Parameters.Append cmd.CreateParameter("@salutation", adVarChar, adParamInput, 50, Me.salutation)
    cmd.Parameters.Append cmd.CreateParameter("@FirstName", adVarChar, adParamInput, 50, Me.FirstName)
    cmd.Parameters.Append cmd.CreateParameter("@MiddleName", adVarChar, adParamInput, 50, Me.MiddleName)
    cmd.Parameters.Append cmd.CreateParameter("@LastName", adVarChar, adParamInput, 50, Me.LastName)
    cmd.Parameters.Append cmd.CreateParameter("@OtherName", adVarChar, adParamInput, 200, Me.OtherName)
    cmd.Parameters.Append cmd.CreateParameter("@suffix", adVarChar, adParamInput, 50, Me.Suffix)
    cmd.Parameters.Append cmd.CreateParameter("@ethnicity", adInteger, adParamInput, 50, Me.ethnicity)
    cmd.Parameters.Append cmd.CreateParameter("@dateOfBirth", adDate, adParamInput, 50, Me.dateOfBirth)
    cmd.Parameters.Append cmd.CreateParameter("@gender", adInteger, adParamInput, 50, Me.Gender)
    cmd.Parameters.Append cmd.CreateParameter("@notes", adVarChar, adParamInput, 4000, Me.Notes)
    cmd.Parameters.Append cmd.CreateParameter("@insertUser", adVarChar, adParamInput, 50, strCUser)
    cmd.Parameters.Append cmd.CreateParameter("@insertDate", adDate, adParamInput, 50, date)
cmd.Execute

Call clearParameters(cmd)

DoCmd.SetWarnings WarningsOn
Form_frmMain.Visible = True
Form_frmAddPerson.SetFocus
DoCmd.close
Form_frmMain.Requery

End Sub

And my stored procedure:

USE [GCDF_DB]
GO

/****** Object:  StoredProcedure [dbo].[insertPeople]    Script Date: 8/14/2017 3:13:34 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE Procedure [dbo].[insertPeople]
(
@peopleID int,
@salutation nvarchar(50),
@FirstName nvarchar(50),
@MiddleName nvarchar(50),
@LastName nvarchar(50),
@OtherName nvarchar(200),
@suffix nvarchar(50),
@ethnicity int,
@dateOfBirth datetime,
@gender int,
@notes nvarchar(4000),
@insertUser nvarchar(50),
@insertDate datetime
)
As
Update people Set salutation = @salutation,
				  FirstName = @FirstName,
				  MiddleName = @MiddleName,
				  LastName = @LastName,
				  OtherName = @OtherName,
				  suffix = @suffix,
				  ethnicity = @ethnicity,
				  dateOfBirth = @dateOfBirth,
				  gender = @gender,
				  notes = @notes,
				  insertUser = @insertUser,
				  insertDate = @insertDate
Where peopleID = @peopleID

GO

AND DDL:

USE [GCDF_DB]
GO

/****** Object:  Table [dbo].[People]    Script Date: 8/14/2017 3:14:45 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[People](
	[peopleId] [int] IDENTITY(1,1) NOT NULL,
	[tempflag] [bit] NULL CONSTRAINT [DF_People_tempflag]  DEFAULT ((0)),
	[customId] [int] NULL,
	[FirstName] [nvarchar](50) NULL,
	[MiddleName] [nvarchar](50) NULL,
	[LastName] [nvarchar](50) NULL,
	[OtherName] [nvarchar](200) NULL,
	[salutation] [nvarchar](50) NULL,
	[suffix] [nvarchar](50) NULL,
	[dateOfBirth] [datetime] NULL,
	[gender] [int] NULL,
	[ethnicity] [int] NULL,
	[notes] [nvarchar](4000) NULL,
	[insertUser] [varchar](50) NULL,
	[insertDate] [datetime] NULL,
	[entryMethod] [varchar](50) NULL,

 CONSTRAINT [PK_People] PRIMARY KEY NONCLUSTERED 
(
	[peopleId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

 

[pbaldy]

I've seen that when you have bad data that can't be converted to a date/time value, so double check what values you're passing to the date/time fields. Also, in my experience you don't want anything in the size parameter for the date/time values:

cmd.Parameters.Append cmd.CreateParameter("@dateOfBirth", adDate, adParamInput, 50, Me.dateOfBirth)

I've used adDBTimeStamp instead of adDate, but that may not be relevant.