ASP / SQL

jfrizelle · May 19th, 2010, 06:57 AM

Hi,

I've been running a piece of code for the last couple of years on my ASP page (self-catering website) that takes the fields on my 'Enquiry' page and inserts them into a db using and SQL statement as follows:

Code:

strSQL = "Insert into tblEnquiries (EnquiryDate, EnqName, Address, Email, Telephone, HouseType, NoHouses, ArrivalDate, DepartureDate, NoAdults, NoChildren, SpecialRequests, HearOfUs) VALUES('" & now() & "', '" & _
    EnqName & "', '" & Address & "', '" & Email & "', '" & Telephone & "', '" & HouseType & "', " & NoHouses & ", '" & _
    ArrDate & "', '" & DepDate & "', " & Adults & ", " & Children & ", '" & SpecReq & "', '" & HearOfUs & "')"

However it is suddenly giving me a HTTP Error 500 and I don't know why. I haven't changed the code or the db in any way. If I do a

Code:

Response.Write(strSQL)

it shows the date as 19/05/2010 11:55:29', which seems long - my database field is a datetime field, length 8. I've also tried using the date() function but still get the same error.

If I insert a date myself as the first value instead of using any of the date functions (eg. 20100519) it works ok.

Thanks for any help!

J.

mat41 · May 19th, 2010, 06:37 PM

The 500 error is very ambigous and says nothing. you should turn of friendly http errors in your browser and run the page again to get a detailed error for a start. Go to tools > internet options > advanced > now scrol down til you see 'friendly HTTP errors' and uncheck this. you never want friendly errors when developing. Accept this close the browser, open a fresh brower, hit the problematic page, what is the error now?

You say:

;;;;it shows the date as 19/05/2010 11:55:29'

Well then this would be the problem, you are missing a leading single quote. it should say '19/05/2010 11:55:29'

However I think not. According to your SQL:

'" & now() & "'

I can not see how its missing the leading single quote. If you DB datatype is dateTime it can not be this. 19/05/2010 11:55:29 is not to long, its simply the date time. Please post the non friendly error and your entire SQL statement once written to the browser.

One thing it could be - I notice you are not dealing with EG single quotes in strings. If a user types in a string containing a single quote your SQL will fail. Im suprised it worked for so long without dealing with this very common situation

mat41 · May 19th, 2010, 06:48 PM

Here is the function I use to deal with such strings:

Function StoreText(theText)
StoreText = ""
on error resume next
StoreText = CStr(theText)
if (len(StoreText) > 0) Then
StoreText = Replace(StoreText, """", """, 1, -1, 1)
StoreText = Replace(StoreText, "'", "''", 1, -1, 1)
end if
End Function

Place it in your global functions file and use it, for example in your SQL like this:

strSQL = "Insert into tblEnquiries (EnquiryDate, EnqName, Address, Email, Telephone, HouseType, NoHouses, ArrivalDate, DepartureDate, NoAdults, NoChildren, SpecialRequests, HearOfUs) VALUES('" & now() & "', '" & _
trim(StoreText(EnqName)) & "', '" & trim(StoreText(Address)) & "', '" & trim(Email) & "', '" & trim(StoreText(Telephone)) & "', '" & trim(StoreText(HouseType)) & "', " & NoHouses & ", '" & _
trim(StoreText(ArrDate)) & "', '" & trim(StoreText(DepDate)) & "', " & Adults & ", " & Children & ", '" & trim(StoreText(SpecReq)) & "', '" & trim(StoreText(HearOfUs)) & "')"

If the single quote issue is your problem this will fix it. Also notice the trim function, this is just good practice for obvious reasons, you should always use it around strings.

Hope that helps

jfrizelle · May 27th, 2010, 05:15 AM

Thanks Matt,

I copied and pasted the date in my post from a Response.Write, but left out one of the single quotes - they were both there alright. I should have taken into account people entering a single quote - thanks for that, I'll implement that, and the Trim.

I've turned off friendly errors, both in IE and FF, but both of them still just show the 500 error. I can't see why, but it's most unhelpful! I've shut down the browser and restarted it, also rebooted, but both still show the 500 error.

I've changed my code now anyway so that I'm building up the date from its parts to create a string variable - eg. '20100527', etc. and just inserting that variable as the VALUE for my EnquiryDate field. Isn't it nuts that you can't put now() as the default value in the db???

J.

Old Pedant · May 27th, 2010, 05:54 PM

Quote:

Isn't it nuts that you can't put now() as the default value in the db???

You can. It's just not called Now( ) when you use SQL Server:

Code:

CREATE TABLE foo ( somedate DATETIME DEFAULT getDate() )

jfrizelle · May 28th, 2010, 07:02 AM

Many thanks for your help guys!

I'm having a problem with the function Matt (http 500 error), but as I'm still getting 'friendly' errors I'm going to leave that for now. I've asked my web host if it's something that's set at their end, because I've switched off friendly errors in my browser but it made no difference.

On a slightly different note, once I open my db in myLittleAdmin, how can I clean it up? There are a lot of rubbish records in there, but I can't see any 'clickable' way to remove them - if possible I'd rather not use an SQL statement, in case I inadvertently remove something that I want to keep. Is it possible to browse through the records deleting the odd one here and there as I go?

J.