Trailing Zero missing from Data in Data Table

Trailing Zero missing from Data in Data Table

Postby dyslexicanaboko » Wed 09 Dec 2009 23:05

Hello All,

I am trying to debug this issue and I will tell you what led up to this problem. Recently we (the company I work for) upgraded their DB to MySQL 5.1 and since then for some reason or another columns with the data type decimal will be missing their trailing zeros when they are put into a datatable. I am upgraded to the latest provider (dotConnect) as well.

I am posting here because when I run a query on the MySQL 5.1 and I check the column with the decimal data type (lets call this amount) the trailing zeros DO show up.

However when I run the same query in code, the data that is returned is missing its trailing zero.

For example:

Column: Amount decimal(8,2)

Example 1:
Data in Data Base: 100.10
Data in Data Table: 100.1

Example 2:
Data in Data Base: 125.00
Data in Data Table: 125

Under normal conditions this wouldn't be a big deal, but because this is accounting software the client is complaining that they want their zeros back.

If this isn't a data provider issue, I will post in the MySQL forums next.

Any recommendations?

Thanks in Advance.
Last edited by dyslexicanaboko on Thu 10 Dec 2009 15:27, edited 1 time in total.
dyslexicanaboko
 
Posts: 5
Joined: Wed 09 Dec 2009 22:48

Follow Up

Postby dyslexicanaboko » Thu 10 Dec 2009 15:25

I was informed by one of my colleagues that we were using a different provider before with the previous version of MySQL. When we upgraded to MySQL 5.1 we changed our provider to the Devart provider, so now I am 90% sure it is the provider.
dyslexicanaboko
 
Posts: 5
Joined: Wed 09 Dec 2009 22:48

Postby StanislavK » Fri 11 Dec 2009 11:30

The decimals stored in databases have trailing zeros as it is the way MySQL represents them. The decimals stored in DataTable are of the .NET type "decimal" which has no information about their precision. Thus, when the decimals from DataTable are displayed, e.g. in some Grid class, they are converted to strings by the standard ToString() method.

Probably, the provider you've previously used had specific ToString() conversion which added trailing zeros. dotConnect for MySQL doesn't have such functionality, hence we can only advice you to customize the controls in which you display data.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby dyslexicanaboko » Fri 11 Dec 2009 15:34

Thanks for the reply. We were tinkering around with the properties of the Devart.Data.MySql dll and here is what we found:

Code: Select all
MySqlCommand cmd = new MySqlCommand(SQLStatementHere, ConnectionStringHere);
cmd.CommandTimeout = 0;

MySqlDataAdapter adapter = new MySqlDataAdapter();
adapter.ReturnProviderSpecificTypes = true;
adapter.SelectCommand = cmd;
dataset = new DataSet();
adapter.Fill(dataset);


If I set the "ReturnProviderSpecificTypes" to true, it fixes the above issue I was having, however it creates another problem for me and that is I can't convert the provider types to a native .Net type.

Example Problem:

In our code, when this point is reached:

Code: Select all
PercentOwned = Convert.ToDecimal(ds.Tables[0].Rows[0]["Percent_Owned"]);


This exception occurs:
"Unable to cast object of type 'Devart.Data.MySql.MySqlDecimal' to type 'System.IConvertible'."

This can be remedied by doing the following:
Code: Select all
PercentOwned = Convert.ToDecimal(ds.Tables[0].Rows[0]["Percent_Owned"].ToString());


However I am trying to find the easiest solution for us because this would require a large number of code changes. Same problem with updating all my grids. I am trying to find a single point solution if possible.

Any recommendations?

If not I already have a few choices now, just choosing the one that hurts the least.

Thank you for your time
dyslexicanaboko
 
Posts: 5
Joined: Wed 09 Dec 2009 22:48

Postby dyslexicanaboko » Fri 11 Dec 2009 16:55

I found another issue in the mean time as well, is there a System.DBNull equivalent for the provider? The reason I ask is because a statement like this...

Code: Select all
if(ds.Tables[0].Rows[0]["Percent_Owned"] != DBNull.Value)
    //Do something...


...does not evaluate properly if I have the "ReturnProviderSpecificTypes" set to true.

Meaning if the value of "ds.Tables[0].Rows[0]["Percent_Owned"]" is null, it is not equivalent to the value of "DBNull.Value", which makes sense if I am not using dotNet types and using provider types instead.

I tried searching the forum and the reference pages for a Devart.Data.MySql DNull equivalent, but I couldn't find any.

Any recommendations on this?
dyslexicanaboko
 
Posts: 5
Joined: Wed 09 Dec 2009 22:48

Postby StanislavK » Mon 14 Dec 2009 12:28

To resolve the problem with converting MySqlDecimal you can try creating your own conversion function, e.g.
Code: Select all
class MyConverter
{
    public static decimal ToDecimal(object value)
    {
        if (value is MySqlDecimal)
            return (decimal)((MySqlDecimal)value);
        else return System.Convert.ToDecimal(value);
    }
}

Though this solution is not completely one-point, at least you'll be able to auto-replace Convert.ToDecimal with MyConverter.ToDecimal.

As for DBNull issue, you can use the IsNull property casting objects like ds.Tables[0].Rows[0]["Percent_Owned"] to System.Data.SqlTypes.INullable interface.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby dyslexicanaboko » Mon 14 Dec 2009 15:05

Thank you for your reply.
dyslexicanaboko
 
Posts: 5
Joined: Wed 09 Dec 2009 22:48

Re: Trailing Zero missing from Data in Data Table

Postby szaheer » Mon 29 Sep 2014 10:13

Hi,

I just came across this post & found that this is what the issue I have been facing.
I was curious to know if Devart has come up with any solution for the issue concerned here in any of latest version of dotConnect for MySql after 2009.

I have used Devart.Data.MySql - 8.1.45.0.

Looking forward to have solution,
szaheer
 
Posts: 2
Joined: Mon 29 Sep 2014 09:52

Re: Trailing Zero missing from Data in Data Table

Postby Pinturiccio » Wed 01 Oct 2014 12:08

When in a database, a table column is of the Decimal(*,*) type, then when using the Fill method of the MySqlDataAdapter type, the DataType property of the corresponding DataTable column will be of the System.Decimal type. Consequently, when you set this DataTable as a DataSource for DataGridView, the value of the System.Decimal type will be transmitted to the DataGridView column, what consequently results in that the reqired nulls are not displayed.

If you set the ReturnProviderSpecificTypes = true property for MySqlDataAdapter, then, when using the Fill method of the MySqlDataAdapte type, the DataType property of the corresponding DataTable column will be of the Devart.Data.MySql.MySqlDecimal type. And if you set the given DataTable as a DataSource for DataGridView, then the required nulls will be displayed in the column of the Decimal type.
Pinturiccio
Devart Team
 
Posts: 1930
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for MySQL