469,946 Members | 1,958 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,946 developers. It's quick & easy.

Stored procedure syntax problem

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

--
Message posted via DotNetMonster.com
http://www.dotnetmonster.com/Uwe/For...p-net/200512/1
Dec 2 '05 #1
2 1311
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)
---------------------------------------------------------------------------------

"martinharvey via DotNetMonster.com" <u14945@uwe> wrote in message
news:583d793a0b56e@uwe...
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

--
Message posted via DotNetMonster.com
http://www.dotnetmonster.com/Uwe/For...p-net/200512/1

Dec 3 '05 #2
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 martin


--
Message posted via DotNetMonster.com
http://www.dotnetmonster.com/Uwe/For...p-net/200512/1
Dec 4 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Toonman | last post: by
4 posts views Thread by Jarrod Morrison | last post: by
7 posts views Thread by eholz1 | last post: by
5 posts views Thread by Dennis | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.