none
Losing Decimal Place When Transfered to Access 2013

    Question

  • I am trying to send a decimal value to an Access database and for some reason or another I am losing the decimal during transfer. The value is displaying correctly in the application and I can manually enter a decimal value in the table which displays correctly. 

    Access field is setup as:

    Data Type: Number

    Field Size: Decimal

    Format: #.##

    Precision: 18

    Scale: 3

    Decimal Places: 3

            If phvalue > 0 Then
                Try
                    NewRow = phdataDataSet.CitypH.NewRow()
                    NewRow.Cityph = Decimal.ToDouble(phvalue)
                    phdataDataSet.CitypH.AddCitypHRow(NewRow)
                    CitypHTableAdapter1.Update(phdataDataSet.CitypH)
                    txcount = ++txcount
                Catch ex As Exception
                    dbstatuslabel.ForeColor = Color.Red
                    dbstatuslabel.Text = "Database Error!"
                End Try
    
            End If

     
    Saturday, March 25, 2017 3:59 PM

Answers

  •  Although, while looking at your sample, I did find that one of my fields in the dataset was set to Int32. I changed it to Decimal but still does not work. 

    If I remember correctly, the dataset was setup as untyped. Language is set to English.

    I have since found my issue and fixed it. Naturally it was something simple but I was unaware the settings existed. 

    I quoted from one of my previous posts where I fixed an issue with the Dataset. When I initially setup the Dataset, it was setup for Int32. I changed the column datatype in the dataset to Decimal, but I was not aware of that the TableAdapter also has datatype settings for the Select, Insert, etc commands. 

    Under the TableAdapter properties, expand Insert Command, Click the Ellipse button in the Parameters property, the Parameter Collection Editor opens, Select the Parameter you are writing to, Select DbType as Decimal and Provider type as Decimal.

    Now the application is passing the numbers to the right of the decimal to the access database. 

    Monday, March 27, 2017 3:52 PM

