Solved

put the @@identity  into a list

Posted on 2014-04-18
12
174 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
  • 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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Today, I was working on some optimization and spam-stopping techniques when I encountered Ben Nadel's post to reduce spam feature using Math (http://www.bennadel.com/blog/197-How-I-Stop-Spammers-On-My-ColdFusion-Blog.htm). While this method is not o…
This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

828 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