Inserting into two tables

Topic

New Reply

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

——————————-

 

Consignment ID	<input type="text" size="1" disabled name="x_ConsignmentID" value="”	<input type="text" name="x_ConsignmentID" value="” size=50 maxlength=50>
Destination	<% SQL = "Select distinct * FROM tbldestination ORDER BY Destination ASC" 'assumption 3 SET RS = Conn.Execute(SQL) While NOT RS.EOF response.write "” & RS(“Destination”) & “” RS.MoveNext WEND Set RS = NOTHING Response.Write “” 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 %>
Status
<INPUT TYPE="Radio" NAME="x_Status" VALUE=”0″ checked >
Origin	<input type="text" name="x_Origin" value="” disabled size=30 maxlength=50>  <input type="hidden" name="x_Origin" value="” size=30 maxlength=50>
Type	<% x_TypeList = "” x_TypeList = x_TypeList & “” & “” & “” x_TypeList = x_TypeList & “” & “Document” & “” x_TypeList = x_TypeList & “” & “Parcel” & “” x_TypeList = x_TypeList & “” response.write x_TypeList %>
Description
Remarks
Depart Date	<input type="hidden" name="x_DepartDate" size=25 value=" “>

Sender Details

Name:

Business:

Address:

Work No:

Home No:

Mobile No:

Email:

Receiver Details

Name:

Business:

Address:

Work No:

Home No:

Mobile No:

Email:

—————————–

Reply | Quote

Replies

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:

<%
SET cnn = Server.Create("ADODB.Connection")
cnn.Open("")

'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 
%>

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!

Reply | Quote

Thanks Mark, the approach I tried was this way, and it seems to work ok so far, so fingers crossed .

‘ 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

Reply | Quote

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!

Reply | Quote

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.

Reply | Quote