Another ASP error

[goldfish]

Kodomeister come help me out.

I get Syntax error in INSERT INTO statement... what might be the problem?


Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & Server.MapPath("db/gblablablabal.mdb") _
& ";Jet OLEDB:Database Password=im not going to tell you!;"
'Adds new record into database
name = Request.Form("name")
if name = "" then name = "Annon"
sub_date = FormatDateTime(date(),vblongdate)
name = Replace(name, "'", "'")
name = Replace(name, """", """)
tehtitle = Request.Form("title")
post = Request.Form("article")
post = Replace(post, vbCrLf, "<br>")
post = Replace(post, "'", "'")
post = Replace(post, """", """)
post = Replace(post, "", "<a href=")
post = Replace(post, "", "</a>")
post = Replace(post, "", "<a href=mailto:")
post = Replace(post, "", "</a>")
post = Replace(post, "[LT]", ">")
pic_url = Request.Form("pic_url")
If pic_url = "" then
pic_url = "none"
End If
msg = "submitted suscessfully"
strSQL = "INSERT INTO articles(author, sub_date, article, pic_url, title) Values('" & name & "','" & sub_date & "','" & post & "','" & pic_url &"','" & tehtitle & "')"
Conn.Execute(strSQL)
Conn.Close
Set Conn = nothing


And the form it gets it from :

<form action="addnews.asp?add=yes" method=post>
<tr><td>Name : </td><td><input name="name" type=text></td>
<tr><td>Title: </td><td><input name="title" type=text></td>
<tr><td>Pic URL: </td><td><input name="pic_url" type=text></td>
<tr><td colspan=2><textarea name="article" cols=70 rows=20></textarea></td></tr>
<tr><td colspan=2><input type=submit value="Submit Article">
</form></table>

Any ideas?

[Kodo]

JET database engine must use # for dates

#"&mydatevar&"#

you're also doing WAY more work than you need to for your custom tags. Use Regular expressions

http://www.aspfaqs.com/aspfaqs/ShowCategory.asp?CatID=16

here's one I wrote a while ago for using the LINK tags


mylink="http://www.blah.com"


function Linkme(strlink)
set objRegExp= new Regexp
objRegExp.pattern = "\[LINK\]((.|\n)*?)\[\/LINK\]"
strlink=objRegExp.replace (strlink, "<a href="" $1 "">$1</a>")
objRegExp.Global=true
objRegExp.ignorecase=true
linkme=strlink
end function


response.write linkme(mylink)

I am passing mylink to the function.

Regexps are a pain in the butt and I'm not super solid on them just yet. They're fun, but the syntax can be confusing at times.

have fun

[Kodo]

additionally.. these are useless

name = Replace(name, "'", "'")
name = Replace(name, """", """)


it should just be name =replace(name,"'","''")
(you should avoid using NAME as a variable fyi)

[goldfish]

blah!

Lol, that whole thing for replaing Links, thats the only bit i didnt write, actually, a friend of mine wrote it for a guestbook and insisted i used it on everything on the website. Mind if i nick that function :D

[goldfish]

Wwwwaaaaaaaaaaaaaaaaaaaaaaaa i dont get regular whatzamgigits!
Dont get how thats supposed to help me?

[Kodo]

Regular Expressions..lol. They aren't the easiest things to understand at first, I'll give you that :) they do , however, relieve some work on the server and it's a function so you can pass a variable to the function of the regular expression and not have to do REPLACE on everything. Regular expressions allow you do search for patterns in strings that you define. What your buddy insisted on was stripping the HTML from the code so that no one would be able to "abuse" the site by allowing HTML to be interpreted upon display. If that's the case, the EASY way around that is to do
response.write (server.htmlencode(varname))
that prints the HTML out instead of interpreting it.

anyway, the original part of your question...did you get that fixed?

[goldfish]

No. im still having the same problem, and ive checked the type for the date.......
HtmlEncode sounds much better

[goldfish]

The EXACT error :

Microsoft JET Database Engine error '80040e14'

Syntax error in INSERT INTO statement.

/yarryarr/addnews.asp, line 43

line 43 is thus :

strSQL = "INSERT INTO news(user, sub_date, article, imgurl, title) Values('" & name & "','" & sub_date & "','" & post & "','" & pic_url &"','" & tehtitle & "')"
Conn.Execute(strSQL)

[Kodo]

Ok, I rewrote the whole thing for you.. it should work.

<%

author = Trim(replace(Request.Form("name"),"'","''"))
thetitle = Trim(replace(Request.Form("title"),"'","''"))
post = Trim(replace(Request.Form("article"),"'","''")) 'replace the vbcrlf with <br> tag upon display not while it's inserting. it saves db size.
pic_url = Request.Form("pic_url")
sub_date = date() 'format the date on display

if author = "" then
author = "Annon"
end if
If pic_url = "" then
pic_url = "none"
End If
msg = "submitted suscessfully"
strSQL = "INSERT INTO articles(author, sub_date, article, pic_url, title) Values('" & author & "',#" & sub_date & "#,'" & post & "','" & pic_url &"','" & thetitle & "')"
Conn.Execute(strSQL)
Conn.Close
Set Conn = nothing

%>

now when you want to display the data, you must do the formatting then
post=replace(post,vbcrlf,"<br>")
response.write server.htmlencode(post)

reponse.write formatdatetime(sub_date,1)


give it a try, and remember.. format upon dispaly not upon inserting.