Add Records to Multiple Tables with one ASP Page ?
This program will demonstrate how to use one HTML form to collect
information and insert into multiple tables with one ASP page.
Here is the code :-
First create the Input form : Name the form as inputform.htm
---note add the this tag < in front of every line in the code below. this will recreate the form as it should be---
----page 1 starts here-----
html>
head>
title>Input Page</title>
body>
form method="post" action="processform.asp" name="form1">
table BORDER="0" CELLSPACING="0" CELLPADDING="0" WIDTH="390" height="63">
tr>
td height="24"><big><font face="Verdana"><big><strong>Post A Match</strong></big></font></big></td>
/tr>
/table>
div align="center"><center>
/center></div><blockquote>
div align="left"><table>
tr>
td ALIGN="right"><strong>User name:</strong></td>
td><input TYPE="TEXT" NAME="YourName" SIZE="25"> </td>
/tr>
tr>
td ALIGN="right"><strong>Opponent's name:</strong></td>
td><input TYPE="TEXT" NAME="OpponentName" SIZE="25"> </td>
/tr>
tr>
td ALIGN="right"><strong>Games Scores Results:</strong></td>
td>Your Score<strong> <select name="YourScore" size="1">
option value="12">12</option>
option value="13">13</option>
option value="14">14</option>
option value="15">15</option>
/select> </strong>Opp's Score<strong> <select name="OpponentScore" size="1">
option value="9">9</option>
option value="10">10</option>
option value="11">11</option>
/select></strong></td>
/tr>
/table>
input type="Submit" value="Submit Results" name="btnSubmit">
/form>
/body>
/html>
----page 1 ends here--------
Page 2--The Process Form : Name the form as processform.asp
//remember to put your asp tags I would have done so but the forum doesn't support it
---page 2 starts here------
---opening asp tag goes here ---
'Declare all local variables
dim conn
dim strconn
dim Gamers
dim strsql
dim strID
' You can change the connection string according to your needs.
'Sets the DSN-less connection string to a local variable
strconn = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("MultiTable.mdb")
'Opens a connection object
set conn = server.createobject("adodb.connection")
conn.open strconn
'Records when the game was played table
set GameRS = Server.Createobject("adodb.recordset")
GameRS.open "tblWhenPlayed", conn, 3, 3
GameRS.addnew
GameRS("WinnersName") = request.form("YourName")
GameRS("LosersName") = request.form("OpponentName")
GameRS("DatePlayed") = Date()
GameRS.update
GameRS.movelast
'Get the Game ID that is created when the record was created
strID = GameRS("GameID")
GameRS.Close
set GameRS = nothing
'Records Winner's into the Losers table
strSQL = "INSERT INTO tblWinners(GameID, ZoneName, NumOfPoints)"
strSQL = strSQL & " SELECT "
strSQL = strSQL & strID & " as text1,"
strSQL = strSQL & "'" & request("YourName") & "' as text2,"
strSQL = strSQL & request("YourScore") & " as text3"
conn.execute(strsql)
'Records Losers Info into the Losers table
strSQL = "INSERT INTO tblLosers(GameID, ZoneName, NumOfPoints)"
strSQL = strSQL & " SELECT "
strSQL = strSQL & strID & " as text1,"
strSQL = strSQL & "'" & request("OpponentName") & "' as text2,"
strSQL = strSQL & request("OpponentScore") & " as text3"
conn.execute(strsql)
conn.close
set conn = nothing
--closing asp tag goes here----
html>
head>
/head>
body>
Your Record has been recorded</h1>
Here is your Unique ID:---opening asp tag--- = strID ---closing asp tag-----
This demo has just inserted data into Three different tables
body>
/html>
---end of page 2------
let me know how it goes---