asked on

I need to use a different option for INSERT_IDENTITY within a cold fusion file using sql queries.

I need to rewrite a slq query within a cold fusion file to get rid of INDENTITY_ON and IDENTITY_OFF due to security issues. I've started recoding using IDENTIYCOL but I'm stuck. Below is the current code that will need to be rewritten w/ result_name.IDENTITYCOL vs. INSERT_IDENTITY ON/OFF. I opened a previous question about this but that was to work using IDENTIY_INSERT ON/OFF. I'll close that question but really need help on this asap.

1:<cfif ((#duplClaim# NEQ "Y") and (#requestId# EQ 0))>
2:<cfquery name="getNextID" datasource="#dsn#">
3:select (max(id) + 1) as nextID from TLAClaims
4:</cfquery>
5:<cfset requestId = "#getNextID.nextID#">
6:
7:<cfquery name="InsertClaim" datasource="#dsn#">
8:SET IDENTITY_INSERT TLACLAIMS ON
9:insert into TLAClaims (ID, lastName, firstName, eeEmail, travelbegindate, travelenddate, numberofdays, unit, approver, submitdate, tono, claimAmount, customerNotes, employeeId, documentation, status, deoEmail, claimType)
10:values ('#requestId#', upper('#lastName#'), upper('#firstName#'), '#eeEmail#', '#travelbegindate#', '#travelenddate#', '#numberofdays#', '#unit#', '#approver#', '#todaysDate#', '#tono#', '#claimAmount#', '#customerNotes#', '#employeeId#', '#documentation#', 'Inactive', '#deoEmail#', '#claimType#')
11:SET IDENTITY_INSERT TLACLAIMS OFF
12:</cfquery>

I've started writing this code w/o the INSERT_IDENTIY ON/OFF but I'm stuck now:
1:<cfif ((#duplClaim# NEQ "Y") and (#requestId# EQ 0))>
2:<cfquery name="getNextID" datasource="#dsn#">
3:select (max(id) + 1) as nextID from TLAClaims
4:</cfquery>
5:
6:<cfquery name="InsertClaim" datasource="#dsn#">
7:insert into TLAClaims (ID, lastName, firstName, eeEmail, travelbegindate, travelenddate, numberofdays, unit, approver, submitdate, tono, claimAmount, customerNotes, employeeId, documentation, status, deoEmail, claimType)
8:values ('#requestId#', upper('#lastName#'), upper('#firstName#'), '#eeEmail#', '#travelbegindate#', '#travelenddate#', '#numberofdays#', '#unit#', '#approver#', '#todaysDate#', '#tono#', '#claimAmount#', '#customerNotes#', '#employeeId#', '#documentation#', 'Inactive', '#deoEmail#', '#claimType#')
9:</cfquery>
10:
11:<cfset requestId = "#getNextID.nextID#">

Can someone help please? pushing deadline so any help is greatly appreciated! Thanks. txt file of the cfm file is attached
TLAattachments_sample.txt

ste5an

D'oh?? Why don't you simply use the IDENTITY value? E.g. this

<cfquery name="InsertClaim" datasource="#dsn#">
INSERT INTO TLAClaims ( lastName, firstName, eeEmail, travelbegindate, travelenddate, numberofdays,  unit, approver, submitdate, tono, claimAmount, customerNotes,  employeeId, documentation, status, deoEmail, claimType)  
OUTPUT INSERTED.ID
VALUES ( UPPER('#lastName#'), UPPER('#firstName#'), '#eeEmail#', '#travelbegindate#', '#travelenddate#', '#numberofdays#', '#unit#', '#approver#', '#todaysDate#', '#tono#', '#claimAmount#', '#customerNotes#', '#employeeId#', '#documentation#', 'Inactive', '#deoEmail#', '#claimType#' );
</cfquery>

Open in new window

<cfquery name="InsertClaim" datasource="#dsn#">
INSERT INTO TLAClaims ( lastName, firstName, eeEmail, travelbegindate, travelenddate, numberofdays,  unit, approver, submitdate, tono, claimAmount, customerNotes,  employeeId, documentation, status, deoEmail, claimType)  
VALUES ( UPPER('#lastName#'), UPPER('#firstName#'), '#eeEmail#', '#travelbegindate#', '#travelenddate#', '#numberofdays#', '#unit#', '#approver#', '#todaysDate#', '#tono#', '#claimAmount#', '#customerNotes#', '#employeeId#', '#documentation#', 'Inactive', '#deoEmail#', '#claimType#' );
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];
</cfquery>

Open in new window

p.s. embed your code into [code] tags (the CODE button in the toolbar). Edit your post. This increads readability.

Quack

ASKER

I'll check to see if that works...thnx...will get back to you shortly.

Quack

ASKER

what is the SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]; accomplishing exactly?

Quack

ASKER

I'm getting this error:

Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Cannot insert explicit value for identity column in table 'TLAClaims' when IDENTITY_INSERT is set to OFF.

The error occurred in D:/inetpub/wwwroot/TLA/Admin/TLAattachments.cfm: line 148

146 : <cfquery name="InsertClaim" datasource="#dsn#">
147 : insert into TLAClaims (ID, lastName, firstName, eeEmail, travelbegindate, travelenddate, numberofdays, unit, approver, submitdate, tono, claimAmount, customerNotes, employeeId, documentation, status, deoEmail, claimType)
148 : values ('#requestId#', upper('#lastName#'), upper('#firstName#'), '#eeEmail#', '#travelbegindate#', '#travelenddate#', '#numberofdays#', '#unit#', '#approver#', '#todaysDate#', '#tono#', '#claimAmount#', '#customerNotes#', '#employeeId#', '#documentation#', 'Inactive', '#deoEmail#', '#claimType#')
149 : SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];
150 : </cfquery>

ste5an

Embed your code into [code] tags (the CODE button in the toolbar). Edit your post. This increads readability!

Take a look again at my samples. There is no ID column.

Quack

ASKER

so you're saying pull the ID column all together?

also, so I understand can you let me know this: what is the SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]; accomplishing exactly?

thanks

Quack

ASKER

that seems to be working but when I submit the form and check the dev table the information isn't showing up. I'm checking to see if there's another step to the process before it will show.

Let me know about the question above please so I can more fully understand what its doing.

thanks

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Quack

ASKER

Hi again...Can you elaborate on the SCOPE_IDNENTITY() usage? Does that need to be referenced any where else w/in the cold fusion code to be effective or is it just being used w/in the SQL script?

Thanks

ste5an

It must be run in the same batch.