Retrieving value in C# from oracle database

Question

I dont know why but I'm getting an error trying to get the maximum integer value from a table in the oracle database: Here is the code:

string oradb = "Data Source=";
            oradb = oradb + Login.db + ";";
            oradb = oradb + "User Id=" + Login.user;
            oradb = oradb + ";Password=" + Login.pass + ";";
            OracleConnection conn = new OracleConnection(oradb);
            conn.Open();
            string term = GetTerminal(terminal);
            string sql = "SELECT tallynumber from "+frmSchemas.schema + ".tallies" ;
             MessageBox.Show(sql);
            OracleCommand cmd = new OracleCommand(sql, conn);
            cmd.CommandType = CommandType.Text;
            OracleDataReader dr = cmd.ExecuteReader();
            MessageBox.Show("1");
            Int64 TallyNo = dr.GetInt32(0);  



      // lblTallyNo.Text = (TallyNo).ToString()

the returned value should be: 72332 if that is any relevance

So what does the error message say and on what line does it occurr? — Codo
– Codo, Commented Jul 18, 2011 at 10:06

Sai Kalyan Kumar Akshinthala · Accepted Answer · 2011-07-18 10:20:08Z

3

You can try with the Following

        string sql = "SELECT max(tallynumber) from "+frmSchemas.schema + ".tallies" ;
         MessageBox.Show(sql);
        OracleCommand cmd = new OracleCommand(sql, conn);
        cmd.CommandType = CommandType.Text;
        OracleDataReader dr = cmd.ExecuteReader();
        MessageBox.Show("1");
        if (dr.Read())
        {
        Int64 TallyNo = dr.GetInt32(0); 
        }

EDIT:

if(dr.Read())
{
Int64 TallyNo = Convert.ToInt64(dr["tallynumber"].ToString());
}

edited Jul 18, 2011 at 10:20

answered Jul 18, 2011 at 10:08

Sai Kalyan Kumar Akshinthala

11.9k8 gold badges45 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Privesh Over a year ago

ExecuteScalar is not being recognised by the C#, it underlines it

Privesh Over a year ago

int TallyNo = Convert.ToInt32(dr[0].ToString());

Sai Kalyan Kumar Akshinthala Over a year ago

Ok, You can try with the Code I have given also.

Daniel Hilgarth · Accepted Answer · 2011-07-18 10:05:18Z

1

You are missing a dr.Read(); before accessing the value:

OracleDataReader dr = cmd.ExecuteReader();
dr.Read();
Int64 TallyNo = dr.GetInt32(0);

answered Jul 18, 2011 at 10:05

Daniel Hilgarth

175k41 gold badges346 silver badges450 bronze badges

Comments

FIre Panda · Accepted Answer · 2011-07-18 10:47:51Z

1

If you want to find the max value from a table, the query should be

SELECT max(tallynumber) from ....

If you want to get the max, min etc values you should be calling ExecuteScalar() instead of ExecuteReader().

So you code could become

   string sql = "SELECT Max(tallynumber) from "+frmSchemas.schema + ".tallies";              MessageBox.Show(sql);             
    OracleCommand cmd = new OracleCommand(sql, conn);             
    cmd.CommandType = CommandType.Text;             
    object val = cmd.ExecuteScalar();
int res = int.MinValue;             
    //MessageBox.Show("1");
  if(int.TryParse(val.ToString(), out res)) 
    Int64 TallyNo = res;

edited Jul 18, 2011 at 10:47

answered Jul 18, 2011 at 10:04

FIre Panda

6,6272 gold badges28 silver badges39 bronze badges

1 Comment

FIre Panda Over a year ago

Try it now, changed if(int.TryParse(val, out res)) To if(int.TryParse(val.ToString(), out res))

Collectives™ on Stack Overflow

Retrieving value in C# from oracle database

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related