Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

put the @@identity  into a list

Posted on 2014-04-18
12
Medium Priority
?
178 Views
Last Modified: 2014-04-18
Hi,

How can I put the @@identity into a list?


<cfif isdefined('url.IDP') && #url.IDP# gt 0>
		<cfquery name = "QSelectParentFiles"  datasource="#strDSN#" username="#strUID#" password="#strPWD#" result="s">		
            Insert into Files (flag, IssueID, FileName,Description, UploadedFile, 
			Status, Uploadedasbinary,CreatedDate)
			Select     
            1,
				<cfif (isdefined('url.ID') && #url.ID# neq "")> 
					#url.ID#,
				<cfelseif (isdefined('IssueIDVal') && #IssueIDVal# neq "")>
 					#IssueIDVal#,
				</cfif>
			FileName,Description, UploadedFile, 
			Status, Uploadedasbinary,<cfqueryparam cfsqltype="cf_sql_date" value="#Now()#" />  from Files 
			where IssueID = #url.IDP#  
              
                  SELECT @@identity AS FileIDs      
		</cfquery>
</cfif>


// How can I have all the insert identities in a list 
<cfset IDlistitem = ValueList(@@identity)>

Open in new window

0
Comment
Question by:lulu50
[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
  • 7
  • 4
12 Comments
 
LVL 16

Expert Comment

by:Gurpreet Singh Randhawa
ID: 40009289
try this:

<cfif isdefined('url.IDP') && #url.IDP# gt 0>
  <cfquery name = "QSelectParentFiles"  datasource="#strDSN#" username="#strUID#" password="#strPWD#" result="s">		
            Insert into Files (flag, IssueID, FileName,Description, UploadedFile, 
			Status, Uploadedasbinary,CreatedDate)
			Select 1,
				<cfif (isdefined('url.ID') && #url.ID# neq "")> 
					#url.ID#,
				<cfelseif (isdefined('IssueIDVal') && #IssueIDVal# neq "")>
 					#IssueIDVal#,
				</cfif>
			FileName,Description, UploadedFile, 
			Status, Uploadedasbinary,<cfqueryparam cfsqltype="cf_sql_date" value="#Now()#" />  from Files 
			where IssueID = #url.IDP#      
		</cfquery>
</cfif>

// How can I have all the insert identities in a list
<cfset IDlistitem = s.IDENTITYCOL>

<cfdump var="#IDlistitem#">

Open in new window



as you are already using the result attribute of cfquey and sql server you can use the IDENTITYCOl to fetch the inserted Latest ID

i can see also you are not using any union or union all to insert multiple values so using valuelist makes no sense,

try above code and see the results
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 40009314
Lulu,
Because you are performing an insert with a select, I don't believe you can pull the identity, but if you can, you are probably only getting the last one.

Just so you know, the way to refer to your identity select statement is like this...

<cfset IDlistitem = ValueList(QSelectParentFiles.FileIDs)>

this is the query name with the alias name you gave the identity

   SELECT @@identity AS FileIDs  


If you need all the IDs, maybe you can mark the database records with either a date or a batch number so you can know which group of records was just created.
0
 

Author Comment

by:lulu50
ID: 40009466
Can you please tell me what I am doing wrong.

I have an error that says:


Error Executing Database Query.  
[Macromedia][SQLServer JDBC Driver][SQLServer]Invalid column name 'IDdellist'.  



 <cfloop from="1" to="3" index="cc">
 <cfif StructKeyExists(form,"TempID" & cc) && len(trim(Form["TempID" & cc])) && #Form["TempID" & cc]# neq "">
 		<cfquery name = "QallDeletefiles"  datasource="#strDSN#" username="#strUID#" password="#strPWD#" result="de">		
  		select FileID from Files 
        where FileID = #Form["TempID" & cc]#
        </cfquery>
 </cfif>
 </cfloop>

 <cfloop query="QallDeletefiles">  
      	<cfset IDdellist = ValueList(QallDeletefiles.FileID,",")>
 </cfloop>


<cfif isdefined('url.IDP') && #url.IDP# gt 0>
		<cfquery name = "QSelectParentFiles"  datasource="#strDSN#" username="#strUID#" password="#strPWD#" result="s">		
            Insert into Files (IssueID, FileName,Description, UploadedFile, 
			Status, Uploadedasbinary,CreatedDate)
			Select 
				<cfif (isdefined('url.ID') && #url.ID# neq "")> 
					#url.ID#,
				<cfelseif (isdefined('IssueIDVal') && #IssueIDVal# neq "")>
 					#IssueIDVal#,
				</cfif>
			FileName,Description, UploadedFile, 
			Status, Uploadedasbinary,<cfqueryparam cfsqltype="cf_sql_date" value="#Now()#" />  from Files 
            where IssueID = #url.IDP# and FileID not in (IDdellist) 
		</cfquery>
</cfif>

Open in new window

0
Amazon Web Services EC2 Cheat Sheet

AWS EC2 is a core part of AWS’s cloud platform, allowing users to spin up virtual machines for a variety of tasks; however, EC2’s offerings can be overwhelming. Learn the basics with our new AWS cheat sheet – this time on EC2!

 
LVL 39

Expert Comment

by:gdemaria
ID: 40009512
IDdellist is a coldfusion variable, but you don't have it enclosed in #s so the code things it's a column name in the SQL statement...

          where IssueID = #url.IDP# and FileID not in (IDdellist)

Should be...

          where IssueID = #url.IDP# and FileID not in (#IDdellist#)
0
 

Author Comment

by:lulu50
ID: 40009561
gdemaria,

Thank you

is there a way to put do this:
can I loop and store my tempID in a list?

 <cfloop from="1" to="3" index="cc">
 <cfif StructKeyExists(form,"TempID" & cc) && len(trim(Form["TempID" & cc])) && #Form["TempID" & cc]# neq "">
  <cfset Flist=ValueList(Form["TempID" & cc],",")>>  
 </cfif>
 </cfloop>

Open in new window

0
 

Author Comment

by:lulu50
ID: 40009589
I need to be able to store the list and do the select based on the stored list.

but I have an error:

Complex constructs are not supported with function ValueList.  
Use simple variable instead.

not sure how to fix this


 <cfloop from="1" to="3" index="cc">
 <cfif StructKeyExists(form,"TempID" & cc) && len(trim(Form["TempID" & cc])) && #Form["TempID" & cc]# neq "">
  <cfset Flist=ValueList(Form["TempID" & cc],",")>  
 </cfif>
 </cfloop>
 
 
 	<cfquery name = "QallDeletefiles"  datasource="#strDSN#" username="#strUID#" password="#strPWD#" result="de">		
  		select FileID from Files 
        where FileID in (#Flist#)
    </cfquery>

Open in new window

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 40009607
valueList() is a function whose argument must be the name of a cfquery and a column selected in that query.    valueList(MyQuery.report_ID)

You are using it with a form variable, which is causing the error.   Are you trying to append the new value onto the list?

<cfset flist = listAppend(FList, Form["TempID" & cc]  )>
0
 

Author Comment

by:lulu50
ID: 40009622
gdemaria,

hmmm no wonder why my valuelist would work with query but not with my forms.

Thank you for explaining that to me.

I do want to append the list.  

but I get this error now

Variable FLIST is undefined.  
 
 
The error occurred in D:/inetpub/wwwroot-dev/mpdb/Impact/SubEntry.cfm: line 1274
Called from D:/inetpub/wwwroot-dev/mpdb/Impact/SubEntry.cfm: line 19
Called from D:/inetpub/wwwroot-dev/mpdb/Impact/SubEntry.cfm: line 18
 
1272 :  <cfloop from="1" to="3" index="cc">
1273 :  <cfif StructKeyExists(form,"TempID" & cc) && len(trim(Form["TempID" & cc])) && #Form["TempID" & cc]# neq "">
1274 :  <cfset FList = listAppend(FList, Form["TempID" & cc]  )> 
1275 :  </cfif>
1276 :  </cfloop>
 

Open in new window

0
 

Author Comment

by:lulu50
ID: 40009652
can I do this

<CFSET temp = "">

<!---Begin insert and update files --->
 <cfloop from="1" to="3" index="cc">
 <cfif StructKeyExists(form,"TempID" & cc) && len(trim(Form["TempID" & cc])) && #Form["TempID" & cc]# neq "">
 <cfset FList = listAppend(temp, Form["TempID" & cc]),",">
 </cfif>
 </cfloop>
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 2000 total points
ID: 40009679
Before the CFLOOP, you need to just define the Flist as empty

<cfset FList = "">

This is because here, you are using it before you assign it. (It is inside the ListAppend)
So it must exist before getting there...

 <cfset FList = listAppend(FList, Form["TempID" & cc]  )>
0
 

Author Comment

by:lulu50
ID: 40009690
oh gdemaria,

it's working!!!!!

ah I spent all day on it.

Thank you again,
0
 

Author Closing Comment

by:lulu50
ID: 40009691
Thank you
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

721 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