CODEWITHSUNDEEP
c#oracle-database
Keshani
Keshani

Reputation: 3

ORA-00907: missing right parenthesis with a SQL Statement in C#

When running the below query getting the above error,

String sql = "((NAME= '" + sReceipts[0] + "' ) OR (SECTION IN ('RECEIPT', 'PROJECT') AND NAME IS NULL))";

sReceipts[0] value is 'Tom's' (with an APOSTROPHE) gives the error

But if sReceipts[0] = 'Ann' no error occur.

Tried to solve with String.Format but I couldn't. What is the best approach please?

Upvotes: 0

Views: 725

Answers (2)

SpruceMoose
SpruceMoose

Reputation: 10320

You should use parameterised queries such as the below:

OracleCommand oraCommand = new OracleCommand("SELECT YourColumn FROM 
YourTable WHERE ((NAME= :receiptName ) OR (SECTION IN ('RECEIPT', 'PROJECT') AND NAME IS NULL))", db);
oraCommand.Parameters.Add(new OracleParameter("recieptName", sReceipts[0]));

See MSDN OracleCommand.Parameters Property if you are using the Microsoft class and OracleParameterCollection for the equivalent Oracle parameter collection.

Upvotes: 4

Mark Wagoner
Mark Wagoner

Reputation: 1769

I would wrap sReceipts[0] in a regular expression that replaces the apostrophe with two apostrophes.

Escaping single quote

Oracle regular expressions

Upvotes: -2

Related Questions