protected void TB_SearchCust_TextChanged(object sender, EventArgs e)
{
try
{
/*string query = " select top 20 ROW_NUMBER() OVER(ORDER BY tbl_MPos.Mposid DESC) AS [ID],tbl_Mpos.Mposid,tbl_Mpos.BillNO,billdat,left(billtim,8) as [billtim], tbl_MPos.CustomerName,Customers_.CellNo1,tbl_DPos.productid, " +
* " productname,proqty,grntttl, tbl_Mpos.createdby,convert(varchar, tbl_Mpos.createdat, 103) as [createdat] " +
* " from tbl_Mpos inner join tbl_DPos on tbl_MPos.Mposid = tbl_DPos.Mposid INNER JOIN Customers_ on tbl_MPos.CellNo1 = Customers_.CellNo1 " +
* " inner join Products on tbl_DPos.ProductID = Pr where tbl_Mpos.CompanyId = '" + Session["CompanyID"] + "' and tbl_Mpos.BranchId= '" + Session["BranchID"] + "' and tbl_MPos.CellNo1 ='" + TB_SearchCust.Text.Trim() + "' order by Mposid desc ";*/
string query = " SELECT top 20 ROW_NUMBER() OVER(ORDER BY SS.Mposid DESC) AS [ID], SS.Mposid,SS.BillNO, SS.CustomerName, (SELECT ',' + US.ProductID FROM tbl_Dpos US WHERE US.Mposid = SS.Mposid FOR XML PATH('')) [Items], SS.CellNo1,billdat,left(billtim,8) as [billtim], SS.createdby,convert(varchar, SS.createdat, 103) as [createdat] FROM tbl_Mpos SS INNER JOIN Customers_ on SS.CellNo1 = Customers_.CellNo1 where SS.CompanyId = '" + Session["CompanyID"] + "' and SS.BranchId= '" + Session["BranchID"] + "' and SS.CellNo1 ='" + TB_SearchCust.Text.Trim() + "' GROUP BY SS.Mposid, SS.CustomerName,SS.BillNO, billdat, billtim, SS.CellNo1,SS.createdby,SS.createdat";
SqlCommand cmd = new SqlCommand(query, con);
DataTable dt_ = new DataTable();
SqlDataAdapter adp = new SqlDataAdapter(cmd);
adp.Fill(dt_);
if (dt_.Rows.Count > 0)
{
GVSerachCust.DataSource = dt_;
GVSerachCust.DataBind();
TB_SearchCust.Text = "";
lblerr.Text = "";
}
else
{
lblerr.Text = "Sorry No Record Exits!";
}
}
catch (Exception ex)
{
lblerr.Text = ex.Message;
}
}
public void FillGrid()
{
try
{
//Sales Order
using (SqlCommand cmd = new SqlCommand())
{
/*cmd.CommandText = " select top 20 ROW_NUMBER() OVER(ORDER BY tbl_MPos.Mposid DESC) AS [ID],tbl_Mpos.Mposid,tbl_Mpos.BillNO,billdat,left(billtim,8) as [billtim], tbl_MPos.CustomerName,Customers_.CellNo1,tbl_DPos.productid, " +
* " productname,proqty,grntttl, tbl_Mpos.createdby,convert(varchar, tbl_Mpos.createdat, 103) as [createdat] " +
* " from tbl_Mpos inner join tbl_DPos on tbl_MPos.Mposid = tbl_DPos.Mposid INNER JOIN Customers_ on tbl_MPos.CellNo1 = Customers_.CellNo1 " +
* " inner join Products on tbl_DPos.ProductID = Products.ProductID where tbl_Mpos.CompanyId = '" + Session["CompanyID"] + "' and tbl_Mpos.BranchId= '" + Session["BranchID"] + "' order by Mposid desc ";*/
cmd.CommandText = "SELECT top 20 ROW_NUMBER() OVER(ORDER BY SS.Mposid DESC) AS [ID], SS.Mposid,SS.BillNO, SS.CustomerName, (SELECT ',' + US.ProductID FROM tbl_Dpos US WHERE US.Mposid = SS.Mposid FOR XML PATH('')) [Items], SS.CellNo1,billdat,left(billtim,8) as [billtim], SS.createdby,convert(varchar, SS.createdat, 103) as [createdat] FROM tbl_Mpos SS INNER JOIN Customers_ on SS.CellNo1 = Customers_.CellNo1 GROUP BY SS.Mposid, SS.CustomerName,SS.BillNO, billdat, billtim, SS.CellNo1,SS.createdby,SS.createdat";
cmd.Connection = con;
con.Open();
DataTable dtchkcust_ = new DataTable();
SqlDataAdapter adp = new SqlDataAdapter(cmd);
adp.Fill(dtchkcust_);
GVSerachCust.DataSource = dtchkcust_;
GVSerachCust.DataBind();
con.Close();
}
}
catch (Exception ex)
{
lblerr.Text = ex.Message;
}
}