Hi,

I have one asp page that is supposed to update two Oracle recordsets in a database.

My schema is as follows:

1 order has many order Items

Orders{OrderID, Date, Delivery_Address, CC_Details}
OrderItems{Order_item_ID, OrderID, Product_ID, QTY, Price}

I am using a trigger to create the primary key(Order_ID) for the orders table and one for the primary Key(Order_item_ID) of the Order_Items table.

I have two problems Here

A) I need to fire the Order_ID from the Orders table in to the Order_ID of the Order_Items table. In oracle I have set-up that Order_id in the Order_Items table to be a foreign Key but it does not work?

Is there anything else in Oracle or my asp script that is missing?

B) Once the order has gone though I would like to display the Order_ID to the customer, but this does not work because of the same problem.

Bellow is a copy of my ASP code. I hope you can help.

Regards,

Paul

Script for Orders Recordset

Dim rsorder
Dim rsorder_num_Rows

Set rsorder = Server.CreateObject("ADODB.Recordset")
rsorder.ActiveConnection = MM_ORCL_STRING
rsorder.Source = "SELECT * FROM PAUL.ORDERS"
rsorder.CursorType = 2
rsorder.CusorLocation = 2
rsorder.LockType = 3

rsorder.Open()

rsorder_numrows = 0

'Add the order details

rsorder.AddNew
rsorder.("Delivery_FIRST_NAME") = session("First_Name")
rsorder.("Delivery_LAST_NAME") = session("Last_Name")
rsorder.("Delivery_ADDRESS1") = session("Address_Line_1")
rsorder.("Delivery_ADDRESS2") = session("Address_Line_2")
rsorder.("Delivery_CITY") = session("City")
rsorder.("Delivery_COUNTY") = session("County")
rsorder.("Delivery_POSTAL_CODE") = session("Postal_Code")
rsorder.("Delivery_COUNTRY") = session("Country")
rsorder.("ORDER_DATE") = date()
rsorder.("USER_NAME") = session("MM_USERNAME")
rsorder.("CC_NUMBER") = session("ccnumber")
rsorder.("CC_TYPE") = session("cctype")
rsorder.("CC_EXPIRES") = session("ccexpire")

'Get the Order_ID to display to the customer
order_id = rsorder("ORDER_ID")

'Update the Record
rsorder.update

rsorder.Close
set rsorder = Nothing

Script for Order_Items Recordset

Dim rsorderitems
dim rsorderitems_numRows

Set rsorderitems = Server.CreateObject("ADODB.Recordset")
rsorderitems.ActiveConnection = MM_ORCL_STRING
rsorderitems.Source = "insert into ORDER_ITEMS (PRODUCT_ID, QTY, PRODUCT_UNIT_PRICE, ORDER_ID) VALUES ('" & ACART(I,0) & "','" & ACART(I,1) & "', '" & currentprice & "', '" & orderid & "')"
rsorderitems.CursorType= 2
rsorderitems.CursorLocation = 2
rsorderitems.Locktype = 2
rsorderitems.Open()
rsorderitems)numRows = 0

rsorderitems.Close
set rsorderitems = Nothing

Hi,

Do not use trigger to generate the id.

First create a sequence in oracle.

Use ADO to get the next value from the sequence.
SELECT SQ_TEST.NEXTVAL AS GBR_ID FROM DUAL"

SQ_TEST is the name of sequence.
GBR_ID is the new ID that U can use to update both the tables.


I hope that helps.

Kiran Math

Thanks Kiran,

Looks intersting. I think it may work

Cheers for the reply

Hi i'm new

but the use of Nextval, currval may be easier for you