mysql server not storing decimal value


Not sure on what am I doing wrong, I had to add a field to store some new tax value , and want to use MySQL table just to store the tax value, all adding or subtracting operations will be done by .Net , but somehow it seem that MYSQL always round it up this value,
In debug mode I follow the tax value checking for mistakes and somehow I round it that value in my code but no , up to the proc if the value is for example 10.2 in :" myCommand.Parameters.AddWithValue("gvtax", SqlDbType.Decimal).Value = Gtax "  Gtax value is 10.2 ,
but when I check after in MySQL the value stored  in the Gtax field is 10 . it round it up to the lower or higher  is the decimal is above 5 .
my field inMYsql server is set as follow;
 `Gtax` decimal(10,2) DEFAULT '0.00',

I tried to set the field as double, float and now decimal to no avail ..... not sure of what I am doing wrong, I google to find some solutions but the consensus seem to be to set the field to Decimal to have exact value .

Sure that I miss something so any help will be greatly appreciated.


Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John Gates, CISSPSecurity ProfessionalCommented:
What you have should work but just for grins try DECIMAL(4,2) instead.  If that does not work then your code snippet that is writing the db would be useful to help further.

hiramlightAuthor Commented:
hello john,

tfried but didn't change anything.

in debug mode , when I check the value of "SqlDbType.Decimal"  in  myCommand.Parameters.AddWithValue("gvtax", SqlDbType.Decimal).Value = Gtax  ..    I get 5 only... !  when the value of Gtax is 5.4 , so my call must be wrong
John Gates, CISSPSecurity ProfessionalCommented:
I agree can you post the code?  I am sure I can help but need to see the constructor, call etc.
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

Jacques Bourgeois (James Burger)PresidentCommented:
You do not use AddWithValue correctly.

The second parameter should be the value that you assign, not the type. Try the following:

myCommand.Parameters.AddWithValue("gvtax", Gtax)
hiramlightAuthor Commented:
Hi Jacques,

Already tried this format , didn't change anything  , that format is mainly used for string ...

and john the full proc call, and is working find except for me not being able to save decimal value with gtax

 Public Function insertNewBooking_tax(ByVal Room_id As Integer, ByVal checkin As DateTime, ByVal checkOut As DateTime, _
ByVal depost_amnt As Double, ByVal rate_type As String, ByVal rate_cost As Double, ByVal Total_cost As Double, _
ByVal depo_type As Integer, ByVal nites_paid As Integer, ByVal Total_nites As Integer, ByVal Gu_Number As Integer, _
ByVal StaID As Integer, ByVal BookDet As String, ByVal amtBalance As Double, ByVal GenBookID As String, _
ByVal GuestCardNo As Integer, ByVal PayType As Integer, ByVal deposit As Double, ByVal Isgroup As String, ByVal Gtax As Double) As Integer

        Dim Recdate As DateTime = DateTime.Today
        ' Recdate = String.Format("{0:yyyy/MM/dd HH:mm:ss}", Recdate)
        Recdate = Format(DateTime.Now, "MMMM d, yyyy. hh:mm tt")

        Dim conn As New MySqlConnection

            conn.ConnectionString = dbUtilsConStr

            Dim myCommand As New MySqlCommand
            myCommand.CommandType = CommandType.StoredProcedure
            myCommand.Connection = conn
            myCommand.CommandText = "InsertNewBookingTax"

            myCommand.Parameters.AddWithValue("Room_id", SqlDbType.BigInt).Value = Room_id
            myCommand.Parameters.AddWithValue("checkin", checkin)
            myCommand.Parameters.AddWithValue("checkOut", checkOut)
            myCommand.Parameters.AddWithValue("depost_amnt", SqlDbType.Float).Value = depost_amnt
            myCommand.Parameters.AddWithValue("rate_type", rate_type)
            myCommand.Parameters.AddWithValue("rate_cost", SqlDbType.Float).Value = rate_cost
            myCommand.Parameters.AddWithValue("Total_cost", SqlDbType.Float).Value = Total_cost
            myCommand.Parameters.AddWithValue("depo_type", SqlDbType.BigInt).Value = depo_type
            myCommand.Parameters.AddWithValue("nites_paid", SqlDbType.BigInt).Value = nites_paid
            myCommand.Parameters.AddWithValue("Total_nites", SqlDbType.BigInt).Value = Total_nites
            myCommand.Parameters.AddWithValue("Gu_Number", SqlDbType.BigInt).Value = Gu_Number
            myCommand.Parameters.AddWithValue("StaID", SqlDbType.BigInt).Value = StaID
            myCommand.Parameters.AddWithValue("BooDet", BookDet)
            myCommand.Parameters.AddWithValue("Recdate", Recdate)
            myCommand.Parameters.AddWithValue("amtBalance", SqlDbType.Float).Value = amtBalance
            myCommand.Parameters.AddWithValue("GenBookID", GenBookID)
            myCommand.Parameters.AddWithValue("GuestCardN", SqlDbType.BigInt).Value = GuestCardNo
            myCommand.Parameters.AddWithValue("TypeOfPay", SqlDbType.BigInt).Value = PayType
            myCommand.Parameters.AddWithValue("depos", SqlDbType.Float).Value = deposit
            myCommand.Parameters.AddWithValue("isgroup", Isgroup)
            myCommand.Parameters.AddWithValue("gvtax", SqlDbType.Float).Value = Gtax

            Dim NewId As Integer
            myCommand.Parameters.AddWithValue("newId", SqlDbType.BigInt).Value = NewId
            myCommand.Parameters.Item("newId").Direction = ParameterDirection.Output



            Return myCommand.Parameters.Item("newId").Value

        Catch myerror As MySqlException

        End Try

        Return 0 ''indicating insert failed

    End Function


