Nick
|
| Posted: 04/17/2003, 3:28 PM |
|
I have been trying to figure this out for a week now and I am still stuck. I could really use some help understanding what I am doing wrong. I have gone through several articles recommended and am not able to resolve this as I do not understand this product well enough.
I have placed all the following code in the Open Event on a PrepareCheckout form in the bookstore example.
I determine a NextKey Value from a Counters Table and set the value as a session variable. I believe this works because I receive no error.
if GetParam("param")= "purchase" then
pkSQL = "select NextKey from counters where TableName= 'Invoices'"
openrs rs, pkSQL
last = GetValue(rs, "NextKey ")
session("last") = GetValue(rs, "NextKey ")
I then increment the value so the NextKey value is unique. This works because I can verify that the value has changed.
updateSQL = "update counters set NextKey = NextKey+1 where TableName= 'Invoices'"
cn.execute updateSQL
I then need to take the session variable and include it in the selection to perform an insert into Invoices that will contain the InvoiceID = NextKey and CustomerID. I would eventually like to sum the items as well but this is fine.
sSQLinsert = "insert into invoices (invoiceid, customerid) select order_id, customerid from orders where customerid = " & Session("UserID")
cn.execute sSQLinsert
I then need to insert the OrderItems into InvoiceItems and assign the InvoiceID of the Session NextKey same as Invoices.
sSQLcopy = "insert into InvoiceItems (invoiceid, customerid, item_id, quantity) select order_id, customerid, item_id, quantity from orders where customerid = " & Session("UserID")
cn.execute sSQLcopy
Finally I delete the orders and redirect the user. I am not able to use the Session NextKey in any of the inserts performed. I have tried every variation on the theme and gone through every posting even remotely similar. Is what I am doing just wrong?
Any help would be much appreciated
Thank You
|
|
|
 |
Hamilton
|
| Posted: 04/18/2003, 7:12 AM |
|
Can you send the code snippet & are you using MS Access or SQL Server?
|
|
|
|
Nick
|
| Posted: 04/18/2003, 2:55 PM |
|
Hamilton
Thanks for taking a look. How would I send the server DB to you. It is MS SQL Server 2000 by the way. I will send whatever you need to give this a once over. I posted the previous code in error how it actual look is the following:
The Goal is to determine NextKey Value from a table called counters
Set it as a session Variable session(“last”)
Increment Counters by one
Use the session(“last”) as New InvoiceID
Use the session(“last”) as new Foreign Key in InvoiceItems and Copy fields from order into InvoiceItems
Delete Order
Redirect User.
All Done
if GetParam("param")= "purchase" then
pkSQL = "select NextKey from counters where TableName= 'Invoices'"
openrs rs, pkSQL
last = GetValue(rs, "NextKey ")
session("last") = GetValue(rs, "NextKey ")
updateSQL = "update counters set NextKey = NextKey+1 where TableName= 'Invoices'"
cn.execute updateSQL
sSQLinsert = "insert into invoices (invoiceid, customerid) select " & session("last") & ", customerid from orders where customerid = " & Session("UserID")
cn.execute sSQLinsert
sSQLcopy = "insert into InvoiceItems (invoiceid, customerid, item_id, quantity) select " & session("last") & ", customerid, item_id, quantity from orders where customerid = " & Session("UserID")
cn.execute sSQLcopy
sSQLdel = "delete from Orders where customerID= " & Session("UserID")
cn.execute SSQLdel
response.redirect "ThankYou.asp"
end if
Do you see a problem with the syntax?
Thanks
Nick
|
|
|
|
Hamilton
|
| Posted: 04/25/2003, 8:01 AM |
|
Nick, If you're still stuck, post your email address and i'll be in touch.
You really should be doing some of this with stored procedures rather than inline sql.
|
|
|
|
|
