• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 276
  • Last Modified:

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.


  • 6
  • 2
  • 2
  • +1
1 Solution
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.
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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.
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+   ;)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 6
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now