Click here to Skip to main content
12,251,445 members (57,122 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: ASP.NET4
When there is a Word Such as " it's " , then it generate a error , sql syntax error , check your mysql manual .

we can use apostrophe two times instead of single or backslash ,but this is not solution

Is there any way , so anyone can directly post any word having apostrophe.

really need solution.
i am using vb language and mysql db.
Posted 26-Jan-13 1:28am
Edited 28-Apr-16 10:56am
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Don't concatenate strings to build your command.

For example, if your code is:
MySqlCommand cmd = new MySqlCommand("INSERT INTO myTable (myColumn) VALUES('" + myTextBox.Text + "')", con);
and your text box contains
It's cold today
The the command as seen by SQL is:
INSERT INTO myTable (myColumn) VALUES('It's cold today')
which will cause an error becasue it assumes the
'It'
is the value to insert in the myColumn field, and
s cold today'
is a further part of the actual command.

Instead, use parametrised queries:
MySqlCommand cmd = new MySqlCommand("INSERT INTO myTable (myColumn) VALUES(@MC)", con);
cmd.Parameters.AddWithValue("@MC", myTextBox.Text);
Using this also protects you from accidental or deliberate SQL Injection attacks, which can damage or destroy your database.
  Permalink  
Comments
Mike Meinz 26-Jan-13 7:42am
   
Excellent example! Shows how to handle the single quote issue, prevent SQL Injection Attacks and use parameters to improve SQL Server performance.
MinhajAli 27-Jul-13 3:24am
   
Thanks alot Excellent answer
OriginalGriff 27-Jul-13 3:33am
   
You're welcome!
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

 using--
 replace(textbox.text,"'","''") solve inserting issues in mysql or sql
 
just use any control in replace function which automatically replace the single
( ' )with this ( '' )and no syntax error.Major Problem of Inserting apostrophe in Sql ,
Hence solved....
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 4

The problem is there because you are passing the query directly. Use parameterized query and you will find the problem solved.
  Permalink  
Comments
Richard Deeming 28-Apr-16 17:19pm
   
As mentioned in Solution 1 - over THREE YEARS AGO!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160426.1 | Last Updated 28 Apr 2016
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100