[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 248
  • Last Modified:

System.InvalidCastException error

CREATE PROCEDURE cartTotal
(@CartID char(36), @gift smallint  OUTPUT)

SELECT @gift = SUM(Quantity)
FROM ShoppingCart
WHERE ShoppingCart.CartID = @CartID AND Gift = 1

Gift is a bit field. Quantity an int.
What I want to do is to total the Quantity field for every row where Gift = 1.
Example:
  Gift             Quantity
   1                   1
   0                   1
   1                   3           so in this case @gift would equal 4.

I tried the above but get an error:

Exception Details:
System.InvalidCastException: Cast from type 'DBNull' to type 'Short' is not valid

details.gift = CType(command.Parameters("@gift").Value, Short)

But this line works in above: SELECT @gift = count(*)        . . . but I need to add the
Quantity to the count for every row where Gift = 1.
thanks
0
MikeMCSD
Asked:
MikeMCSD
1 Solution
 
rafranciscoCommented:
Try this one:

SELECT @gift = ISNULL(SUM(Quantity), 0)
FROM ShoppingCart
WHERE ShoppingCart.CartID = @CartID AND Gift = 1
0
 
MikeMCSDAuthor Commented:
thank you . . that worked.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now