Insert Multiple Records into a table with one form

[Rhyno]

Hi there

I'd like to have a single form insert multiple records/rows to a table using
a single form.

The form is used for members to refer their friends. The member enters their
Username and then the email addresses of up to 3 friends.

The first page (form1.asp) includes the following form:

<form method="POST" action="form2.asp">
<p>Username
<input type="text" name="username" size="20"></p>
<p>1st Mate's Email
<input type="text" name="matesemail1" size="20"></p>
<p>2nd Mate's Email
<input type="text" name="matesemail2" size="20"></p>
<p>3rd Mate's Email
<input type="text" name="matesemail3" size="20"></p>
<p><input type="submit" value="Submit" name="B1"></p>
</form>

This form posts to the second page (form2.asp), which inserts to the Access
DB using an ASP script.

The table which holds the results has two fields: 'Username' and
'MatesEmail'. As you'll see, the ASP code below would insert one record to
the table. I'd like to modify the code to insert into three records, each
with the Username and one of the 3 email addresses.

<%
Dim name, email
Dim sConnString, connection, sSQL
sSQL = "INSERT INTO results (Username, MatesEmail) values ('" &
request.form("username") & "', '" & request.form("matesemail") & "')"
sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("\fpdb\test1.mdb")
Set connection = Server.CreateObject("ADODB.Connection")
connection.Open(sConnString)
connection.execute(sSQL)
response.write "The form information was inserted successfully."
connection.Close
Set connection = Nothing
%>

I/m not great with coding, so if anyone has the time to actually modify the
code for me it'd be greatly appreciated.

Thanks in advance!

Ryan

 

[Mike Mueller]

It has been long since I have hand coded SQL... but I think you will
actually need 3 SQL statements, 1 per insert operation


Dim name, email

Dim sConnString, connection

sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("\fpdb\test1.mdb")
Set connection = Server.CreateObject("ADODB.Connection")

Dim sSQL1, sSQL2, sSQL3

sSQL1 = "INSERT INTO results (Username, MatesEmail) values ('" &
request.form("username") & "', '" & request.form("matesemail1") & "')"
sSQL2 = "INSERT INTO results (Username, MatesEmail) values ('" &
request.form("username") & "', '" & request.form("matesemail2") & "')"
sSQL3 = "INSERT INTO results (Username, MatesEmail) values ('" &
request.form("username") & "', '" & request.form("matesemail3") & "')"

connection.Open(sConnString)

connection.execute(sSQL1)
connection.execute(sSQL2)
connection.execute(sSQL3)

response.write "The form information was inserted successfully."
connection.Close
Set connection = Nothing

*********************************************

 

[Rhyno]

Hi Mike

Thanks so much for your help...

When I used the script as it was written, the form1 page lagged before
failing. So I tweaked a couple of things..

1. Removed DIM name, email' (this was my errror - the values are declared
later in the INSERT into statement).
2. Declared sSQL1, sSQL2, sSQL3 as part of the first DIM statement
3. Added an underscore to the INSERT into statments (where the code is
carried to the next line)

So the udpated code is:

<%
Dim sConnString, connection, sSQL1, sSQL2, sSQL3

sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("\fpdb\test1.mdb")
Set connection = Server.CreateObject("ADODB.Connection")

sSQL1 = "INSERT INTO results (Username, MatesEmail) values ('" & _
request.form("Username") & "', '" & request.form("MatesEmail1") & "')"
sSQL2 = "INSERT INTO results (Username, MatesEmail) values ('" & _
request.form("Username") & "', '" & request.form("MatesEmail2") & "')"
sSQL3 = "INSERT INTO results (Username, MatesEmail) values ('" & _
request.form("Username") & "', '" & request.form("MatesEmail3") & "')"

connection.Open(sConnString)

connection.execute(sSQL1)
connection.execute(sSQL2)
connection.execute(sSQL3)

response.write "The form information was inserted successfully."
connection.Close
Set connection = Nothing
%>

Works perfectly!

Thanks again

Ryan
*****************************************

 

[Stefan B Rusynko]

You should probably tweak that to prevent errors for missing data

