[RESOLVED] hell with getting Max number

[daimous]

hi! currently i have the code below that query the max number in my id_number column but it walys gives me an error maybe i just Dont know how to check the return value of the query..can you help me guys here..thanks!

        public int get_maxid()
        {
            int return_value;
            if (db_connection())
            {
                string qryStr = "Select Max(id_number) from docs_ref";
                OracleCommand OraCmd = new OracleCommand(qryStr, OraConn);
                object result = OraCmd.ExecuteOracleScalar();
                MessageBox.Show("Result:" + result.ToString());
                if (result is int)//if it returns the max value for my id_number column
                {
                    return_value = (int)result;
                }
                else
                {
                   //id_number column has no rows/value
                    return_value = 0;
                }
                OraCmd.Connection.Close();
                OraConn.Close();
            }
            else
            {
                //error in my Database Connection
                return_value = -1; 
            }
            return return_value;
        }

by the way, my database is Oracle 10g.

[jmcilhinney]

I should just post this to every thread. Where does the error occur and what is the error message?

[daimous]

sorry for that jm...actually, there is no error in my code it is just a logical error--I just cant get the result im expecting. Becuase im pretty sure that it it should go to the below code since column id_number has one value which is 132

                if (result is int)
                {
                    return_value = (int)result;
                }

instead of here

                else
                {
                    return_value = 0;
                }

my code:

        public int get_maxid()
        {
            int return_value;
            if (db_connection())
            {
                string qryStr = "Select Max(id_number) from docs_ref";
                OracleCommand OraCmd = new OracleCommand(qryStr, OraConn);
                object result = OraCmd.ExecuteOracleScalar();
                if (result is int)
                {
                    return_value = (int)result;
                }
                else
                {
                    return_value = 0;
                }       
                OraCmd.Connection.Close();
                OraConn.Close();
            }
            else
            {
                return_value = -1;
            }
            return return_value; //return_value is equal to zero but it should be the max number in my id_number column
        }

[jmcilhinney]

So then, again, you should assign the result to an object variable, which you're doing, then set a breakpoint and put a watch on that variable to see what type of object it refers to and what its value is. Take the screen shot below as an example.

[daimous]

the return variable has a value of 123 and of type OracleNumber ive attached the screenshot below. I've tried to change this code

                if (result is int)
                {
                    return_value = (int)result;
                }

whith this code

                if (result is OracleNumber)
                {
                    return_value = (int)result;
                }

but it is useless becuase even the null return value is of type OracleNumber so there is no way to check if id_column has atleast 1 or No rows.

[jmcilhinney]

As the help topic for ExecuteOracleScalar says:

Executes the query, and returns the first column of the first row in the result set returned by the query as an Oracle-specific data type.

If you don't want an Oracle-specific data type then call ExecuteScalar instead. The OracleNumber structure has a Value property that returns a Decimal object. If you try to use a member and it doesn't work as expected, the first thing you should do WITHOUT FAIL is read the help topic for that member.

[daimous]

great! i got it..Thanks for the help Jm...here the code

        public int get_maxid()
        {
            int return_value;
            if (db_connection())
            {
                string qryStr = "Select Max(id_number) from docs_ref";
                OracleCommand OraCmd = new OracleCommand(qryStr, OraConn);
                object result = OraCmd.ExecuteScalar();
                if (result is System.DBNull)
                {
                    return_value = 0;
                }
                else
                {
                    return_value = int.Parse(result.ToString()); ;
                }       
                OraCmd.Connection.Close();
                OraConn.Close();
            }
            else
            {
                return_value = -1;
            }
            return return_value;
        }

[jmcilhinney]

I don't get this bit:

                if (result is System.DBNull)
                {
                    return_value = 0;
                }
                else
                {
                    return_value = int.Parse(result.ToString()); ;
                }

Surely if the value isn't DBNull.Value then it's an integer, so why do you need to convert it to a string and then parse it back to an integer. If the object is an integer then you should simply be able to cast it as such. If it isn't null and it isn't an integer then what is it?

[daimous]

Thanks for that! but i've already try the code below but it has an error

                if (result is System.DBNull)
                {
                    return_value = 0;
                }
                else
                {
                    return_value = result;
                }

error:

Connot implicitly convert type 'object' to 'int'. An explicit conversion exists(are you missing a cast)

[jmcilhinney]

No, you can't simply assign from an object reference to an int reference, but the actual object itself, I would have thought, would be an int so you should be able to cast it as such:

                if (result is System.DBNull)
                {
                    return_value = 0;
                }
                else
                {
                    return_value = (int)result;
                }

If that doesn't work then the actual object must be some other type. I would have thought that ExecuteScalar would return an int in that situation but, if it doesn't, it would have to be an OracleNumber like ExecuteOracleScalar. In that case it would be more appropriate to do this:

                if (result is System.DBNull)
                {
                    return_value = 0;
                }
                else
                {
                    return_value = Convert.ToInt32(((OracleNumber)result).Value);
                }

[daimous]

Oh I see...thanks! The first one did not work. My result object is of type Decimal so maybe the return type of executescalar is of type decimal..maybe i'll just use

 int.Parse(result.Tostring());

i convert it to string becuase int.Parse only accept, but im not so sure, type string.

[jmcilhinney]

If it's type decimal then you should be converting that straight to an integer, not going through a string.

return_value = Convert.ToInt32(result);

I'm interested to know the type of your database column. Is it an integer type in the database? If so I'm surprised that you're not getting an integer type returned by the query, but maybe that's an Oracle thing. If it's not an integer type in the database then, assuming that Oracle provides one, I think that you should change it for an ID column. If it is already an integer type or Oracle doesn't offer one then I guess you need to convert, but do so straight from decimal to int. Never, never, NEVER convert anything that isn't a string into a string if there isn't a specific need.

[daimous]

My id_number column is of type NUMBER...I dont think even I change the id_number column to type int it will return value of type Integer..anyway, i will give it a try...thanks for the advice.

[jmcilhinney]

From a quick read of the help for the OracleType enumeration it doesn't look like Oracle supports a native integer type. The Number type is specifically stated as being mapped to the .NET Decimal type as it must be used for integral and fractional values. If that's the case then you're stuck with getting a Decimal back. Still, as I said, you should convert straight from Decimal to Int32 and not go via String.

[daimous]

Oh! I've already change my code to convert straight from decimal to Int32 as you have adviced...Thanks for that information anyway.