Update problem with decimal value

[Andre Botelho]

Hi there...

I am trying to update a dataset with a decimal value (precision 9,
scale 4).

This is the column in the XML file generated by the DataSet
<PERC_DESCONTO>23.0402</PERC_DESCONTO>

As you can see the value is 23.0402 but when I update this dataset the
exception "Parameter value '230402,0000' is out of range." is raised.
If I change the value to 23.04 it works fine. I am using
SqlCommandBuilder with SqlDataAdapter.

This is the code of the update method:

private bool UpdateDataSet(string TableName, DataSet dsUpdate,
SqlConnection Connection)
{
try
{
Connection.Open();
try
{
string CommandText = "SELECT * FROM " + TableName;
SqlDataAdapter Adapter = new SqlDataAdapter(CommandText,
Connection);
SqlCommandBuilder Builder = new SqlCommandBuilder(Adapter);

Adapter.RowUpdated += new
SqlRowUpdatedEventHandler(OnRowUpdated);
Adapter.Update(dsUpdate.Tables[0]);

return true;
}
finally
{
Connection.Close();
}
}
catch (Exception ex)
{
ErrorList.Append(ex.Message + Environment.NewLine +
ex.GetType().FullName);
return false;
}
}

Is this something about using commandbuilder? Maybe it's default scale
value is 2.
Thanks in advance for those who can help me...

Sorry for the poor english.

 

[Miha Markic [MVP C#]]

Hi Andre,

You are certainly experiencing digital separator issue.
You are using , while elsewhere is used .
Hard to say where since you are not providing much code (where do you assign
parameter value, etc.)

 

[Andre Botelho]

Thanks Miha!!! That exactly my problem, I should have guessed it before.

Let me explain a little bit more about my project. It will read data
from a database, create a XML file and send it by FTP. Later the system
will download the XML and update data into another database.

To create the XML I use DataSet.WriteXML, after dowload to read it I use
DataSet.ReadXML. I parse the dataset and make some necessary updates. I
am testing this application right now and I am writing and reading the
XML in the same machine, so using the same regional settings. I thought
ADO.NET would follow my regional configuration, but as I could see now
it doesn't, no problem about that, but how can I set the decimal
separator during the SqlDataAdapter.Update method? Can I use it with
SqlCommandBuider or should I create the SqlCommands myself?

Thanks a lot for the help!





Andre Botelho

 

[Andre Botelho]

Hi Miha... it's me again...

I've tried the following code:

CultureInfo myCI = new CultureInfo("es-ES", false);
myCI.NumberFormat.NumberGroupSeparator = ".";
myCI.NumberFormat.NumberDecimalSeparator = ",";
System.Threading.Thread.CurrentThread.CurrentCulture = myCI;

I did it before reading the source DataSet. Even changing the regional
settings the error remains the same. I am still making researchs on the
web... I thought it would be easier to solve...

see you...


Andre Botelho

 

[Marina]

After you read the data in, and you check in your dataset, is the value
correct? Is the column a numeric column in the dataset? I am wondering if
this is an issue with the machine you are running your application on, or on
the database server (which can have its own regional settings).

 

[Cor Ligthert [MVP]]

Marina,

This is a strange problem. My settings are Dutch which have almost
completely the same behaviour as the Spanish even the currency sign.

This code (I had VB test project open and was to lazy to start a new C#
project)
\\\
Dim dt As New DataTable("Marina")
dt.Columns.Add("DecimalTest", GetType(System.Decimal))
dt.LoadDataRow(New Object() {23.0402}, True)
dt.WriteXml("c:\test1\marina.xml")
///
Creates this XML file
\\\
<?xml version="1.0" standalone="yes" ?>
- <DocumentElement>
- <Marina>
<DecimalTest>23.0402</DecimalTest>
</Marina>
</DocumentElement>
///

In an XML file and in a SQLserver the decimal seperator is just a dot.
(with a toString it is represented with a comma for me 23,0402)

The region settings are AFAIK not important.

Cor

 

[Andre Botelho]

Hi Marina...

I have just solved the problem some hours ago... it was exactly what
Miha told. The digital separator in the XML file is "." but my machine
is configured for ",".

In my previous post I said that I was trying to change the regional
configuration and it was not working, my failure, it does work.

I use the following code before parsing the DataSet and apply the
changes I want:

CultureInfo myCI = new CultureInfo("en-US", false);
myCI.NumberFormat.NumberGroupSeparator = ",";
myCI.NumberFormat.NumberDecimalSeparator = ".";
System.Threading.Thread.CurrentThread.CurrentCulture = myCI;

Now it is working fine... it is reading the value 23.0402 as 23,0402
instead of 230402,0000.

Thank you all for the help. I hope this issue will be usefull for others
in the future.

bye,

Andre Botelho

 

[Cor Ligthert [MVP]]

Doh,

The dot is of course not in SQL Server in that it are just value types in
which the decimal pointer is integrated.

Cor

 

[Miha Markic [MVP C#]]

Hi Andre,

You might also want to use CultureInfo.InvariantCulture (instead of setting
the separators yourself) which is sort of independent one.

 

[Andre Botelho]

Thanks for this tip Miha!

Andre Botelho