IF Len(request.form("Username"))>0 THEN
IF Len(request.form("MatesEmail1")>0 THEN connection.execute(sSQL1)
IF Len(request.form("MatesEmail2")>0 THEN connection.execute(sSQL2)
IF Len(request.form("MatesEmail3")>0 THEN connection.execute(sSQL3)
response.write "The form information was inserted successfully."
ELSE
response.write "The form was missing Data"
END IF
--

_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
_____________________________________________


| Hi Mike
|
| Thanks so much for your help...
|
| When I used the script as it was written, the form1 page lagged before
| failing. So I tweaked a couple of things..
|
| 1. Removed DIM name, email' (this was my errror - the values are declared
| later in the INSERT into statement).
| 2. Declared sSQL1, sSQL2, sSQL3 as part of the first DIM statement
| 3. Added an underscore to the INSERT into statments (where the code is
| carried to the next line)
|
| So the udpated code is:
|
| <%
| Dim sConnString, connection, sSQL1, sSQL2, sSQL3
|
| sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
| "Data Source=" & Server.MapPath("\fpdb\test1.mdb")
| Set connection = Server.CreateObject("ADODB.Connection")
|
| sSQL1 = "INSERT INTO results (Username, MatesEmail) values ('" & _
| request.form("Username") & "', '" & request.form("MatesEmail1") & "')"
| sSQL2 = "INSERT INTO results (Username, MatesEmail) values ('" & _
| request.form("Username") & "', '" & request.form("MatesEmail2") & "')"
| sSQL3 = "INSERT INTO results (Username, MatesEmail) values ('" & _
| request.form("Username") & "', '" & request.form("MatesEmail3") & "')"
|
| connection.Open(sConnString)
|
| connection.execute(sSQL1)
| connection.execute(sSQL2)
| connection.execute(sSQL3)
|
| response.write "The form information was inserted successfully."
| connection.Close
| Set connection = Nothing
| %>
|
| Works perfectly!
|
| Thanks again
|
| Ryan
| *****************************************
|
| "Mike Mueller" wrote:
|
| > It has been long since I have hand coded SQL... but I think you will
| > actually need 3 SQL statements, 1 per insert operation
| >
| >
| > Dim name, email
| >
| > Dim sConnString, connection
| >
| > sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
| > "Data Source=" & Server.MapPath("\fpdb\test1.mdb")
| > Set connection = Server.CreateObject("ADODB.Connection")
| >
| > Dim sSQL1, sSQL2, sSQL3
| >
| > sSQL1 = "INSERT INTO results (Username, MatesEmail) values ('" &
| > request.form("username") & "', '" & request.form("matesemail1") & "')"
| > sSQL2 = "INSERT INTO results (Username, MatesEmail) values ('" &
| > request.form("username") & "', '" & request.form("matesemail2") & "')"
| > sSQL3 = "INSERT INTO results (Username, MatesEmail) values ('" &
| > request.form("username") & "', '" & request.form("matesemail3") & "')"
| >
| > connection.Open(sConnString)
| >
| > connection.execute(sSQL1)
| > connection.execute(sSQL2)
| > connection.execute(sSQL3)
| >
| > response.write "The form information was inserted successfully."
| > connection.Close
| > Set connection = Nothing
| >
| > *********************************************
| >
| > | > > Hi there
| > >
| > > I'd like to have a single form insert multiple records/rows to a table
| > > using
| > > a single form.
| > >
| > > The form is used for members to refer their friends. The member enters
| > > their
| > > Username and then the email addresses of up to 3 friends.
| > >
| > > The first page (form1.asp) includes the following form:
| > >
| > > <form method="POST" action="form2.asp">
| > > <p>Username
| > > <input type="text" name="username" size="20"></p>
| > > <p>1st Mate's Email
| > > <input type="text" name="matesemail1" size="20"></p>
| > > <p>2nd Mate's Email
| > > <input type="text" name="matesemail2" size="20"></p>
| > > <p>3rd Mate's Email
| > > <input type="text" name="matesemail3" size="20"></p>
| > > <p><input type="submit" value="Submit" name="B1"></p>
| > > </form>
| > >
| > > This form posts to the second page (form2.asp), which inserts to the
| > > Access
| > > DB using an ASP script.
| > >
| > > The table which holds the results has two fields: 'Username' and
| > > 'MatesEmail'. As you'll see, the ASP code below would insert one record to
| > > the table. I'd like to modify the code to insert into three records, each
| > > with the Username and one of the 3 email addresses.
| > >
| > > <%
| > > Dim name, email
| > > Dim sConnString, connection, sSQL
| > > sSQL = "INSERT INTO results (Username, MatesEmail) values ('" &
| > > request.form("username") & "', '" & request.form("matesemail") & "')"
| > > sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
| > > "Data Source=" & Server.MapPath("\fpdb\test1.mdb")
| > > Set connection = Server.CreateObject("ADODB.Connection")
| > > connection.Open(sConnString)
| > > connection.execute(sSQL)
| > > response.write "The form information was inserted successfully."
| > > connection.Close
| > > Set connection = Nothing
| > > %>
| > >
| > > I/m not great with coding, so if anyone has the time to actually modify
| > > the
| > > code for me it'd be greatly appreciated.
| > >
| > > Thanks in advance!
| > >
| > > Ryan
| >