问题描述
我正在尝试从多个表中返回结果。
我的桌子是这些
Masterleave：
代码varchar（20）未选中
名称varchar（150）已检查
描述varchar（250）已检查
-------------------- ----------------------
TrLeaveRequest：
RequestID int未选中
RequestBy varchar（20）已检查
LeaveCode varchar（20）已检查
RequestDate datetime已检查
RequiredDate datetime已检查
JoinDate datetime Checked
TotalDays int Checked
还有更多...
---- -----------------------------------
TrLeaveApproval：
RequestID int未选中
RefTrID int已检查
RequestTo varchar（20）已检查
TrDate datetime已检查
状态int已检查
------------------------------- -------
我的asp代码是
i am trying to return result from multiple tables.
My tables are these
Masterleave:
Codevarchar(20)Unchecked
Namevarchar(150)Checked
Descriptionvarchar(250)Checked
------------------------------------------
TrLeaveRequest:
RequestIDintUnchecked
RequestByvarchar(20)Checked
LeaveCodevarchar(20)Checked
RequestDatedatetimeChecked
RequiredDatedatetimeChecked
JoinDatedatetimeChecked
TotalDaysintChecked
there is some more...
---------------------------------------
TrLeaveApproval:
RequestIDintUnchecked
RefTrIDintChecked
RequestTovarchar(20)Checked
TrDatedatetimeChecked
StatusintChecked
--------------------------------------
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");
// objLst = objDB.GetListForGrid(Session["empid"].ToString());
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.推荐答案
CREATE PROCEDURE
@param1 type,
@param2 type
@paramN type
AS
BEGIN
--sample SELECT statement
SELECT <Field_list>
FROM TableName
WHERE Field1 = @param1
END
You can use something like below, but just check the highlihted code below as this condition does seems the cause of issue for getting no data as you are trying to match some filter string i.e. 1=1+some filter which will never match. i am not sure if this is neccessary condition , so if it is still required you can write that line as 1 = 1 +@param3
Create Proc Rpt_LeaveInformation(@param1 Nvarchar(100),@param2 Nvarchar(100),@param3 Nvarchar(50))
As
BEGIN
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 RequestDate) AS 'RowNumber'
FROM
TrLeaveRequest R
INNER JOIN MasterLeave T ON R.LeaveCode = T.Code
INNER JOIN EmployeeMaster E ON R.RequestBy = E.EmpID
WHERE
<pre>1 = 1 " + strFilter + ") </pre>SELECT *
FROM TempTable
WHERE RowNumber BETWEEN @param1 AND @param2
END
这篇关于从多个表中检索数据。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！