none
Numeric Precision in Data Column RRS feed

  • Question

  • 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?
    Monday, February 10, 2020 12:09 PM

Answers

All replies

  • You should ask here

    https://sqlanywhere-forum.sap.com/


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, February 10, 2020 12:19 PM
    Moderator
  • i thinks it is more related to C# as I’m defining the data type into the code.
    Monday, February 10, 2020 2:00 PM
  • i thinks it is more related to C# as I’m defining the data type into the code.
    I doubt that as precision for a decimal is 28-29 digits, see documentation.  Seems like you are using the wrong type for the column.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, February 10, 2020 2:49 PM
    Moderator
  • I was making some additional tests and it seems something related to the precision as the only columns giving me problems are those with NUMERIC(40,20). I've tested with NUMERIC(38,8) and Numeric(19,4) and it worked fine, just with the 20 digits is not working.

    Monday, February 10, 2020 4:48 PM
  • Hi FcabralJ,
    In SQL Server, the default maximum precision of numeric and decimal data types is 38.
    And you can use Column.Precision property to get or set a value that specifies the numeric precision of the database column.
    Here are some related documents you can refer to.
    [Column.Precision Property]
    [Precision, scale, and Length (Transact-SQL)]
    Hope are helpful for you.
    Best Regards,
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, February 11, 2020 6:33 AM
  • Hi Daniel_Zhang,

    Thank you for the answering. The problem is in Company we've decided to use Sybase (which for me doesn't make sense) and they accept a Number (40,20).

    I've tried to trick a bit the reflection code, checking if the precision is higher the 38 and then transform the datatype to String, like this:

     
    if (scale + precision > 28)   
    {
             dataColumn.DataType = Type.GetType("System.String");    
    }    
    else   
    {        
            dataColumn.DataType = Type.GetType(dataRow["DataType"].ToString());     
    }

    It didn't work. Now I'm stucked because when I do the DataReader.Read method it says that the data is invalid.

    This is the error I'm getting:

    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, List`1 lstColumnsToFilter, String _sourceConnString) in IQ_Queries.cs:line 277 at RiskAlm_DataExtraction.DataModels.IQ_Queries.<>c__DisplayClass5_0`1.b__0(ListFilter filterValue) in IQ_Queries.cs:line 215 at System.Linq.Parallel.ForAllOperator`1.ForAllEnumerator`1.MoveNext(TInput& currentElement, Int32& currentKey) at System.Linq.Parallel.ForAllSpoolingTask`2.SpoolingWork() at System.Linq.Parallel.SpoolingTaskBase.Work()

    Error 103 -

    Name Value Type
    Message "Invalid data conversion" string




    • Edited by FcabralJ Tuesday, February 11, 2020 12:03 PM
    Tuesday, February 11, 2020 9:34 AM
  • Hi FcabralJ, 
    Base on your description,I can't reproduce the situation. In your code, what does "scale + precision > 28" mean?
    And  the error is mostly caused by the loss of accuracy, so I suggest that you canreduce the accuracy.
    Best Regards,
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Sunday, February 16, 2020 7:50 AM
  • > The problem is in Company we've decided to use Sybase (which for me doesn't
    > make sense) and they accept a Number(40,20).

    No, they don't.  Here's the Sybase documentation where it states that their limit is ALSO 38.

    http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc32300.1570/html/sqlug/sqlug251.htm


    Tim Roberts | Driver MVP Emeritus | Providenza &amp; Boekelheide, Inc.

    • Marked as answer by FcabralJ Thursday, February 27, 2020 7:09 AM
    Sunday, February 16, 2020 7:51 PM