Solved

put the @@identity  into a list

Posted on 2014-04-18
12
171 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 15

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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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 …
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…

943 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now