Prepared Statements, Oracle & C#

Hi.

I'm simply trying to use Prepared Statements with Oracle and c#.

I tried several code snippets from all over the web and my brain (It works with java&oracle or .NET&MSSQL...)

Here's my current test-code-snippet:

                sqlStatement = "INSERT INTO tmp (ID, NAME) VALUES(@ID, @NAME)";
                OracleCommand cmd = new OracleCommand(sqlStatement, this.dbCon);

                cmd.Parameters.Add(new OracleParameter("@ID",   OracleDbType.Varchar2, 15));
                cmd.Parameters.Add(new OracleParameter("@NAME", OracleDbType.Varchar2, 15));

                cmd.Prepare();

                cmd.Parameters["@ID"].Value = "3wfwef";
                cmd.Parameters["@NAME"].Value = "lyxfggh";

                cmd.ExecuteNonQuery();

Whenever I try to Execute the Statement I get the following execption:
Oracle.DataAccess.Client.OracleException ORA-00936: missing expression
The exeption is not the problem, it is clear: The db always tries to insert the dummy parameters (eg.: @ID)  to the table, and throws an error because (in our case because of VARCHARs) of the missing apostrophs.

It seems that it does not even try to precompile the SQL Statement.

Any suggestions?

Best Regards Gernot

securityresearchAsked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
I've never used 'Prepare' but I don't think that's the problem.  Bind variables in Oracle use the colon not @.

Give this a try:
sqlStatement = "INSERT INTO tmp (ID, NAME) VALUES(:ID, :NAME)";


0
 
securityresearchAuthor Commented:
Thanks a lot, this was the problem.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.