存储过程指定的参数过多的问题如何解决？

[mfwxb]

异常详细信息: System.Data.SqlClient.SqlException: 为过程或函数 UserInfo 指定的参数太多。

源错误:


行 193： myConnection.Open();
行 194： //进行数据库操作
行 195： myCommand.ExecuteNonQuery();
行 196： //关闭数据库连接
行 197： myConnection.Close();


源文件: c:\inetpub\wwwroot\newbookstore\nbookstoredbo.cs 行: 195

堆栈跟踪:


[SqlException: 为过程或函数 UserInfo 指定的参数太多。]
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +742
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +196
NewBookStore.NBookStoreDBO.GetUserDetails(String userID) in c:\inetpub\wwwroot\newbookstore\nbookstoredbo.cs:195
NewBookStore.Login.ImageButton1_Click(Object sender, ImageClickEventArgs e) in c:\inetpub\wwwroot\newbookstore\login.aspx.cs:66
System.Web.UI.WebControls.ImageButton.OnClick(ImageClickEventArgs e) +109
System.Web.UI.WebControls.ImageButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +69
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain() +1292

[mfwxb]

存储过程代码：
ALTER Procedure UserInfo
(
@UserID int,
@UserName nvarchar(50) OUTPUT,
@Password nvarchar(50) OUTPUT,
@Name nvarchar(50) OUTPUT,
@Email nvarchar(50) OUTPUT,
@IDCartNumber nvarchar(50) OUTPUT,
@TelephoneNumber nvarchar(50) OUTPUT，
@Sex bit OUTPUT,
@Post int output

)
AS

SELECT
@UserName = UserName,
@Password = Password,
@Name = Name,
@Email = Email,
@IDCartNumber = IDCartNumber,
@TelephoneNumber = TelephoneNumber，
@Sex= Sex,
@Post= Post
FROM
Users
WHERE
UserID = @UserID

[yf1025]

看看在写调用这存储过程时的代码里面参数是否多写了

[xjliang007]

看看你的调用代码

[mabuchi]

你的调用代码

[mfwxb]

SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
SqlCommand myCommand = new SqlCommand("UserInfo", myConnection);

//指明Sql命令的操作类型是使用存储过程
myCommand.CommandType = CommandType.StoredProcedure;

//给存储过程添加参数
SqlParameter parameterUserID = new SqlParameter("@UserID", SqlDbType.Int, 4);
parameterUserID.Value = Int32.Parse(userID);
myCommand.Parameters.Add(parameterUserID);

SqlParameter parameterUserName = new SqlParameter("@UserName", SqlDbType.NVarChar, 50);
//指出该参数是存储过程的OUTPUT参数
parameterUserName.Direction = ParameterDirection.Output;
myCommand.Parameters.Add(parameterUserName);

SqlParameter parameterPassword = new SqlParameter("@Password", SqlDbType.NVarChar, 50);
parameterPassword.Direction = ParameterDirection.Output;
myCommand.Parameters.Add(parameterPassword);

SqlParameter parameterName = new SqlParameter("@Name", SqlDbType.NVarChar, 50);
parameterName.Direction = ParameterDirection.Output;
myCommand.Parameters.Add(parameterName);

SqlParameter parameterEmail = new SqlParameter("@Email", SqlDbType.NVarChar, 50);
parameterEmail.Direction = ParameterDirection.Output;
myCommand.Parameters.Add(parameterEmail);

SqlParameter parameterIDCartNumber = new SqlParameter("@IDCartNumber", SqlDbType.NVarChar, 50);
parameterIDCartNumber.Direction = ParameterDirection.Output;
myCommand.Parameters.Add(parameterIDCartNumber);

SqlParameter parameterTelephoneNumber = new SqlParameter("@TelephoneNumber", SqlDbType.NVarChar, 50);
parameterTelephoneNumber.Direction = ParameterDirection.Output;
myCommand.Parameters.Add(parameterTelephoneNumber);

SqlParameter parameterMobile = new SqlParameter("@Mobile", SqlDbType.NVarChar, 50);
parameterTelephoneNumber.Direction = ParameterDirection.Output;
myCommand.Parameters.Add(parameterMobile);

SqlParameter parameterSex = new SqlParameter("@Sex", SqlDbType.Bit,1);
parameterSex.Direction = ParameterDirection.Output;
myCommand.Parameters.Add(parameterSex);

SqlParameter parameterPost = new SqlParameter("@Post", SqlDbType.Int ,4);
parameterPost.Direction = ParameterDirection.Output;
myCommand.Parameters.Add(parameterPost);

/*SqlParameter parameterBirthday = new SqlParameter("@Birthday", SqlDbType.DateTime,8);
parameterBirthday.Direction = ParameterDirection.Output;
myCommand.Parameters.Add(parameterBirthday);*/

//打开数据库连接
myConnection.Open();
//进行数据库操作
myCommand.ExecuteNonQuery();
//关闭数据库连接
myConnection.Close();

//产生UserDetails类的对象
UserDetails myUserDetails = new UserDetails();

//根据存储过程的输出参数的值对myUserDetails对象进行赋值
myUserDetails.UserName = (string)parameterUserName.Value;
myUserDetails.Password = (string)parameterPassword.Value;
myUserDetails.Name = (string)parameterName.Value;
myUserDetails.Email = (string)parameterEmail.Value;
myUserDetails.IDCartNumber = (string)parameterIDCartNumber.Value;
myUserDetails.TelephoneNumber = (string)parameterTelephoneNumber.Value;
myUserDetails.Mobile=(string)parameterMobile.Value;
？？？myUserDetails.Sex=Convert.ToString((string)parameterSex.Value);
？？？myUserDetails.Post=Convert.ToString((string)parameterPost.Value);

[Cassava]

参数太多肯定是你多写参数了

[yf1025]

可以不用写这么多输出参数
存储过程代码：
ALTER Procedure UserInfo
(
@UserID int
)
AS

SELECT
UserName,Password,Name,Email,IDCartNumber,TelephoneNumber，Sex,Post
FROM
Users
WHERE
UserID = @UserID

//调用
using(SqlConnection conn = new SqlConnection(sqlstr))
{
conn.Open();
SqlCommand comm = new SqlCommand("UserInfo",conn);
comm.CommandType = CommandType.StoredProcedure;
comm.Parameters.Add("@UserID",SqlDbType.VarChar,50);
comm.Parameters["@UserID"].Value = userid;
SqlDataReader reader = comm.ExecuteReader();
if(reader.Read())
{
MessageBox.Show(reader["UserName"].ToString());
MessageBox.Show(reader["Password"].ToString());
.......
}
}
个人意见:-)

[Samen168]

写多了，hoho

[marvelstack]

//@Mobile就是多出来的参数，去掉就行。
SqlParameter parameterMobile = new SqlParameter("@Mobile", SqlDbType.NVarChar, 50);
parameterTelephoneNumber.Direction = ParameterDirection.Output;
myCommand.Parameters.Add(parameterMobile);


//@Birthday参数已被你注销
/*SqlParameter parameterBirthday = new SqlParameter("@Birthday", SqlDbType.DateTime,8);
parameterBirthday.Direction = ParameterDirection.Output;
myCommand.Parameters.Add(parameterBirthday);*/

[mfwxb]

我减少了两个参数就可以了，不过我就不清楚多两个（Sex和Post）为什么就不行？

[mfwxb]

不好意思 我刚才没有看到秋风的回答，确实是多了一行，哈哈，谢谢大家