Solved

put the @@identity  into a list

Posted on 2014-04-18
12
170 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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

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 …
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 …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

760 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

22 Experts available now in Live!

Get 1:1 Help Now