Move data from one HTML Table to another HTML table (Sorting) and have the information update the database.

ASKER

I am just thinking here.
In the insert.asp pages (There are two of them)
There needs to be a way to grab the record from the List2 table and have it insert into the List table.
There would need to be the first thing.
Then from there, it needs to update the row positioning.

ASKER

When you move items from the bottom list to the top list, the records in the database need to get updated with the new recorded added. (Please read my above post)
Then once added, the Row positioning needs to be updated.

I know my NOT explanation when I wrote the header was not going to give enough info.
That's what we get for posting late at night.

I found the project you are using and see there is an onEnd function

https://codepen.io/padas/pen/JjJmwqM?editors=1010

// Example 2 - Shared lists
new Sortable(example2Left, {
   group: 'shared', // set both lists to same group
   animation: 150,
  onEnd: function (/**Event*/evt) {
      var itemEl = evt.item;  // dragged HTMLElement
    alert(itemEl.id);
   },
 
});

With that in mind, where I have the alert, replace that with calling a function for lack of a better term, "update"

Then the update function can perform the ajax where it sends the id to your server and your asp code can delete the id from the current position and then add the new.

leakim971

as Scott mentioned, you can use the onEnd event (I was a bit lost as you put the jQuery UI library...) :

<script type="text/javascript">
    new Sortable(example2left,{
        group: {
            name: 'shared',
            pull: 'clone'
        },
        animation: 150,
        onEnd: function(event) {
            fetch("update.asp", {
                method: "post",
                headers: {
                    "Accept": "application/json",
                    "Content-Type": "application/x-www-form-urlencoded" //"application/json"
                },
                body: JSON.stringify({id:event.item.id, position:event.newIndex })
            }).then((response) => {
                if(response.ok) {
                    return response.json();
                } else {
                    throw new Error("error");
                }
            }).then((json) => {
                alert(json.message);
            }).catch((response) => alert(response.responseText));
        }
    });
    new Sortable(example2right,{
        group: {
            name: 'shared',
            pull: 'clone'
        },
        animation: 150
    });
</script>

update.asp (not tested and I don't want to wake up my IIS server... :) :

<%'Option Explicit%>
<!--#include file="ACN.asp"-->
<%

Dim id, position

id = Request.Form("id")
position = Request.Form("position")

Set getSQL = CreateObject("ADODB.Command")
getSQL.ActiveConnection = Conn
getSQL.Prepared = true
getSQL.commandtext = "Update lists set PositionOrder=? where ListID=?"
getSQL.Parameters.Append getSQL.CreateParameter("@PositionOrder", 3, 1, , position)
getSQL.Parameters.Append getSQL.CreateParameter("@ListID", 3, 1, , id)
getSQL.execute

Response.Write "{""message"":\""Your listing order has been updated.""}"

%>

ste5an

Just a comment:

What about sending two arrays to your page, let the page do its job using plain JS and no callbacks, and only transmit the final result to you page?

ASKER

@ste5an
So, take all the entries from the 2nd HTML list and move them to the top list.
And after done, let it send to the update database page.
Is that what you are saying?
I will not be against it if there is a way to do the update without the user pressing a button at the end.

@leakim971
With the code, you have provided.
JS code,
Where would this go?
In the Main.asp, or the Sortable2.js or Sortable.js
When I add it to the Main.asp and replace the existing code, I get the following error.
Sortable is not defined.

And for the update.asp
On the existing page, it is this.

getItems = Request.QueryString("favItem[]")
'response.Write getItems
if getItems<>"" then

getItems = Split(getItems, ",")
For j = LBound(getiTems) TO UBound(getItems)
Set getSQL = CreateObject("ADODB.Command")
getSQL.ActiveConnection=Conn
getSQL.Prepared = true
getSQL.commandtext = "Update lists set PositionOrder=? where ListID=?"
getSQL.Parameters.Append getSQL.CreateParameter("@PositionOrder", 3, 1, , j)
getSQL.Parameters.Append getSQL.CreateParameter("@ListID", 3, 1, , getItems(j))
getSQL.execute
next
Response.Write "Your listing order has been updated."&time
end if

Would what you provided take this over, or would it add to it?

@Scott
Thanks for coming in to have a look.
Your code is throwing the same error.
Sortable is not defined
So, when I move a record from the bottom to the top, it is not setting off the alert.
Also, I've placed it on the Main.asp page, replacing the existing code for testing. Is this the correct place for it?

My script is just a very basic example and Leakim did an excellent job expanding on it to give a complete example.

Here is a full static html example. Notice I only have the top part of the table sortable just for testing.

https://jsbin.com/vucodibayi/edit?html,console,output

