How do I Insert to Table1, Update Table 2, Update Table 3? 1

[sinistapenguin]

Hi All

This is hopefully my last question for my current project.

The problem is one that I know has been addressed many times on these forums and various tutorial websites, but I can’t find the answer now I need it (well not one that I can get to work anyway):

I have a form which has a number of text fields and hidden fields. From this form I need to insert some fields into one table whilst updating 2 other tables with some other fields.

I would like all the above to happen when I click the submit button.

I have the following: (hf = hidden field)

hfInvoiceNo – hfOrderNo – hfWarehouse – txtStockCode – hfQty(stores calculation result) – hfToFollow – txtPrice – txtVAT – txtTotal

I need to insert InvoiceNo, OrderNo, StockCode, Price, VAT, Total into my Invoice_Line table
I need to update Qty in my Stock_Location table with hfQty where WarehouseID = hfWarehouse and where StockCode = txtStockCode
I also need to update Qty in Order_Line with hfQty where OrderNo = hfOrderNo and StockCode = txtStockCode.

I realise that what I’ve just written is almost the SQL script I would use for a SP, but I can’t seem to get it to work. Could it be something to do with the position of the SP within the code? I can’t see why though, because although some of the fields aren’t populated to begin with, they are all populated by the time the submit button is pressed.

Any help gratefully received.

Thanks

Sinista

 

[Cheech]

I tend to submit the form to an asp page that only has the insert/update functions on it. This way you can play about with inserting 1 form element into as many tables or even databases as you wish.

So, at the top of your page read all of the form elements into variables, as in:

varInvoiceNo = Request.Form("hfInvoiceNo&quot
and so on

then just do your inserts/updates individually

Cheech

[Peace][Pipe]

 

[sinistapenguin]

Hi Cheech

When you say 'submit to a page that only has the insert/update commands on it' do you mean create a new page which is called something like 'Confirm Transaction', then request all of the form elements from the previous 'Invoice Line Form' page as Request.form("XXX&quot variables. Then use this page to do the inserts/updates on loading?

Presumably doing it this way ensures that all the form elements are there before it runs the insert/update scripts?

Am I thinking along the right lines????

Sinista

 

[Cheech]

Yep, You can also redirect if the form isnt filled in, But I tend to javascript validate the form before submitting. Then just read in the variables do my updates/inserts and redirect on.

Cheech

[Peace][Pipe]