Insert to multiple tables

Results 1 to 2 of 2

Thread: Insert to multiple tables

  1. #1
    Join Date
    Dec 1969

    Default Insert to multiple tables

    Is it possible to insert fields from a form to multiple tables rather than having to split the form up in to several pages and do it step by step?<BR><BR>I have a relationship between 2 tables. The first table being the job and the second being the products needed for the job (one to many relationship). I want to be able to add the single job with the products needed in one form to their respective tables. Can this be done?<BR><BR>Can anyone give me an example?

  2. #2
    Join Date
    Dec 1969

    Default RE: Insert to multiple tables

    Just do one insert after another:<BR><BR>oConn.BeginTrans<BR><BR>sSQL = "INSERT INTO TableA (FieldA, FieldB) VALUES (" & Request.Form("FieldA") & ", " & Request.Form("FieldB") & ")"<BR>oConn.Execute sSQL<BR><BR>sSQL = "INSERT INTO TableB (FieldB, FieldC, FieldD) VALUES (" & Request.Form("FieldB") & ", &#039;" & Request.Form("FieldC") & "&#039;, &#039;" & Request.Form("FieldD") & "&#039;"<BR>oConn.Execute sSQL<BR><BR>If Err.Number &#060;&#062; 0 Then<BR> oConn.RollbackTrans<BR>Else<BR> oConn.CommitTrans<BR>End If<BR><BR>You will want to use a transaction so that if one insert fails, the other does also. You can get the ID of the record from the first insert and use that in your second (FAQ on this).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts