CODEWITHSUNDEEP
vb.net
Gofastonit
Gofastonit

Reputation: 11

Converting textbox string to a database as a decimal

I have been trying to convert a string in a textbox that is being added to a database as a decimal number. Everything I have tried has not worked. I get the number that has been rounded off.

newrow("Price") = Convert.ToDecimal(txtCost.Text)
newrow("Price") = Convert.ToInt32(txtCost.Text)
newrow("Price") = Cdbl(txtCost.Text)
newrow("Price") = Cdec(txtCost.Text)

I know there is a lot of information out there on this, but I can't find anything that works or me.

My text box contains 28.57, when the row gets added my gridview and the access database both display 29. The database data type for price is set to "Number" and field size "Long Integer".

Private Sub DataGridView5_RowHeaderMouseDoubleClick(sender As Object, e As DataGridViewCellMouseEventArgs) Handles DataGridView5.RowHeaderMouseDoubleClick



    i = DataGridView5.CurrentRow.Index
    txtPartNum.Text = DataGridView5.Item(0, i).Value
    txtTitle.Text = DataGridView5.Item(4, i).Value
    txtCost.Text = DataGridView5.Item(3, i).Value

    Dim cb As New OleDb.OleDbCommandBuilder(da)
    Dim tbl As DataTable = ds.Tables("Material")
    Dim newrow As DataRow = tbl.NewRow()
    cb.QuotePrefix = "["
    cb.QuoteSuffix = "]"

    newrow("Decription") = txtTitle.Text
    newrow("Part_Num") = txtPartNum.Text
    newrow("Price") = Convert.ToDecimal(txtCost.Text)
    newrow("Job_Title") = Form3.txtJob_Title.Text


    ds.Tables("Material").Rows.Add(newrow)
    da.Update(ds, "Material")

    'SET UP THE PROVIDER
    dbProvider = "PROVIDER=Microsoft.ACE.OLEDB.12.0;"

    'SET THE DATABASE AND WHERE THE DATABASE IS
    TheDatabase = "/WOG.accdb"
    MyFolder = "C:\WorkOrderGen"
    FullDatabasePath = MyFolder & TheDatabase

    'SET THE DATA SOURCE
    dbSource = "Data Source = " & FullDatabasePath

    'SET THE CONNECTION STRING
    con.ConnectionString = dbProvider & dbSource

    'OPEN THE DATABASE
    con.Open()

    'STORE THE SQL STRING AND CONNECTION OBJECT TO THE DATA_ADAPTER
    sql = "SELECT * FROM Material WHERE [Job_Title] LIKE " & "'%" & Form3.txtJob_Title.Text & "%'"

    da = New OleDb.OleDbDataAdapter(sql, con)
    ds.Clear()
    da.Fill(ds, "Material")
    Form3.DataGridView3.DataSource = ds.Tables("Material")

    'Close THE DATABASE  '
    con.Close()

I hope this helps clarify.

Upvotes: 0

Views: 2959

Answers (1)

Blackwood
Blackwood

Reputation: 4534

If you want to store the price as a decimal value in Access, you will need to set the Field Size of the Price field in Access as Decimal instead of Long Integer.

Before converting the contents of the TextBox to Decimal, you should check that the TextBox contains a valid decimal number. You can use Decimal.TryParse to both validate the input and convert it to Decimal. You will have to decide what you want to do if the text is invalid.

Dim price As Decimal
If Not Decimal.TryParse(TextBox1.Text, price) Then
    'Textbox does not contain a decimal number - do whatever you think is appropriate
    Exit Sub
End If
newrow("Price") = price

Upvotes: 1

Related Questions