Results 1 to 4 of 4
  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
    Feb 2003
    Posts
    6

    Re: Pass Foreign Key value Oracle/ASP

    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

  3. #3
    Join Date
    Aug 2002
    Posts
    28
    Thanks Kiran,

    Looks intersting. I think it may work

    Cheers for the reply

  4. #4
    Join Date
    Nov 2003
    Posts
    1

    Post

    Hi i'm new

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

Posting Permissions

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