Retrieving Oracle data using a SqlDataSource and a C# prepared statement

Question

I'm attempting to write a prepared statement for retrieving data using a SqlDataSource, but cannot seem to compose a good SelectCommand statement (7th line below) to do this. Could someone please shed some light on how to do this?

OracleCommand cm = new OracleCommand();
cm.CommandText = "SELECT FTPOSCODE FROM PPTBL WHERE DESC LIKE '%" + "@parm1" + "%";
cm.Parameters.Add("@parm1", "ACCOUNT");
OracleConnection cn = new OracleConnection();
cn.ConnectionString = ConfigurationManager.ConnectionStrings["ORCLConnectionString"].ConnectionString;
cn.Open();
SqlDataSource1.SelectCommand = cm.ToString();  // "ORA-00900: invalid SQL statement"
cn.Close();

The above was simplified. Slawomir and I continued in chat and came up with the following working code. Thanks Slawomir!

using (OracleConnection cn = new OracleConnection(ConfigurationManager.ConnectionStrings["ORCLConnectionString"].ConnectionString)) {
    SqlDataSource1.SelectCommand = "SELECT FTPOSCODE, PTPOSCODE, POSCLASSTITLE, FKSA, JOBCAT, PAYGRADE, HRPAYRANGE, MONTHPAYRANGE FROM HR_PAY_PLAN WHERE UPPER(POSDESC) LIKE :1 OR UPPER(POSCLASSTITLE) LIKE :2 ORDER BY POSCLASSTITLE";
    SqlDataSource1.SelectParameters.Add(":1", "%" + key1 + "%");
    SqlDataSource1.SelectParameters.Add(":2", "%" + key1 + "%");
}

Sławomir Rosiek · Accepted Answer · 2013-02-26 19:50:06Z

4

In Oracle you should use :parm1 instead of @parm1. Additionally you should add percent signs in parameter value. Also you don't need create command and connection because it will be created by SqlDataSource1.

SqlDataSource1.SelectCommand = "SELECT FTPOSCODE FROM PPTBL WHERE DESC LIKE :parm1";
SqlDataSource1.SelectParameters.Add(":parm1", "%ACCOUNT%");

edited Feb 26, 2013 at 19:50

answered Feb 26, 2013 at 18:42

Sławomir Rosiek

4,0733 gold badges29 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Sławomir Rosiek Over a year ago

Do you have the same error? I'm wonder if you really have column called DESC since it's reserved for keywords

DavidC Over a year ago

I tried to post a simplified version of the code, sorry. I added the actual code I have now to the original question. Yes, it's the "invalid SQL" error.

Sławomir Rosiek Over a year ago

You should also remove apostrophes (') surrounding parameters in query

DavidC Over a year ago

This is what I have now, still getting the error: "SELECT FTPOSCODE, PTPOSCODE, POSCLASSTITLE, FKSA, JOBCAT, PAYGRADE, HRPAYRANGE, MONTHPAYRANGE FROM HR_PAY_PLAN WHERE UPPER(POSDESC) LIKE :parm1 OR UPPER(POSCLASSTITLE) LIKE :parm2 ORDER BY POSCLASSTITLE"

Sławomir Rosiek Over a year ago

Try assign connection to command and use cm.ExecuteReader instead of assign cm.ToString()

|

Collectives™ on Stack Overflow

Retrieving Oracle data using a SqlDataSource and a C# prepared statement

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related