?
Solved

Best Practice to capture the Id of a newly inserted recordset (MSQL/CFML)

Posted on 2010-08-12
5
Medium Priority
?
547 Views
Last Modified: 2012-05-10
     I am adding a new ministry into the db with this sql statement:


<cfquery datasource="DSN_Vineyard">
            INSERT INTO ministries (church_id, ministry_desc, ministry_leader
                  <cfif admin1_new is not "">, admin1 </cfif>
                  <cfif admin2_new is not "">, admin2 </cfif>
                  <cfif admin3_new is not "">, admin3 </cfif>
                  <cfif admin4_new is not "">, admin4 </cfif>
                  <cfif admin5_new is not "">, admin5 </cfif>)
            VALUES (#church_id#, '#ministry_desc_new#', #ministry_leader_new#
                  <cfif admin1_new is not "">, #admin1_new# </cfif>
                  <cfif admin2_new is not "">, #admin2_new# </cfif>
                  <cfif admin3_new is not "">, #admin3_new# </cfif>
                  <cfif admin4_new is not "">, #admin4_new# </cfif>
                  <cfif admin5_new is not "">, #admin5_new# </cfif>)
      </cfquery>


I need to be able to capture the id of the newly inserted recordset and add that to a second db collumn titled page_entities which is used to track everything. What is the best practice to capture the id of the newly inserted recordset?
0
Comment
Question by:btintermedia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 33426781
I don't have knowledge about CFML

but for MSSQL, when you insert new data into table, you can obtain the PRIMARY KEY value by function SCOPE_IDENTITY()  or by variable @@IDENTITY (but the first one is recommend)

for ex

INSERT INTO table (field1, field2) VALUES (value1, value2)

SELECT @new_id = SCOPE_IDENTITY()
0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 33426786
Oops,  value to obtain is the identity value, not primary key. (since PRIMARY KEY is not always identity value)
0
 
LVL 11

Accepted Solution

by:
JoeNuvo earned 1200 total points
ID: 33426811
2 methods

1. using SCOPE_IDENTITY()  

<cfquery datasource="DSN_Vineyard">
            INSERT INTO ministries (church_id, ministry_desc, ministry_leader
                  <cfif admin1_new is not "">, admin1 </cfif>
                  <cfif admin2_new is not "">, admin2 </cfif>
                  <cfif admin3_new is not "">, admin3 </cfif>
                  <cfif admin4_new is not "">, admin4 </cfif>
                  <cfif admin5_new is not "">, admin5 </cfif>)
            VALUES (#church_id#, '#ministry_desc_new#', #ministry_leader_new#
                  <cfif admin1_new is not "">, #admin1_new# </cfif>
                  <cfif admin2_new is not "">, #admin2_new# </cfif>
                  <cfif admin3_new is not "">, #admin3_new# </cfif>
                  <cfif admin4_new is not "">, #admin4_new# </cfif>
                  <cfif admin5_new is not "">, #admin5_new# </cfif>)

          SELECT SCOPE_IDENTITY() as NewID
      </cfquery>

2. using OUTPUT
     assume your identity field name is MinistryID

<cfquery datasource="DSN_Vineyard">
            INSERT INTO ministries (church_id, ministry_desc, ministry_leader
                  <cfif admin1_new is not "">, admin1 </cfif>
                  <cfif admin2_new is not "">, admin2 </cfif>
                  <cfif admin3_new is not "">, admin3 </cfif>
                  <cfif admin4_new is not "">, admin4 </cfif>
                  <cfif admin5_new is not "">, admin5 </cfif>)
             OUTPUT INSERTED.MinistryID as [NewID]
            VALUES (#church_id#, '#ministry_desc_new#', #ministry_leader_new#
                  <cfif admin1_new is not "">, #admin1_new# </cfif>
                  <cfif admin2_new is not "">, #admin2_new# </cfif>
                  <cfif admin3_new is not "">, #admin3_new# </cfif>
                  <cfif admin4_new is not "">, #admin4_new# </cfif>
                  <cfif admin5_new is not "">, #admin5_new# </cfif>)
      </cfquery>

0
 
LVL 19

Assisted Solution

by:erikTsomik
erikTsomik earned 800 total points
ID: 33426814
you can do this
<cfquery datasource="DSN_Vineyard" result="qryResult">
            INSERT INTO ministries (church_id, ministry_desc, ministry_leader
                  <cfif admin1_new is not "">, admin1 </cfif>
                  <cfif admin2_new is not "">, admin2 </cfif>
                  <cfif admin3_new is not "">, admin3 </cfif>
                  <cfif admin4_new is not "">, admin4 </cfif>
                  <cfif admin5_new is not "">, admin5 </cfif>)
            VALUES (#church_id#, '#ministry_desc_new#', #ministry_leader_new#
                  <cfif admin1_new is not "">, #admin1_new# </cfif>
                  <cfif admin2_new is not "">, #admin2_new# </cfif>
                  <cfif admin3_new is not "">, #admin3_new# </cfif>
                  <cfif admin4_new is not "">, #admin4_new# </cfif>
                  <cfif admin5_new is not "">, #admin5_new# </cfif>)
      </cfquery>

<cfset newID = qryResult..IDENTITYCOL>
0
 
LVL 52

Expert Comment

by:_agx_
ID: 33426968
>> VALUES (#church_id#, '#ministry_desc_new#', #ministry_leader_new#

As an aside, always use cfqueryparam with MS SQL. Because that query is highly vulnerable to sql injection.
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question