ColdFusion

Insert query.

Please support our ColdFusion advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

•

Join Date: Mar 2008

Posts: 5

Reputation: Hercf is an unknown quantity at this point

Solved Threads: 0

Hercf

Offline

Newbie Poster

Insert query.

Jun 15th, 2008

Hi everybody!
Could you teach me how to get back the “primary key”: (“id”), from an “insert query”?

*

 Help with Code Tags 
 coldfusion Syntax (Toggle Plain Text) 
<cffunction name="insertData" access="public">
  <cfargument name="formData" type="struct" required="yes">
 
  <cfquery name="qInsert" datasource="mydata">
                    INSERT INTO mytable
                        (email, firstname, name)
                    VALUES
                        ('#formData.email#', '#formData.firstname#', '#formData.name#')
           </cfquery>
</cffunction>
<cffunction name="insertData" access="public">
  <cfargument name="formData" type="struct" required="yes">
  
  <cfquery name="qInsert" datasource="mydata">
                    INSERT INTO mytable
                        (email, firstname, name)
                    VALUES
                        ('#formData.email#', '#formData.firstname#', '#formData.name#')
           </cfquery>
</cffunction>

*
How could I get back the primary key:”id” and send it to another page? (MySQL)
Thanks a lot!

Last edited by peter_budo; Jun 16th, 2008 at 10:06 am. Reason: Keep It Organized - please use [code] tags

•

Join Date: Feb 2008

Posts: 72

Reputation: cmhampton is an unknown quantity at this point

Solved Threads: 10

cmhampton

Offline

Junior Poster in Training

Re: Insert query.

Jun 23rd, 2008

Use SCOPE_IDENTITY(). It will return the id of the record just created, provided the field is set as the identity column.

 Help with Code Tags 
 ColdFusion Syntax (Toggle Plain Text) 
<cffunction name="insertData" access="public">
  <cfargument name="formData" type="struct" required="yes">
 
  <cfquery name="qInsert" datasource="mydata">
       INSERT INTO mytable
          (email, firstname, name)
       VALUES
          ('#formData.email#', '#formData.firstname#', '#formData.name#')
 
       SELECT id = SCOPE_IDENTITY()
   </cfquery>
 
   <cfreturn qInsert.id />
 
</cffunction>
<cffunction name="insertData" access="public">
  <cfargument name="formData" type="struct" required="yes">
  
  <cfquery name="qInsert" datasource="mydata">
       INSERT INTO mytable
          (email, firstname, name)
       VALUES
          ('#formData.email#', '#formData.firstname#', '#formData.name#')

       SELECT id = SCOPE_IDENTITY()
   </cfquery>

   <cfreturn qInsert.id />

</cffunction>

•

Join Date: Mar 2008

Posts: 5

Reputation: Hercf is an unknown quantity at this point

Solved Threads: 0

Hercf

Offline

Newbie Poster

Re: Insert query.

Jun 29th, 2008

Thank you, cmhampton !
How about cfquery: "result" ?What you prefer?
H.

•

Originally Posted by cmhampton

Use SCOPE_IDENTITY(). It will return the id of the record just created, provided the field is set as the identity column.
Help with Code Tags

ColdFusion Syntax (Toggle Plain Text)
<cffunction name="insertData" access="public">
  <cfargument name="formData" type="struct" required="yes">
 
  <cfquery name="qInsert" datasource="mydata">
       INSERT INTO mytable
          (email, firstname, name)
       VALUES
          ('#formData.email#', '#formData.firstname#', '#formData.name#')
 
       SELECT id = SCOPE_IDENTITY()
   </cfquery>
 
   <cfreturn qInsert.id />
 
</cffunction>
<cffunction name="insertData" access="public"> <cfargument name="formData" type="struct" required="yes"> <cfquery name="qInsert" datasource="mydata"> INSERT INTO mytable (email, firstname, name) VALUES ('#formData.email#', '#formData.firstname#', '#formData.name#') SELECT id = SCOPE_IDENTITY() </cfquery> <cfreturn qInsert.id /> </cffunction>