Apostrophe in insert command

When trying to insert a sentence with an apostrophe, nothing happens. I reckon its because the sign is code as well and it destroys the meaning of the page for the system, is there anyway around it?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
I presume you are building the SQL query ad-hoc using user input.

you should use parametrized queries, that way this issue, including SQL Injection, will be solved (and code will be more readable also, queries should execute faster etc)
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Jini JoseSenior .Net DeveloperCommented:

cQuery = "INSERT INTO [PAList]" +
                    " ([PAListId],[NomineeName])" +
                    " SELECT" +
                    " @PAListId" +
                    " ,@NomineeName";

            SqlParameter[] oparam = new SqlParameter[2];
            oparam[0] = new SqlParameter("@PAListId", PAListId);
            oparam[2] = new SqlParameter("@NomineeName", txtNomineeName.Text);

                SQLData.ExecuteNonQuery(Util.Constring, CommandType.Text, cQuery, oparam);
                lblMessage.Text = "Successfully Updated";
            catch (Exception ex)
                lblMessage.Text = ex.Message;

Open in new window

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

use the StoredProcedure to insert any data then there will be no problem.
Rajar AhmedConsultantCommented:
Just Replace one Apstrophe with one more

fStory = replace ( fStory ,"'","''")

INSERT Table1 (chColumnName) VALUES (fStory)

Open in new window

Like angellll said. It's a good approach to always use parametrized queries.

This will also keep your system clean and secure from sql injections in log in screens. It helps you also when you are dealing with utf8 characters etc...
there are two ways to do this:

A. the safest method is to use a parameter based approach (sql procedure or just SqlParameter):
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "INSERT INTO Table1 (FirstName, Address) VALUES (@FirstName, @Address)";

            SqlParameter SqlParameter1 = new SqlParameter("@FirstName", Textbox1.Text);

            SqlParameter SqlParameter2 = new SqlParameter("@Address", Textbox2.Text);


B. add one extra line before the sql excution:
1 mysqlstring = Textbox1.Text;
2 mysqlstring = mysqlstring.Replace( "'", "'" + CHAR(29) + "'" );
3 //execute the SQL mysqlstring here;


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jini JoseSenior .Net DeveloperCommented:
if you are using parametered query then there is no need for a replacement for ( ' ).
Vx_ChemicalAuthor Commented:
The system i am using is on a secure network with only cleared users. So if i didnt want to change too much of the code. How would i easiest exchange one ` for two ``
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.