如何使用SQL查询的结果填充gridview [英] How do I populate the gridview with the results of an SQL querie

问题描述

我正在使用3层架构，我在gridview中显示数据时遇到问题。



我一直收到错误

过程或函数'spDisplayComplainant'需要参数'@IDNum'，这是没有提供的

但我没看到我没有提供ID的地方



我尝试过：



在我的BLL中我有

公开列表<投诉人> GetComplainantDetails（string idnum）
 {
 return db.GetComplainantDetails（idnum）; 
} 

然后在我的数据库访问中我有

公开列表<投诉人> GetComplainantDetails（string idnum）
 {
 List< Complainant> comp = new List< Complainant>（）; 
投诉人co = null; 
 SqlParameter [] pars = new SqlParameter [] 
 {
 new SqlParameter（@ IDNum，idnum），
}; 
 using（DataTable table = DBHelper.ExecuteParamerizedSelectCommand（spDisplayComplainant，CommandType.StoredProcedure，pars））
 {
 if（table.Rows.Count> 0）
 {
 foreach（table.Rows中的DataRow行）
 {
 co = new Complainant（）; 
 
 co.ComplainantID = row [ComplainantID]。ToString（）; 
 co.FirstName =（row [FirstName]。ToString（））; 
 co.LastName =（row [LastName]。ToString（））; 
 co.Statement =（row [Statement]。ToString（））; 
 comp.Add（co）; 
} 
} 
} 
返回comp; 
} 
 

然后在我要显示gridview的页面上

 protected void Page_Load（object sender，EventArgs e）
 {
 if（！IsPostBack）
 {
 / / string txtSession =; 
 if（Session [IDNumber] == null）
 {
 
} 
 
 else 
 {
 string Id = Session [IDNumber]。ToString（）; 
 bll = new BusinessLogicLayer（）; 
 GridView1.DataSource = bll.GetComplainantDetails（Id）; 
 GridView1.DataBind（）; 
 
 BindGrid（）; 
} 
} 
 
 
} 

和我的存储过程看起来如下



SELECT [ComplainantID]，[FirstName]，[LastName]，[Statement]

来自投诉人

WHERE ComplainantID = @IDNum

解决方案

Quote：

程序或功能'spDisplayComplainant'需要参数'@IDNum'，未提供

通常，当存储过程接受非可空类型并尝试将 null 值传递给参数。



它可以通过

来整理，更改过程参数以接受 null 值为

 CREATE PROCEDURE dbo.Sample_Procedure 
（@IDNum varchar（50） = null ）
 AS 
 begin 
 SELECT [Complaina ntID]，[FirstName]，[LastName]，[Statement] 
 FROM COMPLAINANT 
 WHERE ComplainantID = @IDNum 
 end 

或

您必须验证非空值 idnum 值c $ c>

 if（！String.IsNullOrEmpty（idnum））{
 
} 

除此之外，你需要在第一时间找到为什么idnum值为 null 并在那里修复它。 / BLOCKQUOTE>

I am using 3 tier architecture and I am having problems with displaying the data in the gridview.

I keep getting the error "

Procedure or function 'spDisplayComplainant' expects parameter '@IDNum', which was not supplied

" but i dont see where I have not supplied the ID

What I have tried:

In my BLL I have

public List<Complainant> GetComplainantDetails(string idnum)
        {
            return db.GetComplainantDetails(idnum);
        }

Then in my db access i have

public List<Complainant> GetComplainantDetails(string idnum)
       {
           List<Complainant> comp = new List<Complainant>();
           Complainant co = null;
           SqlParameter[] pars = new SqlParameter[]
           {
               new SqlParameter("@IDNum", idnum),
           };
           using (DataTable table = DBHelper.ExecuteParamerizedSelectCommand("spDisplayComplainant", CommandType.StoredProcedure, pars))
           {
               if (table.Rows.Count > 0)
               {
                   foreach (DataRow row in table.Rows)
                   {
                       co = new Complainant();

                       co.ComplainantID = row["ComplainantID"].ToString();
                       co.FirstName = (row["FirstName"].ToString());
                       co.LastName = (row["LastName"].ToString());
                       co.Statement = (row["Statement"].ToString());
                       comp.Add(co);
                   }
               }
           }
           return comp;
       }

And then on the page where I want to display the gridview

protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                //string txtSession = " ";
                if (Session["IDNumber"] == null)
                {

                }

                else
                {
                    string Id = Session["IDNumber"].ToString();
                    bll = new BusinessLogicLayer();
                    GridView1.DataSource = bll.GetComplainantDetails(Id);
                    GridView1.DataBind();

                    BindGrid();
                }
            }


        }

and my stored procedure looks as follows

SELECT [ComplainantID], [FirstName], [LastName], [Statement]
FROM COMPLAINANT
WHERE ComplainantID = @IDNum

解决方案

Quote:

Procedure or function 'spDisplayComplainant' expects parameter '@IDNum', which was not supplied

Generally you get this error when the stored procedure accepts a parameter which is of non nullable type and you try to pass null value to the parameter.

it can be sorted out by
changing the procedure parameter to accept null values as

CREATE PROCEDURE dbo.Sample_Procedure 
 (  @IDNum varchar(50) = null )
AS
begin
 SELECT [ComplainantID], [FirstName], [LastName], [Statement] 
FROM COMPLAINANT
WHERE ComplainantID = @IDNum
end

or
you will have to validate the idnum value for non null value

 if( !String.IsNullOrEmpty(idnum)){
               
}

on top of all these, you need to find why the idnum value is null at the first place and fix it there.

这篇关于如何使用SQL查询的结果填充gridview的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！