Decimal Rounding (VB.Net)

Today I ran upon an issue with using the ROUND function in Visual Basic .net (VB.net) that I would assume that others have also found to cause them a problem.

I was attempting to get 65.3125 to round correctly up to 65.313 and every way I tried to edit the code I was getting 65.312.   At first I was thinking that it was an order of operations issue, but after dissecting the code, I realized that it was not.

I was using the following code and failing every time:

Dim dblTotal as Double

dblTotal = ROUND(65.3125 , 3)

Result of the above code is 65.312.

The issue with the above coding is that you cannot represent 65.3125 because it rounds a double-precision floating-point value to a specified number of fractional digits. So when using the ROUND function it uses true representation and rounds down.

Correct way to code it to make it round is using MATH.ROUND :

Dim dblTotal as Double

dblTotal = MATH.ROUND(65.3125 , 3, MidpointRounding.AwayFromZero)

Result of the above code is 65.313.

The reason  the above code works is it rounds a double-precision floating-point value to a specified number of fractional digits. A parameter specifies how to round the value if it is midway between two numbers.

I hope this helps someone out a bit and saves them a bit of time. I know it helped me out greatly.

If you like this post and want to see more, follow me on my website www.chadcompton.com
Or if you prefer...

SQL INSERT INTO SELECT Statement

The other day I ran upon some of my code that I thought might be helpful to some of you.  It is a quick and easy way to use an INSERT INTO statement combined with a SELECT statement in SQL to copy data from one table to another existing table.  Please note that any existing data that is already in the target table is unaffected by this process.

Here I will show you two different ways to accomplish this task.   

The first way will copy all of the columns from tblOriginal table to the tblTarget Table.

INSERT INTO tblTarget
SELECT * FROM tblOriginal;

The second way will copy specific columns from tblOriginal table to the tblTarget Table.

INSERT INTO tblTarget
(Column1, Column2, Column5)
SELECT Column1, Column2, Column5
FROM tblOrigninal;

I hope this helps someone out a bit and saves them a bit of time. I know it helped me out greatly.

If you like this post and want to see more, follow me on my website www.chadcompton.com
Or if you prefer...

Concatenate many rows into one single text string (SQL)

I recently starting re-coding some of my old Visual Basic 6 code to make it faster and more efficient while adding it to VB.Net and taking out many of the steps.  In this process, I have taken many lines of code and let SQL do most of the work for me by being smarter with my SQL Statements I produce.  One huge time saver I ran across was this one that I am going to show you.


I have three tables (tblOwner, tblDogs, EndTable) that I am working with.  The field Dog is found common in all three tables (I made typed it in Red Font).  I have a field called Owner that I wish to add together while being separated with commas without having to write code to loop and edit from VB.  Luckily I ran across a couple of articles that explained it to a degree.  I was able to join two tables with a one-to-many relationships.  In SQL, I found that XML Path method can do the concatenation of the rows very easily.


Table called tblOwner:

Dog             Owner
----------      -------------
13               Mark
13               Jerry
13               Paul
44               John
44               Duke

Table called tblDogs:

Dog             DogName
----------      -------------
13               Fido
44               Snoopy

Here is my code:

Update EndTable 
set Owner = t.Owners 
              from (Select distinct ST2.dog,  
          substring( 
          (   Select  ', '+  Owner  AS [text()] 
              From  tblDogs ST1 
              join tblOwners on st1.dog = tblOwners.dog 
              Where ST1.dog = ST2.dog               
              ORDER BY ST1.dog 
              For XML PATH ('') 
           ), 2, 1000) [Owners] 
From dbo.EndTable ST2)  t  
join EndTable 
on EndTable.dog = t.dog 
where t.dog > 0

Result that are expected for EndTable:

Dog             DogName              Owners
----------      -------------        -------------
13               Fido                 Mark, Jerry, Paul
44               Snoopy               John, Duke

I hope this helps someone out a bit and saves them a bit of time.  I know it helped me out greatly.


If you like this post and want to see more, follow me on my website www.chadcompton.com
Or if you prefer...