fractional value saved as zero

Posted on 2014-10-20
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 300 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 51

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.
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


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 1200 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 51

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 http://msdn.microsoft.com/en-us/library/aa290042(v=vs.71).aspx. It will give you a rundown of the most often used Tools available.

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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.

764 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