Consider the SQL Insert statement below.
INSERT INTO table_name (table_field_name) VALUES (‘O’reill’, ‘92314567’)
Notice that there is an apostrophe in the text “O’reill”. In SQL, the apostrophe is an illegal character. It is interpreted as a string delimiter or the end of the string. So when it encounters to the text and detects another apostrophe, an error will occur.
A very simple solution to solve the problem: to allow apostrophes to be inserted into a database, simply double-up all occurrences of the apostrophes. So the final SQL will look like this:
INSERT INTO table_name (table_field_name) VALUES (‘O’’reill’, ‘92314567’)
In VB.NET, in order to convert the single enclosed apostrophe to two apostrophes, you can use Replace statement to accomplish the replacement.
"'" & Replace (txtName.text, "'", "''") & "'"
Good Luck!
2 comments:
Hi,
Just wanted to know how you implement this code into a SQL string, here is my current code:
("SELECT ProjectId FROM osaka_Projects WHERE ProjectName = '" & TextBox1.Text & "'")
Want to replace all apostrophes entered into "textbox1".
Thanks
use the REPLACE method e.g.:
inputSQL.Replace("'", "''");
Post a Comment