Edit Update row in jQuery DataTables using Ajax JSON and WebService in ASP.Net

Last Reply 5 months ago By dharmendr

Posted 5 months ago

how can we edit update row in jquery datatable using ajax json.

below is the code copied from 

Display binary Images inside jQuery DataTable using Ajax JSON in ASP.Net 

<script type="text/javascript" src="https://code.jquery.com/jquery-1.11.1.min.js"></script>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.7/css/jquery.dataTables.min.css" />
<script type="text/javascript" src="https://cdn.datatables.net/1.10.7/js/jquery.dataTables.min.js"></script>
<script type="text/javascript">
    $(document).ready(function () {
        $.ajax({
            url: '<%= Page.ResolveUrl("~/WebService.asmx/GetFiles")%>',
            method: 'post',
            dataType: 'json',
            success: function (data) {
                $('#datatable').dataTable({
                    paging: true,
                    sort: true,
                    pageLength: 3,
                    searching: true,
                    data: data,
                    columns: [
                        { 'data': 'Id' },
                        { 'data': 'Name' },
                        {
                            'data': 'Data',
                            'sortable': false,
                            'searchable': false,
                            'render': function (Data) {
                                if (!Data) {
                                    return 'N/A';
                                } else {
                                    var img = 'data:image/png;base64,' + Data;
                                    return '<img src="' + img + '" height="50px" width="50px" />';
                                }
                            }
                        }
                    ]
                });
            }, error: function (response) {
                alert(response.responseText);
            }
        });
    });
</script>
<div style="width: 100%; border: 1px solid black; padding: 3px">
    <table id="datatable">
        <thead>
            <tr>
                <th>Id</th>
                <th>Name</th>
                <th>Image</th>
            </tr>
        </thead>
        <tfoot>
            <tr>
                <th>Id</th>
                <th>Name</th>
                <th>Image</th>
            </tr>
        </tfoot>
    </table>
</div>

 

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Script.Serialization;
using System.Web.Services;
 
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
[System.Web.Script.Services.ScriptService]
public class WebService : System.Web.Services.WebService
{
    public class File
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Data { get; set; }
    }
 
    [WebMethod]
    public void GetFiles()
    {
        string cs = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        List<File> files = new List<File>();
        using (SqlConnection con = new SqlConnection(cs))
        {
            SqlCommand cmd = new SqlCommand("SELECT id,Name,Data FROM tblFiles WHERE ContentType = 'image/jpeg'", con);
            cmd.CommandType = CommandType.Text;
            con.Open();
            SqlDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                File file = new File();
                file.Id = Convert.ToInt32(rdr["id"]);
                file.Name = rdr["Name"].ToString();
                file.Data = Convert.ToBase64String((byte[])rdr["Data"]);
                files.Add(file);
            }
        }
        JavaScriptSerializer js = new JavaScriptSerializer();
        Context.Response.Write(js.Serialize(files));
    }
}

 

Posted 5 months ago

Hi jovceka,

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

Database

For this example i have used table named tblFiles whose schema is defined as follows.

For save record in databse refer below article.

Save and Retrieve images from database in ASP.Net

HTML

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.2/css/bootstrap.min.css" />
    <script type="text/javascript" src="https://code.jquery.com/jquery-1.11.1.min.js"></script>
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.7/css/jquery.dataTables.min.css" />
    <script type="text/javascript" src="https://cdn.datatables.net/1.10.7/js/jquery.dataTables.min.js"></script>
    <script type="text/javascript">
        $(document).ready(function () {
            $.ajax({
                url: '<%= Page.ResolveUrl("~/WebServiceCS.asmx/GetFiles")%>',
                method: 'post',
                dataType: 'json',
                success: function (data) {
                    $('#datatable').dataTable({
                        paging: true,
                        sort: true,
                        pageLength: 3,
                        searching: true,
                        data: data,
                        columns: [
                            { 'data': 'Id' },
                            { 'data': 'Name' },
                            {
                                'data': 'Data',
                                'sortable': false,
                                'searchable': false,
                                'render': function (Data) {
                                    if (!Data) {
                                        return 'N/A';
                                    }
                                    else {
                                        var img = 'data:image/png;base64,' + Data;
                                        return '<img src="' + img + '" height="50px" width="50px" />';
                                    }
                                }
                            },
                            {
                                "data": null,
                                "defaultContent": '<input type="button" id="btnEdit" class="btn btn-primary" value="Edit" />'
                            }
                        ]
                    });
                }, error: function (response) {
                    alert(response.responseText);
                }
            });

            $('body').on('click', '[id*=btnEdit]', function () {
                var data = $(this).parents('tr').find('td');
                $('[id*=tbtUpdate]').show();
                var id = data.eq(0).html();
                var name = data.eq(1).html();
                $('[id*=txtId]').val(id);
                $('[id*=txtName]').val(name);
            });
        });
    </script>
