Stored procedure syntax problem

[martinharvey via DotNetMonster.com]

I would be very grateful if someone could help me with a stored procedure
syntax problem

I want to insert the value "OrderTotal" into databasetable("Newtable") column
"OrderTotal"
(money (8)). The value can be returned from the page (Dim amount As Decimal =
ShoppingCart.GetTotal() totalAmountLabel.Text = String.Format("{0:c}",amount)
or returned by the function "ShoppingCart.GetTotal".

This is the syntax i have tried, but I get the error message "Invalid column
name OrderTotal"

CREATE PROCEDURE SP_NewOrder
(@CartID char (36),@CustomerID Varchar (50),@OrderTotal decimal(8))

AS

INSERT INTO NewTable (FirstName,ProductID,OrderTotal)

SELECT Customer.FirstName,Products.ProductID,Ordertotal

From Customer,Products Join ShoppingCart ON Products.ProductID =ShoppingCart.
ProductID
WHERE Customer.CustomerID=@CustomerID AND ShoppingCart.CartID = @CartID
GO

Can anyone tell me where i am going wrong

many thanks

martin

 

[Guest]

Hi,

Simple mistake but surprisingly easy to over look. When ever you use
variables in Transac SQL you need to prefix it with the '@' symbol so the
select statement will become:

Select Customer.FirstName, Products.ProductID, @OrderTotal

It is not however, case sensitive it is just the fact it's missing the @.

Another problem you will be having once that is fixed is the declaration of
the decimal. It differs from the other type declarations. The other tryes
require you to set the size of the variable. For example @CartID char(36)
gives you a char with 36 bytes.

The decimal in your database takes up 9 bytes (or whatever you set it to)
however, when you declare the variable you need to specify the persition
(number of intergers) and the scale (number of decimal places) so:

Create Procedure SP_NewOrder (@CartID char(36), @CustomerID varchar(50),
@OrderTotal decimal(18,4) )

AS ...

Would give you a decimal with 4 decimal places.

Hope this helps

- Mike

 

[martinharvey via DotNetMonster.com]

wrote:

Hi,

Simple mistake but surprisingly easy to over look. When ever you use
variables in Transac SQL you need to prefix it with the '@' symbol so the
select statement will become:

Select Customer.FirstName, Products.ProductID, @OrderTotal

It is not however, case sensitive it is just the fact it's missing the @.

Another problem you will be having once that is fixed is the declaration of
the decimal. It differs from the other type declarations. The other tryes
require you to set the size of the variable. For example @CartID char(36)
gives you a char with 36 bytes.

The decimal in your database takes up 9 bytes (or whatever you set it to)
however, when you declare the variable you need to specify the persition
(number of intergers) and the scale (number of decimal places) so:

Create Procedure SP_NewOrder (@CartID char(36), @CustomerID varchar(50),
@OrderTotal decimal(18,4) )

AS ...

Would give you a decimal with 4 decimal places.

Hope this helps

- Mike

---------------------------------------------------------------------------------
<a href="http://www.cogitar.net"> Cogitar Software. (http://www.cogitar.net)
</a>
http://www.web-dominion.co.uk Web-Dominion. (Web Design and hosting )
http://shop-dominion.com (senery landscape pictur gallery)
---------------------------------------------------------------------------------

I would be very grateful if someone could help me with a stored procedure
syntax problem

[quoted text clipped - 32 lines]

many thanks for your he;p with this mike

Martin