fractional value saved as zero

Posted on 2014-10-20
Last Modified: 2014-10-26
I have a visual basic application in which a billing module saves invoice line data to an sql database.  The "value", expressed as a decimal type with one place after the decimal, will save a quantity of 0.5 as 0.00 in the sql database.
I can't figure out why.
Question by:GregorySinger
  • 3
  • 2
  • 2
  • +1
LVL 69

Assisted Solution

ScottPletcher earned 100 total points
ID: 40392989
--Like due to integer arithmetic.  
SELECT 1/2 --= 0
--Explicitly cast one of the values to decimal, such as:
SELECT CAST(1 AS decimal(9, 2)) / 2 --=0.5

Or a decimal (or float) value being saved into an int column.
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40393783
You need to post here the part of the code that do the insert into SQL table.
Can be a format issue like 0,5 instead of 0.5
LVL 40
ID: 40395091
In VB, you have to be careful if Option Strict is set to Off in the project's properties, which is the default, or at the top of the code window.

In such a case, if you assign a decimal value to any integer type (Integer, Short, Byte, Long), the decimal is automatically removed without any warning or error. It's always better to work with Option Strict On to be advised of such potential problems.

Author Comment

ID: 40404296
Thank you all for the input.
I am somewhat of a novice but have succeeded in creating an application that is otherwise working well.
Only recently did I first have the occasion to bill for a "half hour", i.e. 0.5 hours and noticed that it displayed on the invoice as "0.0" hours.  Below is the code segment that is involved in assigning the data to the new datarow.  Note that other aspects of my application that are saving decimal numbers to the sql database which are directly bound to fields in the vb application are working fine.  The apparent difference here is that I am saving the datarow to the sql database programmatically.  


                Dim Ndr As DataRow
                'set it to a new row
                Ndr = ProfProjectDataSet.Invline.NewRow

                Ndr("InvoiceID") = invno
                Ndr("InvBillCatID") = JobCostDataGridView.Rows(i).Cells("JobCostBillCatID").Value.ToString
                Ndr("InvQuan") = JobCostDataGridView.Rows(i).Cells("JobCostQuan").Value
                Ndr("InvUnit") = JobCostDataGridView.Rows(i).Cells("JobCostUnit").Value.ToString
                Ndr("InvPrice") = JobCostDataGridView.Rows(i).Cells("JobCostPr").Value.ToString
                Ndr("InvDesc") = JobCostDataGridView.Rows(i).Cells("JobCostDesc").Value.ToString
                Ndr("InvLogDate") = JobCostDataGridView.Rows(i).Cells("JobCostLogDate").Value.ToString
                Ndr("InvExtended") = JobCostDataGridView.Rows(i).Cells("JobCostExtended").Value.ToString
                Ndr("InvGross") = JobCostDataGridView.Rows(i).Cells("JobCostGross").Value.ToString
                Ndr("JobCostID") = JobCostDataGridView.Rows(i).Cells("JobCostID").Value
                Ndr("InvSource") = JobCostDataGridView.Rows(i).Cells("JobCostSource").Value.ToString
                Ndr("InvPPaySItemID") = JobCostDataGridView.Rows(i).Cells("JobCostPPaySItemID").Value


                'add the row to the data table

                'save the new Invoice data row
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

LVL 40

Accepted Solution

Jacques Bourgeois (James Burger) earned 400 total points
ID: 40404323
Do you actually see the decimal in the grid? If not, then the problem is in the grid, not in the assignment to the field.

As for the assignments, they seem to show an important problem in your design. You are assigning a String to invPrice (that is a probably a number) and a String to invLogDate (that is a probably a date), and so on. Are these also defined as text fields in the database? If so, this is a very bad way of working.

Let's do it for the date, because it is the one that is the easiest to understand, but that plays with all the other types of data.

The date should normally be defined as a Date or DateTime field in the database, depending on the database you use and whether you need to record the time or not with it. This way, a date is stored in a way that is independent of the language or the format. It prevents you from having stuff as 13 for the month, or 32 for the day, things that can easily happen in a text field, an gives you bad results when you try to work with the database.

When you assign the date to a Date or DateTime field, you new to convert to a date instead of converting to a string as you are doing in your code. Note the CDate in the following:

Ndr("InvLogDate") = CDate(JobCostDataGridView.Rows(i).Cells("JobCostLogDate").Value)

Once again, this prevents invalid dates from being entered in the database. This implies that you are sure that the user has entered the date properly. You can do this in different ways, the best one being to use Date.TryParse to try to assign the date to a Date variable that you will then assign to the database field is the value is OK. Lookup DateTime.TryParse (In VB you use Date.TryParse instead of DateTime).

In fact, a date should be treaded as a date as much as possible, and as a String as little as possible.

The same holds for numbers, while making a distinction between numbers that can contain decimals, and numbers that don't. For instance, if the field is defined as an integer (int) in the database, and you try to assign 10.5, you will lose the .5, because an integer field cannot hold a decimal.

It's your job to define properly the types of the fields in the database, the type of variables that you use in your code, and to validate that the user entered the data in text that is valid for a conversion to the type of data in which you are recording it.

And I repeat what I said before. If Option Strict is set to Off in the project's properties, you are delegating the conversion to the system, and it will automatically removes the decimal without telling you if it needs to. If you set it to On, the compiler will tell you when there is a danger that this can happen, and it such a case, will flag you that you have a decision to make, such as "should my field be a real or an integer".
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40404527
Compare your computer regional settings against the server regional settings, specially for numbers.

Author Closing Comment

ID: 40405520

Thank you for the detailed explanation.  I actually was very careful and consistent in the assignment of data types both on the sql and vb end.  However I had not been using "option strict" and realize now that letting vb determine the data type where there is an inconsistency could be an issue.
In testing, when I setup a msgbox to read out the value of ndr("invQuan") where the expected value should have been 0.5, that variable does contain 0.5.  Somewhere then its data type must be somehow incorrect and I have to figure out why and how.
Thanks again for the help.
LVL 40
ID: 40405607
Do not use a MessageBox to interpret what happens in your application when you have problems. Use the debugger.

By settings up breakpoints in strategic locations, you can pause the application and look around for variable values and run the code line by line and see when and how the values are manipulated.

If you are not familiar with the debugging Tools, give a look at It will give you a rundown of the most often used Tools available.

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA.…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now