CODEWITHSUNDEEP
c#
Privesh
Privesh

Reputation: 657

Retrieving value in C# from oracle database

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

Upvotes: 2

Views: 7255

Answers (3)

FIre Panda
FIre Panda

Reputation: 6637

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;

Upvotes: 1

Sai Kalyan Kumar Akshinthala
Sai Kalyan Kumar Akshinthala

Reputation: 11844

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());
}

Upvotes: 3

Daniel Hilgarth
Daniel Hilgarth

Reputation: 174299

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

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

Upvotes: 1

Related Questions