Dealing with ' (Apostrophise) within SQL statments.

[ukwebsite]

Please help im trying to insert data from a form into a Access database


If someone puts an apostrophise it causes an error with the SQL statment. How do i get around it.

Thanks
Matthew Wilde
matthew@ukwebsite.com

 

[luciddream]

write a function/subroutine to escape them.

 

[ukwebsite]

How?
Matthew Wilde
matthew@ukwebsite.com

 

[WaltLukeIII]

There should be some command like InString("x", Variable)

Then use the If Instring("x", Variable) > 0 then
do something
end if

The sytax may not be exact on Instring check your help menu for options or the index of your book if you have one.

Walter III


Walt III
SAElukewl@netscape.net

http://www.muc.edu/~lukewl

 

[ukwebsite]

Thanks ill have a look and post the result back to help other people.
Matthew Wilde
matthew@ukwebsite.com

 

[ukwebsite]

SOLVED IT - TESTED AND WORKS

What ever variable you send to the SQL statment. Make sure you pass it through this line:

SQLVariable = replace(SQLVariable,"'","''&quot

This will solve your apostrophe nightmare.


Cheers
Matthew Wilde
matthew@ukwebsite.com

 

[culshaja]

I have previously posted a much better solution. This is shown below:

set cmInsert as Server.CreateObject("ADODB.Command")
set cmInsert = cnDB 'your connection object
cmInsert.CommandType = 1
cmInsert.CommandText = "INSERT INTO myTable (Name) VALUES (?)"
cmInsert.Parameters.Append cmInsert.CreateParameter("Name",200,1,,myVar)
cmInsert.Execute

This can also be set to be prepared and so will become a temporary stored procedure.

James
James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305

 

[ukwebsite]

If your trying to update a database you could also use the ADO style of updating a database. This causes no problems as you arnt using sql scripts.

But trust me try this

SQLVariable = replace(SQLVariable,"'","''&quot

It does work and its only one line of code extra. EASY

Matthew Wilde
matthew@ukwebsite.com

 

[Stevan]

This handy little function was posted on another ASP forum.
I have changed a couple of things.

I am replacing the both the Apostrophe and the Double Quotes with a Left Quote. You could change this to just remove those characters, but for my users, they gain comfort in at least seeing some sore of quote mark.

This works fine in both Access and SQL Server.


'' ***** Clean Evil Characters from Comments Field *****

myData = CleanEvilCharacters(dataString)

Function CleanEvilCharacters(varTemp)
varTemp = Replace(varTemp,&quot;<&quot;,&quot;&quot
varTemp = Replace(varTemp,&quot;>&quot;,&quot;&quot
varTemp = Replace(varTemp,&quot;{&quot;,&quot;&quot
varTemp = Replace(varTemp,&quot;}&quot;,&quot;&quot
varTemp = Replace(varTemp,Chr(34),&quot;`&quot
varTemp = Replace(varTemp,&quot;'&quot;,&quot;`&quot
varTemp = Replace(varTemp,&quot;%&quot;,&quot;&quot
varTemp = Replace(varTemp,&quot;;&quot;,&quot;&quot
varTemp = Replace(varTemp,&quot&quot;,&quot;&quot
varTemp = Replace(varTemp,&quot;(&quot;,&quot;&quot
varTemp = Replace(varTemp,&quot;&amp;&quot;,&quot;&quot
varTemp = Replace(varTemp,&quot;+&quot;,&quot;&quot
' varTemp = Replace(varTemp,&quot;-&quot;,&quot;&quot
CleanEvilCharacters = varTemp

End Function

Comments = Request(&quot;Comments&quot
Call CleanEvilCharacters(Comments)

-- Steve