<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width">
  <title>test</title>


  </head>
<body>
 <table class="table table-bordered" id="shared-lists" style="width:420px; font-size:12pt; color:#000;">
            <tr>
                <th style="width:15%;">Order</th>
                <th style="width:20%;">List ID</th>
                <th style="width:50%;">List Name</th>
                <th style="width:30%;">Edit</th>
            </tr>
            <tbody class="row_position" id="example2left">
           
                <tr data-order="973" data-list_id="973" id="favItem_973" class="dragit">
                <td style="border-bottom:1px double #CCC;">0</td>
                    <td style="border-bottom:1px double #CCC;">973</td>
                    <td style="border-bottom:1px double #CCC;">as</td>
                    <td style="border-bottom:1px double #CCC;">
<button class="btnRemove">Remove</button>
                    </td>
                   
                </tr>
           
                <tr data-order="967" data-list_id="967" id="favItem_967" class="dragit">
                <td style="border-bottom:1px double #CCC;">1</td>
                    <td style="border-bottom:1px double #CCC;">967</td>
                    <td style="border-bottom:1px double #CCC;">here</td>
                    <td style="border-bottom:1px double #CCC;">
<button class="btnRemove">Remove</button>
                    </td>
                   
                </tr>
           
                <tr data-order="969" data-list_id="969" id="favItem_969" class="dragit">
                <td style="border-bottom:1px double #CCC;">2</td>
                    <td style="border-bottom:1px double #CCC;">969</td>
                    <td style="border-bottom:1px double #CCC;">there</td>
                    <td style="border-bottom:1px double #CCC;">
<button class="btnRemove">Remove</button>
                    </td>
                   
                </tr>
           
                <tr data-order="968" data-list_id="968" id="favItem_968" class="dragit">
                <td style="border-bottom:1px double #CCC;">3</td>
                    <td style="border-bottom:1px double #CCC;">968</td>
                    <td style="border-bottom:1px double #CCC;">and</td>
                    <td style="border-bottom:1px double #CCC;">
<button class="btnRemove">Remove</button>
                    </td>
                   
                </tr>
           
                <tr data-order="966" data-list_id="966" id="favItem_966" class="dragit">
                <td style="border-bottom:1px double #CCC;">5</td>
                    <td style="border-bottom:1px double #CCC;">966</td>
                    <td style="border-bottom:1px double #CCC;">goes</td>
                    <td style="border-bottom:1px double #CCC;">
<button class="btnRemove">Remove</button>
                    </td>
                   
                </tr>
           
                <tr data-order="970" data-list_id="970" id="favItem_970" class="dragit">
                <td style="border-bottom:1px double #CCC;">9</td>
                    <td style="border-bottom:1px double #CCC;">970</td>
                    <td style="border-bottom:1px double #CCC;">and</td>
                    <td style="border-bottom:1px double #CCC;">
<button class="btnRemove">Remove</button>
                    </td>
                   
                </tr>
           
                <tr data-order="972" data-list_id="972" id="favItem_972" class="dragit">
                <td style="border-bottom:1px double #CCC;">10</td>
                    <td style="border-bottom:1px double #CCC;">972</td>
                    <td style="border-bottom:1px double #CCC;">here</td>
                    <td style="border-bottom:1px double #CCC;">
<button class="btnRemove">Remove</button>
                    </td>
                   
                </tr>
           
                <tr data-order="965" data-list_id="965" id="favItem_965" class="dragit">
                <td style="border-bottom:1px double #CCC;">11</td>
                    <td style="border-bottom:1px double #CCC;">965</td>
                    <td style="border-bottom:1px double #CCC;">else</td>
                    <td style="border-bottom:1px double #CCC;">
<button class="btnRemove">Remove</button>
                    </td>
                   
                </tr>
           
                <tr data-order="964" data-list_id="964" id="favItem_964" class="dragit">
                <td style="border-bottom:1px double #CCC;">12</td>
                    <td style="border-bottom:1px double #CCC;">964</td>
                    <td style="border-bottom:1px double #CCC;">Something</td>
                    <td style="border-bottom:1px double #CCC;">
<button class="btnRemove">Remove</button>
                    </td>
                   
                </tr>
           
</tbody>


