I am having a jquery data table, out side which i have a dropdown list consisting of years and i want that by default datatable should be loaded and when i change year from dropdown list the datatable should change according to the year.
I tried this much but stuck
public ActionResult Index()
{
ViewBag.years = new SelectList(Enumerable.Range(DateTime.Today.Year, 20).Select(x => new SelectListItem()
{
Text = x.ToString(),
Value = x.ToString()
}), "Value", "Text");
return View();
}
[HttpPost]
public ActionResult LoadData(string year)
{
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,convert(varchar(100),[FeedBackDate],103) as Feedback_Date FROM [DHIFeedback].[dbo].[FeedBack] where year([FeedBackDate])=@FeedBackDate order by [FeedBackDate] desc ", con))
{
//cmd.Parameters.Add("@FeedBackDate", SqlDbType.NVarChar).Value = year;
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.years,"--Select Month--",new { @class = "form-control" , onchange = "UserChanged()" })
function UserChanged() {
var year = $('#SelectedMonth option:selected').html();
$.ajax({
type: "GET",
url: "/ViewFeedback/LoadData",
data: "year=" + year,
success: function (data) {
//alert(year);
$('#FeedbackDetails').DataTable({
"processing": true,
"ajax": {
"url": "/ViewFeedback/LoadData",
"type": "GET",
"datatype": "json"
},
"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 User"
},
});
}
});
}
