Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 193
  • Last Modified:

put the @@identity into a list

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
lulu50
Asked:
lulu50
  • 7
  • 4
1 Solution
 
Gurpreet Singh RandhawaWeb DeveloperCommented:
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
 
gdemariaCommented:
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
 
lulu50Author Commented:
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
gdemariaCommented:
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
 
lulu50Author Commented:
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
 
lulu50Author Commented:
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
 
gdemariaCommented:
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
 
lulu50Author Commented:
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
 
lulu50Author Commented:
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
 
gdemariaCommented:
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
 
lulu50Author Commented:
oh gdemaria,

it's working!!!!!

ah I spent all day on it.

Thank you again,
0
 
lulu50Author Commented:
Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now