System.data.OracleClient OracleDataReader invalid column error

Hello,

I am new to this forum. I am developing an application using c# with an Oracle (v8.1.7) backend. I have .NET Framework 1.1 deployed on my workstation along with Visual Studio. I am able to open the connection to the database just fine, but when I attempt to access the rows and columns using the OracleReader object methods, I get an Oracle error, Invalid column name.


Any help and suggestions would be much appreciated.

Sincerely,

netnovice

Code

...
using System.Data.OracleClient;

...
string connectString = "Data Source = qpss; User Id = qpss; Password = qpssora;";
string user = "irwin";
string pwd = "";
string qpssSql = "select password from qpss_user_table where login_name = " + user;
OracleConnection OraConn = null;
OraConn = new OracleConnection(connectString);
OraConn.open();
OracleCommand qpssCmd = OraConn.CreateCommand();
qpssCmd.CommandText = qpssSql;
OracleDataReader qpssRdr = qpssCmd.ExecuteReader();
while (qpssRdr.read()) {
pwd = qpssRdr.getString(0);
}

qpssCmd.close();
OraConn.close();
...

Hi,

As far as I know of Oracle, 'qpss_user_table' exists in your datamodel since oracle did not crash on a "table or view does not exist" first. It's either 'password' or 'login_name' that does not exist as a column of this table.

Can you check your datamodel?

MrKoala

PS: a "desc qpss_user_table" command in a SQL*Plus session would do the trick.

Hello,

And thank you for the response. As it turns out, I checked an ADO reference on doing parameterized queries. One cannot simply concatenate a string at the end of a sql query. One must create a parameter object, assign it a data type, size and value. Code follows:

..
using System.Data.OracleClient;

...
string connectString = "Data Source = qpss; User Id = qpss; Password = qpssora;";

string pwd = "";
string qpssSql = "select password from qpss_user_table where login_name = :LOGIN_NAME ";
OracleConnection OraConn = null;
OraConn = new OracleConnection(connectString);

OracleCommand qpssCmd = new OracleCommand(qpssSql,OraConn);
OracleParameter OraParamLN;
OraParamLN.Parameters.Add(":LOGIN_NAME",OracleType.VarCar,10);
OraParamLN.Parameters[":LOGIN_NAME"].value = "irwin";
OraConn.open();
OracleDataReader qpssRdr = qpssCmd.ExecuteReader();
while (qpssRdr.read()) {
pwd = qpssRdr.getString(0);
}

qpssCmd.close();
OraConn.close();
...

Sincerely,

Netnovice

btw, similar conventions exist in jdbc. In jdbc, the object for doing a parameterized query is called a preparedstatement

OK.

Actually, in the meantime, I checked the code I am working on (I am not an Oracle expert, but use Oracle at a higher level), and statements get prepared this way too (but the parameter places are '?' characters and they use the stream redirection operator '<<' to fill in the parameters)

Happy I you found the cause. Interesting though. do you think they're doing it this way to avoid reparsing the query afterwards?

MrKoala

Thanks for your response. I tried every which way to sunday, modifying the manner in which I created the command object, the connection object and the datadeader object. Still got the Oracle Invalid coumn error. I know that it was not related to the 8.1.7 database, as I could submit a query either via sqlnet, or odbc.

As soon as I saw the methodolgy for both OleDb and Oracle, I had an aha! experience. Being a java programmer for many years, the solution made perfect sense.

Netnovice