Mark rows displayed and update a table

I have a table that I want to display and let users mark which rows to delete with a Yes/No drop down and then update the table with the tagged rows.

Attached is a basic table display that I'd like to incorporate the above modifications.

Thanks for any assistance.
<cfquery name="listorders"
         datasource="OrderDB"
 username="reportuser" password="GPreports">
select orev.sopnumbe
      , orev.moddate
      , orev.delete
      , orev.SOPUser
      , orev.DynamicsExtUser 
      , orev.DocDate
      , orev.OrdDate
from keleg.dbo.orderreview orev
 
</cfquery>
<cftable query="listorders" colheaders htmltable border>
<cfcol text="#sopnumbe#" header="SOP Number" align="LEFT">
<cfcol text="#moddate#" header="Modified Date" align="LEFT">
<cfcol text="#SOPUser#" header="SOP10100-User" align="LEFT">
<cfcol text="#DynamicsExtUser#" header="Dynamics_EXT-User" align="LEFT">
<cfcol text="#docdate#" header="Document Date" align="LEFT">
<cfcol text="#orddate#" header="Order Date" align="LEFT">
<cfcol text="#delete#" header="Delete" align="LEFT">
 
</cftable>
<hr>

Open in new window

jdr0606Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
ZvonkoConnect With a Mentor Systems architectCommented:
Insert that tag after the cfquery tag with your select.
Like this:
<cfquery name="listorders"
         datasource="OrderDB"
 username="reportuser" password="GPreports">
select orev.sopnumbe
      , orev.moddate
      , orev.RecDelete
      , orev.SOPUser
      , orev.DynamicsExtUser 
      , orev.DocDate
      , orev.OrdDate
from keleg.dbo.orderreview orev
 
</cfquery>
<cfdump var="#listorders#" >

Open in new window

0
 
ZvonkoSystems architectCommented:
First step would be this:

<cfform name="delform" method="post" >
<cftable query="listorders" colheaders htmltable border>
<cfcol text="#sopnumbe#" header="SOP Number" align="LEFT">
<cfcol text="#moddate#" header="Modified Date" align="LEFT">
<cfcol text="#SOPUser#" header="SOP10100-User" align="LEFT">
<cfcol text="#DynamicsExtUser#" header="Dynamics_EXT-User" align="LEFT">
<cfcol text="#docdate#" header="Document Date" align="LEFT">
<cfcol text="#orddate#" header="Order Date" align="LEFT">
 
<cfcol text="<input type='checkbox' name='del' value='#sopnumbe#' > " header="Delete" align="LEFT">
 
</cftable>
<input type="submit" >
</cfform>
<hr>

Open in new window

0
 
jdr0606Author Commented:
Great!

Now how do I write back to the table those that have been flagged to delete?

Thanks
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.

 
ZvonkoSystems architectCommented:
Put this statements before your query named listorders:
<cfif isDefined("FORM.del") >
 <cfloop index="r" list="#FORM.del#" >
   <cfquery name="upd" datasource="OrderDB" >
     UPDATE keleg.dbo.orderreview SET [delete]=1 WHERE sopnumbe = #r#
   </cfquery>
 <cfloop>
</cfif>

Open in new window

0
 
ZvonkoSystems architectCommented:
That will update the delete column in the table but there is no way to chow that value as checkbox in cftable. If you want to show the checked state then you need to change the cftable to cfloop or cfoutput and drow your table by tr and td tags not by cftable.
0
 
jdr0606Author Commented:
I receive the following error when I 'submit'.

Error Diagnostic Information
ODBC Error Code = S0022 (Column not found)
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'K080101JR123'.
The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (35:4) to (36:39).

Thanks!
0
 
ZvonkoSystems architectCommented:
If the value is a String then you have to put it into quotes:

UPDATE keleg.dbo.orderreview SET [delete]=1 WHERE sopnumbe = '#r#'

Also namig your column DELETE was not the best idea ;-)
0
 
ZvonkoSystems architectCommented:
I have found a way to display the checked state:

<cfquery name="check" dbtype="query" >
  SELECT sopnumbe,moddate,SOPUser,DynamicsExtUser,docdate,orddate, 'CHECKED' as chk from listorders where [delete]=1
  UNION
  SELECT sopnumbe,moddate,SOPUser,DynamicsExtUser,docdate,orddate, ' ' as chk from listorders where [delete]<>1
</cfquery>
 
<cfform name="delform" method="post" >
<cftable query="check" colheaders htmltable border>
<cfcol text="#sopnumbe#" header="SOP Number" align="LEFT">
<cfcol text="#moddate#" header="Modified Date" align="LEFT">
<cfcol text="#SOPUser#" header="SOP10100-User" align="LEFT">
<cfcol text="#DynamicsExtUser#" header="Dynamics_EXT-User" align="LEFT">
<cfcol text="#docdate#" header="Document Date" align="LEFT">
<cfcol text="#orddate#" header="Order Date" align="LEFT">
 
<cfcol text="<input type='checkbox' name='del' value='#sopnumbe#' #chk# > " header="Delete" align="LEFT">
 
</cftable>
<input type="submit" >
</cfform>
<hr>

Open in new window

0
 
ZvonkoSystems architectCommented:
Can you show me the query result from your listorders query?
Use this to generate output:


<cfdump var="#listorders#" >
0
 
jdr0606Author Commented:
I had already changed "Delete to "RecDelete"!

Where do I insert the '<cfdump var="#listorders#" >' ?

Thanks
0
 
eszaqCommented:
The way I read original question you need to delete rows from the database, but you need to show which ones were deleted. If so, try code below:

<cfquery name="listorders"
         datasource="OrderDB"
         username="reportuser" password="GPreports">
select orev.sopnumbe
      , orev.moddate
      , orev.delete
      , orev.SOPUser
      , orev.DynamicsExtUser 
      , orev.DocDate
      , orev.OrdDate
from keleg.dbo.orderreview orev 
</cfquery>
 
 
Your output before deleting rows from db table:
 
<cftable query="listorders" colheaders htmltable border>
<cfcol text="#sopnumbe#" header="SOP Number" align="LEFT">
<cfcol text="#moddate#" header="Modified Date" align="LEFT">
<cfcol text="#SOPUser#" header="SOP10100-User" align="LEFT">
<cfcol text="#DynamicsExtUser#" header="Dynamics_EXT-User" align="LEFT">
<cfcol text="#docdate#" header="Document Date" align="LEFT">
<cfcol text="#orddate#" header="Order Date" align="LEFT">
<cfif isDefined("form.del") and len(form.del) and listFind(form.del,sopnumbe)>
 <cfcol text="*DELETED*" header="Delete" align="LEFT">
<cfelse>
 <cfcol text="<input type='checkbox' name='del' value='#sopnumbe#' >" header="Delete" align="LEFT">
</cfif>
</cftable>
 
<p>Now, if any rows are subject to deletion, run delete query in the database:</p>
 
<cfif  isDefined("form.del") and len(form.del)>
 <cfquery name="deleteRows"
         datasource="OrderDB"
         username="reportuser" password="GPreports">
   delete 
   from keleg.dbo.orderreview
   where  sopnumbe in (#form.del#)
</cfquery>
 
</cfif>

Open in new window

0
 
eszaqConnect With a Mentor Commented:
Sorry, missed in my code. Of course your <cftable> should be enclosed inside <form> (or <cfform> tag and must have submit button.
0
 
eszaqCommented:
Do you still need assistance in solving this problem?
0
All Courses

From novice to tech pro — start learning today.