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 47

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.
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.


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
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 47

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
export sql results to csv 6 34
Attaching Database Failed ? 3 39
Can > be used for a Text field 6 37
SQL View nearest date 5 35
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

786 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