Handle Database Null values in jQuery DataTable Plugin in ASP.Net

Hi manionasp,

Add defaultContent in the options while initializing the DataTable plugin.

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

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

HTML

<div class="container">
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" ClientIDMode="Static">
        <Columns>
            <asp:TemplateField HeaderText="ID">
                <ItemTemplate>
                    <asp:Label runat="server" Text='<%#Eval("CustomerID") %>'> </asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Name">
                <ItemTemplate>
                    <asp:Label runat="server" Text='<%#Eval("ContactName") %>'>
                    </asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Region">
                <ItemTemplate>
                    <asp:Label runat="server" Text='<%#Eval("Region") %>'>
                    </asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>
        <EmptyDataTemplate>
            No Record Available
        </EmptyDataTemplate>
    </asp:GridView>
</div>
<script type="text/javascript" src="https://code.jquery.com/jquery-1.10.2.js"></script>
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.12/css/jquery.dataTables.min.css" />
<link rel="stylesheet" href="https://cdn.datatables.net/buttons/1.2.2/css/buttons.dataTables.min.css" />
<script src="http://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.10.2.min.js" type="text/javascript"></script>
<script type="text/javascript" src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/buttons/1.2.2/js/dataTables.buttons.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jszip/2.5.0/jszip.min.js"></script>
<script type="text/javascript" src="https://cdn.rawgit.com/bpampuch/pdfmake/0.1.18/build/pdfmake.min.js"></script>
<script type="text/javascript" src="https://cdn.rawgit.com/bpampuch/pdfmake/0.1.18/build/vfs_fonts.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/buttons/1.2.2/js/buttons.html5.min.js"></script>
<script type="text/javascript">
    $(document).ready(function () {
        $.ajax({
            type: "POST",
            url: '<%=ResolveUrl("Default.aspx/GetCustomers") %>',
            dataType: "json",
            data: {},
            contentType: "application/json",
            success: function (data) {
                displayTable(data.d);
            },
            error: function (result) {
                alert("Error login");
            }
        });
    });
    function displayTable(data) {
        $('#GridView1').prepend($("<thead></thead>").append($('#GridView1').find("tr:first"))).DataTable({
            data: JSON.parse(data),
            "columns": [
                { "data": "CustomerID", "defaultContent": "<b>N/A</b>" },
                { "data": "ContactName", "defaultContent": "<b>N/A</b>" },
                { "data": "Region", "defaultContent": "<b>N/A</b>" }
            ]
        });
    }
</script>

Namespaces

using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using Newtonsoft.Json;
using System.Web.Services;

Code

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        DataTable dt = GetData().Clone();
        dt.Rows.Add();
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }
}

[WebMethod(EnableSession = true)]
public static string GetCustomers()
{
    string JSONString = string.Empty;
    JSONString = JsonConvert.SerializeObject(GetData());
    return JSONString;
}

private static DataTable GetData()
{
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    string query = "SELECT CustomerID,ContactName,Region FROM Customers";
    SqlCommand cmd = new SqlCommand(query);
    using (SqlConnection con = new SqlConnection(conString))
    {
        using (SqlDataAdapter sda = new SqlDataAdapter())
        {
            cmd.Connection = con;
            sda.SelectCommand = cmd;
            using (DataTable dt = new DataTable())
            {
                sda.Fill(dt);
                return dt;
            }
        }
    }
}

Screenshot