Solved

Apostrophe in insert command

Posted on 2010-08-19
11
563 Views
Last Modified: 2012-06-27
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?
0
Comment
Question by:Vx_Chemical
  • 2
  • 2
  • 2
  • +4
11 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33472656
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)
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33472659
0
 
LVL 10

Assisted Solution

by:Jini Jose
Jini Jose earned 250 total points
ID: 33472666
TRY THIS


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);



            try

            {

                SQLData.ExecuteNonQuery(Util.Constring, CommandType.Text, cQuery, oparam);

                lblMessage.Text = "Successfully Updated";

            }

            catch (Exception ex)

            {

                lblMessage.Text = ex.Message;

            }

Open in new window

0
 
LVL 5

Expert Comment

by:muhammadyasir
ID: 33472675
use the StoredProcedure to insert any data then there will be no problem.
0
 
LVL 18

Expert Comment

by:Rajar Ahmed
ID: 33472678
Just Replace one Apstrophe with one more




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



INSERT Table1 (chColumnName) VALUES (fStory)

Open in new window

0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 5

Expert Comment

by:muhammadyasir
ID: 33472679
0
 
LVL 12

Expert Comment

by:HugoHiasl
ID: 33472724
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...
0
 
LVL 2

Accepted Solution

by:
JuniorMember earned 250 total points
ID: 33473683
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);
            cmd.Parameters.Add(SqlParameter1);

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

cmd.ExecuteNonQuery();




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;


0
 
LVL 10

Expert Comment

by:Jini Jose
ID: 33474157
if you are using parametered query then there is no need for a replacement for ( ' ).
0
 

Author Comment

by:Vx_Chemical
ID: 33508357
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 ``
0

Featured Post

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Why Does This Page Not Show Up? 4 24
Split in Javascript 5 31
SQL Login 17 40
Show/Hide ASP.NET controls with JQuery best practices 3 19
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now