gridview分页按条件查询时列名无效怎么办？分页没问题就是按主管查询就报UserName列名不存在

Question

sql语句

 if exists (select * from sys.procedures where name='proc_page')
drop proc proc_page
go

create proc proc_page  @beginIndex int,@endIndex int,@where varchar(100)
as
    declare @sql varchar(1000)
    set @sql='select * from (select ROW_NUMBER() over(order by a.DeptID) rowIndex,a.*,b.UserName from Department a left join UserInfo b on a.ManagerID=b.UserID where 1=1 '+@where+') c where rowIndex between '+str(@beginIndex)+' and '+str(@endIndex)
    exec(@sql)
go

给gridview绑定数据

 public static DataTable SelectDepartment(int PageIndex, int PageSize, string DeptName,string UserName, out int totalCount)
        {
            string sql = "exec proc_page @begIndex,@endIndex,@where";
            string where = string.Empty;
            if (!string.IsNullOrEmpty(DeptName))
            {
                where += string.Format(" and DeptName='{0}'", DeptName);
            }
            if (UserName != "请选择")
            {
                where += string.Format(" and UserName='{0}'", UserName);
            }
            int begIndex = (PageIndex - 1) * PageSize + 1;
            int endIndex = PageSize * PageIndex;

            SqlParameter[] ps =
            {
                new SqlParameter("@begIndex",begIndex),
                new SqlParameter("@endIndex",endIndex),
                new SqlParameter("@where",where)
            };
            //string sql = string.Format("select * from (select ROW_NUMBER() over(order by a.DeptID) rowIndex,a.*,b.UserName from Department a left join UserInfo b on a.ManagerID=b.UserID where 1=1 {0}) c where rowIndex between {1} and {2}",where,begIndex,endIndex);
            string sqlCount = string.Format("select * from Department where 1=1 {0}",where);
            totalCount = int.Parse((DBHelper.GetDataTable1(sqlCount).Rows.Count.ToString()));

            return DBHelper.GetDataTable(sql,ps);
        }

后台绑定

 public void BindGv()
        {
            string DeptName = this.txtDeptName.Text.Trim();
            string UserName = this.DDLUserName.SelectedItem.ToString();
            int totalCount;
            this.GridView1.DataSource = BLL.Department_BLL.selectDepartment(PageIndex, PageSize, DeptName, UserName, out totalCount);
            this.GridView1.DataBind();
            this.Label1.Text = "页数:" + PageIndex.ToString();
            this.Label2.Text = "总记录行数:" + totalCount.ToString();
            ViewState["TotalCount"] = totalCount;
        }

Answer 1

if (UserName != "请选择")
{
where += string.Format(" and UserName='{0}'", UserName);
}
这里出错 不懂加我