Demo on how to insert records into Multiple tables with one html form and one asp form.

Contributed by aspfree
Rating:

/ 29
November 15, 1999

Rate this Article:
MEH MEH++

SEARCH ASP FREE

<table BORDER="0" CELLSPACING="0" CELLPADDING="0" WIDTH="390" height="63"> 
<tr> 
<td height="24"><big><big>Post A Match</big></big></td> 
</tr> 
</table> 
<div align="center"><center>  
</center></div><blockquote> 
<div align="left"><table> 
<tr> 
<td ALIGN="right">User name:</td> 
<td><input TYPE="TEXT" NAME="YourName" SIZE="25"> </td> 
</tr> 
<tr> 
<td ALIGN="right">Opponent's name:</td> 
<td><input TYPE="TEXT" NAME="OpponentName" SIZE="25"> </td> 
</tr> 
<tr> 
<td ALIGN="right">Games Scores  Results:</td> 
<td>Your Score <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>  Opp's Score <select name="OpponentScore" size="1"> 
<option value="9">9</option> 
<option value="10">10</option> 
<option value="11">11</option> 
</select></td> 
</tr> 
</table> 
<input type="Submit" value="Submit Results" name="btnSubmit"> 
</form> 
</body> 
</html>
<h3>Page 2--The Process Form</h3>
<% @Language="VbScript" %> 
<% 
 
'Declare all local variables 
dim conn 
dim strconn 
dim Gamers 
dim strsql 
dim strID 
 
'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 
 
%> 

<html> 
<head> 
<title>Post Score Summary</title> 
</head> 
<body> 
<h1>Your Record has been recorded</h1> 
<h3>Here is your Unique ID:<% = strID %></h3> 
This demo has just inserted data into Three different tables 
</body> 
</html>