I am using Visual Studio 2005 pro and C#.
I am converting part of my application to work in SQL database, but I have a slight technical difficulty to get over.
My application decodes a file which contains names and stores them into a database. However, some of the records in the decoded file contains the apostrophe ' symbol which causes problems with my varchar strings that I need to build and send to the SQL database, for example, the name " St. James' " contains the apostrophe symbol which is also a termination of a varchar string in SQL syntaxes.
Is there any way I can get around this
Also, if I need to drop/adjust this character from the string, what's the easiest way to do this
Thanks,
Sean.
Help with apostrophe symbols in database items
ChrisTullier
lorijean44
The easiest, best, and safest way to handle this kind of problems, is by using parametrized queries.
Instead of concatening the values you want to enter in the database, you use parameters.
For instance, instead of buidling your query like this:
string query = "INSERT INTO people (name, age) values ( \'" + strName + \'" , + " intAge + " )";
You do it like this:
string query = "INSERT INTO people (name, age) values (@p_name, @p_age)";
Then, you add parameters to your SqlCommand, and you give values to the parameters:
SqlCommand cmd = new SqlCommand();
cmd.CommandText = query;
cmd.Parameters.Add ("@p_Name", SqlDbType.Varchar).Value = strName;
You don't have to bother about quotes in the name, date/time formats, sql injection, etc...
For more information, see here:
http://fgheysels.blogspot.com/2005/12/avoiding-sql-injection-and-date.html
Vincent Josset
Hi
For those following this thread, I have fixed the problem. I did the following with my code to double up the apostrophe symbols:
string tempstr = "";
string newstr = "";
for (x=0; x< tempstr.Length; x++)
{
if (tempstr.Substring(x,1) != "'" )
newstr += tempstr.Substring(x,1);
else
newstr += tempstr.Substring(x,1) + tempstr.Substring(x,1);
}