SELECT @@identity does not work

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>

Select allOpen in new window

Now I'm getting this error.

Incorrect syntax near the keyword 'select'.

<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>

Select allOpen in new window

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>

Select allOpen in new window

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>

Tried it both ways.

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

interesting ...
http://www.tek-tips.com/viewthread.cfm?qid=1001108&page=155

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>

Select allOpen in new window

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?

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..    )

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>

Select allOpen in new window

@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

yes, i agree, you should try myselfrandhawa's method using the result on the query

"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.

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

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>

Select allOpen in new window

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>

Select allOpen in new window

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..