How to sort listbox items and save this sorting in database RSS

10 replies

Last post Aug 11, 2020 09:25 PM by Learner94

‹ Previous Thread|Next Thread ›

Shortcuts

- Reply
Learner94
None

0 Points

12 Posts

How to sort listbox items and save this sorting in database

Jul 31, 2020 11:58 AM|Learner94|LINK

Learner94

None

0 Points

12 Posts

Re: How to sort listbox items and save this sorting in database

Jul 31, 2020 11:59 AM|Learner94|LINK

now this is the code in .aspx file

<div>
                <center><asp:DataList ID="DataList1" runat="server" RepeatColumns="5" CellPadding="4" CellSpacing="10"  OnItemCommand="DataList1_ItemCommand">
                <ItemTemplate>
                    <table width="90%">
                        <tr>
                            <td>
                               <asp:ImageButton ID="ImageButton1" CommandArgument='<%#Eval("D_NAME") %>' runat="server" Height="300px" Width="250px" ImageUrl='<%#Eval("D_IMAGE") %>' CommandName="viewdetail" /> <br /></td>
                            <td><br /></td>
                        </tr>
                    </table>
                </ItemTemplate>
                </asp:DataList></center>
            </div>

and this s the code in .cs file

 SqlCommand cmd = new SqlCommand();
            SqlConnection con = new SqlConnection();
            SqlDataReader sdr;
            DataSet d = new DataSet();
           // string[] dno, dimage;
            int i = 0;
            protected void Page_Load(object sender, EventArgs e)
            {
                if (Session["user_id"] == null)
                {
                    Response.Redirect("login.aspx");
                }
                try
                {
                    if (!IsPostBack)
                    {
                        con.ConnectionString = "Data Source=SHAHZAIB;Initial Catalog=CHILDREN_ISLAM;Integrated Security=True";
                        con.Open();
                        string q = "select * from DUAS inner join Selected_List on DUAS.DNO=Selected_List.DNO where Selected_List.U_ID='"+ Session["user_id"] + "'";
                        //cmd = new SqlCommand(q, con);
                        //sdr = cmd.ExecuteReader();
                        SqlDataAdapter sda = new SqlDataAdapter(q, con);
                        DataTable dt = new DataTable();
                        sda.Fill(dt);
                        DataList1.DataSource = dt;
                        DataList1.DataBind();
                        // dno[i] = sdr["DNO"].ToString();
                        //dimage[i] = sdr["D_IMAGE"].ToString();
                        //daudio[i] = sdr["D_AUDIO"].ToString();
    
                        //CheckBoxList2.DataValueField = "DNO";
                        ////CheckBoxList1.DataValueField = "D_IMAGE";
                        //CheckBoxList2.DataTextField = "D_IMAGE";
                        //// CheckBoxList1.DataValueField = "D_AUDIO";
                        ////DataTable dt = new DataTable();
                        ////sda.Fill(dt);
                        ////string[] a = { "a", "b", "c" };
                        //CheckBoxList2.DataSource = sdr;
                        //CheckBoxList2.DataBind();
                        i++;
    
                    }
    
                    con.Close();
                }
                catch (Exception ex)
                {
    
                    Response.Write(ex.ToString());
                }
    
            }
    
    
    
            protected void DataList1_ItemCommand(object source, DataListCommandEventArgs e)
            {
                string DNO = e.CommandArgument.ToString();
                Response.Redirect("duacontainer.aspx?e=" + DNO);
    
    
    
    
            }



what i simply want to do is to drag one of the listbox item which is being displayed and if i place it on another listbox it should be replaced with that and this sorting should be saved in database as soon as its replaced
i know i can use jquery draggable or sortable but i don't know how to store it in database so that when user logs in again he get to see the sorting he did last time

Learner94

None

0 Points

12 Posts

Re: How to sort listbox items and save this sorting in database

Jul 31, 2020 07:09 PM|Learner94|LINK

i am using this javascript function and its working fine to interchange the pics displayed

