Update database column after drag and drop (reordering) ASP.Net GridView row using jQuery AJAX

Hi kdtdc,

Check this example. Now please take its reference and correct your code.

SQL

CREATE TABLE TBLClientData
(
	ClientKey INT, 
	ClientName VARCHAR (50), 
	ClientCode VARCHAR (50), 
	IsActive TINYINT
)
GO
INSERT INTO TBLClientData VALUES (1,'Client 1','Code 1',1)
INSERT INTO TBLClientData VALUES (2,'Client 2','Code 2',1)
INSERT INTO TBLClientData VALUES (3,'Client 3','Code 3',0)
INSERT INTO TBLClientData VALUES (4,'Client 4','Code 4',1)
INSERT INTO TBLClientData VALUES (5,'Client 5','Code 5',0)
INSERT INTO TBLClientData VALUES (6,'Client 6','Code 6',0)
INSERT INTO TBLClientData VALUES (7,'Client 7','Code 7',1)
INSERT INTO TBLClientData VALUES (8,'Client 8','Code 8',0)
GO
SELECT * FROM TBLClientData

HTML

<div class="container">
    <div class="row pad5b">
        <div class="col-sm-4">
            <asp:GridView ID="InactiveClients" runat="server" ShowHeaderWhenEmpty="true" CssClass="drag_drop_grid gridview"
                HeaderStyle-CssClass="gridview-header" AutoGenerateColumns="false">
                <Columns>
                    <asp:TemplateField HeaderText="Key" SortExpression="">
                        <ItemTemplate>
                            <asp:Label ID="ClientKey" runat="server" Text='<%# Bind("ClientKey") %>' />
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Name" SortExpression="">
                        <ItemTemplate>
                            <asp:Label ID="ClientName" runat="server" Text='<%# Bind("ClientName") %>' />
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Code" SortExpression="">
                        <ItemTemplate>
                            <asp:Label ID="ClientCode" runat="server" Text='<%# Bind("ClientCode") %>' />
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="IsActive" SortExpression="">
                        <ItemTemplate>
                            <asp:CheckBox ID="IsActive" runat="server" Checked='<%# Convert.ToBoolean(Eval("IsActive")) %>' Enabled="false" />
                        </ItemTemplate>
                    </asp:TemplateField>
                </Columns>
            </asp:GridView>
        </div>
        <div class="col-sm-4 text-center">
            <div class="row pad5b">
                <div class="col-sm-12"><-- move between grids --></div>
            </div>
        </div>
        <div class="col-sm-4">
            <asp:GridView ID="ActiveClients" ShowHeaderWhenEmpty="true" runat="server" CssClass="drag_drop_grid gridview"
                HeaderStyle-CssClass="gridview-header" AutoGenerateColumns="false">
                <Columns>
                    <asp:TemplateField HeaderText="Key" SortExpression="">
                        <ItemTemplate>
                            <asp:Label ID="ClientKey" runat="server" Text='<%# Bind("ClientKey") %>' />
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Name" SortExpression="">
                        <ItemTemplate>
                            <asp:Label ID="ClientName" runat="server" Text='<%# Bind("ClientName") %>' />
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Code" SortExpression="">
                        <ItemTemplate>
                            <asp:Label ID="ClientCode" runat="server" Text='<%# Bind("ClientCode") %>' />
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="IsActive" SortExpression="">
                        <ItemTemplate>
                            <asp:CheckBox ID="IsActive" runat="server" Checked='<%# Convert.ToBoolean(Eval("IsActive")) %>' Enabled="false" />
                        </ItemTemplate>
                    </asp:TemplateField>
                </Columns>
            </asp:GridView>
        </div>
    </div>
</div>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<link rel="stylesheet" href="https://ajax.googleapis.com/ajax/libs/jqueryui/1.8.24/themes/smoothness/jquery-ui.css" />
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.8.24/jquery-ui.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/json2/20130526/json2.min.js"></script>
<script type="text/javascript">
    $(function () {
        $(".drag_drop_grid").sortable({
            items: 'tr:not(tr:first-child)',
            cursor: 'arrow',
            connectWith: '.drag_drop_grid',
            axis: 'x,y',
            dropOnEmpty: true,
            start: function (e, ui) {
                ui.item.addClass("selected");
            },
            receive: function (e, ui) {
                setTimeout(function () {
                    ui.item.find('[id*=IsActive]').removeAttr('checked');
                    var obj = {};
                    obj.Id = $.trim(ui.item.find('[id*=ClientKey]').html());
                    obj.ClientKey = $.trim(ui.item.find('[id*=ClientKey]').html());
                    if (ui.sender[0].id == "InactiveClients") {
                        obj.IsActive = "true";
                        ui.item.find('[id*=IsActive]').attr('checked', 'checked');
                    }
                    else if (ui.sender[0].id == "ActiveClients") {
                        obj.IsActive = "false";
                        ui.item.find('[id*=IsActive]').removeAttr('checked');
                    }

                    ui.item.removeClass("selected");
                    $.ajax({
                        type: "POST",
                        url: "Default.aspx/SaveClient",
                        data: JSON.stringify({ cdata: obj }),
                        contentType: "application/json; charset=utf-8",
                        dataType: "json"
                    });
                    $(this).find("tbody").append(ui.item);
                    return false;
                }, 100);
            },
        });
    });
