Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New to the CF scene
    Join Date
    Dec 2011
    Posts
    9
    Thanks
    1
    Thanked 0 Times in 0 Posts

    foreign/primary key problems

    hi, i try to make add room page where the admin can add new room and insert it to the database. i have 2 tables, room & room_type, and i need to update both

    Room
    Room_ID (pk)
    RoomType_ID (fk)
    Admin_ID (fk)

    Room_Type
    RoomType_ID (pk)
    Admin_ID (fk)
    Description
    Price

    i don't get any error when i add the new room, but only the room_type table updated and there's no value in the room table. can someone please tell me why it's not working?

    Code:
    <!-- #include file = "connection.asp" -->
    
    <%
    
    if request.form("submit") = "Add" then
    
    ad_id = request.form("ad_id")
    r_id = request.form("r_id")
    rt_id = request.form("r_id")
    rdesc = request.form("desc")
    rprice = request.form("rprice")
    
    sSQL = "Insert into Room(Room_ID,RoomType_ID,Admin_ID)" &_
    "values ('" & r_id & "','" & rt_id & "','" & ad_id & "')"
     
    sSQL = "Insert into Room_Type(Admin_ID,RoomType_ID,Description,Price)" &_
    "values ('" & ad_id & "','" & r_id & "','" & rdesc & "','" & rprice & "')"
    
    connection.execute sSQL
    
    %>
    
    <script type="text/javascript">
    alert("Room added");
    </script>
    
    <%
    
    end if
    
    %>
    
    
    <html>
    <head></head>
    </style>
    
    <body>
    
    <h2><b>Add New Room</b></h2>
    <br/>
    <table>
    <form name="addroom" method="post" action="">
    <tr>
    	<td><b>Admin ID</b>&nbsp;</td>
    	<td><input type="text" name="ad_id" /></td>
    </tr>
    <tr>
    	<td><b>Room ID</b> &nbsp;</td>
    	<td><input type="text" name="r_id" /></td>
    </tr>
    <tr>
    	<td><b>Room Type ID</b> &nbsp;</td>
    	<td><input type="text" name="rt_id" /></td>
    </tr>
    <tr>	
    	<td><b>Description</b> &nbsp;</td>
    	<td><input type="text" name="desc" /></td>
    </tr>
    <tr>
    	<td><b>Room Price</b> &nbsp;</td>
    	<td><input type="text" name="rprice" /></td>
    </tr>
    <tr>
    	<td></td>
    	<td><input type="submit" name="submit" value="Add" /></td>
    </tr>
    </form>
    </table>
    </body>
    
    </html>
    Last edited by azie.; 12-29-2011 at 11:49 AM.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,084
    Thanks
    80
    Thanked 4,552 Times in 4,516 Posts
    You only called connection.execute sSQL after the SECOND sSQL assignment.

    You must call it after *EACH* assignment.

    Code:
    sSQL = "Insert into Room(Room_ID,RoomType_ID,Admin_ID)" &_
    "values ('" & r_id & "','" & rt_id & "','" & ad_id & "')"
     
    connection.execute sSQL
    
    sSQL = "Insert into Room_Type(Admin_ID,RoomType_ID,Description,Price)" &_
    "values ('" & ad_id & "','" & r_id & "','" & rdesc & "','" & rprice & "')"
    
    connection.execute sSQL
    I don't know if that is your only problem, but fix that first and if you still have a problem post again.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    azie. (12-28-2011)

  • #3
    New to the CF scene
    Join Date
    Dec 2011
    Posts
    9
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    You only called connection.execute sSQL after the SECOND sSQL assignment.

    You must call it after *EACH* assignment.

    Code:
    sSQL = "Insert into Room(Room_ID,RoomType_ID,Admin_ID)" &_
    "values ('" & r_id & "','" & rt_id & "','" & ad_id & "')"
     
    connection.execute sSQL
    
    sSQL = "Insert into Room_Type(Admin_ID,RoomType_ID,Description,Price)" &_
    "values ('" & ad_id & "','" & r_id & "','" & rdesc & "','" & rprice & "')"
    
    connection.execute sSQL
    I don't know if that is your only problem, but fix that first and if you still have a problem post again.
    yes, it's working now. thanks!


  •  

    Tags for this Thread

    Posting Permissions

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