Greetings,
I'm very new to ASPRunnerPro. I have no ASP experience. I usually make necessary database edits via SQL Management Studio. But the time has come to put this task in the hands of one of our clerks.
I have a SQL database that does not have the functionality to correct a salesman ID if it was entered incorrectly. The salesman ID appears in 4 different tables:
invoice_hdr
invoice_hdr_salesrep
invoice_line_salesrep
oe_hdr_salesrep
The first two are always present. If there is an invoice, there will be an entry in the invoice header table and the invoice header salesrep table.
The other two do not always have an associated record. For instance, we can charge a customer through an invoice only transaction. That would produce only invoice header and invoice header salesrep records. Also, unless there is a specific commission rule, there will not be an invoice_line_salesrep record.
Ultimately, what I need to do is declare a set of variables that would serve as the field value to be updated to (Salesrep ID) as well as WHERE clause criteria (Invoice Number and Order Number) to make sure the correct records are being updated. I then need to update the same value in all 4 tables.
What I have been doing is manually running multiple UPDATE queries at once in SQL Management Studio. Those queries are below.
declare @SALEREPID int
declare @ORDERNUM int
declare @INVOICENUM int
--SET VALUES HERE:
set @SALEREPID = 1015
set @ORDERNUM = 1052454
set @INVOICENUM = 99149641
update invoice_hdr
set salesrep_id = @SALEREPID
where invoice_no = @INVOICENUM
update invoice_hdr_salesrep
set salesrep_id = @SALEREPID
where invoice_number = @INVOICENUM
and invoice_hdr_salesrep.primary_salesrep = 'Y'
update invoice_line_salesrep
set salesrep_id = @SALEREPID
where invoice_no = @INVOICENUM
update oe_hdr_salesrep
set salesrep_id = @SALEREPID
where order_number = @ORDERNUM
and oe_hdr_salesrep.primary_salesrep = 'Y'
How can I achieve this from one page in ASPRunnerPro.
Thanks!
