Missing semicolon problem

[TimMcGeary]

I am getting the error of [Macromedia][SequeLink JDBC Driver][ODBC
Socket][Microsoft][ODBC Microsoft Access Driver] Missing semicolon at end of
SQL statement on the LAST insert of the following code, but I don't know why.



<cfquery name="find_requestor_id" datasource="storage">
SELECT requestor_id AS uid FROM requestors WHERE LIN = #LIN#
</cfquery>
<cfset uid = find_requestor_id.uid>

<cfif find_requestor_id.recordcount LT 1>
<cfquery datasource="storage">
INSERT INTO requestors (firstname, lastname, LIN, email) VALUES
('#firstname#', '#lastname#', #LIN#, '#email#')
</cfquery>
<cfquery name="get_requestor_id" datasource="storage">
SELECT MAX(requestor_id) AS luid FROM requestors
</cfquery>
<cfset uid = get_requestor_id.luid>
</cfif>

<cfquery datasource="storage">
INSERT INTO requests (requestor_id) VALUES (<cfoutput>#uid#</cfoutput>) WHERE
request_id=<cfoutput>#last_id#</cfoutput>
</cfquery>

[jdeline]

Are uid and last_id numeric?

[TimMcGeary]

yes. They are both auto-generated ids created by Access.

[mxstu]

INSERT INTO requests (requestor_id) VALUES (<cfoutput>#uid#</cfoutput>) WHERE
request_id=<cfoutput>#last_id#</cfoutput>

The syntax for the last INSERT statement is incorrect. You cannot use a WHERE
clause with the INSERT ... VALUES syntax.

Either use:

INSERT INTO someTable (SomeColumn) VALUES (SomeValuesHere)....

OR

INSERT INTO someTable (SomeColumn)
SELECT SomeValue
FROM SomeTable
WHERE SomeNumericColumn = #SomeNumber#

Also, you don't need to use CFOUTPUT with the CFQUERY.

Use ...
INSERT INTO requests (requestor_id) VALUES (#uid#)

Instead of ...
INSERT INTO requests (requestor_id) VALUES ( <cfoutput>#uid#</cfoutput> )

[TimMcGeary]

Thank you. Even better, I will use an UPDATE - it was a brainfart that I
didn't to begin with.

Just to clarify, too, I am using a cftransaction, but I only posted a snippet
of the code that was failing. Thank you for confirming that.

And thank you for informing me that I don't need the <cfoutput> in the
<cfquery>. That makes it much easier to read. :)