CODEWITHSUNDEEP
.netasp.netoracle-databasec#-4.0ora-01426
Dbloom
Dbloom

Reputation: 1402

Getting an ORA-01426: Numeric Overflow in .NET 4.0, C#, using the Oracle DataAccess .dll

I am trying to write a very simple webpage using Visual Studio 2010, .Net 4, C#. I am using the Oracle DataAccess .DLL and have made a connection to the database just fine. When I run this code, I get the exception 'ORA-01426: Numeric Overflow." I am not doing any calculations, and I put the whole thing into a string before I submit the query. Below is my code sample:

string sql = "SELECT * from users.training WHERE per_id_no = " + strIdNo;
        OracleCommand cmd = new OracleCommand(sql, conn);
        cmd.CommandType = System.Data.CommandType.Text;

        try
        {
            Label1.Visible = false;
            //Read data from the database
            OracleDataReader dr = cmd.ExecuteReader(); // C#
            dr.Read();

Upvotes: 1

Views: 967

Answers (3)

Harv
Harv

Reputation: 523

If per_id_no is a varchar2 data type then you probably need to put the value in single quotes.
string sql = "SELECT * from users.training WHERE per_id_no = '" + strIdNo + "'";

Of course, I entirely agree with @jgauffin, in which case I think the query should look like this
string sql = "SELECT * from users.training WHERE per_id_no = '@id'";
But I am pretty sure in Oracle the paramter should be :id and not @id which I believe is SQL Server syntax.

Anyway, HTH
Harv

Upvotes: 1

jgauffin
jgauffin

Reputation: 101150

This is not an answer, but your code is vulnerable to SQL injection. Never include parameters directly into the SQL statement. Use named arguments instead:

    string sql = "SELECT * from users.training WHERE per_id_no = @id";
    using (var cmd = conn.CreateCommand()) 
    {
        cmd.CommandText = sql;

        //syntax differs between different providers
        cmd.Parameters.Add("id", strIdNo); 

        try
        {
            Label1.Visible = false;
            OracleDataReader dr = cmd.ExecuteReader();
            dr.Read();
        }
     }

Upvotes: 2

agent-j
agent-j

Reputation: 27923

strIdNo may be too large for the type that would contain it. Check to be sure it doesn't need to be quoted (to convert it into a string). Also check the type of the per_id_no column to make sure it is the type you expect (and that strIdNo can fit in it).

Upvotes: 0

Related Questions