thanks for the replies.  I'll try both suggestions.  

i have two tables that are related by userID number.  the primary table 
has records of users.  the related table has none yet.  I want to be able 
to insert data into the related table by referencing the userID.  Can 
anyone help? 

it goes something like this: 


sex=request.form("sex")
age=request.form("age")
ethnicity=request.form("ethnicity")

query="UPDATE TABLE1 SET sex = '" & sex & "', age = '" & age & "', 
ethnicity = '" & ethnicity & "' WHERE TABLE2.userID=TABLE1.userID AND 
TABLE2.username='" & username & "'"  


Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & 
Server.MapPath("/db.mdb")
objConn.Execute(query)

	
thanks
Mo

Are you sure the SQL command is correct...I think it should be ..

Query =3D "UPDATE TABLE1 SET sex =3D '" & sex & "', age =3D '" & age & 
"',
ethnicity =3D '" & ethnicity & _
        "' WHERE userID =3D (SELECT userID from TABLE2 WHERE username 
=3D '"
& username & "');"

This assumes you do not have duplicate usernames in TABLE2

-----Original Message-----
From: Vaishnavi [mailto:mail2vaish@y...]
Sent: Thursday, July 18, 2002 2:33 AM
To: ASP Databases
Subject: [asp_databases] Re: inserting and/or updating related tables


Hello,

Is TABLE2 the primary table and TABLE1 the secondary table?
Am I right in assuming that u need to insert the details obtained thru
request.form in TABLE1. For this u also need the key-userid
And once u have the key u do not have to refer to the primary table
TABLE2. For checking whether the user id alsready exists in TABLE1, u
canhave a
select count(*) from TABLE1 where userid =3Dspecificuserid
If this returns 0, then insert in TABLE1 else update TABLE1 Hope this
helps,

Vaishnavi


> i have two tables that are related by userID number.  the primary
> table
h> as records of users.  the related table has none yet.  I want to be
able
t> o insert data into the related table by referencing the userID.  Can
a> nyone help?

> it goes something like this:

>
s> ex=3Drequest.form("sex")
a> ge=3Drequest.form("age")
e> thnicity=3Drequest.form("ethnicity")

> query=3D"UPDATE TABLE1 SET sex =3D '" & sex & "', age =3D '" & age & 
"',
e> thnicity =3D '" & ethnicity & "' WHERE TABLE2.userID=3DTABLE1.userID 
AND
T> ABLE2.username=3D'" & username & "'" 

>
S> et objConn =3D Server.CreateObject("ADODB.Connection")
o> bjConn.Open "Provider=3DMicrosoft.Jet.OLEDB.4.0;Data Source=3D" &
S> erver.MapPath("/db.mdb")
o> bjConn.Execute(query)

> =09
t> hanks
M

Hello,

Is TABLE2 the primary table and TABLE1 the secondary table?
Am I right in assuming that u need to insert the details obtained thru 
request.form in TABLE1. For this u also need the key-userid
And once u have the key u do not have to refer to the primary table TABLE2.
For checking whether the user id alsready exists in TABLE1, u canhave a 
select count(*) from TABLE1 where userid =specificuserid
If this returns 0, then insert in TABLE1 else update TABLE1
Hope this helps,

Vaishnavi
 

> i have two tables that are related by userID number.  the primary table 
h> as records of users.  the related table has none yet.  I want to be 
able 
t> o insert data into the related table by referencing the userID.  Can 
a> nyone help? 

> it goes something like this: 

> 
s> ex=request.form("sex")
a> ge=request.form("age")
e> thnicity=request.form("ethnicity")

> query="UPDATE TABLE1 SET sex = '" & sex & "', age = '" & age & "', 
e> thnicity = '" & ethnicity & "' WHERE TABLE2.userID=TABLE1.userID AND 
T> ABLE2.username='" & username & "'"  

> 
S> et objConn = Server.CreateObject("ADODB.Connection")
o> bjConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & 
S> erver.MapPath("/db.mdb")
o> bjConn.Execute(query)

> 	
t> hanks
M