<script >
        (function () {
            var droppableParent;

            $('ul .element').draggable({
                revert: 'invalid',
                revertDuration: 200,
                start: function () {
                    droppableParent = $(this).parent();

                    $(this).addClass('being-dragged');
                },
                stop: function () {
                    $(this).removeClass('being-dragged');
                }
            });

            $('ul li').droppable({
                hoverClass: 'drop-hover',
                drop: function (event, ui) {
                    var draggable = $(ui.draggable[0]),
                        draggableOffset = draggable.offset(),
                        container = $(event.target),
                        containerOffset = container.offset();

                    $('.element', event.target).appendTo(droppableParent).css({ opacity: 0 }).animate({ opacity: 1 }, 200);

                    draggable.appendTo(container).css({ left: draggableOffset.left - containerOffset.left, top: draggableOffset.top - containerOffset.top }).animate({ left: 0, top: 0 }, 200);
                }
            });
        }());


      </script>

and this is the code in .aspx

  <ul>
    <li>
        <div class="element">
    <asp:LinkButton ID="ImageButton1"  CommandArgument='<%#Eval("D_NAME") %>' runat="server" >
            <asp:Image ID="Image1" runat="server" Height="300px" Width="250px" ImageUrl='<%#Eval("D_IMAGE") %>'/>

    </asp:LinkButton>
        

        </div>
    </li>
                     
                     </ul>

now i just need to somehow save this position in database as soon as images are interchanged but i cannot think of a way to do this.

XuDong Peng

Member

690 Points

214 Posts

Re: How to sort listbox items and save this sorting in database

Aug 04, 2020 05:03 AM|XuDong Peng|LINK

Hi learner94,

According to your description, you could try to use serialize to format the string to send to your database update script.

Something like this:

<head runat="server">
    <title></title>
    <script src="https://code.jquery.com/jquery-1.12.4.js"></script>
    <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
    <script>
        $(function () {
            $("#sortable").sortable({
                stop: function (event, ui) {
                    alert($(this).sortable('serialize'));
                }
            });
            $("#sortable").disableSelection();
        });
    </script>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <ul id="sortable">
                <li id="order_1" class="ui-state-default"><span class="ui-icon ui-icon-arrowthick-2-n-s"></span>Item 1</li>
                <li id="order_2" class="ui-state-default"><span class="ui-icon ui-icon-arrowthick-2-n-s"></span>Item 2</li>
                <li id="order_3" class="ui-state-default"><span class="ui-icon ui-icon-arrowthick-2-n-s"></span>Item 3</li>
                <li id="order_4" class="ui-state-default"><span class="ui-icon ui-icon-arrowthick-2-n-s"></span>Item 4</li>
                <li id="order_5" class="ui-state-default"><span class="ui-icon ui-icon-arrowthick-2-n-s"></span>Item 5</li>
                <li id="order_6" class="ui-state-default"><span class="ui-icon ui-icon-arrowthick-2-n-s"></span>Item 6</li>
                <li id="order_7" class="ui-state-default"><span class="ui-icon ui-icon-arrowthick-2-n-s"></span>Item 7</li>
            </ul>
        </div>
    </form>
</body>

Split this string to get the order after the interchange, and store it in the database.

When binding the data source to the DataList, items are displayed on the page according to the order.

Please refer to this similar case, it may be helpful to you.

Best regards,

Xudong Peng

- Reply
Learner94
None

0 Points

12 Posts

Re: How to sort listbox items and save this sorting in database

Aug 04, 2020 08:05 AM|Learner94|LINK
How do i do that? My list is displayed by a for loop that iterates over all the items in the table how do i get to display them in order? Do i need to add a new column?
Plus i think the code which you shared doesn't interchange two pics it only places the pic in the position to which its dragged but the picture that was already in that place doesn't get placed to the picture from where the original pic was dragged.

XuDong Peng

Member

690 Points

214 Posts

Re: How to sort listbox items and save this sorting in database

Aug 05, 2020 09:07 AM|XuDong Peng|LINK

Hi Learner94,

It seems a bit troublesome to use DataList, but using Repeater can easily achieve your requirements:

Add a new column to the table to store the order mark.
After the page moves the element, use ajax in the stop callback function to store the changed order in the database.
Use the ItemDataBound function to manually place the pictures in this order.

Please refer to below code:

Page code:

