ASP date error coding with my SQL table

getting an error for the date in asp

[cherry]

Hi guys

I have created a table for a competition where people leave their email address. I have encountered a problem and don't know if it's my SQL or my ASP.

Here is the error that I get

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[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.

/content/default.asp, line 22

In my SQL table I have 4 columns
name - data type - length - allow nulls

(in the data type smalldatetime is the value)

and this is the code that I use on the default.asp

Set address = Server.CreateObject("ADODB.Recordset")
sqlstmt1 = "INSERT INTO compentry(email,date,comp) VALUES ('" & request("submit_email") & "','" & now() & "','" & request("comp") & "')"

address.Open sqlstmt1, db, 3, 3
else

What am I missing?Can anyone point me in the right direction :-)

hope you can help me
thanks

[Dreamquick]

You need to be aware of two things;

ASP's default date format (based on the OS & User)
SQL's default date format (based on the User)

Typically *char to date errors are caused by using two different date formats and SQL gettings its knickers in a twist - for example trying to treat a UK date as a US data.

Personally I'd just set the column to default to GETDATE() at a SQL level and by omitting it from the INSERT statement that would let SQL automatically set it whenever I added a new row, but then again I'm cynical, lazy and have been doing db management for far too long. :)

If you want to do it manually you could try something like;

sqlstmt1 = "INSERT INTO compentry(email,date,comp) VALUES ('" & request("submit_email") & "',GETDATE(),'" & request("comp") & "')"

...which uses the GETDATE function as an integral part of the query, or manually bluff your way through it by making a string and then explicitly telling SQL what the format is...

sqlstmt1 = "INSERT INTO compentry(email,date,comp) VALUES ('" & request("submit_email") & "',CONVERT( SMALLDATETIME, '" & Day(Now) & "/" & Month(Now) & "/" & Year(Now) & "' , 103 ),'" & request("comp") & "')"

- Tony

[WebJoe]

If I read your code correctly, you put the now() function in single quotes (from the SQL-statement pov). Depending on your DBMS you have to use other delimiters if you have the datetime as a string...#date# in MS, TO_DATE() in Oracle etc.

Or try this:

sqlstmt1 = 
"INSERT INTO compentry(email,date,comp) VALUES ('" & request("submit_email") & "'," & now() & ",'" & request("comp") & "')" 

(note that I removed the single quotes around the now-function)

I'm not quite sure about this, but I think it's worth a shot...

[WebJoe]

Another idea:

Use the DBMS' function to get the date...

Jet:

sqlstmt1 = "INSERT INTO compentry(email,date,comp) VALUES ('" & request("submit_email") & "', now() ,'" & request("comp") & "')"

Oracle:

sqlstmt1 = "INSERT INTO compentry(email,date,comp) VALUES ('" & request("submit_email") & "', SYSDATE ,'" & request("comp") & "')"

[cherry]

Thanks guys

I tried the easy one first and removed the extra ' around the now statemant as webjoe suggested but got a different error up telling me I had an incorrect syntax around '20' which I think means the line that the code was on.

Anyway I will try your other suggestions and keep you posted of my (slow) progress :-)

Thanks again

[WebJoe]

Cherry

not knowing the error message I can only assum that there is a difference in date format between your DBMS and the machines OS/user-config, as Tony mentioned

[musicales]

Dreamquick has it correct about the confusion between default date formats. One thing I could add: I recently came across session.lcid which you can use in your asp page to set the country (eg. Session.LCID = 2057 for the UK). I'm not entirely sure whether this sets things up so both ASP and SQL server are set correctly to the same thing, but I think that is the case. Anyway, it seemed to sort out a lot of problems for me when I tried it.