`IsXtra` char(1) DEFAULT 'f',
  `Xtra_cost` double(10,2) DEFAULT '0.00',
  `Xtra_paid` double(10,2) DEFAULT '0.00',
  `Isckox` char(1) DEFAULT 'f',
  `iscko_cst` double(10,2) DEFAULT '0.00',
  `Gtax` float(4,2) DEFAULT '0.00',
  PRIMARY KEY (`bo_id`)

Open in new window

hiramlightAuthor Commented:
and john I forgot the proc :

CREATE DEFINER=`rmsadmin`@`%` PROCEDURE `InsertNewBookingTax`( in Room_id bigint, in checkin datetime, in checkOut datetime, in depost_amnt Float, in rate_type varchar(5), in rate_cost Float, in Total_cost Float, in depo_type bigint, in nites_paid bigint, in Total_nites bigint, in Gu_Number bigint, in StaID varchar(45), in BooDet longtext, in Recdate datetime, in amtBalance Float, in GenBookID varchar(25), in GuestCardN bigint, in TypeOfPay bigint, in depos Float, in isgroup varchar(1), in gvtax float, out newId bigint)

INSERT INTO ro_booking( `roomID`, `check_in`, `check_out`, `depo_amount`, `rate_type`, `rate_cost`, `Total_cost`, `depo_type`, `nite_paid`, `nite_total`, `Gu_Number`, `Staff_ID`, `Book_det`, `Rec_date`, `book_balance`, `Booking_ID`, `Gu_CardNumber`, `Pay_type`, `deposit`, `ISgroup`, `Gtax`)
                              values (Room_id, checkin, checkOut, depost_amnt, rate_type, rate_cost, Total_cost, depo_type, nites_paid, Total_nites, Gu_Number, StaID,BooDet, Recdate, amtBalance, GenBookID, GuestCardN, TypeOfPay, depos, isgroup, gvtax);
            SELECT @@identity INTO newId;

I don't understand how the rest of your code is working as there is only one definition of AddWithValue which takes parameter name as first parameter and value as second parameter

Try changing all your parameter statements to following format
myCommand.Parameters.Add("gvtax", SqlDbType.Float).Value = Gtax
hiramlightAuthor Commented:

first thank you for all your input , I had to be away for work for a couple days and would like to apologize for a tardy reply.

The way I resolve for the moment is not pretty but it work and give he time to look at a more proper solution.

I change my data field to :  `Gtax` varchar(10) DEFAULT '0',
then I convert my values from double to string , so I change my function to : ByVal Gtax As String)   , all other entries remain unchanged, and my stor proc change to: myCommand.Parameters.AddWithValue("gvtax", Gtax).

and convert it back to a double, with format to .2 after the decimal ....

and it work , I get my tax value up to 2 digits after the decimal like eg: 10.35 and it add, sub fine ...

not pretty but it work , when I come with a cleaner solution I try to post it as un update .


Jacques Bourgeois (James Burger)PresidentCommented:
I would have rather multiplied by 100, save the result as an integer, and then divide by 100 when you need to bring back the 2 decimals. That way you stick with numbers, and it is probably more efficient than converting to strings an back.

In fact, I know a few programmers who always save money as cents using that tricks. This is not possible in all situations, because you often have to keep track of the extra decimals after the cents, but because integers are treated faster than decimals, they improve their performance by running most of their routines on integers.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hiramlightAuthor Commented:
Hi Jaacques,

yep .. very good point , I will try this . and by doing so awarding the points to you ...

And  thank's for that little trick..

hiramlightAuthor Commented:
not exactly what I need it, but it show me a different approach to what I was doing .. and may ended up being better that I  first thought

so a B+   ;)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.