get lastID

[newcf]

    <!---cfc--->

    <cffunction name="add"  access="public">

        <cfargument name="form" type="struct" required="yes">

        <cfquery name="insert"   datasource="#request.dsn#" result="returnID">

        ...insert goes here---

        </cfquery>

            <cfset variables.newDRD_ID= returnID.IDENTITYCOL />   

        </cffunction>

        <!---cfm--->

        <!---insert into table--->

         <cfobject type="component" name="request.type" component="#request.cfc#.type">

         <cfset variables.addNewType = request.type.add(form) />

         after calling the object above to insert into the type, i want to display

         an id here and ID is retured from cfc.  Can any one show me how to have ID display

         in this cfm page?

         Thanks

[Dan_Bracuk]

Return it from your function.

[itisdesign]

Hi newcf,

Dan is right.  Also, within cffunctions you should var/local-scope your variables.  Could you try this?

1) Change <cfquery name="insert"   datasource="#request.dsn#" result="returnID"> to <cfquery name="insert" datasource="#request.dsn#" result="local.returnID">

2) Change <cfset variables.newDRD_ID= returnID.IDENTITYCOL /> to <cfreturn local.returnID.IDENTITYCOL />

3) In the cfm, add <cfoutput>#variables.addNewType#</cfoutput>

Note: If you're on CF9+, you can change IDENTITYCOL to GENERATEDKEY which is not database-specific.

Thanks,

-Aaron

[newcf]

thanks for all your replies. the reason i did not have cfreturn because i want to get that new id and insert into the child tables.  if i have cfreturn from first query then i can't get the new id to insert into the second query,  any suggessions?

thanks

--insert into parent table-->

<cfquery name="insert"   datasource="#request.dsn#" result="returnID">

        ...insert goes here---

        </cfquery>

<!---<cfreturn local.returnID.IDENTITYCOL />--->

            <cfset variables.newID= returnID.IDENTITYCOL />   

        </cffunction>

---insert into child tables

<cfloop index="backup"  list="#arguments.form.backup#">

                <cfquery name="insert_backup"  datasource="#request.dsn#">

                    INSERT into backups (vou, type_id)

                    VALUES

                    (

                    <cfqueryparam value="#arguments.form.vou#" cfsqltype="cf_sql_varchar" maxlength="255" />,

                    <cfqueryparam value="#variables.newID#" cfsqltype="cf_sql_integer" />           

                    )

                </cfquery>

                </cfloop>

[Dan_Bracuk]

Return the new id from the first function and pass it as an argument to the function with the other queries.

[BKBK]

Some suggestions:

1) Give function a returntype.

2)Use a select query to return the result set, not an insert query.

3) It may be inefficient to pass the whole form object, when the function requires just one or two parameters from the form.

4) You imply that form.backup is a list. It is poor design to pass a list as a form field.

5) You loop through the list arguments.form.backup, storing each list element in the variable backup. However, backup occurs nowhere in the loop.

Here follows a guess from me:

<cffunction name="add"  access="public" returntype="numeric">

<cfargument name="form" type="struct" required="yes">

    <cfset var idList = "">

    <!---insert into parent table--->

    <cfquery name="insert"   datasource="#request.dsn#">

        ...insert goes here---

    </cfquery>

    <!--- my guess for select query --->

    <cfquery name="getID" datasource="#request.dsn#" result="returnID">

        SELECT identityCol

        FROM myTBL

        WHERE vou in (/* list goes here*/)

    </cfquery>

    <cfset idList = valueList(returnID.identityCol)>

    <!--- loop through IDs, inserting into each child table--->

    <cfloop index="id"  list="#idList#">

        <cfquery name="insert_backup"  datasource="#request.dsn#">

            INSERT into backups (vou, type_id)

            VALUES

            (

            <cfqueryparam value="#arguments.form.vou#" cfsqltype="cf_sql_varchar" maxlength="255" />,

            <cfqueryparam value="#id#" cfsqltype="cf_sql_integer" />           

            )

        </cfquery>

    </cfloop>

<!---return last ID --->

<cfreturn listLast(idList)>

</cffunction>