How to output entire dataset but "focus" on a specific known row number

Question

I am currently outputting a dataset as an HTML table with each record linked to a page where that record can be edited. After the edit process the user is taken back to the list with the first record being the one they just edited. Now the client would like to see the entire data set but have the list "focused" on the edited record so that they can scroll either forward or backward from there. It would be best if the dataset didn't have to be reloaded.

I'm using SQL Server row_number() function to establish the row number of the record edited. Also using ColdFusion 2018 server.

<cfquery datasource="census" name="theData">
WITH cte_heads AS (
    SELECT
        ROW_NUMBER() OVER (
        ORDER BY lastname, firstname, year
        ) row_num, refno,firstname,lastname,year, family
        FROM census INNER JOIN relationship ON census.relationship = relationship.relationshipID
        WHERE relationship = 34 AND lastName LIKE '#nameStart#%'
        ) SELECT *
        FROM cte_heads
        WHERE row_num  >= #url.row#

1. You need to show some of your code 2. A partial reload vs a full reload solution is quite different. I would be good to see the code to see what you have tried 3. I would just add some columns with boolean flags for all this. 4. Are you using a javascript library? If so, showing some code would be useful. — James A Mohler, Dec 02 '19 at 02:19
What you described as the client's wish is not clear to me. If it's not clear to you, ask for clarification. If it is, please elaborate. — Dan Bracuk, Dec 02 '19 at 04:57
Recommend using jQuery DataTables, which has excellent inline data editing controls. https://datatables.net/ — Adrian J. Moreno, Dec 02 '19 at 18:36
Make sure that the column in your `ROW_NUMBER() OVER (ORDER BY...` isn't being changed in your update. Otherwise, that column may appear in a different position if the page refreshes. — Shawn, Dec 04 '19 at 16:32
@Shawn Since you are actually referring to the method I am using, I am passing the row_num in the querystring I am using to make the recursive call and it never gets changed. Then I use that url.row to limit the SQL query like WHERE row_num >= #url.row# — Jim Butchart, Dec 05 '19 at 18:30

score 1 · Accepted Answer · answered Dec 02 '19 at 11:42

1

Jim,

We can use jQuery plug-ins to edit the dataset rows in-line.

Here is an example that suits your requirements: https://www.jqueryscript.net/demo/table-editing-creation-bootstable/

Please remember that the editing dataset information on the client-side might lead to data loss or improper data when multiple people using the same page concurrently.

answered Dec 02 '19 at 11:42

Sree Reddy

126
3

Not a problem here. It is for one Admin who has to log in. Will this work with Bootstrap 3? – Jim Butchart Dec 03 '19 at 04:37
I found the previous version of bootstable that works with bootstrap 3 and I got it to work... except to actually update the database table. I will need to experiment on how to actually update the database table using the onEdit:function(). I'm using Coldfusion to query the DB and I have yet to find any documentation on how to build the SQL statement in the onEdit:function(). – Jim Butchart Dec 03 '19 at 06:18
@JimButchart You say client-side editing isn't a problem, but I will point out that if it's an Admin, you still want to validate what they are trying to do before you submit it to your DBMS. Especially for Admins, who likely have more elevated permissions than regular users. Ignoring the Insider-Threat, Admins can make mistakes, too, and their mistakes are much more likely to be serious. – Shawn Dec 04 '19 at 16:29

How to output entire dataset but "focus" on a specific known row number

1 Answers1