<head runat="server">
    <title></title>
    <script src="https://code.jquery.com/jquery-1.12.4.js"></script>
    <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
    <script>
        $(document).ready(function () {
            $('html').css('font-size', '30px');
            var items = $('#sortable li');
            items.each(function (index, li) {
                $(this).attr('id', 'order_' + (index + 1));
            });
        })

        $(function () {
            $("#sortable").sortable({
                stop: function (event, ui) {
                    var order = $(this).sortable('serialize');
                    order = order.replace('order[]=', '');
                    do {
                        order = order.replace('&order[]=', ' ');
                    } while (order.includes('&order[]='));
                    $.ajax({
                        url: 'SiteMapPath.aspx/updateOrder',
                        type: 'POST',
                        contentType: "application/json; charset=utf-8",
                        dataType: 'json',
                        data: "{'newOrder':'" + order+"'}",
                        success: function (res) {
                            console.log(res);
                            console.log('update order success');
                        },
                        error: function () {
                            alert('something wrong')
                        }
                    });
                    //alert($(this).sortable('serialize'));
                }
            });
            
            $("#sortable").disableSelection();
        });
    </script>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <%--<asp:DataList ID="ImageDataList" OnItemDataBound="ImageDataList_ItemDataBound" runat="server">
                <ItemTemplate>
                    <asp:ImageButton ID="ImageButton1" CommandArgument='<%#Eval("D_NAME") %>' runat="server" Height="100px" Width="100px" ImageUrl='<%#Eval("D_IMAGE") %>' CommandName="viewdetail" />
                </ItemTemplate>
            </asp:DataList>--%>

            <ul id="sortable">
                <asp:Repeater runat="server" ID="Repeater1" OnItemDataBound="Repeater1_ItemDataBound">
                    <ItemTemplate>
                        <li class="ui-state-default">
                            <asp:ImageButton ID="ImageButton1" CommandArgument='<%#Eval("D_NAME") %>' runat="server" Height="100px" Width="100px" ImageUrl='<%#Eval("D_IMAGE") %>' CommandName="viewdetail" />
                        </li>
                    </ItemTemplate>
                </asp:Repeater>
            </ul>
        </div>
    </form>
</body>

public static DataTable dt;
        public static List<int> list;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                dt = new DataTable();
                list = new List<int>();
            }
            getList();
            Repeater1.DataSource = dt;
            Repeater1.DataBind();
        }

        public void getList() {
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ToString()))
            {
                string query = "select a.*,b.ImgOrder from ImgTB a,ImgUser b";
                using (SqlCommand cmd = new SqlCommand(query, conn))
                {
                    conn.Open();
                    SqlDataAdapter sda = new SqlDataAdapter(cmd);
                    sda.Fill(dt);
                    string orderList = dt.Rows[0].Field<string>("ImgOrder");
                    string[] vs = orderList.Split(' ');
                    foreach (string order in vs)
                    {
                        list.Add(int.Parse(order));
                    }
                }
            }
        }

        [WebMethod]
        public static void updateOrder(string newOrder) {
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ToString()))
            {
                string query = "update ImgUser set ImgOrder='"+ newOrder + "'";
                using (SqlCommand cmd = new SqlCommand(query, conn))
                {
                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
            }
        }

protected void Repeater1_ItemDataBound(object sender, RepeaterItemEventArgs e)
        {
            ImageButton imagebtn = e.Item.FindControl("ImageButton1") as ImageButton;
            imagebtn.CommandArgument = dt.Rows[list[0] - 1].Field<string>("D_NAME");
            imagebtn.ImageUrl = dt.Rows[list[0] - 1].Field<string>("D_IMAGE");
            list.RemoveAt(0);
        }

Result:

Hope this can help you.

Best regards,

Xudong Peng

- Reply
Learner94
None

0 Points

12 Posts

Re: How to sort listbox items and save this sorting in database

Aug 06, 2020 08:10 AM|Learner94|LINK

thankyou so much for the detailed answer just two concerns i have?

first i think your javascript i think doesn't interchange two pics what it does(according to me) is that if we drag a pic to another pic it will place the dragged pic there but it won't change the other pic back to the place from where the pic was dragged.

