0

I am trying to store a price but it stores it with the pence always being rounded up or down. How do I stop that so it stores the actual value after the decimal point ? Many Thanks.

VB CODE

Public Sub UpdateItemBuyPrice(ByVal fileID As Integer, ByVal BuyPrice As Decimal)

    Dim DBConnect As New DBConn
    Using db As DbConnection = DBConnect.Conn("DBConnectionString")
        Dim cmd As SqlCommand = DBConnect.Command(db, "UpdateItemBuyPrice")

        cmd.Parameters.Add(New SqlParameter("fileID", SqlDbType.Int, ParameterDirection.Input)).Value = fileID
        cmd.Parameters.Add(New SqlParameter("BuyPrice", SqlDbType.Decimal, ParameterDirection.Input)).Value = BuyPrice
        db.Open()
        cmd.ExecuteNonQuery()
        cmd.Dispose()
        cmd = Nothing
        db.Dispose()
        db.Close()
    End Using
End Sub

MS SQL

PROCEDURE [ocbUser].[UpdateItemBuyPrice]
    @fileID             integer,
    @BuyPrice       decimal
AS
BEGIN

UPDATE [tblItems]
   SET [BuyPrice] = @BuyPrice
 WHERE [fileID] = @fileID

END

Eggybread
  • 161
  • 1
  • 12
  • this may help: you have to add precision ,but i am not sure what is the default precision http://stackoverflow.com/questions/8758204/using-sqldbtype-decimal-in-prepared-statement-c-sharp – TheGameiswar Feb 28 '17 at 09:07
  • It's good to see you have implemented `Using` and `Parameters`. As a side note, the beauty of `Using` is you don't need to `Dispose` of your objects. Implement `Using` for your `SqlCommand` also and you can then remove `cmd.Dispose()`, `cmd = Nothing`, `db.Dispose()` and `db.Close()`. Exiting a `Using` block will dispose of objects and for your `SqlConnection` it will close the connection too. – Bugs Feb 28 '17 at 09:58
  • @Bugs - as an aside, are you sure SqlCommand even needs to be disposed? Isn't it just memory and hence garbage-collectable? – peterG Feb 28 '17 at 11:42
  • @peterG personally, if an object implements `IDisposable` I would wrap it in a `Using` block. For `SqlCommand` I don't think this is strictly necessary as you say. For me it's a habit. You can also use a `,` to implement one `Using` block. `Using db As DbConnection = DBConnect.Conn("DBConnectionString"), cmd As SqlCommand = DBConnect.Command(db, "UpdateItemBuyPrice") ... End Using` so it makes for easier reading IMO. [This may shed further light](http://stackoverflow.com/questions/1808036/is-sqlcommand-dispose-required-if-associated-sqlconnection-will-be-disposed). – Bugs Feb 28 '17 at 12:02
  • @Bugs Interesting - Thanks. Also found [this](http://stackoverflow.com/questions/38371063/how-to-properly-dispose-of-datatable-and-tableadapter) and the discussion in the onward link in PimpTech's comment. There seems to be more controversy around this issue than I thought! – peterG Feb 28 '17 at 12:56
  • @peterG thanks for that, it would certainly seem so. I had no idea about that until you pointed me towards the link. – Bugs Feb 28 '17 at 13:14

1 Answers1

1

Specify a precision and scale for example like decimal(18,2), it can handle the cost without rounding off..

**Decimal (p,s)**

Exact numerical, precision p, scale s.

Example: decimal(5,2) is a number that has 3 digits before the decimal and 2 digits after the decimal

Arockia Nirmal
  • 737
  • 1
  • 6
  • 20
  • Oops yep it's working now :-) I have the column defined as buyPrice(10,2) but forgot to add the (10,2) bit in the sql code. Thank you ! – Eggybread Feb 28 '17 at 09:17