Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

ASP tries to process text field contents 2

Status
Not open for further replies.
Webflex

Webflex

Technical User
Apr 20, 2001
101
GB
I have a page that submits to an access database, in a description (text) field people can type in the information to be stored in the des field of the database (the html is below)
<td> <font face=&quot;Verdana&quot; size=&quot;2&quot;> Description:</font></td>
<td>
<input type=&quot;text&quot; name=&quot;des&quot;> value=&quot;<%=des %>&quot;>

however if someone adds something formatted as below

Open Form 'Master Items'.

Enter new Oracle Item Code and description

etc etc

VALUED for all except CAB which is ABC EXPENSED).

SAVE.

End

it tries to process the Open Form statement in the text field and throws an error like this

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''Open Form 'Master Items'.

How can I stop this without setting rules on my users to what they can add to the box, any ideas ? My ASP knowledge is fairly basic.

Regards

Ian
 
Sort by date Sort by votes
Hi!

The problem is not that it runs Open Form. Seems it is in the way you pass the textbox value to the server. How you pass that value? Can you send here these rows of code?

If you use ADO for working with data and saving is just an updating of the ADO recordset, send a code, so we will see where is a problem.

If you use direct sending of the SQL command to update data,
you can solve this 2 way. One is use the parameters in the query and ADO parameters collection. Than provide values as is in the parameters. Another is to fix the value by replacing invalid characters in it. Again, need mroe information for that.


Vlad Grynchyshyn
vgryn@softserve.lviv.ua
The professional level of programmer could be determined by level of stupidity of his/her bugs
 
Upvote 0 Downvote
OK - here is the code from the page


<%
option explicit
dim useraction
dim dsn,sql,conn,rs
dim cat,typ,itm,des
useraction=request(&quot;action&quot;)
select case useraction
case &quot;add&quot;
cat=request.form(&quot;cat&quot;)
typ=request.form(&quot;typ&quot;)
itm=request.form(&quot;itm&quot;)
des=request.form(&quot;des&quot;)
sql = &quot;insert into geosol (cat,typ,itm,des) values('&quot;&cat&&quot;','&quot;&typ&&quot;','&quot;&itm&&quot;','&quot;&des&&quot;')&quot;
dsn=&quot;DBQ=&quot; & Server.Mappath(&quot;../db/geosol.mdb&quot;) & &quot;;Driver={Microsoft Access Driver (*.mdb)};&quot;
set conn=server.createObject(&quot;adodb.connection&quot;)
conn.open dsn
conn.execute(sql)
conn.close
set conn = nothing
response.write &quot;The solution added successfully&quot;
end select
%>
<html>

<head>
<LINK REL=STYLESHEET HREF=&quot; TYPE=&quot;text/css&quot;>

<title>add</title>

</head>

<body>
<table border=0 width=300>
<tr>
<td colspan=2 align=center><b><font face=&quot;Verdana, Arial, Helvetica, sans-serif&quot; size=&quot;3&quot;>
Solutions Database</font><br>
&nbsp;<br>
</b><font face=&quot;Verdana&quot; size=&quot;2&quot;> Add New Solution</font><br>
</tr>
<td width=&quot;514&quot;><form name=&quot;addform&quot; action=&quot;add.asp?action=add&quot; method=&quot;post&quot;>