</script>

Namespaces

C#

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Script.Services;
using System.Web.Services;

VB.Net

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Script.Services
Imports System.Web.Services

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!Page.IsPostBack)
    {
        string conString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
        using (SqlConnection con = new SqlConnection(conString))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT ClientKey, ClientName, ClientCode, IsActive FROM TBLClientData WHERE IsActive = 0 ORDER BY ClientName", con))
            {
                con.Open();

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);
                InactiveClients.UseAccessibleHeader = true;
                InactiveClients.DataSource = dt;
                InactiveClients.DataBind();
                con.Close();
            }

            using (SqlCommand sqlcmd = new SqlCommand("SELECT ClientKey, ClientName, ClientCode, IsActive FROM TBLClientData WHERE IsActive = 1 ORDER BY ClientName", con))
            {
                con.Open();
                SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
                DataTable dt = new DataTable();
                da.Fill(dt);
                ActiveClients.UseAccessibleHeader = true;
                ActiveClients.DataSource = dt;
                ActiveClients.DataBind();
                con.Close();
            }
        }
    }
}

public class UpdateClient
{
    public string Id { get; set; }
    public string ClientKey { get; set; }
    public string IsActive { get; set; }
}

[WebMethod]
[ScriptMethod]
public static void SaveClient(UpdateClient cdata)
{
    bool isact = Convert.ToBoolean(cdata.IsActive);
    string conString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
    using (SqlConnection con = new SqlConnection(conString))
    {
        using (SqlCommand cmd = new SqlCommand("UPDATE TBLClientData SET IsActive = @IsActive WHERE ClientKey = @ClientKey", con))
        {
            cmd.CommandType = CommandType.Text;
            con.Open();
            cmd.Parameters.AddWithValue("@IsActive", isact);
            cmd.Parameters.AddWithValue("@ClientKey", cdata.ClientKey);
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Page.IsPostBack Then
        Dim conString As String = ConfigurationManager.ConnectionStrings("DefaultConnection").ConnectionString
        Using con As SqlConnection = New SqlConnection(conString)
            Using cmd As SqlCommand = New SqlCommand("SELECT ClientKey, ClientName, ClientCode, IsActive FROM TBLClientData WHERE IsActive = 0 ORDER BY ClientName", con)
                con.Open()
                Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
                Dim dt As DataTable = New DataTable()
                da.Fill(dt)
                InactiveClients.UseAccessibleHeader = True
                InactiveClients.DataSource = dt
                InactiveClients.DataBind()
                con.Close()
            End Using

            Using sqlcmd As SqlCommand = New SqlCommand("SELECT ClientKey, ClientName, ClientCode, IsActive FROM TBLClientData WHERE IsActive = 1 ORDER BY ClientName", con)
                con.Open()
                Dim da As SqlDataAdapter = New SqlDataAdapter(sqlcmd)
                Dim dt As DataTable = New DataTable()
                da.Fill(dt)
                ActiveClients.UseAccessibleHeader = True
                ActiveClients.DataSource = dt
                ActiveClients.DataBind()
                con.Close()
            End Using
        End Using
    End If
End Sub

Public Class UpdateClient
    Public Property Id As String
    Public Property ClientKey As String
    Public Property IsActive As String
End Class

<WebMethod>
<ScriptMethod>
Public Shared Sub SaveClient(ByVal cdata As UpdateClient)
    Dim isact As Boolean = Convert.ToBoolean(cdata.IsActive)
    Dim conString As String = ConfigurationManager.ConnectionStrings("DefaultConnection").ConnectionString
    Using con As SqlConnection = New SqlConnection(conString)
        Using cmd As SqlCommand = New SqlCommand("UPDATE TBLClientData SET IsActive = @IsActive WHERE ClientKey = @ClientKey", con)
            cmd.CommandType = CommandType.Text
            con.Open()
            cmd.Parameters.AddWithValue("@IsActive", isact)
            cmd.Parameters.AddWithValue("@ClientKey", cdata.ClientKey)
            cmd.ExecuteNonQuery()
            con.Close()
        End Using
    End Using
End Sub

Screenshots

Record before update

GridView with Drag and Drop

Record after update