</head>
<body style="font-family: Arial">
    <form id="form1" runat="server">
    <table id="tbtUpdate" style="display: none;" class="table">
        <tr>
            <td>Id</td>
            <td><asp:TextBox ID="txtId" runat="server"></asp:TextBox></td>
        </tr>
        <tr>
            <td>Name</td>
            <td><asp:TextBox ID="txtName" runat="server"></asp:TextBox></td>
        </tr>
        <tr>
            <td>Image</td>
            <td><asp:FileUpload ID="fuUpload" runat="server" /></td>
        </tr>
        <tr>
            <td align="center"><asp:Button Text="Update" runat="server" OnClick="Update" class="btn btn-primary" /></td>
            <td></td>
        </tr>
    </table>
    <br />
    <div style="width: 100%; border: 1px solid black; padding: 3px">
        <table id="datatable">
            <thead>
                <tr>
                    <th>Id</th>
                    <th>Name</th>
                    <th>Image</th>
                    <th>Edit</th>
                </tr>
            </thead>
            <tfoot>
                <tr>
                    <th>Id</th>
                    <th>Name</th>
                    <th>Image</th>
                    <th>Edit</th>
                </tr>
            </tfoot>
        </table>
    </div>
    </form>
</body>
</html>

WebService

C#

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Script.Serialization;
using System.Web.Services;
 
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
[System.Web.Script.Services.ScriptService]
public class WebService : System.Web.Services.WebService
{
    public class File
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Data { get; set; }
    }
 
    [WebMethod]
    public void GetFiles()
    {
        string cs = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        List<File> files = new List<File>();
        using (SqlConnection con = new SqlConnection(cs))
        {
            SqlCommand cmd = new SqlCommand("SELECT id,Name,Data FROM tblFiles WHERE ContentType = 'image/jpeg'", con);
            cmd.CommandType = CommandType.Text;
            con.Open();
            SqlDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                File file = new File();
                file.Id = Convert.ToInt32(rdr["id"]);
                file.Name = rdr["Name"].ToString();
                file.Data = Convert.ToBase64String((byte[])rdr["Data"]);
                files.Add(file);
            }
        }
        JavaScriptSerializer js = new JavaScriptSerializer();
        Context.Response.Write(js.Serialize(files));
    }
}

VB.Net

Imports System.Web
Imports System.Collections.Generic
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Script.Serialization
Imports System.Web.Services
Imports System.Web.Services.Protocols
 
' To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
<System.Web.Script.Services.ScriptService()> _
<WebService(Namespace:="http://tempuri.org/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Public Class WebService
    Inherits System.Web.Services.WebService
 
    Public Class File
        Public Property Id As Integer
        Public Property Name As String
        Public Property Data As String
    End Class
 
    <WebMethod()> _
    Public Sub GetFiles()
        Dim cs As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Dim files As List(Of File) = New List(Of File)()
        Using con As SqlConnection = New SqlConnection(cs)
            Dim cmd As SqlCommand = New SqlCommand("SELECT id,Name,Data FROM tblFiles WHERE ContentType = 'image/jpeg'", con)
            cmd.CommandType = CommandType.Text
            con.Open()
            Dim rdr As SqlDataReader = cmd.ExecuteReader()
            While rdr.Read()
                Dim file As File = New File()
                file.Id = Convert.ToInt32(rdr("id"))
                file.Name = rdr("Name").ToString()
                file.Data = Convert.ToBase64String(CType(rdr("Data"), Byte()))
                files.Add(file)
            End While
        End Using
        Dim js As JavaScriptSerializer = New JavaScriptSerializer()
        Context.Response.Write(js.Serialize(files))
    End Sub
End Class

Namespaces

C#

using System.Configuration;
using System.Data.SqlClient;
using System.IO;

VB.Net

Imports System.Configuration
Imports System.Data.SqlClient
Imports System.IO

Code

C#

protected void Update(object sender, EventArgs e)
{
    string contentType = fuUpload.PostedFile.ContentType;
    using (Stream fs = fuUpload.PostedFile.InputStream)
    {
        using (BinaryReader br = new BinaryReader(fs))
        {
            byte[] bytes = br.ReadBytes((Int32)fs.Length);
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("UPDATE tblFiles SET Name=@Name, ContentType=@ContentType, Data=@Data WHERE id=@Id"))
                {
                    cmd.Parameters.AddWithValue("@Id", txtId.Text.Trim());
                    cmd.Parameters.AddWithValue("@Name", txtName.Text.Trim());
                    cmd.Parameters.AddWithValue("@ContentType", contentType);
                    cmd.Parameters.AddWithValue("@Data", bytes);
                    cmd.Connection = con;
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                }
            }
        }
    }
}

VB.Net

Protected Sub Update(ByVal sender As Object, ByVal e As EventArgs)
    Dim contentType As String = fuUpload.PostedFile.ContentType
    Using fs As Stream = fuUpload.PostedFile.InputStream
        Using br As BinaryReader = New BinaryReader(fs)
            Dim bytes As Byte() = br.ReadBytes(CType(fs.Length, Int32))
            Using con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("constr").ConnectionString)
                Using cmd As SqlCommand = New SqlCommand("UPDATE tblFiles SET Name=@Name, ContentType=@ContentType, Data=@Data WHERE id=@Id")
                    cmd.Parameters.AddWithValue("@Id", txtId.Text.Trim())
                    cmd.Parameters.AddWithValue("@Name", txtName.Text.Trim())
                    cmd.Parameters.AddWithValue("@ContentType", contentType)
                    cmd.Parameters.AddWithValue("@Data", bytes)
                    cmd.Connection = con
                    con.Open()
                    cmd.ExecuteNonQuery()
                    con.Close()
                End Using
            End Using
        End Using
    End Using
End Sub

Screenshot