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

by:Scott Pletcher
Scott Pletcher 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 46

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 is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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 46

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.…

862 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

24 Experts available now in Live!

Get 1:1 Help Now