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

Last Reply 8 months ago By dharmendr

Posted 8 months ago

I am using Newton JSON to convert my database table which is bound in datatable. Db table has many null values. How to handle the null values?

string JSONString = string.Empty;
JSONString = Newtonsoft.Json.JsonConvert.SerializeObject(myDataTable);
return JSONString;

 

You are viewing reply posted by: dharmendr 8 months ago.
Posted 8 months ago Modified on 8 months ago

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