How to get the data from start date to end date

Question

How can I find data from database between two dates.

Problem-1. How can I find the data from the database between the two dates if I consider the parameters.

Problem-1. If I use date as parameter then my datatable doesn't show data.

Problem-2. I want to see the sum of the value columns in the footer of my datatable. This function is not working.

Problem-3. How to give validation message if no date is input in input type date.

It would be great if someone could help.

<input type="date" id="first" />

<input type="date" id="last" />

<button onclick="find()" class="btn btn-primary btn-sm">find</button>

<table id="MyDataTable">

<thead>

<tr>

<th> Sl No </th>

<th> Date </th>

<th> Product Name </th>

<th> Value </th>

<th> Action </th>

</tr>

</thead>

<tbody>

</tbody>

<tfoot>

<tr>

<th></th>

<th></th>

<th></th>

<th></th>

<th></th>

</tr>

</tfoot>

</table>

@section Scripts { @{ await Html.RenderPartialAsync("_ValidationScriptsPartial"); } <script type="text/javascript">

function find() { debugger

var objdate = { 'firstdate': $("#first").val(), 'lastdate': $("#last").val() };

$.ajax({

type: "Get",

url: "/Purchase/PurchaseList",

data: objdate,

success: function (response) { BindDataTable(response); }

})

}

var i = 1;

var BindDataTable = function (response) { $("#MyDataTable").DataTable({

"aaData": response,

//'paging': false, '

iDisplayLength': 50,

'scrollY': 400,

"aoColumns": [ { "title": "Serial", render: function (data, type, row, meta) { return meta.row + meta.settings._iDisplayStart + 1; } },

{ "mData": "purchaseDate" },

{ "mData": "product_name" },

{ "mData": "value" }, { "mData": "purchasesId", "render": function (purchasesId, type, full, meta) { return '<a href="#" onclick="DeleteRecord(' + purchasesId + ')"><i style="color:red;" class="bi bi-trash3 "></i></a>&nbsp &nbsp <a href="#" onclick="Edit(' + purchasesId + ')"><i style="color:Blue;" class="bi bi-pencil"></i></a>'

}

}

],

footerCallback: function (tfoot, data, start, end, display) { var api = this.api(); $(api.column(3).footer()).html( api.column(3).data().reduce(function (a, b) { return a + b;

}, 0)

);

}

});

}

</script>

}

public JsonResult PurchaseList(DateTime firstdate, DateTime lastdate)

{

string query = $"Select * from tbl_Purchases where PurchaseDate between {firstdate} and {lastdate}"; var List = _context.purchaseResults.FromSqlRaw(query); return Json(List); }

Answer 1

Hi @jewel,

I have test your code and will share with you. But I still can't point the error directly, you can follow my steps to fix it by yourself.

1.Import jquery datatable js file.

    <!-- #region datatables files -->
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.12/css/jquery.dataTables.min.css" />
    <script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js" asp-append-version="true"></script>

2. Open the F12 Browser Developer Tools, and set breakpoint here to check if the data is returned or not.

3.Set breakponit in your backend api, make sure we have queried the data from database. And please notice, we should use Json(data) here.

4.If there no data returned in the controller, we need to double check the database connectionstring, make sure we have connected to correct database. And we also could copy the sql query sentence and query it in tools(like SSMS), make sure the sql query sentence is correct.

5.Test Result

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best regards,
Jason

Answer 2

You should use parameterized queries to prevent SQL injection.

Dates should be enclosed in single quotes in the SQL query.

public JsonResult PurchaseList(DateTime firstdate, DateTime lastdate)
{
    string query = "SELECT * FROM tbl_Purchases WHERE PurchaseDate BETWEEN @firstdate AND @lastdate";
    
    var list = _context.purchaseResults.FromSqlRaw(query,
        new SqlParameter("@firstdate", firstdate),
        new SqlParameter("@lastdate", lastdate));

    return Json(list);
}