Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

fractional value saved as zero

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.
  • 3
  • 2
  • 2
  • +1
2 Solutions
Scott PletcherSenior DBACommented:
--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.
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
Jacques Bourgeois (James Burger)PresidentCommented:
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!

GregorySingerAuthor Commented:
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
Jacques Bourgeois (James Burger)PresidentCommented:
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".
Vitor MontalvãoMSSQL Senior EngineerCommented:
Compare your computer regional settings against the server regional settings, specially for numbers.
GregorySingerAuthor Commented:

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.
Jacques Bourgeois (James Burger)PresidentCommented:
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


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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