Save data to 2 tables simultaneously

[Gary Williams]

Hi All,

Apoligies if the solution should be obvious to me, I suspect that this is a very common task in relational databases, but my mind has gone blank on this problem.(I suspect I'm getting past it :( ).

I have created a simple ASP/MySQL workflow application. When a new job is entered via a form, one of two departments is selected by the user on the form. The user then answers the questions relevant to the chosen department. Although the jobs when carried out by each department are similar, they each have specific (but different) questions to be answered. To do this elegantly (?), I created three tables:

1. Job Sheet

This contains all the common details, ie, job/record id, username, etc.

2. Department 1

This contains all the answers to the questions specific to Department 1.

3. Department 2

This contains all the answers to the questions specific to Department 2.

The first field in 'Job Sheet' is an autogenerated job/record id. In tables 'Department 1' and 'Department 2', I have a field to store the record id generated in 'Job Sheet' to link 'Job Sheet' to either 'Department 1' or 'Department 2' as necessary for each job requested. That is, the data for any single job is stored across two tables.

Here's the thing. When a user completes the form for a new job and chooses, say, Department1 and saves the data, 'Job Sheet' will autogenerate a new job/record id plus save the data to be stored in 'Job Sheet'. Now, how do I get this new job/record id value stored in table 'Department 1' to link the data so I have a reliable record?

Regards

Gary

[Whatever Jr.]

There's no ASP function like PHP's mysql_insert_id().
Maybe something like this:

NewID = conn.execute("SELECT MAX(id) FROM Job Sheet")

And then continue with inserting data into table Department 1' or 'Department 2'.


It's not the most reliable solution (in case of multiple/simultanuous inserts), but it'll work.

HTH, Tom

[Spudhead]

how do I get this new job/record id value

What database are you using?

If MySQL, you can do SELECT LAST_INSERT_ID() (http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id)

If MSSQL or Access, try SELECT @@IDENTITY (http://msdn2.microsoft.com/en-us/library/ms187342.aspx)

This is quite a common question - google "get last record ID" (http://www.google.co.uk/search?q=get+last+record+ID) and you'll see a whole load of similar discussions :)

HTH

[Gary Williams]

Hi Guys,

Thanks for the code. I see how to get that working. Re: your comment "It's not the most reliable solution (in case of multiple/simultanuous inserts),". I wondered about that.

Do you think I am trying to be too clever in splitting up the job records across 3 tables? I'm thinking now I should just combine the 3 tables into 1 larger table to prevent a "simultanuous inserts" issue.

Regards

Gary

[Whatever Jr.]

Hi,

No, don't mess with your database layout.

Try Spudhead's suggestion:
Use SELECT LAST_INSERT_ID().

I've never used MySQL in combination with ASP, so I didn't that function existed.


HTH, Tom

[Gary Williams]

Hi Whatever,

Yes, the last insert code works fine but how is the situation handled when two users hit the submit buttons on their forms virtually simultanuously?

How do you keep one submit 'on hold' until the other transaction is completed so we don't store the wrong recordid's? Never had to worry about this issue before.

Regards

Gary

[Whatever Jr.]

Hi Gary, if you use LAST_INSERT_ID() you don't have to worry about it. I think it's per session.

HTH, Tom

[Gary Williams]

Hi Whatever,

If I understand this, then the first one to hit 'submit' monopolises the server until his script and associated SQL commands completes, yes?

Definately time for me to read up on server sessions.

Thanks for the help.

Regards

Gary