Insert query.

•

What is DaniWeb IT Discussion Community?

You're currently browsing the ColdFusion section within the Web Development category of DaniWeb, a massive community of 427,937 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,960 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our ColdFusion advertiser: Programming Forums

Thread Tools Display Modes

Views: 1450 | Replies: 3 | Solved

•

Join Date: Mar 2008

Posts: 5

Reputation: Hercf is an unknown quantity at this point

Rep Power: 0

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 9:06 am. Reason: Keep It Organized - please use [code] tags

•

Join Date: Feb 2008

Posts: 70

Reputation: cmhampton is an unknown quantity at this point

Rep Power: 1

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

Rep Power: 0

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>