ADO.NET and single quotes not being handled

[John Gamble]

Hi,

Got a question for you all regarding ADO.NET and single quotes …

I am creating stored procedure parameters, assigning them to a ADO
Command object then executing the Stored Procedure. All works
wonderfully, but when I need to insert the line:

US,Chicago O'Hare Airport-Ord,IL

It complains about the single quote in O'Hare. Now I know ADO.Net is
supposed to handle this for me, but it clearly isn't. I have also
tried escaping it myself using another ' so the string becomes:

US,Chicago O''Hare Airport-Ord,IL

But this generates the same error message below.

Error message:

Line 1: Incorrect syntax near 'Hare'.
Unclosed quotation mark before the character string ',1)'.
..Net SqlClient Data Provider

Code:

DBAccess.DBAccess oDB = new DBAccess.DBAccess();
oDB.SP = "CDR_Add"; // Stored Procedure name
oDB.setConnection();
oDB.setCommand();

SqlParameter oP6 = new SqlParameter("@Location",SqlDbType.VarChar,50);
oP6.Value = "US,Chicago O'Hare Airport-Ord, IL";
oDB.oCommand.Parameters.Add(oP6);

oDB.oConnection.Open();
oDB.oCommand.ExecuteNonQuery();
oDB.close();
oDB = null;

DBAccess is my own wrapper which I use to simplify connections to the
database and ensures that all connections have been closed etc. I've
exposed more of its functionality than is necessary so you can see
what is going on. Let me stress this works perfectly well for strings
that don't have the have the apostrophe. With regard to adding a
parameter to a ADO Command object can anybody seen this behaviour
before?

Thanks in advance

John

 

[Miha Markic]

Hi John,

What exactly does your stored procedure do?
Because parenthesis in parameter's value doesn't cause this problem.

 

[William \(Bill\) Vaughn]

We do this all the time, so I'm confident that it's the way that you're
setting up the SqlCommand object. First are you setting the CommandType to
stored procedure? This can account for the problem. I would eliminate the
special way you're building the Command objects and see if the problem is
there. Turn on the profiler to see what's getting sent.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[John Gamble]

Hi Miha,

Thanks very much for your reply to my post. The code I posted was
indeed working correctly and the error was coming from a completely
separate routine. The problem is now fixed.

Thanks again.

John