Inserting row with single quote

I'm inserting a row of people's names and addresses to a table. This seems to work great, unless the person has ' in their name, for example O' Riordan. When the address has single quote (such as Wilder's Path), I'd get the same error, unclosed quote when inserting the row.

What would be the best way to work around this, other than read the line and replace it with a space.

In a literal you need to double embedded quotes, so it'd be 'O''Riordan'.

The best solution is to use a parameterized query, then you don't have to worry about embedded quotes.

|||How to use parameterized query? the program i currently using are java.. can show me some examples on how to use parameterized query. ^-^|||

Parameterized query is very useful but you can also use string concatenation. I've written a sample on my blog on how to insert records with single quotes. Here's a sample TSQL script

PRINT 'This is the man''' + 's choice'

In order to do this, you use a two-single quote approach. Notice the use of a concatenation operator to separate the first part of the sentence and the one that comes after the single-quote and the use of a two-single quote approach. This simpy means that every time you have a single-quote character included in your string, you have to do string concatenation to accomodate the insertion of a single-qoute in your string.

|||

Take a look at these links

http://msdn2.microsoft.com/en-us/library/ms378878.aspx

http://msdn2.microsoft.com/en-us/library/ms378138.aspx

Hope this helps

|||Sorry.. after looking thru the website i still dun know how cause my sql statement is like that..

String query = "INSERT INTO addresses (" +
"company, address, name, hp, " +
"phone, fax, email, start, day, month, year, " +
"mrc, isp, des, sale, mark" +
") VALUES ('" +
fields.company.getText() + "', '" +
fields.address.getText() + "', '" +
fields.name.getText() + "', '" +
fields.hp.getText() + "', '" +
fields.phone.getText() + "', '" +
fields.fax.getText() + "', '" +
fields.email.getText() + "', '" +
fields.start.getText() + "', " +
Integer.valueOf(dd) + ", " +
Integer.valueOf(mm) + ", " +
Integer.valueOf(yy) + ", '" +
fields.mrc.getText() + "', '" +
fields.isp.getText() + "', '" +
fields.des.getText() + "', '" +
fields.sale.getText() + "', '" +
fields.mark.getText() + "')";

I may need to type S'pore in the fields.address.getText() but instead of ' i need to type " which is very troublesome... in this way how should i replace the " to ' ?

|||If you're not comfortable with this approach, create a function in your application that replaces the single quote to double quote. The sure-st way there is is to create a parameterized query