Data created by query doesn't exist

Frickin brilliant Ken. Thank you!

I believe as was noted in this thread there may have been a trigger
mechanism on the DB and it may have been lost when migrating to a new
version of SQL because the code as it was never should have worked.


snack


Ken Ford wrote:
> Maybe like this?
>
> <cfif NOT len(FORM.item_id)>
> <cfset FORM.item_id = createUUID()>
> <cfquery name="insertItem">
> INSERT INTO table
> (item_id)
> VALUES
> ( <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#FORM.item_id#">
> )
> </cfquery>
> </cfif>
> <cfquery name="itemOptions">
> INSERT INTO items_xref_tb
> (itemOption_id,item_id)
> VALUES
> (
> <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#FORM.item_id#">
> )
> </cfquery>
>

Yet another flavour.
[NB: your second query attempted to insert one value into two columns]

> <cfquery name="insertItem">
> INSERT INTO table(item_id)
> VALUES( <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#createUUID()#">)
> </cfquery>
>
> <cfset form.item_id = insertItem.item_id>

As someone else said: this *never* would have "worked". INSERT queries
don't return anything.

The only thing I can think is that some preceding bit of code set variable
insertItem.item_id, and in previous versions of CF the query name is
ignored if the query doesn't return anything; whereas in CF8 it sets the
variable to null.

To get it to work as intended, set the value to use first, then pass it to
the query.

--
Adam

snack wrote:
> CFSET below is not be working since moving to CF8.
> CF returns "Element ITEM_ID is undefined in INSERTITEM"

I am surprised it ever worked. Afaik, a basic INSERT / VALUES statement does not usually return a value. Not unless there is some sort of trigger happening in the background.

> How can I get the ID generated in the "insertItem" Query

Assuming you mean the UUID value, store it in a variable before your insert query.

Maybe like this?

<cfif NOT len(FORM.item_id)>
<cfset FORM.item_id = createUUID()>
<cfquery name="insertItem">
INSERT INTO table
(item_id)
VALUES
(
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#FORM.item_id#">
)
</cfquery>
</cfif>
<cfquery name="itemOptions">
INSERT INTO items_xref_tb
(itemOption_id,item_id)
VALUES
(
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#FORM.item_id#">
)
</cfquery>

--
Ken Ford
Adobe Community Expert - Dreamweaver/ColdFusion
Fordwebs, LLC
http://www.fordwebs.com


"snack" <snack@snackshack.com> wrote in message news:g74k6e$pdo$1@forums.macromedia.com...
> Hello struggling with inherited code here.
>
> CFSET below is not be working since moving to CF8.
> CF returns "Element ITEM_ID is undefined in INSERTITEM"
>
> How can I get the ID generated in the "insertItem" Query and pass it to
> the the "itemOptions" query?
> The code below works IF I manually place a UUID in the CFSET tag for
> "insertItem.item_id".
>
> Thanks for any help!
>
>
> <cfif NOT len(form.item_id)>
>
> <cfquery name="insertItem">
> INSERT INTO table(item_id)
> VALUES( <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#createUUID()#">)
> </cfquery>
>
> <cfset form.item_id = insertItem.item_id>
>
> </cfif>
>
> <cfquery name="itemOptions">
> INSERT INTO items_xref_tb(itemOption_id,item_id)
> VALUES(<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.item_id#">)
> </cfquery>