I'm working in C# with an oracle database, and i have some issues with InvalidCastException when i try to data out of OracleDataReader.
The problem is the type of the function AVG().
This is the query:
SELECT AVG(MBAR) mbavg, AVG(ML_MIN) mlavg, RSZ FROM (SELECT * FROM ( SELECT ID, RSZ rsz2 FROM SZIVARGASMERO ORDER BY ID DESC ) WHERE ROWNUM=1 ) a, SZIVARGASMERO WHERE RSZ=a.RSZ2 GROUP BY RSZ;
The table looks like this:
CREATE TABLE SZIVARGASMERO
(
ID NUMBER NOT NULL,
RSZ VARCHAR2(1000 BYTE),
CIKKSZ VARCHAR2(500 BYTE),
DATE_G DATE,
DEV_ID INTEGER,
PROG INTEGER,
RES VARCHAR2(50 BYTE),
REG1 INTEGER,
MBAR FLOAT(126),
FE VARCHAR2(50 BYTE),
REG2 INTEGER,
ML_MIN FLOAT(126),
TIMESTAMPA DATE,
ERROR INTEGER
)
I tried
double mbavg=reader.GetDouble(reader.GetOrdinal("mbavg"));
float mbavg=reader.GetFloat(reader.GetOrdinal("mbavg"));
And also
string mbavg=reader.GetString(reader.GetOrdinal("mbavg"));
And i tried parse double with CultureInvariant and System.Conver.ToXXX too, but none of them works.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.IO.Ports;
using System.IO;
using System.Threading;
//using MySql.Data.MySqlClient;
using System.Globalization;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
....
public const string oradb ="....";
public OracleConnection conn = new OracleConnection(oradb);
...
OracleCommand cmd = new OracleCommand();
conn.Open();
try
{
cmd.Connection = conn;
cmd.CommandText = "SELECT AVG(MBAR) mbavg, AVG(ML_MIN) mlavg, RSZ FROM (SELECT * FROM ( SELECT ID, RSZ rsz2 FROM SZIVARGASMERO ORDER BY ID DESC ) WHERE ROWNUM=1 ) a, SZIVARGASMERO WHERE RSZ=a.RSZ2 GROUP BY RSZ";
cmd.CommandType = CommandType.Text;
OracleDataReader dr = cmd.ExecuteReader();
//cmd.ExecuteNonQuery();
while (dr.Read())
{
if (dr.HasRows)
{
// double mbavg = dr.GetDouble(0);
double mlavg = dr.GetDouble(dr.GetOrdinal("mlavg"));
string last_rsz = dr.GetString(dr.GetOrdinal("RSZ"));
// mbaravg.Text = System.Convert.ToString(mbavg) + " mbar";
mlavg_lb.Text = System.Convert.ToString(mlavg) + " ml/min";
last_rsz_lb.Text = last_rsz;
}
}
dr.Dispose();
dr.Close();
}
catch (Exception)
{
throw;
}
finally
{
if (conn.State == ConnectionState.Open)
{
oracle_lb.Text = "OK";
ora_lbl_2.Text = "Oracle: OK!";
ora_lbl_2.ForeColor = Color.DarkGreen;
}
else
{
MessageBox.Show("Nincs adatbáziskapcsolat!");
}
Who could i solve it? Please help me. Thank you very much :)
Edit:
cmd.CommandText = "SELECT TRUNC(AVG(MBAR),6) mbavg, TRUNC(AVG(ML_MIN),6) mlavg, RSZ FROM (SELECT * FROM ( SELECT ID, RSZ rsz2 FROM SZIVARGASMERO ORDER BY ID DESC ) WHERE ROWNUM=1 ) a, SZIVARGASMERO WHERE RSZ=a.RSZ2 GROUP BY RSZ";
decimal mbavg_d=dr.GetDecimal(GetOrdinal("mbavg"));
double mbavg=(double)mbavg_d