Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Inserting into two tables

    I have this problem where I'm trying to insert into 2 tables. As you can see from the code, I can successfully insert into tblConsignments, but I cannot insert into tblSender and tblReceiver. If you scroll to the bottom, I've put the form details that I need inserted.
    Any help would be much appreciated.

    I have 2 tables, one for tblsender and one for tblreceiver.
    They both have exactly the same fields

    CustomerID (AutoNumber)
    ConsignmentID
    Name
    Address
    WorkNo
    HomeNo
    Mobile
    email


    So basically, how do I write the Insert statement so both details from sender and receiver are inserted into tblSender and tblReceiver when the Add button is clicked?. If you can please show me in the context of my below code, then that would be much appreciated.
    Thankyou in advance

    -------------------------------

    <%
    Response.expires = 0
    Response.expiresabsolute = Now() - 1
    Response.addHeader "pragma", "no-cache"
    Response.addHeader "cache-control", "private"
    Response.CacheControl = "no-cache"
    %>

    </script>
    <form onSubmit="return EW_checkMyForm(this);" action="tblConsignmentsadd.asp" method="post">



    <input type="hidden" name="a" value="A">

    <table border="0" cellspacing="1" cellpadding="5" bgcolor="#CCCCCC">
    <tr>
    <td bgcolor="#0099CC"><font color="#FFFFFF"><font size="-1">Consignment ID</font>*</font></td>
    <td bgcolor="#FFFFFF"><font size="-1"><input type="text" size="1" disabled name="x_ConsignmentID" value="<%= Session("userid") %>"
    <td bgcolor="#FFFFFF"><font size="-1"><input type="text" name="x_ConsignmentID" value="<%= x_ConsignmentID %>" size=50 maxlength=50></font>*</td>
    </tr>
    <tr>
    <td bgcolor="#0099CC"><font color="#FFFFFF"><font size="-1">Destination</font>*</font></td>
    <td bgcolor="#F5F5F5"><font size="-1"><SELECT name="x_Destination">


    <%
    SQL = "Select distinct * FROM tbldestination ORDER BY Destination ASC"
    'assumption 3
    SET RS = Conn.Execute(SQL)
    While NOT RS.EOF


    response.write "<option value=""" & RS("destination") & """>" & RS("Destination") & "</option>"

    RS.MoveNext
    WEND
    Set RS = NOTHING

    Response.Write "</select>"

    SQL = "SELECT * FROM tblOrigin WHERE initial = '" & Session("userid") & "'"
    Set RS = Conn.Execute(SQL)

    If RS.EOF = False Then
    x_origin = RS("origin")
    End If

    Conn.Close
    %>
    <tr>
    <td bgcolor="#0099CC"><font color="#FFFFFF"><font size="-1">Status</font>*</font></td>
    <td bgcolor="#FFFFFF"><font size="-1"><INPUT TYPE="Radio" NAME="x_Status" <% If x_Status = "0" Then %><% End If %> VALUE="0" checked ><%= "Departed for Central Office" %></font>*</td>
    </tr>
    </font>*</td>
    </tr>
    <tr>
    <td bgcolor="#0099CC"><font color="#FFFFFF"><font size="-1">Origin</font>*</font></td>
    <td bgcolor="#F5F5F5"><font size="-1"><input type="text" name="x_Origin" value="<%= x_Origin %>" disabled size=30 maxlength=50></font>*
    <input type="hidden" name="x_Origin" value="<%= x_Origin %>" size=30 maxlength=50>
    </td>
    </tr>

    <tr>
    <td bgcolor="#0099CC"><font color="#FFFFFF"><font size="-1">Type</font>*</font></td>
    <td bgcolor="#FFFFFF"><font size="-1"><%
    x_TypeList = "<SELECT name='x_Type'>"
    x_TypeList = x_TypeList & "<OPTION value="""""
    If x_Type = "" Then
    x_TypeList = x_TypeList & " selected"
    End If
    x_TypeList = x_TypeList & ">" & "" & "</option>"
    x_TypeList = x_TypeList & "<OPTION value=""0"""
    If x_Type = "0" Then
    x_TypeList = x_TypeList & " selected"
    End If
    x_TypeList = x_TypeList & ">" & "Document" & "</option>"
    x_TypeList = x_TypeList & "<OPTION value=""1"""
    If x_Type = "1" Then
    x_TypeList = x_TypeList & " selected"
    End If
    x_TypeList = x_TypeList & ">" & "Parcel" & "</option>"
    x_TypeList = x_TypeList & "</select>"
    response.write x_TypeList
    %>
    </font>*</td>
    </tr>
    <tr>
    <td bgcolor="#0099CC"><font color="#FFFFFF"><font size="-1">Description</font>*</font></td>
    <td bgcolor="#FFFFFF"><font size="-1"><textarea cols=25 rows=5 name="x_Description"><%= x_Description %></textarea></font>*</td>
    </tr>
    <tr>
    <td bgcolor="#0099CC"><font color="#FFFFFF"><font size="-1">Remarks</font>*</font></td>
    <td bgcolor="#FFFFFF"><font size="-1"><textarea cols=25 rows=5 name="x_Remarks"><%= x_Remarks %></textarea></font>*</td>
    </tr>
    <tr>
    <td bgcolor="#0099CC"><font color="#FFFFFF"><font size="-1">Depart Date</font>*</font></td>
    <td bgcolor="#FFFFFF"><font size="-1"><input type="hidden" name="x_DepartDate" size=25 value="<%= x_DepartDate %><%= FormatDateTime(Date, 1) %>*<%= FormatDateTime(Now, 3)%>"><%= x_DepartDate %><%= FormatDateTime(Date, 1) %>*<%= FormatDateTime(Now, 3)%></font>*</td>
    </tr>
    </table>





    Sender Details

    Name: <input type="text" name="name">

    Business: <input type="text" name="Business">

    Address: <input type="text" name="Address" >

    Work No: <input type="text" name="WorkNo">

    Home No: <input type="text" name="HomeNo">

    Mobile No: <input type="text" name="MobileNo">

    Email: <input type="text" name="Email" >


    Receiver Details

    Name: <input type="text" name="name">

    Business: <input type="text" name="Business">

    Address: <input type="text" name="Address" >

    Work No: <input type="text" name="WorkNo">

    Home No: <input type="text" name="HomeNo">

    Mobile No: <input type="text" name="MobileNo">

    Email: <input type="text" name="Email" >


    <input type="submit" name="Action" value="ADD">
    </form>

    -----------------------------

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting into two tables

    Hi Mark,

    If i understand correctly, you want to insert the Sender details in to tblSender and the Receiver details into tblReceiver.

    The problem at the moment is that your form elements for Sender and Receiver have the same names. You'll need to change the input names to a unique name for each one, such as Sender_Name, Receiver_Name, etc...

    (Forgive me if my ASP is a bit rusty - I've spent the past 6 months working exclusively with ASP.NET)

    The insert statement should be pretty simple:<pre><%
    SET cnn = Server.Create("ADODB.Connection")
    cnn.Open("<Your Connection String>")

    'Insert Sender
    cnn.Execute("INSERT INTO tblSender (Name, Business, Address, WorkNo, " _
    & "HomeNo, MobileNo, Email) VALUES (" _
    & Request.Form("Sender_Name") & ", " _
    & Request.Form("Sender_Business") & ", "_
    & Request.Form("Sender_Address") &", "_
    & Request.Form("Sender_WorkNo") & ", " _
    & Request.Form("Sender_HomeNo") & ", "_
    & Request.Form("Sender_MobileNo") & ", "_
    & Request.Form("Sender_Email") & ")")

    'Insert Receiver
    cnn.Execute("INSERT INTO tblReceiver (Name, Business, Address, WorkNo, " _
    & "HomeNo, MobileNo, Email) VALUES (" _
    & Request.Form("Receiver_Name") & ", " _
    & Request.Form("Receiver_Business") & ", " _
    & Request.Form("Receiver_Address") & ", "_
    & Request.Form("Receiver_WorkNo") & ", " _
    & Request.Form("Receiver_HomeNo") & ", " _
    & Request.Form("Receiver_MobileNo") & ", "_
    & Request.Form("Receiver_Email") & ")")

    Set cnn = nothing
    %></pre>

    There's a lot more that can be done with this, but I think this should give you an idea of how to handle the insert.

    Hope this helps!

  3. #3
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting into two tables

    Thanks Mark, the approach I tried was this way, and it seems to work ok so far, so fingers crossed .[img]/forums/images/smilies/smile.gif[/img]

    ' update receiver
    strsql = "select * from tblReceiver where 0 = 1"
    set rs = server.CreateObject("ADODB.Recordset")
    rs.open strsql, conn, 1, 2
    rs.addNew
    rs("ConsignmentID") = Request.Form("ConsignmentID") & tmpFld
    rs("name") = Request.Form("receiverName")
    rs("business") = Request.Form("receiverBusiness")
    rs("address") = Request.Form("receiverAddress")
    rs("workNo") = Request.Form("receiverWorkNo")
    rs("homeNo") = Request.Form("receiverHomeNo")
    rs("mobileNo") = Request.Form("receiverMobileNo")
    rs("email") = Request.Form("receiverEmail")
    rs.update
    set rs = nothing


    ' update sender
    strsql = "select * from tblSender where 0 = 1"
    set rs = server.CreateObject("ADODB.Recordset")
    rs.open strsql, conn, 1, 2
    rs.addNew
    rs("ConsignmentID") = Session("userid") & tmpFld
    rs("name") = Request.Form("senderName")
    rs("business") = Request.Form("senderBusiness")
    rs("address") = Request.Form("senderAddress")
    rs("workNo") = Request.Form("senderWorkNo")
    rs("homeNo") = Request.Form("senderHomeNo")
    rs("mobileNo") = Request.Form("senderMobileNo")
    rs("email") = Request.Form("senderEmail")
    rs.update
    set rs = nothing

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting into two tables

    Hey Mark,

    Your method looks just fine. Before becoming comfortable with SQL Inserts and Updates I used the ADO objects for this (as you have below). One thing you will discover is that passing SQL straight to the database is usually quicker, especially in situations where a code loop is involved. However, in this case the difference would be minimal.

    I'm glad to hear it's working! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  5. #5
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting into two tables

    hehe, right now, I'm under so much pressure, if it works, then I'll be over the moon. If you've got time, I've posted another question, it would be appreciated if u could look into it, thanks Mark.

Posting Permissions

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