Solved

SELECT @@identity does not work

Posted on 2011-03-10
19
497 Views
Last Modified: 2012-05-11
For some reason this doesn't work. It returns this error.

Element NEWPAYMENTID is undefined in NEWPAY

Please advise.
<cftransaction>
<cfquery name="newpay" datasource="#request.dataSource#">
INSERT INTO payments
(payment_type, payment_date, payment_amount, payment_notes, uid)
VALUES (
	   <cfqueryparam value="#FORM.payment_type#" cfsqltype="cf_sql_integer">,
       <cfqueryparam value="#FORM.pay_date#" cfsqltype="cf_sql_varchar">,
       <cfqueryparam value="#FORM.payment_amount#" cfsqltype="cf_sql_float">,
       <cfqueryparam value="#FORM.payment_notes#" cfsqltype="cf_sql_varchar">,
       <cfqueryparam value="#SESSION.auth.user_id#" cfsqltype="cf_sql_integer">
       )

SELECT @@identity AS newpaymentid
</cfquery>

<cfset npid = newpay.newpaymentid>

<cfquery name="newpay2" datasource="#request.dataSource#">
<cfset invList = "#FORM.invoice_id#">
<cfloop list="#invList#" index="invID">
INSERT INTO invoice_payments
(inv_id, p_id)
VALUES (
	   <cfqueryparam value="#invID#" cfsqltype="cf_sql_integer">,
       <cfqueryparam value="#npid#" cfsqltype="cf_sql_integer">
	   )
</cfloop>
</cfquery>

<cfquery name="upinvoice" datasource="#request.datasource#"> 
UPDATE invoice
SET invoice_status = '1',
	paid_by = <cfqueryparam value="#FORM.payment_type#" cfsqltype="cf_sql_integer">,
    check_number = <cfqueryparam value="#FORM.check_number#" cfsqltype="cf_sql_integer">
