Problems with my db and code

G

g_r_a_robinson

Regular
Joined
Jan 13, 2004
Messages
71
This is what I'm doing and It makes sense why it doing this but I'm not sure of a solution.

Let me explain.

I have two processes.

I add a job to my db, the job contains a title, date, time etc.

When the user submits it they are returned a job number from the db. They then need to add a note that goes along with the job. This job is stored in a job table

A job can have many notes but they must add at least one, thats the default behaviour. When having submitted the note the details are stored in a note table, its foreign key is the jobid that was returned from the first insert.

IE I can access all the notes by its jobid.

The problem occurs when two people do this

User A : Adds a job

User B : Adds a job

I think user a's jobid is being written over by user b. Therefore when (user a) add a note they are actually submitting the note to the jobid of user b.

Now thats my hunch. And it certainly explains the behaviour. Problem is that I can't really afford to change the structure of the entire thing right now. What would really help is if I was able to prevent (user a's) job number from being over written. I'm using static vars.

Any suggestions would be most helpful!!
 
You could add each one into a transaction, so that its not physically submitted to the database until a note is added.

Or you could change the structure of your interface, so that a user is required to enter the first note at the time they create the new job. Then you kill two birds with one stone and only require one round trip to the database too - more efficient!
 
I would have liked to have combined the processes but by virtue of the user requirements I can't. IE the people using the system will be required to give people a jobID immediately mainly while on the phone. They can get this by simply adding a new job. Completing the note takes some time. Therefore to do it in one would require the note to be completed as well before being returned the job id. Which wouldn't be suitable from a user perspective.
 
What you're trying to do is definetly doable. I think that maybe you need to reexamine your code. Maybe step through the actual process...
 
Back
Top