Invalid character value for cast specification (#0)

I ran in to this rather odious SQL Server Native Client 11.0 error last week, getting a message box saying “ODBC–update on a linked table failed.”  Actually it was an insert, but that’s OK, I never expect software to have particularly good error messages.  (but why, oh why, can’t Microsoft make their error messages so that we can copy the error message out with our mouse to make it easier to look up?)  I’ve since been banging my head against the wall trying to solve it.  It didn’t seem like anything complicated going on: I had a table that I was accessing directly through a subform and adding a record.  The insert was consistently blowing up.

Well, I found my problem.  Your mileage may vary.

First, the SQL Server table:

CREATE TABLE [dbo].[NotesStudents](
    [NoteID] [int] IDENTITY(1,1) NOT NULL,
    [StudentID] [int] NOT NULL,
    [NoteDate] [date] NOT NULL CONSTRAINT [DF_NotesStudents_NoteDate] DEFAULT (getdate()),
    [NoteTime] [time](3) NOT NULL CONSTRAINT [DF_NotesStudents_NoteTime] DEFAULT (getdate()),
    [UserID] [int] NOT NULL,
    [NoteText] [varchar](1000) NOT NULL,
    [RowVer] [timestamp] NOT NULL);

I have the date and time as two separate fields as I want my form to display the records sorted by date descending, time ascending.  I needed to populate the date, time, student ID, and user ID, and the following code on the BeforeInsert did that:

Private Sub Form_BeforeInsert(Cancel As Integer)
    Me.txtNoteLen.Visible = True
    Forms!frmStudentsSPEDs!fsubStudentsNotes.Form!SeekerID = Forms!frmStudentsSPEDs.StudentID
    Forms!frmStudentsSPEDs!fsubStudentsNotes.Form!UserID = Forms!frmMenuSPEDs.UserID
    Forms!frmStudentsSPEDs!fsubStudentsNotes.Form!NoteDate = Date
    Forms!frmStudentsSPEDs!fsubStudentsNotes.Form!NoteTime = CStr(Time)
End Sub

(the txtNotLen field counts how many characters are in the note since they’re limited to 1,000)

So the above code produced the #0 error message.  I could add records through SSMS, so I knew the table was functional.  Today I get back from lunch and decide to manually add a record to the table via Access, using Ctrl-; to add the date and Ctrl-: to add the time.

And it blew up.

Turns out that when you use Ctrl-: to enter the time, you get 12:57:41 PM.  The PM was the problem.  SQL Server was expecting just a time value, so I changed the NoteTime line to say Format(Time, “h:m:s”): the Format “h:m:s” converts the current time to 24 hour format, so 4:49pm becomes 16:49.

So if you’re getting this error and you’re dealing the VBA and a Time(3) field on SQL Server, this might be the cause.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s