asp-insert into multiple tables question

[pampolk]

I have an asp form that will insert form values into two tables. In the db this is a master/detail relationship(meetings - > meeting_details) and I am going to build the form in asp the same way.
--
--
meetings(master table)-> meeting_id, meeting_date, quorum, visitors, notes

meeting_details(detail table) -> meeting_details_id, meeting_id, topic_catagory_id, topic_id

join is on meeting_id
--
--

Would I have to use two insert statements to insert the form values into meetings AND meeting details? In other words would I insert first into meetings, then into meeting_details? If so, since in meetings meeting_id is an autonum field, would I after the first insert statement grab that meeting_id to use in the second insert statement?

Thanks in advance.

Pam Polk

 

[Chopstik]

Presuming that you are using SQL Server, you would have to do two insert statements (actually, any db you would have to use two insert statements). You would first insert into the master Meetings table, then use @@Identity to retrieve the value for the meeting_id so that you can then perform the insert for the MeetingsDetail table.

Hope this helps.

-----------------------------------------------------------------------------------------------------
"If you can't explain something to a six-year-old, you really don't understand it yourself."
-- Albert Einstein

 

[pampolk]

Thank you for your help. I thought that was what I should do. I am using ASP with MS Access 2000.

How would I work the syntax for the @@Identity?

--
--
Below is my connection information:

DIM bSubmit, theserver, errorID, errorText

theServer = Request.ServerVariables("SERVER_NAME&quot
errorID = request("errorID&quot

'//check to see if we are being submitted
bSubmit = ucase(trim(Request("bSubmit&quot))

DIM oCon, objRs, strSql, Username, Password, userID

Username = Request("Username&quot
userID = Request("userID&quot

'//set up connection to db/**CHANGE PATH WHEN IN PRODUCTION**

SET oCon = Server.CreateObject("ADODB.Connection&quot

'oCon.Open "DSN=sc_asp","ui=","pwd="
oCon.Open "DBQ=" & Server.MapPath("../database/sc-asp.mdb&quot & ";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;MaxBufferSize=8192;Threads=20;", "username", "password"

SET objRs = Server.CreateObject("ADODB.Recordset&quot

--
--



--
--
Below is the first insert statement:

strSql = "INSERT INTO meetings (school_id, meeting_date, visitor_info, visitors, quorum)"
strSql = strSql & " VALUES ('" & request.form("school_id&quot & "','" & request.form("meeting_date&quot & "','" & request.form("visitor_info&quot & "','" & f1 & "','" & f2 & "')"
--
--

I am having trouble with syntax for the @@identity stmt:


dim rsGetValue

strSQL="Select @@Identity from meetings"
rsGetValue.Open strSQL

I need to get the value, display it to the screen, then comment that out(the display) then use the variable that contains the id in the next insert statement.

Any suggestions on syntax? The above does not work.

Pam Polk

 

[Chopstik]

I do not use Access myself, but found this via a Google search, so you might be able to get an idea from it. Hope this helps.

http://www.drdev.net/article06.htm

-----------------------------------------------------------------------------------------------------
"If you can't explain something to a six-year-old, you really don't understand it yourself."
-- Albert Einstein

 

[alsoknownas]

it would probably be better if you let the database do the copying for you. write a functions that triggers when a new records is entered and copy that record to a new table

--------------------------------------------------------------------------------
​