i am trying to return result from multiple tables.
My tables are these
Masterleave:
Code varchar(20) Unchecked
Name varchar(150) Checked
Description varchar(250) Checked
------------------------------------------
TrLeaveRequest:
RequestID int Unchecked
RequestBy varchar(20) Checked
LeaveCode varchar(20) Checked
RequestDate datetime Checked
RequiredDate datetime Checked
JoinDate datetime Checked
TotalDays int Checked
there is some more...
---------------------------------------
TrLeaveApproval:
RequestID int Unchecked
RefTrID int Checked
RequestTo varchar(20) Checked
TrDate datetime Checked
Status int Checked
--------------------------------------
My asp code is
private void FillGrid()
{
grvList.PageSize = Convert.ToInt16(drpRecords.SelectedValue.ToString());
int PageNumber = Convert.ToInt32(txtPageNo.Text);
LeaveRequestList objLst = new LeaveRequestList();
string strFilter = "";
string strFilter1 = "";
strFilter1 += " AND R.RequestBy ='" + Session["empid"].ToString() + "'";
if (txtLetterType.Text != "")
{
strFilter += " AND R.LeaveCode LIKE '%" + ExpertGeneric.CleanString(txtLetterType.Text) + "%'";
}
if (txtStatus.Text != "")
{
strFilter += " AND R.Status LIKE '%" + ExpertGeneric.CleanString(txtStatus.Text) + "%'";
}
objDB.strFilter = strFilter1 + strFilter;
int cnt = objDB.Count();
lblRecordNo.Text = "Total Rows : " + cnt.ToString("#0") + " ";
int TotalPage = (cnt / grvList.PageSize) + ((cnt % grvList.PageSize) > 0 ? 1 : 0);
lblShowing.Text = " of " + TotalPage.ToString();
if (PageNumber > TotalPage)
{
txtPageNo.Text = TotalPage.ToString();
PageNumber = TotalPage;
}
objLst = objDB.GetListForGrid(grvList.PageSize, PageNumber, "RequestDate");
grvList.DataSource = objLst;
grvList.DataBind();
foreach (GridViewRow gr in grvList.Rows)
{
if (gr.Cells[3].Text != "Requested")
{
((ImageButton)gr.FindControl("imgDelete")).Visible = false;
((ImageButton)gr.FindControl("imgEdit")).Visible = false;
}
}
}
-------------------------------------------------------
public LeaveRequestList GetListForGrid(int RecCount, int PageNo, string OrderBy)
{
strSql = "WITH TempTable AS (SELECT R.RequestID,R.RequestBy,R.LeaveCode,R.RequestDate,R.RequiredDate,R.JoinDate,R.TotalDays,R.Remark,R.Status,R.CreatedBy,R.CreatedDate,R.ModifiedBy,R.ModifiedDate,T.Name AS LeaveTypeName , E.Name AS EmployeeName, (CASE R.Status WHEN 1 THEN 'Requested' WHEN 2 THEN 'Approved' WHEN 3 THEN 'Rejected' ELSE '--' END) AS StatusString, ROW_NUMBER() OVER (ORDER BY " + OrderBy + ") AS 'RowNumber' FROM TrLeaveRequest R INNER JOIN MasterLeave T ON R.LeaveCode = T.Code INNER JOIN EmployeeMaster E ON R.RequestBy = E.EmpID WHERE 1 = 1 " + strFilter + ") SELECT * FROM TempTable WHERE RowNumber BETWEEN " + ((PageNo == 1) ? 1 : ((PageNo - 1) * RecCount) + 1).ToString() + " AND " + ((PageNo == 1) ? RecCount : (PageNo * RecCount)).ToString().ToString();
LeaveRequestList objList = new LeaveRequestList();
DataTable dt = new DataTable();
dt = objDB.GetDataTableFromSQL(strSql);
if (dt != null)
{
foreach (DataRow Dr in dt.Rows)
{
LeaveRequest obj = new LeaveRequest();
obj.RequestID = Convert.ToInt32(Dr["RequestID"].ToString());
if (Dr["RequestBy"] != DBNull.Value)
obj.RequestBy = Dr["RequestBy"].ToString();
else
obj.RequestBy = "";
if (Dr["LeaveCode"] != DBNull.Value)
obj.LeaveCode = Dr["LeaveCode"].ToString();
else
obj.LeaveCode = "";
if (Dr["LeaveTypeName"] != DBNull.Value)
obj.LeaveTypeName = Dr["LeaveTypeName"].ToString();
else
obj.LeaveTypeName = "";
if (Dr["EmployeeName"] != DBNull.Value)
obj.EmployeeName = Dr["EmployeeName"].ToString();
else
obj.EmployeeName = "";
if (Dr["StatusString"] != DBNull.Value)
obj.StatusString = Dr["StatusString"].ToString();
else
obj.StatusString = "";
if (Dr["RequestDate"] != DBNull.Value)
obj.RequestDate = Convert.ToDateTime(Dr["RequestDate"].ToString());
else
obj.RequestDate = new DateTime();
if (Dr["RequiredDate"] != DBNull.Value)
obj.RequiredDate = Convert.ToDateTime(Dr["RequiredDate"].ToString());
else
obj.RequiredDate = new DateTime();
if (Dr["JoinDate"] != DBNull.Value)
obj.JoinDate = Convert.ToDateTime(Dr["JoinDate"].ToString());
else
obj.JoinDate = new DateTime();
if (Dr["TotalDays"] != DBNull.Value)
obj.TotalDays = Convert.ToInt32(Dr["TotalDays"].ToString());
else
obj.Status = 0;
if (Dr["Remark"] != DBNull.Value)
obj.Remark = Dr["Remark"].ToString();
else
obj.Remark = "";
if (Dr["Status"] != DBNull.Value)
obj.Status = Convert.ToInt32(Dr["Status"].ToString());
else
obj.Status = 0;
objList.Add(obj);
}
}
return objList;
}
--------------------------------------
it is not give me any error, same time no return. please help me to find solution.