Frontpage 2002 SQL SERVER Update Date problem

[AndyLord]

Can anyone help?

I have a created an Intranet, using Frontpage 2002 which access data on an SQL SERVER database. I can view the data, add data, delete data and edit data, created using the DRW in Frontpage.

The problem I get is when I edit a Date field and then submitting the query I get the following error
"Database Results Error
Description: [Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.
Number: -2147217913 (0x80040E07)
Source: Microsoft OLE DB Provider for ODBC Drivers".

In the SQL DB the field is a smalldatetime data type and on the Frontpage form the user will edit the date via a textbox.

I have 3 asp pages to handle this. Page 1 is the view date form with an edit hyper link. This will then send the correct record to Page 2 Edit page, which has the textboxes as mentioned above. Page 3 is where the Update SQL to the table is handled.

I know I need to convert the textbox with the date into a Date format, but I Don't know how or where to do this.

Any help would be greatly appreciated with this. If current code for the pages is required please notify.

Cheers

Andy Lord

 

[AndyLord]

From AndyLord

I have managed to track the source of this problem.
The date formats used are the for UK, dd/mm/yyyy. When entering a date it is converted to the US format of mm/dd/yyyy. So when entering a date e.g 13/07/2004 - 13th of July 2004 either Frontpage or SQL Server thinks that it is a US date 13month 07day 2004year and thsi causes the "[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value." problem to occur.

Now I need to work out hoe to keep the date in UK format.
Can anyone help?

AndyLord

 

[AndyLord]

From AndyLord

I have managed to track the source of this problem.
The date formats used are the for UK, dd/mm/yyyy. When entering a date it is converted to the US format of mm/dd/yyyy. So when entering a date e.g 13/07/2004 - 13th of July 2004 either Frontpage or SQL Server thinks that it is a US date 13month 07day 2004year and this causes the "[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value." problem to occur.

Now I need to work out how to keep the date in UK format.
Can anyone help?

AndyLord