OracleDataReader HasRows throws Invalid Number Exception

Question

I have the following code:

int count = 0;
OracleDataReader dr;
parameters = new Dictionary<string, string>();
parameters.Add("Username", UserName);

query = "Select KEY from TABLE1 where USERID=:Username AND KEY IN(";

foreach (string Location in Locations)
{
    query = query + ":Loc" + count.ToString() + ",";

    parameters.Add("Loc" + count.ToString(), Location);

    count++;
}
query=query.TrimEnd(',');
query = query + ")";

dr=objConexion.ExecuteQuery(query, parameters, objConexion.OracleCnn);


public OracleDataReader ExecuteQuery(String query, Dictionary<string,string> Parameters, OracleConnection oracleCnn)
{
    OracleDataReader OracleDr;
    OracleCommand OracleCmd;
    try
    {


        OracleCmd = oracleCnn.CreateCommand();
        OracleCmd.CommandText = query;


        foreach (KeyValuePair<string, string> kvp in Parameters)
        {

            OracleCmd.Parameters.AddWithValue(kvp.Key, kvp.Value);

        }

        OracleCmd.Prepare();
        OracleDr = OracleCmd.ExecuteReader();

    }
    catch
    {
        OracleDr = null;
    }

    return OracleDr;
}

For some reason the code works fine sometimes and others it doesn´t, After debugging I noticed that when it doesn´t work, the Datareader has an exception on its "HasRows" method, specifically an Invalid Number Exception. I have tried running the resulting query directly on the DBMS and it executes perfectly, does anyone know why this exception is happening? what am I missing?.

Could you provide the exception? Or is it just InvalidNumber? - InvalidNumber is thrown in oracle when conversion of string to number fails. Please provide the table description with column types. — Christian13467, Jan 30 '14 at 22:23
ORA-01722: invalid number, although when looking at it on debug, it only says: HasRows 'dr.HasRows' generated an exception 'System.Data.OracleClient.OracleException'. They Key column which I am selecting is an INTEGER and the UserID is a varchar2 — user3255067, Jan 30 '14 at 22:39

score 0 · Accepted Answer · edited May 23 '17 at 11:49

0

Do the following to find issue:

 OracleCmd.Parameters.AddWithValue(kvp.Key, int.Parse(kvp.Value));

I think, you passing some "bad integer" in one of the locations See this sql error "ORA-01722: invalid number"

And please, remove

OracleCmd.Prepare();

Another thing, why do you pass connection to

public OracleDataReader ExecuteQuery(String query, 
                                     Dictionary<string,string> Parameters, 
                                     OracleConnection oracleCnn)

Pass a connection string and open connection before execution. Use appropriate CommandBehavior

MSDN: [CommandBehavior.CloseConnection]When the command is executed, the associated Connection object is closed when the associated DataReader object is closed.

edited May 23 '17 at 11:49

Community

1
1

answered Jan 30 '14 at 23:26

T.S.

18,195
11
58
78

The issue is not on the parameters as the query works sometimes, I tried it anyways and got the same result, (also tried removing the Prepare statement. The connection is being passed because I have a custom connection object that contains different connection strings (for different environments) and it opens the appropriate connection based on a paramter, the connection is being opened and closed properly in the code before and after the snippet I posted. It might not be the best way, but this is what I have to work with. – user3255067 Jan 30 '14 at 23:51
@user3255067 At which line of code the error occurs? – T.S. Jan 30 '14 at 23:59
Managed to find the problem, it was actually outside the snippet I posted, it was a logic error, that was sending strings sometimes instead of integers as parameters as you suggested, thanks. – user3255067 Jan 31 '14 at 17:10

OracleDataReader HasRows throws Invalid Number Exception

1 Answers1