INSERT INTO ...SELECT syntax error in join operator
- by user1477356
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!
