problem with accessing decimal values in MS SQL 2000

  • Thread starter Thread starter Andreas Oswald
  • Start date Start date
A

Andreas Oswald

Hi there,

I have a problem with accessing decimal values in a table like the
following one:

create table sap_BelegZeile(
....
DEC_STEUER decimal(5,2),
DEC_BETRAG decimal(14,2),
....
);

str_SQL = "select DEC_STEUER from sap_Belegzeile";
OleDbCommand qry_BelegZeilen = new OleDbCommand(str_SQL,
con_DatenbankVerbindung);
OleDbDataReader rst_BelegZeilen = qry_BelegZeilen.ExecuteReader();
//further analysis of the resulting OleDbDataReader
DataTable dt = rst_BelegZeilen.GetSchemaTable();
DataView dv = dt.DefaultView;
dv.RowFilter = "ColumnName='dec_betrag'";
DataRowView dr = dv[0];
Console.WriteLine(dr["ColumnName"]);
Console.WriteLine(dr["ColumnSize"]);
Console.WriteLine(dr["NumericPrecision"]);
Console.WriteLine(dr["NumericScale"]);
Console.WriteLine(dr["DataType"].ToString());
while (rst_BelegZeilen.Read())
{
Console.WriteLine("Row has {0} Columns.",
rst_BelegZeilen.FieldCount);
Console.WriteLine("TypeName: {0}",
rst_BelegZeilen.GetDataTypeName(0));
Console.WriteLine("IsDbNull: {0}", rst_BelegZeilen.IsDbNull(0));
//The following line 822 throws an System.InvalidOperationException
Decimal dec_steuer = rst_Belege.GetDecimal(0);
Console.WriteLine(dec_steuer);
}

For str_SQL = "select DEC_STEUER from sap_Belegzeile"; the above
snippet outputs

DEC_STEUER
19
5
2
System.Decimal
Row has 1 Columns.
TypeName: DBTYPE_NUMERIC
IsDbNull: False

Unbehandelte Ausnahme: System.InvalidOperationException: Keine Daten
für die Zeile/Spalte.
at System.Data.OleDb.OleDbDataReader.DoValueCheck(Int32 ordinal)
at System.Data.OleDb.OleDbDataReader.GetDecimal(Int32 ordinal)
at ApertumFileExport.FileExport.Main(String[] args) in
d:\oswald\experimente\apertumfileexport\class1.cs:line 822

and for str_SQL = "select DEC_BETRAG from sap_belegzeile"; it outputs

select DEC_BETRAG from sap_
DEC_BETRAG
19
14
2
System.Decimal
Row has 1 Columns.
TypeName: DBTYPE_NUMERIC
IsDbNull: False

Unbehandelte Ausnahme: System.InvalidOperationException: Keine Daten
für die Zeile/Spalte.
at System.Data.OleDb.OleDbDataReader.DoValueCheck(Int32 ordinal)
at System.Data.OleDb.OleDbDataReader.GetDecimal(Int32 ordinal)
at ApertumFileExport.FileExport.Main(String[] args) in
d:\oswald\experimente\apertumfileexport\class1.cs:line 822

What's going wrong there? If I do the same query from SQL Query
Analyzer, I get results like the following:

dec_betrag
----------------
19795.05
19795.05

(2 row(s) affected)

I also tried using SqlCommand and OdbcCommand instead of OleDbCommand,
results stay the same, I can't get those values.

I have used very similar code on very similar tables successfully, so I
really don't understand the exception.

For the not German-Speaking "Keine Daten für die Zeile/Spalte." means
something like "No data for Row/Column." and "Unbehandelte Ausnahme:"
is "Unhandled Exception:"

Any hints?

Thanks in advance for your help

Andreas
 
Andreas,

Would it not be better to bring your sample back to understandable as well
to others.

I now get the idea that there is a lot which is not relevant for your
problem in the code.

Just my thought,

Cor
 
Hi Cor,

thanks for your reply.

My thought behind the longer posting was to provide as much information
as possible and I used the information I provided also for my own
debugging, so I thought it might be usefull. OK, let's try to put it a
bit shorter. I have a table on a MS SQL 2000 Server. This table
contains decimal values of format "decimal(5,2)" and "decimal(14,2)".
When I try to read the values from the columns I get the following
run-time error (sorry for the German)

Unbehandelte Ausnahme: System.InvalidOperationException: Keine Daten
für die Zeile/Spalte.
at System.Data.OleDb.OleDbDataReader.DoValueCheck(Int32 ordinal)
at System.Data.OleDb.OleDbDataReader.GetDecimal(Int32 ordinal)
at ApertumFileExport.FileExport.Main(String[] args) in
d:\oswald\experimente\apertumfileexport\class1.cs:line 822

which should be somethin like the following in English:

"Unhandled Exception: System.InvaliOperationException:No Data for the
Row/Column
.... line 822"

Line 822 is
Decimal dec_steuer = rst_Belege.GetDecimal(0);

rst_Belege is a OleDbDataReader. I get the same behaviour also for
OdbcDataReader and SqlDataReader based on the according connections.
I can access columns of ofther datatypes (varchar, int...) in this
problem without no problems.

I hope this posting will make my problem clearer, sorry for boring you
with too long examples.

Take care

Andreas
 
Andreas,

I tried to simulate your problem.

I added in the Northwind database a column which is decimal and filled that.

Then I did this code
\\\
SqlConnection conn = new SqlConnection
("Server=localhost; DataBase=NorthWind; Integrated Security=SSPI");
string sqlstr = "SELECT Andreas FROM Categories Where Andreas = 1";
SqlCommand cmd = new SqlCommand(sqlstr, conn);
conn.Open();
SqlDataReader rdr = cmd.ExecuteReader();
rdr.Read();
decimal Andreas = rdr.GetDecimal(0);
conn.Close();
///

No problem at all.

Thank you by the way for translating the German part in English, you know
how hard it is for most Dutch to understand German.

(I know that it is for others, however could not resist)

Cor
 
Hi Cor,

to be honest I haven't looked at your e-mail-adress to find you're
dutch. There's a nice saying from dutch poet Hermann van Veen "German
is just Dutch with a funny accent" en Ik denk dat klopt!

As I pointed out in my first posting, I used similar code on similar
tables so it's not really a miracle that your code works. In all cases
yet it had worked perfectly for me till yesterday night ;-))) So I'll
still have to figure out, what might be different.

I'm still open for suggestions by the Gurus in Redmond *ggg*

Groeten uit Aken bij Maastricht *ggg*

Prettig weekend

Doie!

Andreas
 
Back
Top