Dealing with Apostrophe 1

[wallaceoc80]

I have an ASP.NET web form using C# as my code behind language. I am working with an oracle database am having problems if I try to insert a new value into the database if it has letters such as apostrophe's in it. The code I'm using is below:

string createMessageSQL = "insert into messages(title, author, body, board, thread, date_created) " + "values(@subject, '" + Session["user"] + "', @body, " + strBoardID + ", " + strThreadID + ", " + "sysdate)";

OleDbCommand cmdCreateMsg = new OleDbCommand(createMessageSQL, conn);

cmdCreateMsg.Parameters.Add("subject", Server.HtmlEncode(tbNewReplySubject.Text));

cmdCreateMsg.Parameters.Add("body", Server.HtmlEncode(tbNewReplyBody.Text));

However, this doesn't deal with it! Any ideas what is wrong here?

Thanks for the help,

Wallace

 

[chiph]

You're using ADO parameter objects, which is good, except for this one value:

'" + Session["user"] + "'

What are the contents of your user variable? Is it possible to have a value like "O'Brady" in there?

Chip H.


____________________________________________________________________
Click here to learn Ways to help with Tsunami Relief
If you want to get the best response to a question, please read FAQ222-2244 first

 

[ca8msm]

As chiph points out it is probably because you are not passing the user as a parameter (passing the values as parameters takes care of apostrophes.

You have two choices:

1) Change the statement so the author field is inserted by passing it a parameter

2) Use the Replace function on the Session["user"] value to replace single quotes with double quotes (e.g. MyString = MyString.Replace("'","''") )

----------------------------------------------------------------------

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[wallaceoc80]

I fixed the problem using:

OleDbCommand cmdCreateMsg = new OleDbCommand("insert into messages(title, author, body, board, thread, date_created) " +
"values(?,?,?,?,?,?)", conn);

cmdCreateMsg.Parameters.Add("subject", Server.HtmlEncode(tbNewReplySubject.Text));
cmdCreateMsg.Parameters.Add("Author", Session["user"] );
cmdCreateMsg.Parameters.Add("body", Server.HtmlEncode(tbNewReplyBody.Text));
cmdCreateMsg.Parameters.Add("Board", strBoardID);
cmdCreateMsg.Parameters.Add("Thread", strThreadID);
cmdCreateMsg.Parameters.Add("DateCreated", DateTime.Now);

Thanks for the help,

Wallace

 

[chiph]

Star for Wallaceoc80, for discovering the benefits of ADO parameter objects.

You'll also notice a performance boost by using them. SQL Server is able to store this query in it's query cache now, reducing the amount of time needed to run it subsequent times.

Chip H.


____________________________________________________________________
Click here to learn Ways to help with Tsunami Relief
If you want to get the best response to a question, please read FAQ222-2244 first