[thelist] SQL Server smalldatetime field problems

[Norman Beresford]

Hi all

I'm having some real problems at the moment with a simple INSERT sql command
on a site.  Basically I want to insert some form values into a SQL Server
table.  The problem I'm having is with the date/time fields.  I've four
fields, which I initially set to smalltimedate (I only needed a time
accuracy of 15mins).  I built the SQL statement using formatDateTime to
ensure that they were in the correct format. However I'm getting an error
message from it, and for the life of me I can't correct it.

I'm sure that there must be some easy solution to this problem, but for the
life of me I can't find it.  If anyone could help I would be most gratefull.

Norman



The table:

motiveID int 4 10 0 0  1 1 1 0
motiveTitle varchar 50 0 0 0  0   0
motiveForename varchar 50 0 0 0  0   0
motiveSurname varchar 50 0 0 0  0   0
motiveOrganisation varchar 50 0 0 0 ('Private Individual') 0   0
motiveAddress varchar 250 0 0 0  0   0
motivePostCode varchar 10 0 0 0  0   0
motiveTelephone varchar 50 0 0 0  0   0
motiveEmail varchar 50 0 0 0  0   0
motiveContactMethod char 4 0 0 0  0   0
motiveDate smalldatetime 8 0 0 0  0   0
motiveStart smalldatetime 8 0 0 0  0   0
motiveFinish smalldatetime 8 0 0 0  0   0
motiveVenue varchar 50 0 0 0  0   0
motiveGuests int 4 10 0 0  0   0
motivePackageID smallint 2 5 0 0  0   0
motiveDateBooked smalldatetime 8 0 0 0  0   0


The ASP

thisSQL = _
"INSERT INTO motivaction ( motiveTitle, motiveForename, motiveSurname,
motiveOrganisation, motiveAddress, motivePostcode, motiveTelephone,
motiveEmail, motiveContactMethod, motiveDate, motiveStart, motiveFinish,
motiveVenue, motiveGuests, motivePackageID, motiveDateBooked)" & _
" VALUES ('" & bookingTitle & "', '" & bookingForename & "', '" &
bookingSurname & "', '" & bookingOrg & "', '" & bookingAddress & "', '" &
bookingPostCode & "', '" & bookingTelephone & "', '" & bookingEmail & "', '"
& bookingContactMethod & "', '" & formatdatetime(bookingDate,vbLongDate) &
"', '" & formatdatetime(bookingTimeStart,VBShortTime) & "', '" &
formatdatetime(bookingTimeFinish,VBShortTime) & "', '" & bookingVenue & "',
" & bookingNumbers & ", " & packageID & ", '" &
formatdatetime(bookingDateBooked,vbLongDate) & "');"


The SQL

INSERT INTO motivaction ( motiveTitle, motiveForename, motiveSurname,
motiveOrganisation, motiveAddress, motivePostcode, motiveTelephone,
motiveEmail, motiveContactMethod, motiveDate, motiveStart, motiveFinish,
motiveVenue, motiveGuests, motivePackageID, motiveDateBooked) VALUES
('Miss', 'Belinda', 'Beresford', 'Private Individual', '105 Houblon
ROad<BR>Richmond<BR>Surrey', 'TW10 6DB', '020 2222 2222,
'n.beresford at anansi.co.uk', 'Email', 'Tuesday, September 10, 2002', '21:00',
'23:30', 'Back garden', 68, 11, 'Tuesday, July 10, 2001');


The Error
Microsoft OLE DB Provider for SQL Server error '80040e07'

Syntax error converting datetime from character string.

/motivaction/booked.asp, line 65