1

I'm working on a C# application that imports an Excel file to be uploaded by users of the application. My application uses Entity Framework and SQL Server 2008 database.

In my SQL Server database I have created a column to hold value in this format: 1,565,000.62. I defined the column as decimal hence a corresponding decimal property has been created for the same in my entity class.

However, when I'm adding my values into my entity class, I get an invalid cast exception.

What's the best datatype to use for this type of value (C# and SQL Server)?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Simua
  • 263
  • 1
  • 10
  • 1
    I don't believe Excel has the concept of decimal - everything is floating-point – lc. Aug 29 '12 at 18:33
  • 1
    `decimal` is your .NET type, too - you'll have to tell us **what exact error message** you're getting, and when exactly (in your application) you're getting that message... upon reading from Excel? When fetching data from SQL Server? Or when?? Can you show us the code that you use to *add my values to my entity class* that causes the error? – marc_s Aug 29 '12 at 18:44
  • @marc_s sorry I can't put the exact error at the moment but the error occurs when reading from Oledbdatareader (my excel table) and inserting into my EF class. Eg. efclass.amount = dataReaderObj.GetDecimal(0); – Simua Aug 29 '12 at 19:04
  • Could it be that this particular cell in Excel is empty? That might be interpreted as a `NULL` - and you cannot exactly convert `NULL` to `decimal` .... – marc_s Aug 29 '12 at 19:12
  • @marc_s, already, I tested for DBNull using the following but no luck: efclass.amount = (datareaderObj.IsDBNull(0)? efclass.amount : datareaderObj.getDecimal(0)); – Simua Aug 29 '12 at 19:34

1 Answers1

0

You should use a float or a double I beleive.

Check out the 2nd answer at this question Difference between decimal, float and double in .NET? It talks about how decimals can't be compared to double or floats without a cast.

Also for the Sql deciaml check out this question and answer: What represents a double in sql server? It talks about decimal for when you know the exact and talks about decimal in c# being in contrast to C# decimal.

Community
  • 1
  • 1
AndyC
  • 1,325
  • 1
  • 13
  • 23
  • Thank you for your reply, I previously tried using Double in my EF property, but I got a compiler error because the SQL server datatype was decimal. I'll try changing both SQl and EF datatypes to float and give you feedback. – Simua Aug 29 '12 at 19:19
  • Ok, I hope I was able to point you in a good direction. Please let me know how it goes. – AndyC Aug 29 '12 at 20:42
  • Changed the datatype to float but Still didn't work, here is my code and error below: Line 88: doa.limit = (dr.IsDBNull(10)? dr.GetFloat(10) : doa.limit); Source File: Default.aspx.cs Line: 88 Stack Trace: [InvalidCastException: Specified cast is not valid.] System.Data.OleDb.ColumnBinding.ValueSingle() +1160739 System.Data.OleDb.OleDbDataReader.GetFloat(Int32 ordinal) +30 – Simua Aug 29 '12 at 23:10
  • Did you change the datatype in the database to float or in the code or both? I think you need to change both the database type to float and in the code set it up to be a float also. – AndyC Aug 30 '12 at 05:09
  • Sorry for my late response, I ended up using float but later discovered that my problem was being caused by an NULL database field being passed to the excel file as an empty string. To deal with this, I had to pass the values of such fields to DBNull.Value (if empty). – Simua Nov 10 '12 at 03:52