[thelist] SQL Server smalldatetime field problems
Norman Beresford
n.beresford at anansi.co.uk
Tue Jul 10 08:10:19 CDT 2001
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
More information about the thelist
mailing list