Add parameter value that contains a single quote (C#)

a.peeringa

I am trying to set a value to a parameter for an OracleCommand object. The SQL statement selects based on the value or set of values contained in the string, e.g. "WHERE work_schedule IN ('CJ','CL','CP')". If I set the value to a string that does not contain a single quote, the query works and I get the expected results. If I pass it a string that contains a single quote, it returns ORA-01403 (no rows found). I have tried preceding each single quote with another single quote, preceding each single quote with a backslash, and trying each of these syntaxes AND preceding the entire string with '@', which according to .net documentation, forces .net to read the string 'verbatim'. NONE of these syntaxes work. I have used parameterized queries in C for years. I am new to C# and .net. There MUST be a way to create and execute a query which searches for multiple values in C#!! I can't believe there isn't. Does anyone know how to do this?? Thanks

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

spgilmore21y ago
This works: "WHERE work_schedule IN ('CJ','CL','CP')" This doesn't, because there is an apostrophe in the last item. "WHERE work_schedule IN ('CJ','CL','isn't working')" Correct it like this: "WHERE work_schedule IN ('CJ','CL','isn" + (char)39 + "t working')" Or instead of concatenating with ASCII char 39, you can put a Unicode character literal in there with the \x escape sequence. Here are examples from the MSDN help. char MyChar = 'X'; // Character literal char MyChar = '\x0058'; // Hexadecimal char MyChar = (char)88; // Cast from integral type char MyChar = '\u0058'; // Unicode If you didn't know that character 39 was ASCII single quote, it might also be useful to get your hands on an ASCII chart. #34 is a double-quote, #9 is a tab, #32 is a space, #13 is a carriage return, #10 is a line feed, #26 is end-of-file, etc. These values are typically valid only for the ASCII encoding character set. If you use a different encoding, your values may vary.
miker21y ago
Check this out. It's for SQL server, but i hoe it may help you. http://support.microsoft.com/default.aspx?scid=kb;en-us;555167