2nd i didn't really get how are you doing sorting?is it according to the name of the pic?i mean if pics have different name then sorting column will store these names in a order?

i am attaching a gif to show my code and the output it produces so far

https://drive.google.com/file/d/1SpbX_3t5qVyAdh51sPWOkDuK4B4Ui-m_/view?usp=sharing

https://drive.google.com/file/d/1MFAIpf8SQ1lo-2Cq8Z4mrfFeDhWy24XN/view?usp=sharing0

XuDong Peng

Member

690 Points

214 Posts

Re: How to sort listbox items and save this sorting in database

Aug 07, 2020 09:01 AM|XuDong Peng|LINK

Hi Learner94,

Each user has a different order setting, so the storage order field should be stored in the user-related table.
Swap the page elements in the <li> tag.
After the exchange is complete, intercept the relevant data and store it in the database using ajax. (Image's src attribute + order of <li>)
Reload the page and rebind the control data source.

For more details, please refer to the following code:

<head runat="server">
    <title></title>
    <script src="Scripts/jquery-3.5.1.min.js"></script>
    <script src="Scripts/jquery-ui-1.11.4.min.js"></script>
    <script>
        $(document).ready(function () {
            var items = $('#sortable li');
            items.each(function (index, li) {
                $(this).attr('id', 'order_' + (index + 1));
            });
        })   
    </script>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <%-- <asp:DataList ID="ImageDataList" OnItemDataBound="ImageDataList_ItemDataBound" RepeatColumns="3" runat="server">
                <ItemTemplate>
                    <asp:ImageButton ID="ImageButton1" CommandArgument='<%#Eval("D_NAME") %>' runat="server" Height="100px" Width="100px" ImageUrl='<%#Eval("D_IMAGE") %>' CommandName="viewdetail" />
                </ItemTemplate>
            </asp:DataList>--%>

            <ul id="sortable">
                <asp:Repeater runat="server" ID="Repeater1" OnItemDataBound="Repeater1_ItemDataBound">
                    <ItemTemplate>
                        <li class="element" style="display:inline">
                            <asp:LinkButton ID="ImageButton1" CommandArgument='<%#Eval("D_NAME") %>' runat="server">
                                <asp:Image ID="Image1" runat="server" Height="100px" Width="100px" ImageUrl='<%#Eval("D_IMAGE") %>' />
                            </asp:LinkButton>
                        </li>
                    </ItemTemplate>
                </asp:Repeater>
            </ul>
        </div>
    </form>
    <script>
        var droppableParent;
        $('ul li').draggable({
            revert: 'invalid',
            revertDuration: 200,
            snap: "ul li",
            start: function () {
                droppableParent = $(this).parent();
                droppableParent.addClass('being-dragged');
            },
            stop: function () {
                droppableParent.removeClass('being-dragged');
            }
        });

        $("#sortable li").droppable({
            accept:'#sortable li',
            drop: function (event, ui) {
                var dropped = ui.draggable;
                var droppedOn = $(this);
                var child = droppedOn.children().clone();

                //swap the page elements in the <li> tag.
                droppedOn.children().remove();
                droppedOn.append(dropped.children());

                dropped.children().remove();
                dropped.append(child);
 
                var orderList = '';
                var items = $('#sortable li');
                items.each(function (index, li) {
                    if ($(this).attr('id') != undefined)
                        orderList += $(this).children('a').children('img').attr('src')+$(this).attr('id') + ' ';
                });
                //data split by space
                //orderList => img/1.png1 img/2.png2 img/3.png3 img/4.png4
                do {
                    orderList = orderList.replace('order_','');
                } while (orderList.includes('order_'));

                //call ajax webmethod to update the database
                $.ajax({
                    url: 'SiteMapPath.aspx/updateOrder',
                    type: 'POST',
                    contentType: "application/json; charset=utf-8",
                    dataType: 'json',
                    data: "{'newOrder':'" + orderList + "'}",
                    success: function (res) {
                        console.log('update order success');
                        //reload the page and rebind the control datasource
                        location.reload();
                    },
                    error: function () {
                        alert('something wrong')
                    }
                });
            }
        });
    </script>
</body>

        public static DataTable dt;
        public static List<string> srclist;
        public static List<int> sortlist;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                dt = new DataTable();
                srclist = new List<string>();
                sortlist = new List<int>();
            }
            getList();
            Repeater1.DataSource = dt;
            Repeater1.DataBind();
        }

        public void getList()
        {
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ToString()))
            {
                string query = "select a.*,b.ImgOrder from ImgTB a,ImgUser b";
                using (SqlCommand cmd = new SqlCommand(query, conn))
                {
                    conn.Open();
                    SqlDataAdapter sda = new SqlDataAdapter(cmd);
                    sda.Fill(dt);
                    string orderList = dt.Rows[0].Field<string>("ImgOrder");
                    string[] vs = orderList.Split(' ');
                    foreach (string order in vs)
                    {
                        if (order != null && order.Trim() != "") { 
                            //Split the queried data, save the image source and its order separately
                            srclist.Add(order.Substring(0,order.IndexOf(".png") + 4));
                            sortlist.Add(int.Parse(order.Substring(order.IndexOf(".png") + 4)));
                        }
                    }
                }
            }
        }

        [WebMethod]
        public static void updateOrder(string newOrder)
        {
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ToString()))
            {
                string query = "update ImgUser set ImgOrder='" + newOrder + "'";
                using (SqlCommand cmd = new SqlCommand(query, conn))
                {
                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
            }
        }

        protected void Repeater1_ItemDataBound(object sender, RepeaterItemEventArgs e)
        {
            LinkButton imagebtn = e.Item.FindControl("ImageButton1") as LinkButton;
            Image Image1 = e.Item.FindControl("Image1") as Image;

            imagebtn.CommandArgument = dt.Rows[sortlist[0] - 1].Field<string>("D_NAME");
            Image1.ImageUrl = srclist[0];
            srclist.RemoveAt(0);
            sortlist.RemoveAt(0);
        }

