Bro, i have a small issue, my data table is working fine. I am having actually two dropdown list one is having year and one will be having month name, so if from one dropdown user select only year, datatable will show report according to that year, and if the user select month from dropdown the report will be shown on the basis of that year and month of that particular year. This is the code bro which i am trying kindly help
public ActionResult Index()
{
List<SelectListItem> Monthname = new List<SelectListItem>();
Monthname.Add(new SelectListItem { Text = "January", Value = "January" });
Monthname.Add(new SelectListItem { Text = "February", Value = "February" });
Monthname.Add(new SelectListItem { Text = "March", Value = "March" });
Monthname.Add(new SelectListItem { Text = "April", Value = "April" });
Monthname.Add(new SelectListItem { Text = "May", Value = "May" });
Monthname.Add(new SelectListItem { Text = "June", Value = "June" });
Monthname.Add(new SelectListItem { Text = "July", Value = "July" });
Monthname.Add(new SelectListItem { Text = "August", Value = "August" });
Monthname.Add(new SelectListItem { Text = "September", Value = "September" });
Monthname.Add(new SelectListItem { Text = "October", Value = "October" });
Monthname.Add(new SelectListItem { Text = "November", Value = "November" });
Monthname.Add(new SelectListItem { Text = "December", Value = "December" });
ViewBag.Month = Monthname;
ViewBag.years = new SelectList(Enumerable.Range(DateTime.Today.Year, 20).Select(x => new SelectListItem()
{
Text = x.ToString(),
Value = x.ToString()
}), "Value", "Text");
return View();
public ActionResult LoadData(string year,string month)
{
string st = ConfigurationManager.ConnectionStrings["Con"].ConnectionString;
List<FeedBack> feedbacks = new List<FeedBack>();
DHIFeedbackEntities2 db = new DHIFeedbackEntities2();
using (SqlConnection con = new SqlConnection(st))
{
con.Open();
//using (SqlCommand cmd = new SqlCommand("Select FeedbackUserName,FeedBackUserEmailID,FeedBackComment,Designation,Organization,ContactNo,City FROM [DHIFeedback].[dbo].[FeedBack] where [FeedbackUserName]=@FeedbackUserName or @FeedbackUserName IS NULL", con))
using (SqlCommand cmd = new SqlCommand("Select FeedbackUserName,FeedBackUserEmailID,FeedBackComment,Designation,Organization,ContactNo,City,convert(varchar(100),[FeedBackDate],103) as Feedback_Date FROM [DHIFeedback].[dbo].[FeedBack] where year([FeedBackDate])=@FeedBackDate and month([FeedBackDate])=@FeedBackMonth or @FeedBackDate IS NULL order by [FeedBackDate] desc ", con))
{
cmd.Parameters.AddWithValue("@FeedBackDate", !string.IsNullOrEmpty(year) ? year : (object)DBNull.Value);
cmd.Parameters.AddWithValue("@FeedBackMonth", !string.IsNullOrEmpty(month) ? month : (object)DBNull.Value);
//cmd.Parameters.AddWithValue("@FeedBackDate", year);
using (SqlDataReader rd = cmd.ExecuteReader())
{
while (rd.Read())
{
feedbacks.Add(new FeedBack
{
FeedbackUserName = rd["FeedbackUserName"].ToString(),
FeedBackUserEmailID = rd["FeedBackUserEmailID"].ToString(),
FeedBackComment = rd["FeedBackComment"].ToString(),
Designation = rd["Designation"].ToString(),
Organization = rd["Organization"].ToString(),
ContactNo = rd["ContactNo"].ToString(),
City = rd["City"].ToString(),
Feedback_date = rd["Feedback_Date"].ToString()
});
}
}
}
con.Close();
}
return Json(new { data = feedbacks }, JsonRequestBehavior.AllowGet);
@Html.DropDownListFor(model=>model.SelectedMonth,(IEnumerable<SelectListItem>)ViewBag.month, "--Select Month--", new { @class = "form-control", onchange = "UserChanged1()" })
@Html.DropDownListFor(model=>model.Selectedyear,(IEnumerable<SelectListItem>)ViewBag.years,"--Select Year--",new {@class="form-control", onchange="UserChanged()" })
$(function () {
$(document).on("click", ".opencomment", function () {
var mycomment = $(this).data('id');
$(".modal-body #commentdesc").html(mycomment);
});
ApplyDataTable("");
});
jQuery.extend(jQuery.fn.dataTableExt.oSort, {
"date-uk-pre": function (a) {
var ukDatea = a.split('/');
return (ukDatea[2] + ukDatea[1] + ukDatea[0]) * 1;
},
"date-uk-asc": function (a, b) {
return ((a < b) ? -1 : ((a > b) ? 1 : 0));
},
"date-uk-desc": function (a, b) {
return ((a < b) ? 1 : ((a > b) ? -1 : 0));
}
});
function ApplyDataTable(year,month) {
$.ajax({
url: '/ViewFeedback/LoadData',
type: 'GET',
dataType: 'json',
data: { year: year , month:month},
success: function (data) {
$('#FeedbackDetails').DataTable({
"bDestroy": true,
"aaData": data.data,
"lengthMenu": [[5, 10, 25, 50, 100, -1], [5, 10, 25, 50, 100, "All"]],
"autoWidth": true,
"responsive": true,
"lengthChange": true,
"ordering": true,
"fnRowCallback": function (nRow, aData, iDisplayIndex) {
var oSettings = this.fnSettings();
$("td:first", nRow).html(oSettings._iDisplayStart + iDisplayIndex + 1);
return nRow;
},
"columns": [
{ "data": null, "autoWidth": true },
{ "data": "FeedbackUserName", "name": "User Name", "autoWidth": true },
{ "data": "FeedBackUserEmailID", "name": "Email ID", "autoWidth": true },
{ "data": "FeedBackComment", "name": "Comment", "autoWidth": true },
{ "data": "Designation", "name": "Designation", "autoWidth": true },
{ "data": "Organization", "name": "Organization", "autoWidth": true },
{ "data": "ContactNo", "name": "Contact No", "autoWidth": true },
{ "data": "City", "name": "City", "autoWidth": true },
{
"data": "Feedback_date", "sType": "date-uk", "autoWidth": true
},
],
columnDefs: [{
targets: 3,
//data:"FeedbackID",
render: function (data, type, row, meta) {
if (type === 'display' && data.length > 40) {
return '<span title="' + data + '">' + data.substr(0, 38) + '...<a href="" data-id="' + data + '" data-toggle="modal" class="opencomment" data-target="#myModal">Show More</a>';
}
else {
return data;
}
}
}],
"language": {
"emptyTable": "No Events Found Related To This Month or Year"
},
});
}
});
}
//Apply custom search on jquery DataTable
function UserChanged() {
//apply search for Year base report
var year = $('#Selectedyear option:selected').val();
//var month = $('#SelectedMonth option:selected').val();
ApplyDataTable(year);
}
function UserChanged1() {
//apply search for Year base report
//var year = $('#Selectedyear option:selected').val();
var month = $('#SelectedMonth option:selected').val();
ApplyDataTable(month);
}
