ASP / SQL - now() suddenly giving HTTP Error 500

[WhitePhantom]

Hi,

I've an ASP ThankYou page that uses SQL to insert a record into a db every time I get an enquiry, but it's not working now for some reason that I can't figure out.

The SQL string consists of the following:

strSQL = "Insert into tblEnquiries (EnquiryDate, EnqName, Address, Email......) VALUES('" & now() & "', '" & EnqName & "','" & Address & "', '" & Email & "'....

It has been coded like this for the last couple of years, always working fine, and I don't know what has changed. When I replace the " & now() & " with an actual date (eg. '20100518') it works perfectly. I've tried replacing now() with date() but that also give the 500 error.

The EnqiryDate field in database is set up as type datetime, length 8.

Help please!
J.

 

[Forbairt]

should you not be using the databases built in function now() instead of the ASP one ?

strSQL = "Insert into tblEnquiries (EnquiryDate, EnqName, Address, Email......) VALUES( now() , '" & EnqName & "','" & Address & "', '" & Email & "'....

? I don't do asp mind

 

[WhitePhantom]

The strSQL is all being built up within an ASP section of code, so I'm building up all my VALUES using variables that are enclosed in quotes. If I remove the quotes around the 'now()' function and do a response.write to have a look at the sql statement that has been constructed, it reads

Insert into tblEnquiries (EnquiryDate, EnqName,.... ) VALUES(now(), 'Joe Bloggs',  'Somewhere',....

- you can see it takes the now() literally, instead of interpreting it as a function.

What's really puzzling me is that this piece of code using now() has worked perfectly for so long. I can't figure out why it has suddenly started having problems.

 

[Forbairt]

well now() is a sql function as well ( at least for mysql ) and I'd generally do that bit at the database side of things

 

[WhitePhantom]

Thanks Forbairt, not sure I understand though - how do you mean 'do that bit at the database side of things'?

 

[Forbairt]

Erm ... the database has its own language called SQL (Structured Query Language) has a number of functions so ... when you send your sql query to the database it sees now() as a function which it knows about ... and calculates the time then and there

Ah anyways maybe someone else can help you out .. not familiar with asp at all

 

[WhitePhantom]

Ah yeh, I know that! :-D

Sorry, I should have said it's MySql I'm using, so I can't put the now() into the db as the default value (which is really dumb!!)

But anyway, I'm breaking the date down into its Year, Month and Day parts now, and putting them together so that I get a string like '20100519', because it accepts that fine. Then it turns it back into a datetime format in the db.

It's still bugging me though, really bugging me!! The sql code using the now() function has worked for years. Then for no apparent reason it starts giving an error. I hate unexplained stuff like that. Sigh!

 

[Daisy]

Do you have control over the database and server? Could the language have changed? And have you checked all the entries in the database to see if any others have been made later in the month than the 12th...