<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<script src="Scripts/jquery-1.7.1.js"></script>
</head>
<body>
<script type="text/javascript">
$(document).ready(function () {
$.ajax({
type: "POST",
contentType: "application/json; charset=utf-8",
url: "Default.aspx/GetCustomers",
data: "{}",
dataType: "json",
async: true,
cache: false,
success: function (result) {
$.each(result.d, function (key, value) {
$("#combo").append($("<option></option>").val(value.id).html(value.name));
});
},
error: function (result) {
alert("Error");
}
});
});
</script>
<script type="text/javascript">
function changeIndex() {
//clear grid rows
$("[id*=gv] tr").not($("[id*=gv] tr:first-child")).remove();
fillgrid($("#combo").val());
}
</script>
<script type="text/javascript">
function fillgrid(val) {
$.ajax({
type: "POST",
contentType: "application/json; charset=utf-8",
url: "Default.aspx/GetComplaints",
data: JSON.stringify({ id: val }),
dataType: "json",
async: true,
cache: false,
success: function (data) {
for (var i = 0; i < data.d.length; i++) {
$("#gv").append(
"<tr><td>" + data.d[i].complaintId +
"</td><td>" + data.d[i].complaintTypeId +
"</td><td>" + data.d[i].complaintSubtypeId +
"</td><td>" + data.d[i].closureDate +
"</td><td>" + data.d[i].expirationDate +
"</td><td>" + data.d[i].isActive +
"</td><td>" + data.d[i].createdBy +
"</td></tr>");
}
},
error: function (result) {
alert("Error");
}
});
};
</script>
<form id="form1" runat="server">
<div>
<asp:DropDownList ID="combo" runat="server" onchange="changeIndex();">
<asp:ListItem Selected="True" Text="Seleccione..." Value="0"></asp:ListItem>
</asp:DropDownList>
<br />
<br />
</div>
<asp:GridView ID="gv" runat="server"></asp:GridView>
</form>
</body>
</html>
Default.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindColumnToGridview();
}
}
public class customer
{
public int id { get; set; }
public string name { get; set; }
}
[System.Web.Services.WebMethod]
public static List<customer> GetCustomers()
{
customer cus = new customer();
List<customer> cuslist = new List<customer>();
System.Data.DataSet ds;
using (SqlConnection con = new SqlConnection(
"Data Source=FACTORY\\FACTORY;User Id=sa;Password=pa$$w0rd;DataBase=SAQ_DEV"))
{
using (SqlCommand cmd = new SqlCommand(
"SELECT DISTINCT c.[CustomerId], c.[FullName] FROM [CMP_Complaint] cc " +
"INNER JOIN [CUS_Customer] c ON c.CustomerId = cc.CustomerId", con))
{
con.Open();
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
ds = new DataSet();
da.Fill(ds);
}
}
}
try
{
if (ds != null)
{
if (ds.Tables.Count > 0)
{
if (ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
cuslist.Add(new customer()
{
id = Convert.ToInt32(dr["CustomerId"]),
name = dr["FullName"].ToString()
});
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return cuslist;
}
public class complaint
{
public int complaintId { get; set; }
public int complaintTypeId { get; set; }
public int complaintSubtypeId { get; set; }
public string closureDate { get; set; }
public string expirationDate { get; set; }
public int isActive { get; set; }
public string createdBy { get; set; }
}
private void BindColumnToGridview()
{
DataTable dt = new DataTable();
dt.Columns.Add("Id queja");
dt.Columns.Add("Id tipo de queja");
dt.Columns.Add("Id subtipo de queja");
dt.Columns.Add("Fecha de cierre");
dt.Columns.Add("Fecha de expiracion");
dt.Columns.Add("Activo");
dt.Columns.Add("Creador");
dt.Rows.Add();
gv.DataSource = dt;
gv.DataBind();
gv.Rows[0].Visible = false;
}
[System.Web.Services.WebMethod]
public static List<complaint> GetComplaints(int id)
{
complaint cmp = new complaint();
List<complaint> cmplist = new List<complaint>();
System.Data.DataSet ds;
using (SqlConnection con = new SqlConnection(
"Data Source=FACTORY\\FACTORY;User Id=sa;Password=pa$$w0rd;DataBase=SAQ_DEV"))
{
using (SqlCommand cmd = new SqlCommand(
"SELECT [ComplaintId],[ComplaintTypeId],[ComplaintSubtypeId],[ClosureDate]," +
"[ExpirationDate],[IsActive],[CreatedBy] FROM [CMP_Complaint] WHERE [CustomerId] = " +
id.ToString(), con))
{
con.Open();
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
ds = new DataSet();
da.Fill(ds);
}
}
}
try
{
if (ds != null)
{
if (ds.Tables.Count > 0)
{
if (ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
try
{
cmplist.Add(new complaint()
{
complaintId = Convert.ToInt32(dr["ComplaintId"]),
complaintTypeId = Convert.ToInt32(dr["ComplaintTypeId"]),
complaintSubtypeId = Convert.ToInt32(dr["ComplaintSubtypeId"]),
closureDate = dr["ClosureDate"].ToString().Substring(0,
dr["ClosureDate"].ToString().IndexOf(' ')),
expirationDate = dr["ExpirationDate"].ToString().Substring(0,
dr["ExpirationDate"].ToString().IndexOf(' ')),
isActive = Convert.ToInt32(dr["IsActive"]),
createdBy = dr["CreatedBy"].ToString()
});
}
catch { }
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return cmplist;
}
}
Database tables and example data view
Final result
