SQL Error

[nmretd]

I am getting an error with my SQL when someone tries to register some details on my website. I am using ASP with a MySQL Database.
The Error is as follows:

INSERT INTO table1 values('Sam's','Place', NULL)

You have an error in your SQL syntax near 's Place', NULL)' at line 1

The error is being caused by the apostrophe in sam's.

How can I resolve this ?

[Skeen]

Are 'Sam's', 'place' variables?
If so do this:

SQL = " INSERT INTO table1(field names should go here) " _
& " values('" & Sam's & "','" & Place & "', NULL)"
Set SQL = ConnectionObjectName.Execute("SQL")

Here you are setting thsql command to a variable calle 'SQL'. You then execute it by associating it witha connection to your database by means of a connection object.

This is how I do it in vb script and it seems to work a treat!

Hope this helps,

Cheers 'n' Beers

Skeen

[nmretd]

Hi Skeen

The problem is that I don't know when the user will submit a word with an apostophe '. When this is not the case it all works ok.

However, if a variable called strClub includes an apostrophe (eg strClub = "sam's place") there is an SQL error. Is there any way I can check the value of the variable strClub to see if it contains an apostrophe, remove it and then parse the SQL ?

[vbdeveloper]

First store the user inputted value in a string
Use Instr() function to locate the apostrophe(if any).

if the apostrophe exists then
replace the apostrophe with a space
use the variable in the sql statement
else
directly use the variable in the sql statement.


Happy Programming!!!!

[monte96]

Or you will have to translate the apostrophe to a different character when writing and back to an apostrophe when reading.

The '~', '|', '`' characters are all good replacements as they are not common in english only systems. You can then parse for them when reading. It adds another layer to the process but unless your users can police themselves from using apostrophes, you don't have alot of options.

(users police themselves.. bahahahaha.. I kill me)