If you are querying an Oracle database using the
System.Data.Odbc class, you
can only use unnamed parameters (defined by "?"). In order
to use named parameters, you’ll need to use the
System.Data.OracleClient class instead (note you’ll need to add that reference manually to your project).
public DataTable queryDatabase_odbc () {
OdbcConnection con = new OdbcConnection("Driver={Microsoft ODBC for Oracle};server=DBSERVER;uid=xxUserxx;pwd=xxPwdxx");
con.Open();
OdbcCommand ps = new OdbcCommand("alter session set current_schema = xxdbschemaxx", con);
ps.Prepare();
ps.ExecuteNonQuery();
OdbcCommand ps2 = new OdbcCommand("select * from aTable where field = :AParam", con);
ps2.Prepare();
ps2.Parameters.Add("AParam",OdbcType.VarChar).Value="Some String Value";
DataSet rs = new DataSet();
OdbcDataAdapter da = new OdbcDataAdapter(ps2);
da.Fill(rs);
return rs.Tables[0];
}
public DataTable queryDatabase_ora () {
OracleConnection con = new OracleConnection("server=DBSERVER;uid=xxUserxx;pwd=xxPwdxx;");
con.Open();
OracleCommand ps = new OracleCommand("alter session set current_schema = xxdbschemaxx", con);
ps.Prepare();
ps.ExecuteNonQuery();
OracleCommand ps2 = new OracleCommand("select * from aTable where field = :AParam", con);
ps2.Prepare();
ps2.Parameters.Add("AParam",OracleType.VarChar).Value="Some String Value";
DataSet rs = new DataSet();
OracleDataAdapter da = new OracleDataAdapter(ps2);
da.Fill(rs);
return rs.Tables[0];
}
For more details check this out :
http://klo2k.wordpress.com/2009/03/21/asp-net-odbc-oracle-error-ora-01008-not-all-variables-bound-solved/[
^]
I hope this will help to you.