1

We have a US7ASCII 11gr2 database that I'm trying to build parameterised queries against.

public CustDetailsModel SelectCustDetails(string CustCode)
{
    string sql;
    OracleDataReader reader;

    OracleConnection OraConn = OraConnection.GetConnection;

        OraConn.Open();

        sql = "SELECT CUSTOMER, NAME, POSTCODE FROM CUSTOMER WHERE CUSTOMER = :CUSTCODE";

        OracleCommand cmd = new OracleCommand(sql, OraConn);

        OracleParameter parameter = cmd.CreateParameter();
        parameter.ParameterName = "CUSTCODE";
        parameter.OracleDbType = OracleDbType.NVarchar2;
        parameter.Value = CustCode;
        cmd.Parameters.Add(parameter);

            reader = cmd.ExecuteReader();

            reader.Read();

            return new CustDetailsModel
            {
                Cust = reader.GetString(reader.GetOrdinal("CUSTOMER")),
                CustName = reader.GetString(reader.GetOrdinal("NAME")),
                CustPostCode = reader.GetString(reader.GetOrdinal("POSTCODE")),
            };

}

At the add parameter line, the above code triggers a System.InvalidCastException "Unable to cast object of type 'System.String' to type 'Oracle.ManagedDataAccess.Client.OracleParameter".

I've tried everything I can find on the internet including stringbuilder, bind by name, different dbtypes etc.

Does anybody know how to set up a string bind variable in C# to query against an ASCII oracle db?

Thanks in advance.

EDIT I should explain that I know it is something to do with the disparate types of the db and application because we have successfully used int bind variables and, we can also replace the string bind variables with hardcoded values. We can also replace string bind variables with regular string variables (which we're trying to avoid). It seems to be an issue with the conversion of the bind variable when it hits the database?

radiator
  • 51
  • 13
  • see if this helps: http://stackoverflow.com/questions/11048910/oraclecommand-sql-parameters-binding – sjramsay Oct 14 '14 at 16:33
  • Really weird. I'd expected that code to work. Will you also post the call stack of the error please? In the mean time, You could try using the overload for "Add", ie cmd.Parameters.Add("CUSTCODE", CustCode) and let it infer the type. That of course does not explain at all why it's trying to convert to Parameter. – b_levitt Oct 15 '14 at 04:34
  • @sjramsay - that is a post I've already looked at. Thanks for replying. – radiator Oct 15 '14 at 08:22
  • @b_levitt - we're quite new to .net so not sure how to get the stack trace - btw the error is only visible when debugging - without debug it doesn't work and doesn't display any errors. I've tried the alternate overload (and many others) but nothing works with string bind variables. – radiator Oct 15 '14 at 08:37
  • can you step through your program and tell us which line the error is occurring on? I am wondering if you need to change your reader.GetString(reader.GetOrdinal("Customer")) and those variables to something like reader["Customer"].ToString(). and we are sure that the variables your putting into like Cust etc are of the correct type like string? – sjramsay Oct 15 '14 at 11:20
  • I've changed the parameter to this overload cmd.Parameters.Add(new OracleParameter("CUSTCODE", CustCode)); and now get "Invalid operation on a closed object" at the "return new CustDetailsModel" block because the reader hasn't any rows. – radiator Oct 15 '14 at 11:49
  • Can you paste the entire error (including the stack dump and error numbers) and not just the message (the difference between Exception.Message.ToString() and Exception.ToString()). – b_levitt Oct 24 '14 at 14:20
  • I'm also wondering what would happen if you changed the NVarchar2 type to just Varchar2. – b_levitt Oct 24 '14 at 14:24
  • I have tried all combinations of types I'm afraid - thanks – radiator Oct 27 '14 at 10:04

0 Answers0