Last Insert ID problem

[Shane930]

Hi I need some help with MySQL and the LAST_INSERT_ID() function. How can I get this to not error in DW.
I want to use the LAST_INSERT_ID() function at the end of the insert statement values like the reference says but I continue to get this error:
Error Executing Database Query.
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; SELECT LAST_INSERT_ID()' at line 57

I have it at the end of the Insert statements as follows:

<cfquery datasource="saa">
INSERT INTO saa.vehicle_ad (mm_username, make, model, doors, drive_type, transmission, mileage, mpg, fuel, price, options, add_info, vehicle_type, VIN) VALUES (
<cfif IsDefined("FORM.mm_username") AND #FORM.mm_username# NEQ "">
'#FORM.mm_username#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.make") AND #FORM.make# NEQ "">
'#FORM.make#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.model") AND #FORM.model# NEQ "">
'#FORM.model#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.doors") AND #FORM.doors# NEQ "">
'#FORM.doors#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.drive_type") AND #FORM.drive_type# NEQ "">
'#FORM.drive_type#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.transmission") AND #FORM.transmission# NEQ "">
'#FORM.transmission#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.mileage") AND #FORM.mileage# NEQ "">
#FORM.mileage#
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.mpg") AND #FORM.mpg# NEQ "">
#FORM.mpg#
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.fuel") AND #FORM.fuel# NEQ "">
'#FORM.fuel#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.price") AND #FORM.price# NEQ "">
#FORM.price#
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.options") AND #FORM.options# NEQ "">
'#FORM.options#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.add_info") AND #FORM.add_info# NEQ "">
'#FORM.add_info#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.vehicle_type") AND #FORM.vehicle_type# NEQ "">
'#FORM.vehicle_type#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.vin") AND #FORM.vin# NEQ "">
'#FORM.vin#'
<cfelse>
NULL
</cfif>
);
SELECT LAST_INSERT_ID();
</cfquery>

But it will not run. Also, how does this output to the next form to be used. Is it a SESSION variable or ??

Any help would be great. Thanks, Shane

[cf_dev2]

Did you first enable execution of multiple queries?
http://www.sumoc.com/blog/index.cfm?mode=entry&entry=7F6F7314-5004-2066-B7A8BDFB26843712

Then give the value returned by the function a name

<cfquery name="insertNewRecord" datasource="saa">
INSERT INTO saa.vehicle_ad (

... more code

);
SELECT LAST_INSERT_ID() AS TheNewID;
</cfquery>

<cfoutput>
insertNewRecord.TheNewID value is #insertNewRecord.TheNewID#
</cfoutput>

[Sankalan]

If you don't have allow multiple queries enabled, then:
The following code should work.

<cfoutput>
<cfquery name="myQuery1" datasource="yourDataSourceName">
INSERT INTO tableName ....
#### then your conditions here ####
</cfquery>
<cfquery name="myQuery2" datasource="yourDataSourceName">
SELECT LAST_INSERT_ID() AS myID;
</cfquery>
#myQuery2.myID#
</cfoutput>

Hope this will work.

Thanks

Sankalan
(www.mindfiresolutions.com)

[Shane930 · Accepted solution]

Actually both of you had information that worked for me so thank you very much for the help. Shane