Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2002
    Posts
    28

    Unanswered: Pass Foreign Key value Oracle/ASP

    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

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Pass Foreign Key value Oracle/ASP

    Because the order_id is being assigned in a database trigger, your ASP application has no way of knowing what value got assigned. A good way to overcome this would be to move the insert statement into a stored procedure, which would look something like this:

    PROCEDURE add_order
    ( p_Order_Date DATE,
    p_Delivery_Address VARCHAR2,
    p_CC_Details VARCHAR2,
    ... etc.
    p_order_id OUT NUMBER
    )
    IS
    BEGIN
    INSERT INTO orders( Order_Date, Delivery_Address, CC_Details, ...)
    VALUES (p_order_date, p_delivery_address, p_cc_details, ...)
    RETURNING order_id INTO p_order_id;
    END;

    Now you can call that procedure from ASP, passing the values as parameters, and it will return the new Order_ID.

    Actually, the procedure should be made part of a package, as you will have other procedures like add_order_line, delete_order, ...

    This approach also means you will be using BIND VARIABLES, which are crucial in all Oracle applications, rather than concatenating the values into the insert statement which is very bad practise (kills performance).

  3. #3
    Join Date
    Aug 2002
    Posts
    28
    Thankyou for your reply Tony, you have been most informative. I will give it a go and let you know the outcome.

    Kind Regards,

    Paul Niland

  4. #4
    Join Date
    Aug 2002
    Posts
    28
    Hi Tony,

    I have given this a go and there is an issue returning the ORDER_ID
    i.e RETURNING ORDER_ID INTO P_ORDER_ID;

    I get the error PLS-00403: expression 'P_ORDER_ID' cannot be used as an INTO-target of a SELECT/FETCH statement.

    Any ideas?
    Last edited by Paul Niland; 02-28-03 at 13:19.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Sounds like you didn't specify OUT on the p_order_id parameter. If you did and it still doesn't work, try RETURNING into a variable and then assigning the variable to p_order_id.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •