存储过程的问题，附源码，长
retail.ascx.cs代码：
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using jxdom.DBUtility;
using System.Data.SqlClient;
public partial class Controls_retail : System.Web.UI.UserControl
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
int totalOrders = (int)jxdom.DBUtility.SqlHelper.ExecuteScalar(SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "P_GetorderNumber ");//这句不懂怎么写才对，提示这里出错：过程 'P_GetOrderNumber ' 需要参数 '@pageindex '，但未提供该参数。
AspNetPager1.RecordCount = totalOrders;
}
}
}
retail.ascx代码：
<asp:SqlDataSource ID= "SqlDataSource3 " runat= "server " ConnectionString= " <%$ ConnectionStrings:SQLConnString1 %> "
SelectCommand= " <%$ AppSettings:pagedSPName %> " SelectCommandType= "StoredProcedure ">
<SelectParameters>
<asp:Parameter DefaultValue= "true " Name= "IsShow " Type= "Boolean " />
<asp:ControlParameter ControlID= "AspNetPager1 " DefaultValue= "10 " Name= "pagesize "
PropertyName= "PageSize " Type= "Int32 " />
<asp:ControlParameter ControlID= "AspNetPager1 " DefaultValue= "1 " Name= "startIndex "
PropertyName= "StartRecordIndex " Type= "Int32 " />
</SelectParameters>
</asp:SqlDataSource>
存储过程：
CREATE procedure P_GetOrderNumber
(@pagesize int,
@pageindex int,
@docount bit)
as
set nocount on
if(@docount=1)
select count(PaiXu) from Item where isshow=1
else
begin
declare @indextable table(id int identity(1,1),nid int)
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=(@pageindex-1)*@pagesize
set @PageUpperBound=@PageLowerBound+@pagesize
set rowcount @PageUpperBound
insert into @indextable(nid) select id from Item where isshow=1 order by PaiXu desc
select * from Item O,@indextable t where O.id=t.nid
and t.id between @PageLowerBound+1 and @PageUpperBound order by t.id
end
set nocount off
GO
在执行时，页面提示：
过程 'P_GetOrderNumber ' 需要参数 '@pageindex '，但未提供该参数。
请高手帮忙
[解决办法]
问题很简单，你的这个存储过程需要参数的，而你在
int totalOrders = (int)jxdom.DBUtility.SqlHelper.ExecuteScalar(SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "P_GetorderNumber ");
这里的调用并没有附加参数，必须调用ExecuteScalar的另一个重载版本传入SqlParameters[]参数才行。
把这些复制到你的SqlHelper类中：
private static SqlParameter MakeParam(string ParamName,ParameterDirection Direction,object Value)
{
SqlParameter param=new SqlParameter();
param.ParameterName=ParamName;
param.Direction=Direction;
if(!(Direction==ParameterDirection.Output && Value==null))param.Value=Value;
return param;
}
private static SqlParameter MakeParam(string paramName,ParameterDirection Direction,SqlDbType Type,int Size,object Value)
{
SqlParameter param=new SqlParameter(paramName,Type,Size);
param.Direction=Direction;
if(!(Direction==ParameterDirection.Output && Value==null))param.Value=Value;
return param;
}
public static SqlParameter MakeInParam(string ParamName,object Value)
{
return MakeParam(ParamName,ParameterDirection.Input,Value);
}
public static SqlParameter MakeInParam(string ParamName,SqlDbType Type,int Size,object Value)
{
return MakeParam(ParamName,ParameterDirection.Input,Type,Size,Value);
}
public static SqlParameter MakeOutParam(string ParamName)
{
return MakeParam(ParamName,ParameterDirection.Output,null);
}
public static SqlParameter MakeOutParam(string ParamName,SqlDbType Type,int Size)
{
return MakeParam(ParamName,ParameterDirection.Output,Type,Size,null);
}
然后在调用之前：
SqlParameter[] params ={
SqlHelper.MakeInParam( "@pageindex ' ", 1)
};
int totalOrders = (int)jxdom.DBUtility.SqlHelper.ExecuteScalar(SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "P_GetorderNumber ",params);
即可。