WHERE invoice_id IN (<cfqueryparam value="#FORM.invoice_id#" cfsqltype="cf_sql_integer" list="yes"> 
</cfquery>

</cftransaction>

Open in new window

0
Comment
Question by:aka_FATCAT
[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
  • 5
  • 5
  • 3
  • +4
19 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35093806
please try:
<cftransaction>
<cfquery name="newpay" datasource="#request.dataSource#">
SET NOCOUNT ON
INSERT INTO payments
(payment_type, payment_date, payment_amount, payment_notes, uid)
VALUES (
	   <cfqueryparam value="#FORM.payment_type#" cfsqltype="cf_sql_integer">,
       <cfqueryparam value="#FORM.pay_date#" cfsqltype="cf_sql_varchar">,
       <cfqueryparam value="#FORM.payment_amount#" cfsqltype="cf_sql_float">,
       <cfqueryparam value="#FORM.payment_notes#" cfsqltype="cf_sql_varchar">,
       <cfqueryparam value="#SESSION.auth.user_id#" cfsqltype="cf_sql_integer">
       )

SELECT SCOPE_IDENTITY() AS newpaymentid
</cfquery>

<cfset npid = newpay.newpaymentid>

<cfquery name="newpay2" datasource="#request.dataSource#">
<cfset invList = "#FORM.invoice_id#">
<cfloop list="#invList#" index="invID">
INSERT INTO invoice_payments
(inv_id, p_id)
VALUES (
	   <cfqueryparam value="#invID#" cfsqltype="cf_sql_integer">,
       <cfqueryparam value="#npid#" cfsqltype="cf_sql_integer">
	   )
</cfloop>
</cfquery>

<cfquery name="upinvoice" datasource="#request.datasource#"> 
UPDATE invoice
SET invoice_status = '1',
	paid_by = <cfqueryparam value="#FORM.payment_type#" cfsqltype="cf_sql_integer">,
    check_number = <cfqueryparam value="#FORM.check_number#" cfsqltype="cf_sql_integer">
WHERE invoice_id IN (<cfqueryparam value="#FORM.invoice_id#" cfsqltype="cf_sql_integer" list="yes"> 
</cfquery>

</cftransaction>

Open in new window

0
 

Author Comment

by:aka_FATCAT
ID: 35093822
Now I'm getting this error.

Incorrect syntax near the keyword 'select'.

0
 
LVL 11

Expert Comment

by:Brijesh Chauhan
ID: 35093842
<cftransaction>
<cfquery name="newpay" datasource="#request.dataSource#">
SET NOCOUNT ON
INSERT INTO payments
(payment_type, payment_date, payment_amount, payment_notes, uid)
VALUES (
	   <cfqueryparam value="#FORM.payment_type#" cfsqltype="cf_sql_integer">,
       <cfqueryparam value="#FORM.pay_date#" cfsqltype="cf_sql_varchar">,
       <cfqueryparam value="#FORM.payment_amount#" cfsqltype="cf_sql_float">,
       <cfqueryparam value="#FORM.payment_notes#" cfsqltype="cf_sql_varchar">,
       <cfqueryparam value="#SESSION.auth.user_id#" cfsqltype="cf_sql_integer">
       )
SELECT newpaymentid = SCOPE_IDENTITY()
</cfquery>

<cfset npid = newpay.newpaymentid>

<cfquery name="newpay2" datasource="#request.dataSource#">
<cfset invList = "#FORM.invoice_id#">
<cfloop list="#invList#" index="invID">
INSERT INTO invoice_payments
(inv_id, p_id)
VALUES (
	   <cfqueryparam value="#invID#" cfsqltype="cf_sql_integer">,
       <cfqueryparam value="#npid#" cfsqltype="cf_sql_integer">
	   )
</cfloop>
</cfquery>

<cfquery name="upinvoice" datasource="#request.datasource#"> 
UPDATE invoice
SET invoice_status = '1',
	paid_by = <cfqueryparam value="#FORM.payment_type#" cfsqltype="cf_sql_integer">,
    check_number = <cfqueryparam value="#FORM.check_number#" cfsqltype="cf_sql_integer">
WHERE invoice_id IN (<cfqueryparam value="#FORM.invoice_id#" cfsqltype="cf_sql_integer" list="yes"> 
</cfquery>

</cftransaction>

Open in new window

0
PowerShell Core for Advanced Linux Administrators

Understand advanced principals around Powershell Core with a focus on the Linux Administrator.  This course covers how to administer numerous environments across multiple platforms including Linux, Azure, AWS, and Google Cloud from a single shell instance.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35093847
sorry.

if this does not work, try to remove the set nocount on completely ....
<cftransaction>
<cfquery name="newpay" datasource="#request.dataSource#">
SET NOCOUNT ON;
INSERT INTO payments
(payment_type, payment_date, payment_amount, payment_notes, uid)
VALUES (
           <cfqueryparam value="#FORM.payment_type#" cfsqltype="cf_sql_integer">,
       <cfqueryparam value="#FORM.pay_date#" cfsqltype="cf_sql_varchar">,
       <cfqueryparam value="#FORM.payment_amount#" cfsqltype="cf_sql_float">,
       <cfqueryparam value="#FORM.payment_notes#" cfsqltype="cf_sql_varchar">,
       <cfqueryparam value="#SESSION.auth.user_id#" cfsqltype="cf_sql_integer">
       )
;
SELECT SCOPE_IDENTITY() AS newpaymentid
</cfquery>

<cfset npid = newpay.newpaymentid>

<cfquery name="newpay2" datasource="#request.dataSource#">
<cfset invList = "#FORM.invoice_id#">
<cfloop list="#invList#" index="invID">
INSERT INTO invoice_payments
(inv_id, p_id)
VALUES (
           <cfqueryparam value="#invID#" cfsqltype="cf_sql_integer">,
       <cfqueryparam value="#npid#" cfsqltype="cf_sql_integer">
           )
</cfloop>
</cfquery>

<cfquery name="upinvoice" datasource="#request.datasource#"> 
UPDATE invoice
SET invoice_status = '1',
        paid_by = <cfqueryparam value="#FORM.payment_type#" cfsqltype="cf_sql_integer">,
    check_number = <cfqueryparam value="#FORM.check_number#" cfsqltype="cf_sql_integer">
WHERE invoice_id IN (<cfqueryparam value="#FORM.invoice_id#" cfsqltype="cf_sql_integer" list="yes"> 
</cfquery>

</cftransaction>

Open in new window

0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35093850
try with

<cfquery name="newpay" datasource="#request.dataSource#">
SET NOCOUNT ON
INSERT INTO payments
(payment_type, payment_date, payment_amount, payment_notes, uid)
VALUES (
         <cfqueryparam value="#FORM.payment_type#" cfsqltype="cf_sql_integer">,
       <cfqueryparam value="#FORM.pay_date#" cfsqltype="cf_sql_varchar">,
       <cfqueryparam value="#FORM.payment_amount#" cfsqltype="cf_sql_float">,
       <cfqueryparam value="#FORM.payment_notes#" cfsqltype="cf_sql_varchar">,
       <cfqueryparam value="#SESSION.auth.user_id#" cfsqltype="cf_sql_integer">
       );

SELECT SCOPE_IDENTITY() AS newpaymentid;
</cfquery>
0
 

Author Comment

by:aka_FATCAT
ID: 35093882
Tried it both ways.

I get the same error. either not found or incorrect syntax.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35094204
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 35094208
Hi,

Try this way..
This should work

- Bhavesh
<cftransaction>
<cfset invList = "#FORM.invoice_id#">

<cfquery name="newpay" datasource="#request.dataSource#">
INSERT INTO payments
(payment_type, payment_date, payment_amount, payment_notes, uid)
VALUES (
           <cfqueryparam value="#FORM.payment_type#" cfsqltype="cf_sql_integer">,
       <cfqueryparam value="#FORM.pay_date#" cfsqltype="cf_sql_varchar">,
       <cfqueryparam value="#FORM.payment_amount#" cfsqltype="cf_sql_float">,
       <cfqueryparam value="#FORM.payment_notes#" cfsqltype="cf_sql_varchar">,
       <cfqueryparam value="#SESSION.auth.user_id#" cfsqltype="cf_sql_integer">
       )

DECLARE @NPID Int	   
SELECT @npid = @@identity

<cfloop list="#invList#" index="invID">
INSERT INTO invoice_payments
(inv_id, p_id)
VALUES (
           <cfqueryparam value="#invID#" cfsqltype="cf_sql_integer">,
       @NPID
           )
</cfloop>

UPDATE invoice
SET invoice_status = '1',
        paid_by = <cfqueryparam value="#FORM.payment_type#" cfsqltype="cf_sql_integer">,
    check_number = <cfqueryparam value="#FORM.check_number#" cfsqltype="cf_sql_integer">
WHERE invoice_id IN (<cfqueryparam value="#FORM.invoice_id#" cfsqltype="cf_sql_integer" list="yes"> 

</cfquery>
</cftransaction>

Open in new window

0
 
LVL 16

Accepted Solution

by:
Gurpreet Singh Randhawa earned 500 total points
ID: 35094224
ok are you using Coldfusion 8, If yes do the Following

<cftransaction>
<cfquery name="newpay" datasource="#request.dataSource#" result="LastID">
INSERT INTO payments
(payment_type, payment_date, payment_amount, payment_notes, uid)
VALUES (
         <cfqueryparam value="#FORM.payment_type#" cfsqltype="cf_sql_integer">,
       <cfqueryparam value="#FORM.pay_date#" cfsqltype="cf_sql_varchar">,
       <cfqueryparam value="#FORM.payment_amount#" cfsqltype="cf_sql_float">,
       <cfqueryparam value="#FORM.payment_notes#" cfsqltype="cf_sql_varchar">,
       <cfqueryparam value="#SESSION.auth.user_id#" cfsqltype="cf_sql_integer">
       )
</cfquery>
<cfset newpaymentid = LastID.IDENTITYCOL>
<cfset npid = newpaymentid>

<cfquery name="newpay2" datasource="#request.dataSource#">
<cfset invList = "#FORM.invoice_id#">
<cfloop list="#invList#" index="invID">
INSERT INTO invoice_payments
(inv_id, p_id)
VALUES (
         <cfqueryparam value="#invID#" cfsqltype="cf_sql_integer">,
       <cfqueryparam value="#npid#" cfsqltype="cf_sql_integer">
         )
</cfloop>
</cfquery>

<cfquery name="upinvoice" datasource="#request.datasource#">
UPDATE invoice
SET invoice_status = '1',
      paid_by = <cfqueryparam value="#FORM.payment_type#" cfsqltype="cf_sql_integer">,
    check_number = <cfqueryparam value="#FORM.check_number#" cfsqltype="cf_sql_integer">
WHERE invoice_id IN (<cfqueryparam value="#FORM.invoice_id#" cfsqltype="cf_sql_integer" list="yes">)
</cfquery>

</cftransaction>

I think this work as you were missing the last ")" in your last Query
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 35094229
Hi,

Secondly

<cfquery name="newpay" datasource="#request.dataSource#">
INSERT INTO payments
(payment_type, payment_date, payment_amount, payment_notes, uid)
VALUES (
           <cfqueryparam value="#FORM.payment_type#" cfsqltype="cf_sql_integer">,
       <cfqueryparam value="#FORM.pay_date#" cfsqltype="cf_sql_varchar">,
       <cfqueryparam value="#FORM.payment_amount#" cfsqltype="cf_sql_float">,
       <cfqueryparam value="#FORM.payment_notes#" cfsqltype="cf_sql_varchar">,
       <cfqueryparam value="#SESSION.auth.user_id#" cfsqltype="cf_sql_integer">
       )

SELECT @@identity AS newpaymentid;
</cfquery>

<cfset npid = newpay.newpaymentid>


This should works too....

Its working in my application.

By the way, which version of coldfusion/sql server you using?
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35095965
Let's first make sure that the table actually has an identity column, can you verify?

If you do an INSERT into payments using SQL Manager, and then select from it, do you see the value in the primary key field added automatically?


INSERT INTO payments
(payment_type, payment_date, payment_amount, payment_notes, uid)
  VALUES (... put in some values here..    )


0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35096011

here are the two ways that I have done it..
** ADD COMMA BEFORE SELECT SCOPE...
<cfquery name="newpay" datasource="#request.dataSource#">
  INSERT INTO payments
  (payment_type, payment_date, payment_amount, payment_notes, uid)  
  VALUES (
       <cfqueryparam value="#FORM.payment_type#" cfsqltype="cf_sql_integer">,
       <cfqueryparam value="#FORM.pay_date#" cfsqltype="cf_sql_varchar">,
       <cfqueryparam value="#FORM.payment_amount#" cfsqltype="cf_sql_float">,
       <cfqueryparam value="#FORM.payment_notes#" cfsqltype="cf_sql_varchar">,
       <cfqueryparam value="#SESSION.auth.user_id#" cfsqltype="cf_sql_integer">
       )
  ;
  select SCOPE_IDENTITY( ) as newpaymentid;
</cfquery>
<cfset npid = newpay.newPaymentId>


**** SEPERATE CFQUERY FOR IDENTITY...
<cfquery name="newpay" datasource="#request.dataSource#">
  INSERT INTO payments
  (payment_type, payment_date, payment_amount, payment_notes, uid)  
  VALUES (
       <cfqueryparam value="#FORM.payment_type#" cfsqltype="cf_sql_integer">,
       <cfqueryparam value="#FORM.pay_date#" cfsqltype="cf_sql_varchar">,
       <cfqueryparam value="#FORM.payment_amount#" cfsqltype="cf_sql_float">,
       <cfqueryparam value="#FORM.payment_notes#" cfsqltype="cf_sql_varchar">,
       <cfqueryparam value="#SESSION.auth.user_id#" cfsqltype="cf_sql_integer">
       )
</cfquery>
<cfquery name="GetPkey" datasource="#request.datasource#">
 select SCOPE_IDENTITY( ) as ID
</cfquery>  
<cfset npid = GetPkey.ID>

Open in new window

0
 
LVL 16

Expert Comment

by:Gurpreet Singh Randhawa
ID: 35096463
@author!

Have you actually tried the way we have provided the info, Try the way i have provided and see if it matches ur requirement or not
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35096526
yes, i agree, you should try myselfrandhawa's method using the result on the query
0
 

Author Closing Comment

by:aka_FATCAT
ID: 35098886
"I think this work as you were missing the last ")" in your last Query"

Once again, it was 4am, and my brain had stopped functioning.

All of the previous suggestions worked as well, but it was that damn missing ")" that was the true culprit.

Thank you so much.
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35098935
How were you getting the error:
 Element NEWPAYMENTID is undefined in NEWPAY

because you were missing a ) in a query that came AFTER the error statement?


You must have fixed the error you posted about and not told us
0
 

Author Comment

by:aka_FATCAT
ID: 35099044
That was mu thought exactly, but as you can see, it was the missing ")" that was causing the error.

I have attached the working code.
<cfquery name="newpay" datasource="#request.dataSource#">
  INSERT INTO payments
  (payment_type, payment_date, payment_amount, payment_notes, uid)  
  VALUES (
       <cfqueryparam value="#FORM.payment_type#" cfsqltype="cf_sql_integer">,
       <cfqueryparam value="#FORM.pay_date#" cfsqltype="cf_sql_varchar">,
       <cfqueryparam value="#FORM.payment_amount#" cfsqltype="cf_sql_float">,
       <cfqueryparam value="#FORM.payment_notes#" cfsqltype="cf_sql_varchar">,
       <cfqueryparam value="#SESSION.auth.user_id#" cfsqltype="cf_sql_integer">
       )
</cfquery>
<cfquery name="GetPkey" datasource="#request.datasource#">
 SELECT @@identity AS ID
</cfquery>  
<cfset npid = GetPkey.ID>

<cfquery name="newpay2" datasource="#request.dataSource#">
<cfset invList = "#FORM.invoice_id#">
<cfloop list="#invList#" index="invID">
INSERT INTO invoice_payments
(inv_id, p_id)
VALUES (
	   <cfqueryparam value="#invID#" cfsqltype="cf_sql_integer">,
       <cfqueryparam value="#npid#" cfsqltype="cf_sql_integer">
	   )
</cfloop>
</cfquery>

<cfquery name="upinvoice" datasource="#request.datasource#"> 
UPDATE invoice
SET invoice_status = '1',
	paidby_id = <cfqueryparam value="#FORM.payment_type#" cfsqltype="cf_sql_integer">,
    check_number = <cfqueryparam value="#FORM.check_number#" cfsqltype="cf_sql_integer">
WHERE invoice_id IN (<cfqueryparam value="#FORM.invoice_id#" cfsqltype="cf_sql_integer" list="yes">) 
</cfquery>

Open in new window

0
 

Author Comment

by:aka_FATCAT
ID: 35099158
This is the original version, with a separate query for the @@identity.

Works great, again it was failing because of the missing ")" at the very end.
<cftransaction>
<cfquery name="newpay" datasource="#request.dataSource#">
INSERT INTO payments
(payment_type, payment_date, payment_amount, payment_notes, uid)
VALUES (
	   <cfqueryparam value="#FORM.payment_type#" cfsqltype="cf_sql_integer">,
       <cfqueryparam value="#FORM.pay_date#" cfsqltype="cf_sql_varchar">,
       <cfqueryparam value="#FORM.payment_amount#" cfsqltype="cf_sql_float">,
       <cfqueryparam value="#FORM.payment_notes#" cfsqltype="cf_sql_varchar">,
       <cfqueryparam value="#SESSION.auth.user_id#" cfsqltype="cf_sql_integer">
       )
</cfquery>

<cfquery name="GetPkey" datasource="#request.datasource#">
 SELECT @@identity AS ID
</cfquery>  
<cfset npid = GetPkey.ID>

<cfquery name="newpay2" datasource="#request.dataSource#">
<cfset invList = "#FORM.invoice_id#">
<cfloop list="#invList#" index="invID">
INSERT INTO invoice_payments
(inv_id, p_id)
VALUES (
	   <cfqueryparam value="#invID#" cfsqltype="cf_sql_integer">,
       <cfqueryparam value="#npid#" cfsqltype="cf_sql_integer">
	   )
</cfloop>
</cfquery>

<cfquery name="upinvoice" datasource="#request.datasource#"> 
UPDATE invoice
SET invoice_status = '1',
	paidby_id = <cfqueryparam value="#FORM.payment_type#" cfsqltype="cf_sql_integer">,
    check_number = <cfqueryparam value="#FORM.check_number#" cfsqltype="cf_sql_integer">
WHERE invoice_id IN (<cfqueryparam value="#FORM.invoice_id#" cfsqltype="cf_sql_integer" list="yes">) 
</cfquery>

</cftransaction>

Open in new window

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35099162

Funny, you used my solution putting the select identity in a different cfquery


You're saying if you remove the ) and only make that one change you get the error

  Element NEWPAYMENTID is undefined in NEWPAY



I think you had multiple issues..


0

Featured Post

Containers & Docker to Create a Powerful Team

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

Question has a verified solution.

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

This article  is about submitting  form through  ColdFusion.Ajax.submitForm to the action page and send a response back in JSON format which later can be decoded using ColdFusion.JSON.decode. By this way you can avoid the usual page refresh for subm…
PROBLEM: How to add your own buttons to the bottom toolbar with paging info ( result count ). While creating a cfgrid, I ran into an issue where I wanted to embed my own custom buttons where the default ones ( insert / delete / etc… ) are for aes…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

636 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