All replies

  • Hello,

    Look at my code sample and compare to yours, mine works just fine.


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, March 25, 2017 5:24 PM
    Moderator
  • Thank you, Karen, but I believe your sample is getting data from Access in decimal format. I need to send a decimal number to Access and for some reason, the decimal is being lost.

    I did not see any controls to send a number to the Access db unless I missed it? 

     Although, while looking at your sample, I did find that one of my fields in the dataset was set to Int32. I changed it to Decimal but still does not work. 

    Saturday, March 25, 2017 6:02 PM
  • You are looking at it wrong then, first field is the primary key, we don't touch that, second is the Decimal field.

    You don't see any controls sending data because the field in question SomeNumber is data bound to the DataSet and BindingSource.

    SomeNumber field definition

    We could write code to do the insert via the DataSet e.g.

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim someDec As Decimal = 44.55D
        Me.Database1DataSet.Table2.Rows.Add(New Object() {Nothing, someDec})
        Table2BindingNavigatorSaveItem.PerformClick()
    End Sub

    Or I could use a class e.g.

    Imports System.Data.OleDb
    
    Public Class DataOperations
        Private Builder As New OleDbConnectionStringBuilder With
        {
            .Provider = "Microsoft.ACE.OLEDB.12.0",
            .DataSource = IO.Path.Combine(Application.StartupPath, "Database1.accdb")
        }
    
        Public Function AddNewRow(ByVal SomeNumber As Decimal, ByRef Identfier As Integer) As Boolean
            Dim Success As Boolean = True
            Dim Affected As Integer = 0
    
    
            Try
                Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                    Using cmd As New OleDbCommand With {.Connection = cn}
                        cmd.CommandText = "INSERT INTO Table2 (SomeNumber) Values(@SomeNumber)"
    
                        cmd.Parameters.AddWithValue("@SomeNumber", SomeNumber)
    
                        cn.Open()
    
                        Affected = cmd.ExecuteNonQuery()
                        If Affected = 1 Then
                            cmd.CommandText = "Select @@Identity"
                            Identfier = CInt(cmd.ExecuteScalar)
                        End If
                    End Using
                End Using
            Catch ex As Exception
                Success = False
            End Try
    
            Return Success
    
        End Function
    End Class

    Form code

    Dim ops As New DataOperations
    Dim theDecimal As Decimal = 4.56D
    Dim newId As Integer = 0
    If ops.AddNewRow(theDecimal, newId) Then
        MessageBox.Show($"{newId}")
    End If
    No matter which of the methods I presented all insert a new row with a Decimal.


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Saturday, March 25, 2017 6:36 PM
    Moderator
  • Thanks Karen,

    I still do not understand how my method would not pass a decimal to Access, but yours will?

            If phvalue > 0 Then
                Try
                    NewRow = phdataDataSet.CitypH.NewRow()
                    NewRow.Cityph = Decimal.ToDouble(phvalue)
                    phdataDataSet.CitypH.AddCitypHRow(NewRow)
                    CitypHTableAdapter1.Update(phdataDataSet.CitypH)
                    txcount = ++txcount
                Catch ex As Exception
                    dbstatuslabel.ForeColor = Color.Red
                    dbstatuslabel.Text = "Database Error!"
                End Try
    
            End If

    I could not get your first method to work in my case.  

    Saturday, March 25, 2017 7:21 PM
  • I still do not understand how my method would not pass a decimal to Access, but yours will?

    What exactly do you mean by 'the decimal is being lost.'? Do you mean that the number is being set to zero, or some other change is occurring?  Show an example of the number that is being passed and the number that appears in the database.

    Also, how are you examining the number before it is passed, and how are you examining it after it is in the database?

    Saturday, March 25, 2017 11:01 PM
  • I mean anything to the right of the decimal is not sent to my database. 

    I am sending for instance, 6.37 and in access it will show as 6.000. I can manually enter 6.37 into the database and it will display as 6.370 which is fine. 

    Monday, March 27, 2017 1:46 PM
  • I have to ask, is your DataSet a typed or un-type? Typed means you can do things like DataSet.Customers, where Customers is the name of a class for a table in a database named Customers vs. DataSet.TableName.Rows(0).Field(Of Decimal)("SomeFieldname")

    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, March 27, 2017 1:50 PM
    Moderator
  • @Seth,

    What is the language setting of the computer which is used (and if that is SA) than tell that as well. 

    There are many options which can cause your problem if that is not English (non SA).  


    Success
    Cor

    Monday, March 27, 2017 2:24 PM
  •  Although, while looking at your sample, I did find that one of my fields in the dataset was set to Int32. I changed it to Decimal but still does not work. 

    If I remember correctly, the dataset was setup as untyped. Language is set to English.

    I have since found my issue and fixed it. Naturally it was something simple but I was unaware the settings existed. 

    I quoted from one of my previous posts where I fixed an issue with the Dataset. When I initially setup the Dataset, it was setup for Int32. I changed the column datatype in the dataset to Decimal, but I was not aware of that the TableAdapter also has datatype settings for the Select, Insert, etc commands. 

    Under the TableAdapter properties, expand Insert Command, Click the Ellipse button in the Parameters property, the Parameter Collection Editor opens, Select the Parameter you are writing to, Select DbType as Decimal and Provider type as Decimal.

    Now the application is passing the numbers to the right of the decimal to the access database. 

    Monday, March 27, 2017 3:52 PM
  • For the record, you have a typed data set as that is the only time a TableAdapter is used.

    Any ways great to hear you are working now :-)


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, March 27, 2017 6:53 PM
    Moderator
  • For the record, you have a typed data set as that is the only time a TableAdapter is used.

    Any ways great to hear you are working now :-)


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thank you Karen and please forgive my ignorance. Very new to this world.
    Tuesday, March 28, 2017 12:42 PM