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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

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
ZvonkoSystems 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
eszaqCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Development Software

From novice to tech pro — start learning today.