HOw to add, update, delete record from 2 tables?

[tekboy]

HOw to add, update, edit, delete record from 2 tables?

[buntine]

You should perform a "join", demonstrated below:

SELECT t1.*, t2.* FROM TableOne t1, TableTwo t2;

As for data modification, the method will vary amongst databases. Personally, I would just execute a seperate query for each table.

Regards.

[Ubik]

DELETE from CHILDTABLE where PARENTID=[your record]
EXCECUTE
DELETE from PARENTTABLE where ID=[your record]

[Bullschmidt]

And here's a snippet of code from a page (invdetail.asp from my sample DB on the Web) that does that. (The transaction and record tracking parts are not actually needed and there are some custom functions used that begin with jpsvb...):

' Begin transaction as updating multiple tables.
objConn.BeginTrans

' Open rs to add.
If InvDetailIDOrig = 0 Then
strSQL = "SELECT * "
strSQL = strSQL & "FROM tblInvDetail "
strSQL = strSQL & "WHERE (1<>1)"

' Open rs.
Set objRS = Server.CreateObject("ADODB.Recordset")
' (1=CursorType of adOpenKeyset in case ever want to get an autonumber of new rec,
' 3=LockType of adLockOptimistic because updating.)
objRS.Open strSQL, objConn, 1, 3

' Add new.
objRS.AddNew
Else ' Open rs to edit.
strSQL = "SELECT * "
strSQL = strSQL & "FROM tblInvDetail "
' No quote for numeric fld.
strSQL = strSQL & "WHERE (InvDetailID=" & jpsvbFixSQL(InvDetailID) & ")"

' Open rs.
Set objRS = Server.CreateObject("ADODB.Recordset")
' (3=LockType of adLockOptimistic because updating.)
objRS.Open strSQL, objConn, , 3

' Move to 1st (and probably only) rec.
objRS.MoveFirst
End If

' Set var.
objRS("InvDetailInvID") = jpsvbBlankToNull(InvDetailInvID)
objRS("InvDetailProdID") = jpsvbBlankToNull(InvDetailProdID)
objRS("InvDetailProdDescr") = jpsvbBlankToNull(InvDetailProdDescr)
objRS("InvDetailProdPrice") = jpsvbBlankToNull(InvDetailProdPrice)
objRS("InvDetailQty") = jpsvbBlankToNull(InvDetailQty)
objRS("InvDetailTotal") = jpsvbBlankToNull(InvDetailTotal)

' Set rec tracking var.
Call SetRecTrack(objRS, "InvDetail")

' Update.
objRS.Update

' Get autonumber.
InvDetailID = objRS("InvDetailID")

' Close rs.
objRS.Close
Set objRS = Nothing

' - - - - -

' Update parent table totals.
' (Similar code is in DelRec().)

' Calc InvSubtotal.
' Sum.
strSQL = "SELECT Sum(InvDetailTotal) AS FldSum "
strSQL = strSQL & "FROM tblInvDetail "
' No quote for numeric fld.
strSQL = strSQL & "WHERE (InvDetailInvID=" & jpsvbFixSQL(InvDetailInvID) & ")"
InvSubtotal = jpsvbDLookup(objConn, objRS, strSQL, 0, 0)

' Set sql.
strSQL = "SELECT * "
strSQL = strSQL & "FROM tblInv "
' No quote for numeric fld.
strSQL = strSQL & "WHERE (InvID=" & jpsvbFixSQL(InvDetailInvID) & ")"

' Open rs.
Set objRS = Server.CreateObject("ADODB.Recordset")
' (3=LockType of adLockOptimistic because updating.)
objRS.Open strSQL, objConn, , 3

' Move to 1st (and probably only) rec.
objRS.MoveFirst

' Set InvSubtotal.
objRS("InvSubtotal") = jpsvbBlankToNull(InvSubtotal)

' Calc & set InvTotal.
InvSalesTax = objRS("InvSalesTax")
InvShipping = objRS("InvShipping")
objRS("InvTotal") = jpsvbBlankToNull(InvSubTotal + InvSalesTax + InvShipping)

' Set rec tracking var.
Call SetRecTrack(objRS, "Inv")

' Update.
objRS.Update

' Close rs.
objRS.Close
Set objRS = Nothing

' End transaction.
objConn.CommitTrans

[tekboy]

great thanks :D