Result:

Hope it will help you.

Best regards,

Xudong Peng

- Reply
Learner94
None

0 Points

12 Posts

Re: How to sort listbox items and save this sorting in database

Aug 11, 2020 10:48 AM|Learner94|LINK

thanks i did everything as per your instructions but still its not saving the sorted order in the db...what i saw from debugger is that its not returning newOrder in updateOrder function i don't know what's the issue i am attaching the gif for you to see

pic
- Reply
Learner94
None

0 Points

12 Posts

Re: How to sort listbox items and save this sorting in database

Aug 11, 2020 12:48 PM|Learner94|LINK

thanks but i tried every thing but still unable to store new order in the database

when i used debugger i see updateOrder function is not getting newOrder from ajax i am attaching a gif for you to see the issue

i manually placed image order in the database to see if it shows the sorted list and it is showing the sorted list but its not saing the sorted list in database

Pic1
- Reply
Learner94
None

0 Points

12 Posts

Re: How to sort listbox items and save this sorting in database

Aug 11, 2020 09:25 PM|Learner94|LINK


<blockquote>
<h4>XuDong Peng</h4>


<img src="https://i.stack.imgur.com/qkfo5.gif">

</blockquote>

with your help i almost did everything but there are only 2 issue left now
1-)as according to my project requirements i need to pass name of the picture to a function from where i am passing it as an argument to open another page according to this name i am attaching the gif below
now s you can see name is going from asp:LinkButton but the asp:image is displayed from Image
now if i click to a image its passing wrong name because i think images are displayed from asp:image but when i click on image it passes link of the image from asp:LinkButton which is of another image its going to listbound repeater both timesveven if i drag nd drop 1bpic from another pic or if i just click on a single pic. I want to pass pic name as parameter when its clicked but its not happening 
<img src="https://i.imgur.com/AFK5q72.gif" alt="image" width="1000" height="529">
2-)how do i store the image order the very first time when user is adding the duas in the table s when adding these duas in the table user is only selecting the images which are inserted in the database in table named(SelectedList) but i think you cannot
possibly know what i am talking about 
so i think we should focus on the first issue 
we can come to second issue after the first one is resolved...waiting for your answer

‹ Previous Thread|Next Thread ›