<tr><td colspan="4">List 2 - Move items to Above list</td></tr>
            <tbody class="row_position" id="example2right">
           
                <tr data-order="972" data-list_id="972" id="favItem_972" class="dragit">
                <td style="border-bottom:1px double #CCC;">0</td>
                    <td style="border-bottom:1px double #CCC;">972</td>
                    <td style="border-bottom:1px double #CCC;">Not</td>
                    <td style="border-bottom:1px double #CCC;">


                    </td>
                   
                </tr>
           
                <tr data-order="970" data-list_id="970" id="favItem_970" class="dragit">
                <td style="border-bottom:1px double #CCC;">1</td>
                    <td style="border-bottom:1px double #CCC;">970</td>
                    <td style="border-bottom:1px double #CCC;">Does</td>
                    <td style="border-bottom:1px double #CCC;">


                    </td>
                   
                </tr>
           
                <tr data-order="973" data-list_id="973" id="favItem_973" class="dragit">
                <td style="border-bottom:1px double #CCC;">2</td>
                    <td style="border-bottom:1px double #CCC;">973</td>
                    <td style="border-bottom:1px double #CCC;">Know</td>
                    <td style="border-bottom:1px double #CCC;">


                    </td>
                   
                </tr>
           
                <tr data-order="964" data-list_id="964" id="favItem_964" class="dragit">
                <td style="border-bottom:1px double #CCC;">3</td>
                    <td style="border-bottom:1px double #CCC;">964</td>
                    <td style="border-bottom:1px double #CCC;">This</td>
                    <td style="border-bottom:1px double #CCC;">


                    </td>
                   
                </tr>
           
                <tr data-order="967" data-list_id="967" id="favItem_967" class="dragit">
                <td style="border-bottom:1px double #CCC;">4</td>
                    <td style="border-bottom:1px double #CCC;">967</td>
                    <td style="border-bottom:1px double #CCC;">About</td>
                    <td style="border-bottom:1px double #CCC;">


                    </td>
                   
                </tr>
           
                <tr data-order="966" data-list_id="966" id="favItem_966" class="dragit">
                <td style="border-bottom:1px double #CCC;">5</td>
                    <td style="border-bottom:1px double #CCC;">966</td>
                    <td style="border-bottom:1px double #CCC;">New</td>
                    <td style="border-bottom:1px double #CCC;">


                    </td>
                   
                </tr>
           
                <tr data-order="965" data-list_id="965" id="favItem_965" class="dragit">
                <td style="border-bottom:1px double #CCC;">6</td>
                    <td style="border-bottom:1px double #CCC;">965</td>
                    <td style="border-bottom:1px double #CCC;">Is</td>
                    <td style="border-bottom:1px double #CCC;">


                    </td>
                   
                </tr>
           
                <tr data-order="968" data-list_id="968" id="favItem_968" class="dragit">
                <td style="border-bottom:1px double #CCC;">7</td>
                    <td style="border-bottom:1px double #CCC;">968</td>
                    <td style="border-bottom:1px double #CCC;">Somewhere</td>
                    <td style="border-bottom:1px double #CCC;">


                    </td>
                   
                </tr>
           
                <tr data-order="969" data-list_id="969" id="favItem_969" class="dragit">
                <td style="border-bottom:1px double #CCC;">8</td>
                    <td style="border-bottom:1px double #CCC;">969</td>
                    <td style="border-bottom:1px double #CCC;">Time</td>
                    <td style="border-bottom:1px double #CCC;">


                    </td>
                   
                </tr>
           
</tbody>
       <script src="https://cdnjs.cloudflare.com/ajax/libs/Sortable/1.14.0/Sortable.min.js"></script>


<script>
   var   example2Left = document.getElementById('example2left');


// Example 2 - Shared lists
new Sortable(example2Left, {
   group: 'shared', // set both lists to same group
   animation: 150,
  onEnd: function (/**Event*/evt) {
      var itemEl = evt.item;  // dragged HTMLElement
    alert(itemEl.id);
   },
 
});




  </script>
</body>
</html>

As you move the order of the top half of the table, you will get the alert showing the id. This is one item you can send to the server.

Leakim's code is incorporating an ajax request starting on line 9 and sending a json string that includes the id and new position. (line 15). Then you can see in his asp code how that data is processed.

ASKER

OK.
I see how the ID shows when you drag around items in the TOP list.
However, what is needed is the ability to drag from the bottom list
To the top list.
And then have that saved to the database, with the added record and the sorting order updated with that position.
In your demo, the bottom list is not draggable.
So, I am unclear on how this is going to work.
If I am missing something, please let me know.

lenamtl

Hi.

You may want to take a look to Datatables
This has many cool features like row reorder
https://datatables.net/extensions/rowreorder/

Here is an example
https://datatables.net/extensions/rowreorder/examples/initialisation/ajax.html

And it is possible to add a row from a table to another table (in 2 way up to down and down to up)
check this example (drag the item from table to table)
https://jsbin.com/pogufut/edit?html,js,output

Datatables has a learning curve but it is very powerful and have many cool widgets.

