lulu50
asked on
put the @@identity into a list
Hi,
How can I put the @@identity into a list?
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)>
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(QSelectParentFil es.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.
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(QSelectParentFil
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.
ASKER
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'.
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>
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#)
where IssueID = #url.IDP# and FileID not in (IDdellist)
Should be...
where IssueID = #url.IDP# and FileID not in (#IDdellist#)
ASKER
gdemaria,
Thank you
is there a way to put do this:
can I loop and store my tempID in a list?
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>
ASKER
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
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>
valueList() is a function whose argument must be the name of a cfquery and a column selected in that query. valueList(MyQuery.report_I D)
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] )>
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] )>
ASKER
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.
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>
ASKER
can I do this
<CFSET temp = "">
<!---Begin insert and update files --->
<cfloop from="1" to="3" index="cc">
<cfif StructKeyExists(form,"Temp ID" & cc) && len(trim(Form["TempID" & cc])) && #Form["TempID" & cc]# neq "">
<cfset FList = listAppend(temp, Form["TempID" & cc]),",">
</cfif>
</cfloop>
<CFSET temp = "">
<!---Begin insert and update files --->
<cfloop from="1" to="3" index="cc">
<cfif StructKeyExists(form,"Temp
<cfset FList = listAppend(temp, Form["TempID" & cc]),",">
</cfif>
</cfloop>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
oh gdemaria,
it's working!!!!!
ah I spent all day on it.
Thank you again,
it's working!!!!!
ah I spent all day on it.
Thank you again,
ASKER
Thank you
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