SQL Query Problem

[CrzySdrs]

Running this query in my webpage results in an error.

conn.Execute("INSERT INTO News (Date, Title, Message, Poster) VALUES ('" & Request.QueryString("Date") & "', '" & Request.QueryString("Title") & "', '" & Request.QueryString("Message") & "', '" & session("User") & "')")

Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in INSERT statement.

I can't seem to find the error in this statement. Any guesses?

[raf]

Welcome here.
Not guesses but some ideas :D
- the date column, is it of variabeltype date? caues then you need to include the values in # instead of quotes
- are you sure all other columns are stringsvariabels
- replace all single quotes in the string values by two single quotes (see sticky at the top of this forum)
- make sure to run some checks on the values from the querystring before you include them in the sql statement. Some of them might very well be empty or contain some scripts that evil minds have pasted into it
- insert this right before the code you postes

dim sql
sql = "INSERT INTO News (Date, Title, Message, Poster) VALUES ('" & Request.QueryString("Date") & "', '" & Request.QueryString("Title") & "', '" & Request.QueryString("Message") & "', '" & session("User") & "')"
response.write sql
response.end

Then browse this page and look at the printed sql statement. You'll probably see what's wrong then.
- personally, i use this form

dim sql, numinserted
sql = "INSERT INTO News (Date, Title, Message, Poster) VALUES (#adate#,'aTitle','aMessage','aUser')"
sql = replace(sql,"aDate",request.querystring("Date"))
sql = replace(sql,"aTitle",replace(request.querystring("Title"),"'","''")
sql = replace(sql,"aMessage",replace(request.querystring("Message"),"'","''")
sql = replace(sql,"aUser",replace(request.querystring("User"),"'","''")

conn.Execute sql,numinserted

if numinserted > 0 then
response.write(numinserted & " record(s) inserted")
else
response.write("Possible databaseproblem. No records inserted")
end if

easier to debug. Note the # ! Numinserted is a nice little control.

[CrzySdrs]

Thanks for the advice, but it seems to be having little effect. Here is an example of the stuff I am trying to put in.

INSERT INTO News (Date, Title, Message, Poster) VALUES (#6/4/2003 0:20#, 'The End has Come', 'There is nothing to say', 'CrzySdrs')

This all seems to be correct, but I still get a syntax error. If I remove the date out of the INSERT statement completely, I get a different error of:

"Operation must use an updateable query."

With the statement of:

INSERT INTO News (Title, Message, Poster) VALUES ('The End has Come', 'There is nothing to say', 'CrzySdrs')

Which unfortunately means absolutely nothing to me. I am frankly at a loss for what could be causing this error. Please enlighten me.

[raf]

I had that error code once on an MSAccess db. It was after i copied a db from my machine at work, to my development machine. At work i used useraccounts for Access. After i copied the db, i got this error. Even on other app's that used MsAccess db's.
I solved it by renaming all Access files, copying them and renaming the copys back to the original filename.

There was a thread on that problem some months ago
http://www.codingforums.com/showthread.php?s=&threadid=16033&highlight=access+copy


About the date --> that value of your is not a standard/predefined datetime format, so check to see if the format in your db is dd/mm/yyy hh:mm (or mm/dd/...)

[CrzySdrs]

Following those instructions on the other post, I got it to work. Apparently it had to do with the security permissions on the IIS folder. It all works excepting the Date, which I cannot get the syntax to work, with either ' or #. I have also tried a few mm/dd/yy configurations. No such luck.

[raf]

which datatime format do you have in your db? What sort of regional settings do you have?
Note : ASP will always treat the datevalue as mm/dd/yyyy !

Did you try with 06/04/2003 ?
Note : yy is only allowed in medium date format, which is 06-june-03

[oracleguy]

You know in access, if you view in design view of your news table then click on your date field it has a "default value" option. Just type "now()" in and whenever you insert a new record it will automatically but the date in.

[CrzySdrs]

Thanks a lot. Everything is working perfectly. I ended up just taking the easy way out and making a default value. Works just like I wanted.