It's not working is a function of jsbin The same code is here https://codepen.io/padas/pen/JjJmwqM

This is the problem with putting too much into a test case :)

Taking Leakim's js code with your html and running it in codepen https://codepen.io/padas/pen/dyRwYYE?editors=0010 and removing the pull:clone is what did the trick as far as removing it from the bottom. I saw this by looking at the main project examples. https://sortablejs.github.io/Sortable/#simple-list See the "Shared" vs "Cloning"

ASKER

Sorry for the lateness in my response.
I have been working my butt off the last week to get money to get parts for my truck.
Got em all, now how to wait to get them in on Tuesday, and time to put it back together, and back on the road after five years.
The car broke down last month, and this truck has GOT to get fixed, which has put everything else on the back burner for right now.

In my example, you can drag from the bottom to the top.
https://ee.cffcs.com/Sortable2/Main.asp

I will take another look at Leakim's code, compare it with mine, and post an update sometime tomorrow, hopefully.
Thanks all.

ASKER

Hello everyone.
Sorry that I have taken so long to revisit this issue.
I decided to retake a look at it, and no matter what I do, I get the same error as before.

Sortable is not defined

So, I have a question for everyone.

I will be porting this project along with many other sites over to vb.net.
So, my question is this.

Is there a better solution for doing this in vb.net (or) C# as I was told C# could be used in VB.net?
If there is not, I will have to figure out how to resolve this error with Sortable and get it working.
I will be waiting for your response.

This is all javascript, so not much to do with back end except of course for accepting the data input and saving the new position to the db.

VB.NET is visual basic. C# would be used in .NET like ASP.NET or .NET Core.

If you have this working in classic asp, and you are going to move it to some flavor of .NET, it is just a matter of updating your back end and leaving the front end as is.

There are 3rd party controls like https://demos.telerik.com/aspnet-core/sortable that are worth checking out. I use telerik myself.

ASKER

Cannot afford the Telerik.
I will continue to work with the project at hand and try to get it working.

Wayne, the majority of that is open source if you just use it as javascript.

lenamtl

Hi,

Have you checked my solution using Datatables (drag and drop)?

example: https://jsbin.com/pogufut/edit?html,js,output

ASKER

Scott
I got the demo working after reading over what you posted, Scott back on "Commented: 2021-09-27"
So this is using Leaks JS.
https://ee.cffcs.com/Sortable3/Main.asp
However, it is not updating the database with the new entries being dragged around the TOP, and it is not updating when you drag from bottom to top. So all around, it is not working. Which Leak did state that it was untested?

Lenamtl
Do you have an example of Datatables updating a database in the way described in this thread?
What I need to do is the following.

Drag from the Bottom rows to the TOP rows. (Not from top to bottom)
When the bottom item is dragged to the TOP and dropped, this will update the database with the new entry, then update the count.

Scott, are you saying that the Telerik Javascript is free to use? Just the components are what you have to pay?

lenamtl

Hi,
The example I posted allow to drag and drop the item from a table to another.
From there you can adapt it and only allow one way, check the columnDefs part in JS.
Once this work, you can add an event that update the DB this can be triggered by click of button or when the table changed.
There also StateSave that can save the state of the table in localstorage or with some code adjusment it can be saved in DB so there are many possibilities of course this will required some custom code.

Datatables has a tons of features, it has a learning curve but this will give you a lot in return.

Yes, there is an open source for most of the components. Not all.

https://www.telerik.com/kendo-ui/core-faq
https://github.com/telerik/kendo-ui-core

https://www.telerik.com/open

https://www.telerik.com/universal-windows-platform-ui
https://github.com/telerik/UI-For-UWP

Everything is front end, threre are no wrappers in the free version and although I use the paid version, I still only use the front end without wrappers. I will admit, it took me about a month of some frustration will working on a project to get the hang of the "kendo way". You will learn as you go not only about kendo but some good front end practices.

If you are just learning .NET, think about sticking with what you know perhaps at first because I know that trying too many new things at once can also be frustrating.

ASKER

I've decided to take this a different route.
Instead of dragging from the bottom table to the top table and having it update (Which would be fantastic)
Instead, I've added a [Add] button to the bottom table, and all you have to do now is click the [Add] button
And then go to the top table and arrange it the way you want it.

What I am working on now, is when you click the [Add] button, it shows the live update in the top table.
If I cannot get it working, I will open a new thread.

Maybe one day in the future, I can revisit this issue and do it the way I want it to be.

Thank you to everyone who has come in to assist in this issue.

lenamtl
I will take a closer look at your suggestions when I have the extra time.

Scott
Same as above, I will take another look at Telerik soon.

ASKER CERTIFIED SOLUTION