Question about insert query

Shortly why this works

insert dbo.t_Shopping_cart (Product_code,Name,Price,Group,Quantity,Total)
select Product_code,Name,Price,Group,Quantity=1,Total=1
FROM dbo.t_Product
WHERE Group=6

But this doesn't

insert dbo.t_Shopping_cart (Product_code,Name,Price,Group,Quantity,Total)
select Product_code,Name,Price,Group,Quantity=1,Total=Quantity*Price --this is actually line 4
FROM dbo.t_Product
WHERE Group=6

It gives this error
Msg 207, Level 16, State 1, Line 4
Invalid column name 'Quantity'. It does not help if Quantity*Price is in brackets. Probably a simple thing, but I don't know it. Is it even possible to do this kind of query?

Regards

Leif

Your problem stems from the fact that there is no Quantity field on the Product table.. from the declaration "Total=Quantity*Price" it does not infer the Quantity from the previous "Quantity=1" - it will look for it on the product table.

You already know your Quantity when building your sql, so you need not use the reference, you can simply put the number in.
e.g.
select Product_code,Name,Price,Group,Quantity=1,Total=1*Price
or
select Product_code,Name,Price,Group,Quantity=245,Total=245*Price

|||

Aha. You mean it should be something like

select Product_code,Name,Price,Group,Quantity=@.Quantity,Total=@.Quantity*Price --this is actually line 4

Leif

Question about GridView update query

I try to calculate a field value and update other. It almost works

UPDATE t_Shopping_cart SET [Product_code] = @.Product_code, [Quantity] = @.Quantity, [Total] = Quantity * Price WHERE ([Product_code] = @.Product_code)

I try to update t_Shopping_cart table with quantity values from grid view and calculate sum of products (quantity*Price). Best I can get is that quantity values are old. Some comments about my query:Price is also a field in table and control. [Product_code] = @.Product_code part is probably not needed.

Any ideas?

Leif

So there is a column in t_Shopping_cart with the name Price?
Then maybe you can use this:

UPDATE t_Shopping_cart SET [Quantity] = @.Quantity, [Total] = @.Quantity * Price WHERE ([Product_code] = @.Product_code)

|||

"So there is a column in t_Shopping_cart with the name Price?" Yes. I'll try that "UPDATE t_Shopping_cart SET [Quantity] = @.Quantity, [Total] = @.Quantity * Price WHERE ([Product_code] = @.Product_code)" tomorrow.

Thanks

Leif

|||

Np, feel free to get back to me if it doesn't work!

|||

Hi. A short question. Is this a good way to this. The more I read about ways to do this the more ways there seems to be calculating sub totals and probably checking duplicates too.

Regards

Leif