LAST_INSERT_ID() in START TRANSACTION

[Kevin Wong]

Hi all,

I have a transaction that runs a 3 insert statements.

The first inserts an record into the "patientsmain" table, generating a patient ID key (PATNR).

The second inserts into "patientdetail" table, this has a foreign key (also called PATNR), which I populate using LAST_INSERT_ID().

The 3rd insert goes into "patientpanel" table, it is similar to the 2nd INSERT. This table also has a PATNR foreign key but using LAST_INSERT_ID() here fails as it takes the ID generated by 2nd INSERT rather than the 1st INSERT.

How can I have the 3rd insert (or subsequent inserts) use the ID generated by my first insert?

If it helps, I am using VB.net, and my sql is something like this:

START TRANSACTION;

INSERT INTO patientmain(BEGDA, ENDDA, UPDAT, VALID)
VALUES(?BEGDA, ?ENDDA, ?UPDAT, ?VALID);

INSERT INTO patientdetail(PATNR, FLLNM, SURNM, GENKY)
VALUES(LAST_INSERT_ID(), ?FLLNM, ?SURNM, ?GENKY);

INSERT INTO patientpanel(PATNR, PNLID, BEGDA, ENDDA, UPDAT )
VALUES(LAST_INSERT_ID, 99, ?BEGDA, ?ENDDA, ?UPDAT );

COMMIT;