Solved

Apostrophe in insert command

Posted on 2010-08-19
11
564 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

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 …
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.

777 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