ASP - input date string turns different once written into database

[alejandroPCM]

i m coding this website in asp and i seem to have ran against a wall.

The date i'm sending through the asp form changes completely once in gets written in the database. for example the input 7/6/2006 becomes 02/06/2448 once it enters the database.

let me describe the code:

i am using an asp page with a form.
this form contains 3 drop down boxes with the days, months, and years.
i then call a variable and add these 3 boxes together to give me the string that will be stored in the db.

like this:

Dim datestring
datestring = Request.Form("u_day") & "/" & Request.Form("u_month") & "/" & Request.Form("u_year")

and then procede to upload:

strSQL = "INSERT into Fdata ( stockID, fdate, quicklist, ftitle, category, description) values "
strSQL = strSQL & "(" & recordID & "," & datestring & "," & Request.Form("quicklist") & ",'" & Request.Form("ftitle") & "','" & categoryStr & "','" & Request.Form("description") & "')"
conn.execute (strSQL)

you can see above that i am inputing DATESTRING in a table called Fdata.

The process terminates without any errors and the value is added in the database....Only the number is nowhere near the same. The FDATE db column is set Date/Time > Short Date.

what is wrong with the whole thing i cannot understand.

[PCBrandon]

What database are you using?

What is the output of Response.Write(datestring)
What is the output of Response.Write(strSQL)

The syntax of your SQL statement should be like:
strSQL = "INSERT INTO Fdata(stockID, fdate, quicklist, ftitle, category, description) VALUES(" & recordID & ",'" & datestring & "')"

Of course that's not the full SQL statement, but what I'm getting at is that unnecessary spaces can sometimes cause issues. Also, any variables that are numbers shouldn't be enclosed in quotations, like I changed for recordID (assuming that is a number).

[faulkner132]

Depending on your database, you need to surround the the date with either # or '

Most likely your code should insert the date in the format:

#7/1/2006#

if that doesn't work, try

'7/1/2006'

[alejandroPCM]

i am using access for the db. i tried to input the string as dd-mm-yyyy, mm-dd-yyyy, yyyy-mm-dd to see if its a format issue but it always goes wrong. I checked my PC date format (my pc is acting as the test server with IIS) and its dd/mm/yyyyy. the db format is also set as dd/mm/yyyy. Finally, the datestring is dd/mm/yyyy.
Also tried to put the datestring in single quotes :


datestring = 'Request.Form("u_day") & "/" & Request.Form("u_month") & "/" & Request.Form("u_year")'

this makes the whole argument turn into an invisible comment.
then i tried with double quotes:

datestring = "Request.Form("u_day") & "/" & Request.Form("u_month") & "/" & Request.Form("u_year")"

which returns and error saying that end of statement is required.
same with

datestring = " "Request.Form("u_day")" & "/" & "Request.Form("u_month")" & "/" & "Request.Form("u_year")" "

the following returns syntax error:

datestring = # Request.Form("u_day") & "/" & Request.Form("u_month") & "/" & Request.Form("u_year")" #

as for the working version which returns the wrong number once it gets into the db....the datestring registers as xx/xx/xxxx with x being the correct answers. but when i open the db to check the string its changed completely!!!!!
i m lost......

[PCBrandon]

Return your code to where you originally had it when you made this post. Then, place a

Response.Write(datestring)

after

datestring = Request.Form("u_day") & "/" & Request.Form("u_month") & "/" & Request.Form("u_year")

Then put a Response.End after the Response.Write. What is that output?

Remove the Response.Write and the Response.End. Add a Response.Write(strSQL) after

strSQL = strSQL & "(" & recordID & "," & datestring & "," & Request.Form("quicklist") & ",'" & Request.Form("ftitle") & "','" & categoryStr & "','" & Request.Form("description") & "')"

But before Conn.Execute(strSQL). Add a Response.End after the Response.Write and before the Conn.Execute. What's the result of that?

[alejandroPCM]

the Response.Write(datestring) returns:

3-7-2006

which is the correct selection.

the Response.Write(strSQL) returns:

INSERT into Fdata ( stockID, fdate, quicklist, ftitle, category, description) values (15,3-7-2006,1,'g dfsg ','Διάφορα','sdfg dsfg dfsg d gdf')

again the date is correct....but in the db its 7/7/1894.


its the same if i use"/" instead of "-"......it just returns 3/7/2006

if i dont use "/" or "-" then it returns an overflow error. i guess the number is too large to handle#

[alejandroPCM]

I got the answer......

i have to enclose my string in "#"...

so the string must be written as:


datestring = "#" & Request.Form("u_day") & "/" & Request.Form("u_month") & "/" & Request.Form("u_year") & "#"

[alejandroPCM]

But now that it works i ran against the second wall and i shouldnt have. maybe you could waste some of your time on this little devil.

The date sorted above is part of a form filled by general characteristics of a db record entry. Once this information gets written in the db the user is forwarded to a second page that allows them to upload files relevant to the entry they just made (e.g. attach a word document).

But alas i am getting an error:

Error Type:
ADODB.Stream (0x800A0BBC)
Write to file failed.

Now.....i have set permissions for IUSR on all folders that are required for the asp upload. Gave full permissions for this account on Security, made them Shared on the network and i also made Web shares and allowed read/write everywhere for IUSR..... still the writing fails....all i could google out of it is that the permissions need to be set....

but i have done so and i still get the error.

any hints? any help is mostrly appreciated.

[faulkner132]

Look in IIS on the "Directory" tab (as I recall), there should be a check box for write. By default, read, index, and log are the only options checked.

[alejandroPCM]

i have already assigned permissions in the IIS interface. This is why i am so confused. It must be something else than permissions because i have set set them all over the place.......

and what i dont understnad is that the first part of the upload (the description) is written into the db (permissions are set to allow it) whereas the file doesnt upload. Permissions seem to be selective on what they permit.....

[faulkner132]

Make sure your Windows Temp directory allows writing by IUSR as well.