This course teaches how to install and configure Windows Server 2012 R2. It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

This is from a SQL Server StoredProcedure

I am using the ceiling function to round off @X to the next highest whole number. Example 1.95, rounds off to 2. When I multiple 10.95 by the value of @X the product is rounding off to the next highest also. Can I stop this? Example: If @x = 2 ATotalweight should be 21.90 but it is rounding to 22. I want 21.90

declare @x decimal

@X=sum(ceiling(CMRC_Products.weight2 * CMRC_ShoppingCart.Quantity)/20),

@Totalweight= 10.95*@X

I am using the ceiling function to round off @X to the next highest whole number. Example 1.95, rounds off to 2. When I multiple 10.95 by the value of @X the product is rounding off to the next highest also. Can I stop this? Example: If @x = 2 ATotalweight should be 21.90 but it is rounding to 22. I want 21.90

declare @x decimal

@X=sum(ceiling(CMRC_Produc

@Totalweight= 10.95*@X

@X=sum(ceiling(CMRC_Produc

select sum(ceiling(1.91 * 2)/20) * 10.95

returns 2.19 as it should ...

declare @X decimal

declare @Y decimal

@X=sum(ceiling(CMRC_Produc

@Y=sum(CMRC_Products.weigh

@Totalweight= 10.95*@Y

or

@X=sum(ceiling(CMRC_Produc

@Totalweight= 10.95*sum(CMRC_Products.we

try something like this:

declare @x decimal(10,2)

@Totalweight= 10.95*sum(CMRC_Products.we

The purpose of this procedure is to charge $10.95 for each 1-20lbs of shipping weight. another words, if the total shipping weight is 19lbs the charge will be $10.95. If the weight is 20.5ibs the charge will be $21.90.

I can do it right on the .aspx page, but this creates other problems down the line. I am rethinking the whole process.

select sum(ceiling(1.91 * 2)/20) * 10.95

gives the behavior you expect

CMRC_Products.weight2 decimal (18,2)

CMRC_ShoppingCart.Quantity

@totalweight money OUTPUT

For this table

Order Weight Quantity

1 1.50 5

1 2.60 2

2 3.50 6

2 4.20 4

You get the ship cost with "10.95*ceiling(sum(weight * Quantity)/20)" not "10.95*sum(ceiling(weight * Quantity)/20)" as you had. Look, for example at this query,

select ordernum,sum(weight * Quantity) 'Total Weight',sum(weight * Quantity)/20 'Weight/20', ceiling(sum(weight * Quantity)/20) '20 Weights',10.95*ceiling(sum

group by orderNum

Which returns

ordernum Total Weight Weight/20 20 Weights Ship Cost

1 12.70 .635000 1 10.95

2 37.80 1.890000 2 21.90

ordernum Total Weight Weight/20 20 Weights Ship Cost

1 12.70 .635000 1 10.95

2 37.80 1.890000 2 21.90

the amount I am getting on the current test order is 11.00 for a 15lb order. it should be 10.95.

I have something wrong somewhere

Here is what I have:

@Totalweight=10.95*ceiling

on my aspx page:

Protected totalweight as decimal

lblshiptotAL.TEXT = String.Format("{0:c}", totalweight)

totalweight gets the value from @totalweight in the procedure

select convert(decimal,10.95), convert(decimal(10,2),10.9

returns

11 10.95

@Totalweight=convert(decim

still get $11.00

10.95*ceiling(sum(CMRC_Pro

thanks!

It has to be something with moving totalweight from the procedure to the web page. Apparantly the webpage is inheriting the ceiling function? Is that possible?

1. (Sub ShipWeightandTotals is from the webpage, I can complete page if you wish. )

2. Public Function GetWeight from .vb busines object ( I experimented with the 10,2 in the totalweight parameter, it made no difference)

3. Stored procedure

1.

Web Page:

Protected totalweight as decimal

Sub ShipWeightandTotals()

dim cart as ASPNET.StarterKit.Commerce

lblshiptotAL.TEXT = String.Format("{0:c}", totalweight)

TotalLbl.Text = String.Format("{0:c}", cart.GetTotal(cartId)+lbls

end sub

2.

Business object: ( .vb file)

Public Function GetWeight(ByVal cartID As String) As Decimal

Dim myConnection As SqlConnection = New SqlConnection(Configuratio

Dim myCommand As SqlCommand = New SqlCommand("CMRC_ShoppingC

myCommand.CommandType = CommandType.StoredProcedur

Dim parameterCartID As SqlParameter = New SqlParameter("@CartID", SqlDbType.NVarChar, 50)

parameterCartID.Value = cartID

myCommand.Parameters.Add(p

Dim parameterTotalWeight As SqlParameter = New SqlParameter("@TotalWeight

parameterTotalWeight.Direc

myCommand.Parameters.Add(p

myConnection.Open()

myCommand.ExecuteNonQuery(

myConnection.Close()

3.

Stored Procedure:

@Totalweight=10.95*ceiling

DonSurplus2 (12:19:22 PM): lblshiptotAL.TEXT = String.Format("{0:c}", strtotalweight)

DonSurplus2 (12:20:07 PM): Protected strtotalweight as decimal

DonSurplus2 signed off at 12:33:07 PM.

DonSurplus2 signed on at 1:13:30 PM.

DonSurplus2 (1:13:33 PM): @Totalweight=convert(decim

DonSurplus2 signed off at 1:26:34 PM.

DonSurplus2 signed on at 4:04:20 PM.

DonSurplus2 (4:04:32 PM): CREATE Procedure CMRC_ShoppingCartWeight

(

@CartID nvarchar(50),

@Totalweight money OUTPUT

)

AS

DECLARE @X decimal

SELECT

@Totalweight=convert(decim

FROM

CMRC_ShoppingCart,

CMRC_Products

WHERE

CMRC_ShoppingCart.CartID = @CartID

AND

CMRC_Products.ProductID = CMRC_ShoppingCart.ProductI

GO

I finally fixed this problem. This may not be the proper way but, I changed the SQLDbType to Money,8 and did the same in the Stored Procedure. I am no getting the correct values.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

GhostMod

Community Support Moderator