Oracle bound variables

I'm new to working with bound variables, so I've got a small question - I'm using an Oracle DB and ODP.NET in a .NET app, and I'm using bound variables like this

string sql = "select * from table1 where loc=:pLoc and pno=:pPno and sno=:pSno union all select * from table2 where loc=:pLoc and pno=:pPno and sno=:pSno union all 
select * from table3 where loc=:pLoc and pno=:pPno and sno=:pSno";

    O开发者_开发技巧racleCommand _cmd = new OracleCommand(sql, DBFacade.DbConnection);

                    OracleParameter pLoc = new OracleParameter(":pLoc", OracleDbType.Varchar2, 3);
                    pLoc.Value = loc;
                    OracleParameter pSno = new OracleParameter(":pSno", OracleDbType.Varchar2, 10);
                    pLoc.Value = sno;
                    OracleParameter pPno = new OracleParameter(":pPno", OracleDbType.Varchar2, 18);
                    pLoc.Value = pno;

                    _cmd.Parameters.Add(pLoc);
                    _cmd.Parameters.Add(pSno);
                    _cmd.Parameters.Add(pPno);

                    _odaContractPrices.SelectCommand = _cmd;

I've used bound variables successfully in other sql statements (in simpler queries like "select * from table1 where column1=:param1 and column2=:param2"), but for some reason in this one it doesn't work. Can anyone explain me why ? Thank you very much!

---

I'm not sure it's the source of the error but try to remove the : in the OracleParameter constructor and use

_cmd.BindByName = true;

Actually, I think that in your case, the following will suffice:

_cmd.Parameters.Add("pLoc", loc);
_cmd.Parameters.Add("pSno", sno);
_cmd.Parameters.Add("pPno ", pno);