Access database doesn't store decimal values correctly
I am having trouble storing decimal values in a table in my Access database.
If I enter values, no problem, but when I try to store them using my program, the database does not store the decimals and deletes ,
(8.7 becomes 87).
The field in the table is Decimal, Scale 2, Precision 8, and Decimal Places 2.
I am using this code to insert data:
Dim price as Decimal
ProductsTableAdapter.Insert(id,name,price)
I tried to put the price as double
too and I still have the same problem.
I have looked at a lot of places and read about changing the datatype in the access db to double
but it didn't work.
Any ideas would be appreciated!
Edit:
As cybermonkey , the value Decimal
is equal 8,7
, so I tried to change it that way.
I changed the code to replace it ,
with .
:
Dim price2 As String
price2 = price.ToString.Replace(",", ".")
ProductTableAdapter.Insert(id, name, price2)
Price 2 is equal 8.7
but again the database shows87,00
Edit 2:
I tried to create another project with a different db to determine if the problem was a specific adapter or not, but I have the same problem again.
After that, I debug the program to see how the decimal value is stored: 8,7
instead of 8.7
. Then I try to insert data into db and it is the same, if you put 8.7
or 8,7
, it works fine with those two values, so now I have no other idea why this doesn't work.
source to share
I was able to recreate the problem. This appears to be an "unfortunate feature" of the OLEDB Access Provider when running on a computer where the Windows locale does not use the period character ( .
) as the decimal character.
Using the following code
Dim price As Decimal = Convert.ToDecimal("8,7") Dim sb As New System.Text.StringBuilder("""price"" is a ") sb.Append(price.GetType.FullName) sb.Append(" whose value is ") If price < 1 Or price > 10 Then sb.Append("NOT ") End If sb.Append("between 1 and 10.") Debug.Print(sb.ToString) ProductsTableAdapter.Insert("myProduct", price)
when I run it with Windows set to "English (US)". I see debug output
"price" is a System.Decimal whose value is NOT between 1 and 10.
and the value 87 is inserted into the database because the string "8.7" is not a valid decimal point in this language.
On Windows set to "Spanish (Spain)" the same code now produces
"price" is a System.Decimal whose value is between 1 and 10.
but the value 87 is still inserted.
On Windows set to "French (Canada)", the debug output is the same
"price" is a System.Decimal whose value is between 1 and 10.
however, insertion does not work with "data type mismatch in criteria expression".
Accurate results have been achieved by replacing
ProductsTableAdapter.Insert("myProduct", price)
from
Dim myConnStr As String myConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=C:\Users\Public\Database1.accdb" Using con As New OleDbConnection(myConnStr) con.Open() Using cmd As New OleDbCommand("INSERT INTO Products ([name], price) VALUES (?,?)", con) cmd.Parameters.AddWithValue("?", "oledbTest") cmd.Parameters.AddWithValue("?", price) cmd.ExecuteNonQuery() End Using End Using
proving that this is a problem between System.Data.OleDb
and the OLEDB access provider and not just idiosyncratic TableAdapter. However, TableAdapters seem to rely solely on OleDb, so unfortunately they probably just won't work under these conditions.
The good news is that simply converting the OleDb code to Odbc seems to fix the problem for both "Spanish (Spain)" and "French (Canada)" Windows locales
Dim myConnStr As String myConnStr = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};" & "DBQ=C:\Users\Public\Database1.accdb" Using con As New OdbcConnection(myConnStr) con.Open() Using cmd As New OdbcCommand("INSERT INTO Products ([name], price) VALUES (?,?)", con) cmd.Parameters.AddWithValue("?", "odbcTest") cmd.Parameters.AddWithValue("?", price) cmd.ExecuteNonQuery() End Using End Using
so one possible solution might be to use OdbcDataAdapter instead of TableAdapter.
source to share
The problem is that .NET expects you to provide a Decimal
dot instead of a comma, which has to do with the way .NET handles localization (as per this ).
This means .NET removes ,
and instead of inserting the expected one 8.7
, you get 87
. It also explains why you manually enter data directly into the database, but no programmatic input is done.
Reading this problem seems like using it System.Globalization.CultureInfo.InvariantCulture
works, but after a while I was able to do it by converting Decimal
to String Constant
:
Const price As String = "8,7"
Dim price2 As String
price2 = String.Format(price, _
price.ToString(System.Globalization.CultureInfo.InvariantCulture))
ProductsTableAdapter.Insert(id,name,price)
The MSDN documentation states that you also need Import
a namespace Globalization
with Imports System.Globalization
.
source to share
I had the same problem but was working on a C # app, just in case someone else has the same problem, I'm going to post what helped ...
It's really bizzare, but I found it on some forum without explaining why it works.
When passing parameters to my SQL command, all I did was add ToString () to the decimal parameter like this:
cmd.Parameters.AddWithValue("@DecimalParameter", model.DecimalParameter.ToString());
And the decimal value is now stored correctly!
source to share
According to @Astopher, the problem is that .NET expects a decimal point with a point instead of a comma from you, which is explained by how. So I changed the type of the command parameter to varchar and it worked.
cm.Parameters.Add("@decimalcolum", OleDbType.VarChar);
cm.Parameters["@decimalcolum"].Value=decimalvalue;
cm.ExecuteNonQuery();
It has been tested in Vietnamese, French (France), English (US) region and language. I hope these issues are resolved!
source to share