INSERT INTO ...SELECT syntax error in join operator
Posted
by
user1477356
on Stack Overflow
See other posts from Stack Overflow
or by user1477356
Published on 2012-06-23T21:06:51Z
Indexed on
2012/06/23
21:16 UTC
Read the original article
Hit count: 175
I'm trying to write a shopping basket into a order + orderline in a sql database from C# asp.net. the orderline will contain a ordernumber, total price, productid, quantity etc. for every item in the basket. The order itself will contain the ordernumber as primary key and will be linked to the different lines through it. Everything worked fine yesterday, but now as i tried to use a SELECT command in the insert into statement to get things more dynamic i'm getting the above described syntax error.
Does anybody know what's wrong with this statement:
INSERT INTO [order]
(klant_id,totaalprijs,btw,subtotaal,verzendkosten)
SELECT klant.id
, SUM(orderregel.totaalprijs)
, SUM(orderregel.btw)
, SUM(orderregel.totaalprijs) - SUM(orderregel.btw)
, 7.50
FROM orderregel
INNER JOIN
klant
ON [order].klant_id = klant.id
WHERE klant.username = 'jerry'
GROUP BY
id;
the ordernumber in the "order" table is on autonumber, in the asp codebehind there is a for each which handles the lines being written for every product, there's an index set on 0 outside of this loop and is heightened with 1 every end of it. The executenonquery of the order is only executed once at the beginning of the first loop and the lines are added after with MAX(ordernumber) as ordernumber.
I hope i have provided enough information and somebody is capable of helping me. Thanks in advance!
© Stack Overflow or respective owner