[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Apostrophe in insert command

Posted on 2010-08-19
11
Medium Priority
?
583 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
10 Comments
 
LVL 143

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 143

Expert Comment

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

Assisted Solution

by:Jini Jose
Jini Jose earned 1000 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

591 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