Syntax error (missing operator) in query expression

dealme · #1 (**permalink**) 01-14-10, 20:13

My syntax upon clicking the submit button is as follows : -
Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error (missing operator) in query expression '''','Courier',''''.
/admin/incomingmail/incoming_mail_saveto.asp, line 34

and I've discovered it happens only when I input a character ' as the input value. Can anyone give me pointers on how to solve this?

<html>
<body>
 Your submission is as follows:-
<%
'variable name on left
name=request.form("name")
nature_of_mail=request.form("nature_of_mail")
date_received=request.form("date_received")
time_received=request.form("time_received")
description_of_sender=request.form("description_of _sender")
attention_to=request.form("attention_to")
hand_over_to=request.form("hand_over_to")

'Display data entered
response.write "Name : " & name & " "
response.write "Nature of mail : " & nature_of_mail & " "
response.write "Date received : " & date_received & " "
response.write "Time item lost : " & time_received & " "
response.write "Description of sender : " & description_of_sender & " "
response.write "Attention to : " & attention_to & " "
response.write "Hand over to : " & hand_over_to & " "

sConn ="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("incoming_mail.mdb")
Set oConn=server.createobject("adodb.connection")
oConn.Open sConn
SQL = "insert into incoming_mail (name,nature_of_mail,date_received,time_received,d escription_of_sender,attention_to,hand_over_to) values ("
SQL = SQL & "'" & name & "',"
SQL = SQL & "'" & nature_of_mail & "',"
SQL = SQL & "'" & date_received & "',"
SQL = SQL & "'" & time_received & "',"
SQL = SQL & "'" & description_of_sender & "',"
SQL = SQL & "'" & attention_to & "',"
SQL = SQL & "'" & hand_over_to & "')"
oConn.Execute(SQL)
oConn.Close
%>

</body>
</html>

sco08y · #2 (**permalink**) 01-15-10, 02:10

You need to use a parameterized query. More info here and here.

Code:

SQL = "insert into incoming_mail 
(name,nature_of_mail,date_received,
time_received, description_of_sender,attention_to,
hand_over_to) values (?, ?, ?, ?, ?, ?, ?)"
Set objCmd = server.createobject("adodb.command")
objCmd.CommandText = SQL
 Set objParm1 = objCmd.CreateParameter("name", adChar, _
                    adParamInput, 5, name)
objCmd.Parameters.Append objParm
...
objCmd.Execute

dealme · #3 (**permalink**) 01-17-10, 09:57

Thanks...I'll give it a try and keep u updated.

Internet Services

Web Development

Digital Marketing

Consumer Tech