I have to update 2 tables who are related ...

can I update these table with a sql select string with INNER JOIN as

follows?

	rem make ADO recordset

	Set rs = Server.CreateObject("ADODB.Recordset")

	if action= "UPDATE" then

	sqlupdate= "select

BcardsID,BcName,BCTitle,BcDep,Bcardsid,Bctel1,Bctel2,Bcemail1,Bcemail2,bcard

s.Coyid,BcFax,addresses.addressid,Address, addressnr,zip,city, country from

BCARDS INNER JOIN ADDRESSES on bcards.addressid=addresses.addressid where

Bcardsid="&Bcardsid

	response.write sqlupdate

	rs.open sqlupdate,conn,adLockOptimistic,adOpenDynamic,adCmdText

	rs("BcName")=Request.form("BcName")

	rs("BcTitle")=Request.form("BcTitle")

	rs("Bcdep")=Request.form("Bcdep")

	rs("Bctel1")=Request.form("tel")

	rs("Bcfax")=Request.form("fax")

	rs("Bcemail1")=Request.form("email")

	rs("BcardsID")=Request.form("BcardsID")

	rs("Address")=Request.form("Address")

	rs("AddressNr")=Request.form("AddressNr")

	rs("Zip")=Request.form("Zip")

	rs("City")=Request.form("City")

	rs("Country")=Request.form("Country")

	rs("tel")=Request.form("tel")

	rs("fax")=Request.form("fax")

	rs("email")=Request.form("email")

	rs.Update

	rs.Close

	set rs = nothing



if i do I get the error i earlier submitted..Microsoft OLE DB Provider for

ODBC Drivers error '80040e21'

please could you give me a fast and reliable method for updating both tables

at one time?

txs

This is a multi-part message in MIME format.



------=_NextPart_000_0280_01C053C4.E52CFC80

Content-Type: text/plain;

	charset="iso-8859-1"

Content-Transfer-Encoding: quoted-printable





hi



UPDATE t

    SET t.ytd_sales =3D t.ytd_sales + s.qty

        FROM titles t, sales s

           WHERE t.title_id =3D s.title_id

           AND s.ord_date =3D (SELECT MAX(sales.ord_date) FROM sales)





u can always update a table using joins

i prefer this way instead of inner join



jigs





  ----- Original Message -----

  From: Eric Van Camp

  To: ASP Databases

  Sent: Tuesday, November 21, 2000 8:47 PM

  Subject: [asp_databases] updating 2 tables;Microsoft OLE DB Provider 

for ODBC Drivers error '80040e21'





  I have to update 2 tables who are related ...

  can I update these table with a sql select string with INNER JOIN as

  follows?

  rem make ADO recordset

  Set rs =3D Server.CreateObject("ADODB.Recordset")

  if action=3D "UPDATE" then

  sqlupdate=3D "select

  

BcardsID,BcName,BCTitle,BcDep,Bcardsid,Bctel1,Bctel2,Bcemail1,Bcemail2,bc

ard

  s.Coyid,BcFax,addresses.addressid,Address, addressnr,zip,city, country 

from

  BCARDS INNER JOIN ADDRESSES on bcards.addressid=3Daddresses.addressid 

where

  Bcardsid=3D"&Bcardsid

  response.write sqlupdate

  rs.open sqlupdate,conn,adLockOptimistic,adOpenDynamic,adCmdText

  rs("BcName")=3DRequest.form("BcName")

  rs("BcTitle")=3DRequest.form("BcTitle")

  rs("Bcdep")=3DRequest.form("Bcdep")

  rs("Bctel1")=3DRequest.form("tel")

  rs("Bcfax")=3DRequest.form("fax")

  rs("Bcemail1")=3DRequest.form("email")

  rs("BcardsID")=3DRequest.form("BcardsID")

  rs("Address")=3DRequest.form("Address")

  rs("AddressNr")=3DRequest.form("AddressNr")

  rs("Zip")=3DRequest.form("Zip")

  rs("City")=3DRequest.form("City")

  rs("Country")=3DRequest.form("Country")

  rs("tel")=3DRequest.form("tel")

  rs("fax")=3DRequest.form("fax")

  rs("email")=3DRequest.form("email")

  rs.Update

  rs.Close

  set rs =3D nothing



  if i do I get the error i earlier submitted..Microsoft OLE DB Provider 

for

  ODBC Drivers error '80040e21'

  please could you give me a fast and reliable method for updating both 

tables

  at one time?

  txs





  ---

  FREE SOFTWARE DEVELOPMENT CODE, CONTENT, AND

  INSIGHTS IN YOUR INBOX!

  Get the latest and best C++, Visual C++, Java, Visual Basic, and XML 

tips, tools, and

  developments from the experts.  Sign up for one or more of EarthWeb?s

  FREE IT newsletters at http://www.earthweb.com today! 




$subst('Email.Unsub')