BellaOnline Forums Computers ASP Update two tables in SQL with one click

Can anyone help me?

I want to update two tables in SQL with only one SUBMIT, I only need to update one field in each one - the fields are identical in both tables

I'm using Dreamweaver MX 2004 for editing my ASP's and the tables reside on SQL.

Thanks <img src="/images/graemlins/smile.gif" alt="" />

I don't think you can update 2 tables at once. Did you mean 2 records in the same table? If so, the SQL statement could look something like:

UPDATE mytable
SET thisfield = 'new data'
WHERE unique_id IN (5, 20)

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

Thanks asp_angel! - I'm playing with it right now.

I'll keep you all posted.

<img src="/images/graemlins/smile.gif" alt="" />

-vagre

I used the sample with a few mods and it worked - now I'm having issues with the GROUP BY but that is a different forum topic.

Thanks again <img src="/images/graemlins/wink.gif" alt="" />

-vagre

Right, sorry to come in late, but you always update one and only one table at a time. Otherwise the issues would be nightmarish, of what could happen with integrity and such.