I have a column in the Database with the following precision: Numeric (40,20) but When I try to read it it's raising me:

iAnywhere.Data.SQLAnywhere.SAException: 'Invalid data conversion' Probably it happens due to in the DataColumn the column precision is not so big. When I loading the data into my object, I declared this property as decimal, like the following:

public decimal? BigNumericColumn{ get; set; } In the loading data processing I'm doing the following.

DataTable schemaTable = dataReader.GetSchemaTable(); DataTable resultTable = new DataTable(typeof(T).Name);

foreach (DataRow dataRow in schemaTable.Rows) { DataColumn dataColumn = new DataColumn(); dataColumn.ColumnName = dataRow["ColumnName"].ToString();

if (lstColumnsToFilter.Contains(dataColumn.ColumnName) && filterEnabled) { lstIndexColumnToFilter.Add(countColumns); }

dataColumn.DataType = Type.GetType(dataRow["DataType"].ToString());
dataColumn.ReadOnly = (bool)dataRow["IsReadOnly"];
dataColumn.AutoIncrement = (bool)dataRow["IsAutoIncrement"];
dataColumn.AllowDBNull = true;

resultTable.Columns.Add(dataColumn);

} Do you know how could I fix it?

This is the complete error message:

at iAnywhere.Data.SQLAnywhere.SAException.CheckException(Int32 idEx) at iAnywhere.Data.SQLAnywhere.SADataReader.Read() at RiskAlm_DataExtraction.DataModels.IQ_Queries.GetDataTableFromDR[T](IDataReader dataReader, String path, List1 lstColumnsToFilter, String _sourceConnString) in D:\C#_RiskAlm_Prototypes\cabralf\RiskAlm_DataExtraction\RiskAlm_DataExtraction\DataModels\IQ_Queries.cs:line 277 at RiskAlm_DataExtraction.DataModels.IQ_Queries.<>c__DisplayClass5_01.b__0(ListFilter filterValue) in D:\C#_RiskAlm_Prototypes\cabralf\RiskAlm_DataExtraction\RiskAlm_DataExtraction\DataModels\IQ_Queries.cs:line 215 at System.Linq.Parallel.ForAllOperator1.ForAllEnumerator1.MoveNext(TInput& currentElement, Int32& currentKey) at System.Linq.Parallel.ForAllSpoolingTask`2.SpoolingWork() at System.Linq.Parallel.SpoolingTaskBase.Work()

Error 103 Message "Invalid data conversion" string

asked 10 Feb, 08:28

fcabralj's gravatar image

fcabralj
113
accept rate: 0%

edited 11 Feb, 07:01

1

Try running your code on some other column, with smaller precision, to see if the precision really is the problem or not.

(10 Feb, 10:11) Breck Carter

I've ran the same code considering the Numeric columns with precision of (19,4) and (38,8) and it ran fine. The problem is with the columns NUMERIC(40,20).

(10 Feb, 11:29) fcabralj
Replies hidden
1

AFAIK the maximum Decimal precision in C# is 28-29.

Are you having problems when the declared precision is larger than that, or when the actual value is larger than that? (I'm trying to figure out why (38,8) worked).

In any event, the problem may be on the client side, not the SQL Anywhere server.

(11 Feb, 07:23) Breck Carter

What happens when you try to write stand-alone C# code that handles a local (40,20) variable?

(11 Feb, 07:24) Breck Carter

What is the version of SA SDK are you using? I see the name of exception: iAnywhere.Data.SQLAnywhere.SAException, but the new namespace is Sap.Data.SQLAnywhere... so you are not using SA17, are you?

(11 Feb, 09:38) Vlad
Replies hidden

Indeed the maximum precision is 28, 29. When I do a Select max(len(field)) in the database it returns 26 chars which should fit the decimal type in C#.

I've made a test as you recommended:

double test = 12345678901234567890.12345678901234567890;

It is accepted, no errors so far. I don't really understand why it's giving me error when I do the read from DataReader.

(11 Feb, 10:16) fcabralj
Replies hidden

How does using "double" confirm anything? I thought we were talking about "decimal".

decimal test = 12345678901234567890.12345678901234567890M; and the result is 30 digits: 12345678901234567890.1234567890

decimal test = 678901234567890.12345678901234567890M; and the result is 30 digits: 678901234567890.123456789012345

so no more than 30 digits are accepted.

Precision: decimal test = 0.1234567890123456789012345678951234567890M; and the result is 0.12345678901234567890123456789 There are at most 29 digits of precision (the MSDN documented precision is 28-29 digits).

decimal test = .000001234567890123456789012345678951234567890M; and the result is 0.0000012345678901234567890123 There are at 28 digits of precision (the MSDN documented precision is 28-29 digits).

(11 Feb, 11:48) JBSchueler

No, I wasn't but I just changed it to SA17 and still getting the same error as you can see:

at Sap.Data.SQLAnywhere.SAException.CheckException(Int32 idEx) at Sap.Data.SQLAnywhere.SADataReader.Read() at RiskAlm_DataExtraction.DataModels.IQ_Queries.GetDataTableFromDR[T](SADataReader dataReader, String path, List1 lstColumnsToFilter, String _sourceConnString, String query) in IQ_Queries.cs:line 274 at RiskAlm_DataExtraction.DataModels.IQ_Queries.<>c__DisplayClass8_01.<export>b__0(ListFilter filterValue) in IQ_Queries.cs:line 231 at System.Linq.Parallel.ForAllOperator1.ForAllEnumerator1.MoveNext(TInput& currentElement, Int32& currentKey) at System.Linq.Parallel.ForAllSpoolingTask`2.SpoolingWork() at System.Linq.Parallel.SpoolingTaskBase.Work()

(12 Feb, 09:40) fcabralj

maybe instead of using decimal, it is possible to use "String" at it is. And then convert it in the code to a custom "big-decimal" implementation such as https://github.com/tjpranic/BigDecimal

I don't know where such precision might be necessary. If 29 digits is not enough, I think "String" or "binary" objects should be used for this purpose. Or the result should be rounded.

If .NET doesn't provide such types out-of-the-box, they simply do not exist and has to be developed :)

(12 Feb, 09:53) Vlad

In my case I'm just doing a query: Select * from Table where one of the fields has this NUMERIC(40,20) so after executing the command I'm doing:

while (DataReader.Read())

And it's here where the error happened, so I don't know exactly how could I manipulate the datatype. Is something happening in memory.

As a workaround I changed my select and did: Select cast(NumericColumn as NUMERIC(38,9))

as you know this is not great because I wouldn't like to couple my code with columns names, etc..

(12 Feb, 11:20) fcabralj
showing 5 of 10 show all flat view
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×64
×36
×6

question asked: 10 Feb, 08:28

question was seen: 156 times

last updated: 12 Feb, 11:20