<tr>
<td width=&quot;514&quot;><font face=&quot;Verdana, Arial, Helvetica, sans-serif&quot; size=&quot;2&quot;>Category.:</font>
</td>
<td width=&quot;280&quot;>
<input type=&quot;text&quot; name=&quot;cat&quot; size=30 value=&quot;Software&quot;>
</tr>
<tr>
<td width=&quot;514&quot;><font face=&quot;Verdana, Arial, Helvetica, sans-serif&quot; size=&quot;2&quot;>Type.:</font></td>
<td width=&quot;280&quot;>
<input type=&quot;text&quot; name=&quot;typ&quot; size=30 value=&quot;Oracle Applications&quot;>
</tr>
<tr>
<td width=&quot;514&quot;><font face=&quot;Verdana, Arial, Helvetica, sans-serif&quot; size=&quot;2&quot;>Item:</font></td>
<td width=&quot;280&quot;>
<select name=&quot;itm&quot;>
<option value=&quot;Accounts Payable&quot;>Accounts Payable</option>
<option value=&quot;Accounts Receivable&quot;>Accounts Receivable</option>
<option value=&quot;Cargo Manifest&quot;>Cargo Manifest</option>
<option value=&quot;Cash Management&quot;>Cash Management</option>
<option value=&quot;Financials&quot;>Financials</option>
<option value=&quot;Fixed Assets&quot;>Fixed Assets</option>
<option value=&quot;General Ledger&quot;>General Ledger</option>
<option value=&quot;Human Resources&quot;>Human Resources</option>
<option value=&quot;Inventory&quot;>Inventory</option>
<option value=&quot;Order Management&quot;>Order Management</option>
<option value=&quot;Other&quot;>Other</option>
<option value=&quot;Proj. Time and Expense&quot;>Proj. Time and Expense</option>
<option value=&quot;Project Billing&quot;>Project Billing</option>
<option value=&quot;Projects&quot;>Projects</option>
<option value=&quot;Purchasing&quot;>Purchasing</option>
<option value=&quot;Self-service HR&quot;>Self-service HR</option>
<option value=&quot;Self-sevice Purch.&quot;>Self-service Purch.</option>
<option value=&quot;Self-service Time Entry&quot;>Self-service Time Entry</option>
</select>
</tr>
<tr>
<td width=&quot;514&quot;><font face=&quot;Verdana, Arial, Helvetica, sans-serif&quot; size=&quot;2&quot;>Description:</font></td>
<td width=&quot;280&quot;>
<textarea name=&quot;des&quot; cols=&quot;75&quot; rows=&quot;20&quot;></textarea>

</tr>
<tr>
<td width=&quot;514&quot;>&nbsp; </td>
<td width=&quot;280&quot;>&nbsp;
</tr>
<tr>
<td width=&quot;514&quot;>&nbsp; </td>
<td width=&quot;280&quot;>&nbsp;
</tr>
<tr>
<td colspan=2 align=&quot;center&quot;>
<input type=&quot;submit&quot; value=&quot;add&quot;>
</tr>
</table>
<!--#include file=&quot;footer.inc&quot;-->
</body>
</html>

Regards
Webflex
 
Upvote 0 Downvote
The problem lies in the fact the user entered a single quote (') in the text box. You need to double the single quote before running your SQL statement.

Ex.
des=request.form(&quot;des&quot;)
des = replace(des, &quot;'&quot;, &quot;''&quot;) 'The last argument is a double quote, two single quotes and a double qoute (Just incase it is hard to read).

This should solve your problem.

Gabe
 
Upvote 0 Downvote
Hi!

Well, the problem is in the passing parameters for INSERT command. Such string as &quot;Bla-bla 'Something' here here&quot; will cause error, because INSERT command will look like following:
INSERT ... VALUES ('Bla-bla 'Something' here here',...
As you see, 'Something' causes spoiling of the SQL command syntax.

As I mensioned, there are 2 solutions.
Most systems accept doubling of the single quotes. Command like following:
INSERT ... VALUES ('Bla-bla ''Something'' here here',...

Probably will work correctly. To do this in ASP using VB, after you take a values of the request form variables, you should replace all single quotes in string by the 2-character single quotes.

Another solution is to use ADO parameters. You command will look like following:
sql = &quot;insert into geosol (cat,typ,itm,des) values(?,?,?,?)&quot;

Than prepare parameters:
set oCommand = Server.CreateObject('ADODB.Command')
oCommand.CommandText=sql
oCommand.CommandType=1
oCommand.Parameters.Append oCommand.CreateParameter(cat,201,1,len(cat)+1,cat))
...
set oCommand.ActiveConnection = conn
oCommand.Execute()

Note that you should call execute of command object, not a connection.

Second approach works for any characters and for very large text data.

Hope this helps.


Vlad Grynchyshyn
vgryn@softserve.lviv.ua
The professional level of programmer could be determined by level of stupidity of his/her bugs
 
Upvote 0 Downvote
GabeC / TomasDill

Thank you for your assistance the quotes issue has resolved the problem.

Ian
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question
Return to current location on page after asp processes.
Replies
2
Views
381
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question
How to send HTML emails with ASP classic?
Replies
0
Views
449
leifoet
leifoet
penguinspeaks
  • Locked
  • Question
write HTML code within ASP line
Replies
1
Views
462
xwb
xwb
penguinspeaks
  • Locked
  • Question
READ TEXT FILE BASED ON VARIABLE 1
Replies
2
Views
361
guitarzan
guitarzan
penguinspeaks
  • Locked
  • Question
Parse a database field that is comma delimited and input the results into another table
Replies
0
Views
330
penguinspeaks
penguinspeaks

Part and Inventory Search

Sponsor

Back
Top