异常抛出'过程或函数INSERT_USER指定了太多参数。' [英] Exception throws 'Procedure or function INSERT_USER has too many arguments specified.'

问题描述

嗨我在这种情况下收到错误



这是我的SP

  ALTER   PROCEDURE  dbo.INSERT_USER 
 
   -  （ 
  @ UserName   varchar （ 50 ），
  @ FirstName   varchar （ 50 ），
  @ LastName   varchar （ 50 ），
  @ Password   varchar （ 50 ），
  @ Email   varchar （ 50 ），
  @ LastLogIn   datetime ，
  @ CreatedBy   varchar （ 50 ），
  @ MSG   VARCHAR （ 10 ）OUT 
   -    @Active位 
 
   -   ） 
 
  AS  
 
  BEGIN  
  BEGIN  TRY 
  INSERT   INTO 用户
（UserName，FirstName，LastName，Password，Email，LastLogIn，CreatedBy，CreatedOn，Active 
）
  VALUES （
  @ UserName ，
  @ FirstName ，
  @ LastName ，
  @ Password ，
  @Email ，
 GETDATE（），
  @ CreatedBy ，
 GETDATE（），
 < span class =code-digit> 1  
）
  SET  @ MSG = '  SUCCESS' 
  END  TRY 
  BEGIN  CATCH 
  SET   @ MSG  = '  FAILED' 
  END  CATCH 
  END  
   -    RETURN  

DAL文件中的代码

  public   string  InsertUsers（Business.Users objUsers）
 {
 < span class =code-keyword> string  retUserInsert =  string  .Empty; 
 尝试 
 {
 
 使用（SqlConnection conn = Connection.OpenConnection（））
 {
 
 SqlCommand cmd =  new  SqlCommand（）; 
 cmd.CommandText =   INSERT_USER; 
 cmd.CommandType = System.Data.CommandType.StoredProcedure; 
 cmd.Connection = conn; 
 SqlParameter [] sqlParams =  new  SqlParameter [ 10 ]; 
 
 SqlParameter UserName =  new  SqlParameter（  @ UserName，objUsers.UserName）; 
 UserName.Direction = System.Data.ParameterDirection.Input; 
 UserName.DbType = System.Data.DbType。 String ; 
 sqlParams [ 0 ] = UserName; 
 
 SqlParameter FirstName =  new  SqlParameter（  @ FirstName，objUsers.FirstName）; 
 FirstName.Direction = System.Data.ParameterDirection.Input; 
 FirstName.DbType = System.Data.DbType。 String ; 
 sqlParams [ 1 ] = FirstName; 
 
 SqlParameter LastName =  new  SqlParameter（  @ LastName，objUsers.LastName）; 
 LastName.Direction = System.Data.ParameterDirection.Input; 
 LastName.DbType = System.Data.DbType。 String ; 
 sqlParams [ 2 ] = LastName; 
 
 SqlParameter密码= 新 SqlParameter（  @ Password，objUsers.Password）; 
 Password.Direction = System.Data.ParameterDirection.Input; 
 Password.DbType = System.Data.DbType。 String ; 
 sqlParams [ 3 ] =密码; 
 
 SqlParameter Email =  new  SqlParameter（  @ Email，objUsers.Email）; 
 Email.Direction = System.Data.ParameterDirection.Input; 
 Email.DbType = System.Data.DbType。 String ; 
 sqlParams [ 4 ] =电子邮件; 
 
 
 SqlParameter LastLogIn =  new  SqlParameter（  @ LastLogIn，objUsers.LastLogIn）; 
 LastLogIn.Direction = System.Data.ParameterDirection.Input; 
 LastLogIn.DbType = System.Data.DbType.DateTime; 
 sqlParams [ 5 ] = LastLogIn; 
 
 
 SqlParameter CreatedBy =  new  SqlParameter（  @ CreatedBy，objUsers.CreatedBy）; 
 CreatedBy.Direction = System.Data.ParameterDirection.Input; 
 CreatedBy.DbType = System.Data.DbType。 String ; 
 sqlParams [ 6 ] = CreatedBy; 
 
 SqlParameter CreatedOn =  new  SqlParameter（  @ CreatedOn，objUsers.CreatedOn）; 
 CreatedOn.Direction = System.Data.ParameterDirection.Input; 
 CreatedOn.DbType = System.Data.DbType.DateTime; 
 sqlParams [ 7 ] = CreatedOn; 
 
 SqlParameter Active =  new  SqlParameter（  @Active，objUsers.Active）; 
 Active.Direction = System.Data.ParameterDirection.Input; 
 Active.DbType = System.Data.DbType。 String ; 
 sqlParams [ 8 ] =有效; 
 
 SqlParameter Msg =  new  SqlParameter（  @ MSG， string  .Empty）; 
 Msg.DbType = System.Data.DbType。 String ; 
 Msg.Direction = System.Data.ParameterDirection.Output; 
 Msg.Size =  500 ; 
 sqlParams [ 9 ] = Msg; 
 
 cmd.Parameters.AddRange（sqlParams）; 
 cmd.ExecuteNonQuery（）; 
 retUserInsert = Msg.Value.ToString（）; 
  //   conn.Close（）;  
 
 
} 
} 
  catch （例外情况）
 {
 retUserInsert =   FAILED; 
 log.Error（System.Reflection.MethodBase.GetCurrentMethod（）。ToString（）+ ex.StackTrace）; 
} 
  return  retUserInsert; 
} 

按钮点击事件代码

  protected   void  btnRegister_Click（ object  sender，EventArgs e） 
 {
  try  
 {
 lblMsg.Text =  ; 
  if （Request.QueryString [  UserID ] ==  null ）
 {
 
 objBusinessUI.FirstName = txtFirstName.Text; 
 objBusinessUI.LastName = txtlastName.Text; 
 objBusinessUI.UserName = txtFirstName.Text +   + txtlastName.Text; 
 txtUserName.Text = objBusinessUI.UserName; 
 objBusinessUI.Email = txtEmail.Text; 
 objBusinessUI.Password = txtPass.Text; 
 objBusinessUI.LastLogIn = DateTime.Now; 
 objBusinessUI.CreatedBy =   Admin; 
 objBusinessUI.CreatedOn = DateTime.Now; 
 objBusinessUI.Active =   True; 
  string  strMsg = objDataUserUI.InsertUsers（objBusinessUI）; 
  if （strMsg ==   SUCCESS ）
 {
 
} 
  else   if （strMsg ==   EMAILEXISTS）
 {
 lblMsg。 Text =  电子邮件已存在; 
} 
 
  else  
 {
 
 lblMsg.Text =  您的注册无法完成！请重试; 
 
} 

此SP中的
...执行申请后''过程或函数INSERT_USER指定了太多参数。'''excpetion

解决方案

您的存储过程中没有名为CreatedOn的参数，但是您创建了一个参数您的参数列表。

您还有剩余的商店程序



@CreatedOn Datetime

< blockquote>从你的DAL注释以下代码，



// SqlParameter CreatedOn = new SqlParameter（@ CreatedOn，objUsers.CreatedOn）;

//CreatedOn.Direction = System.Data.ParameterDirection.Input;

//CreatedOn.DbType = System.Data.DbType.DateTime;

// sqlParams [ 7] = CreatedOn;



// SqlParameter Active = new SqlParameter（@ Active，objUsers.Active）;

//活动.Direction = System.Data.ParameterDirection.Input;

//Active.DbType = System.Data.DbType.String;

// sqlParams [8] =有效;



由于你没有传递@Cctive参数，因此你没有从参数传递@CreatedOn。



同时改变@MSG 7的索引8.



希望它有效：）

Hi i am getting error in this case

This is my SP

ALTER PROCEDURE dbo.INSERT_USER
	
--	(
	@UserName varchar(50),
	@FirstName varchar(50),
	@LastName varchar(50),
	@Password  varchar(50),
	@Email varchar(50),
	@LastLogIn datetime, 
	@CreatedBy varchar(50),
	@MSG VARCHAR(10) OUT
	--@Active bit

--	)
	
AS

BEGIN
BEGIN TRY
	INSERT INTO Users 
	(UserName,FirstName,LastName,Password,Email,LastLogIn,CreatedBy,CreatedOn,Active
	)
	  VALUES    (
					@UserName,
					@FirstName,
					@LastName,
					@Password,  
					@Email,
				       GETDATE(),
					@CreatedBy,
					GETDATE(),			
					1
	            )
SET @MSG='SUCCESS'
END TRY
BEGIN CATCH
SET @MSG = 'FAILED'
END CATCH
END
--	RETURN

Code in DAL file

public string InsertUsers(Business.Users objUsers)
        {
            string retUserInsert = string.Empty;
            try
            {
              
                using (SqlConnection conn = Connection.OpenConnection())
                {
                   
                    SqlCommand cmd = new SqlCommand();
                    cmd.CommandText = "INSERT_USER";
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.Connection = conn;
                    SqlParameter[] sqlParams = new SqlParameter[10];

                    SqlParameter UserName = new SqlParameter("@UserName", objUsers.UserName);
                    UserName.Direction = System.Data.ParameterDirection.Input;
                    UserName.DbType = System.Data.DbType.String;
                    sqlParams[0] = UserName;

                    SqlParameter FirstName = new SqlParameter("@FirstName", objUsers.FirstName);
                    FirstName.Direction = System.Data.ParameterDirection.Input;
                    FirstName.DbType = System.Data.DbType.String;
                    sqlParams[1] = FirstName;

                    SqlParameter LastName = new SqlParameter("@LastName", objUsers.LastName);
                    LastName.Direction = System.Data.ParameterDirection.Input;
                    LastName.DbType = System.Data.DbType.String;
                    sqlParams[2] = LastName;

                    SqlParameter Password = new SqlParameter("@Password", objUsers.Password);
                    Password.Direction = System.Data.ParameterDirection.Input;
                    Password.DbType = System.Data.DbType.String;
                    sqlParams[3] = Password;

                    SqlParameter Email = new SqlParameter("@Email", objUsers.Email);
                    Email.Direction = System.Data.ParameterDirection.Input;
                    Email.DbType = System.Data.DbType.String;
                    sqlParams[4] = Email;

                    
                    SqlParameter LastLogIn = new SqlParameter("@LastLogIn", objUsers.LastLogIn);
                    LastLogIn.Direction = System.Data.ParameterDirection.Input;
                    LastLogIn.DbType = System.Data.DbType.DateTime;
                    sqlParams[5] = LastLogIn;
                    

                    SqlParameter CreatedBy = new SqlParameter("@CreatedBy", objUsers.CreatedBy);
                    CreatedBy.Direction = System.Data.ParameterDirection.Input;
                    CreatedBy.DbType = System.Data.DbType.String;
                    sqlParams[6] = CreatedBy;

                    SqlParameter CreatedOn = new SqlParameter("@CreatedOn", objUsers.CreatedOn);
                    CreatedOn.Direction = System.Data.ParameterDirection.Input;
                    CreatedOn.DbType = System.Data.DbType.DateTime;
                    sqlParams[7] = CreatedOn;                  

                    SqlParameter Active = new SqlParameter("@Active", objUsers.Active);
                    Active.Direction = System.Data.ParameterDirection.Input;
                    Active.DbType = System.Data.DbType.String;
                    sqlParams[8] = Active;
                    
                    SqlParameter Msg = new SqlParameter("@MSG", string.Empty);
                    Msg.DbType = System.Data.DbType.String;
                    Msg.Direction = System.Data.ParameterDirection.Output;
                    Msg.Size = 500;
                    sqlParams[9] = Msg;

                    cmd.Parameters.AddRange(sqlParams);
                    cmd.ExecuteNonQuery();
                    retUserInsert=Msg.Value.ToString();
                   //conn.Close();


                }
            }
            catch (Exception ex)
            {
                retUserInsert = "FAILED";
                log.Error(System.Reflection.MethodBase.GetCurrentMethod().ToString() + ex.StackTrace);
            }
            return retUserInsert;
        }

button click event code

protected void btnRegister_Click(object sender, EventArgs e)
       {
           try
           {
               lblMsg.Text = "";
               if (Request.QueryString["UserID"] == null)
               {

                   objBusinessUI.FirstName = txtFirstName.Text;
                   objBusinessUI.LastName = txtlastName.Text;
                   objBusinessUI.UserName = txtFirstName.Text+" "+txtlastName.Text;
                   txtUserName.Text = objBusinessUI.UserName;
                   objBusinessUI.Email = txtEmail.Text;
                   objBusinessUI.Password = txtPass.Text;
                   objBusinessUI.LastLogIn = DateTime.Now;
                   objBusinessUI.CreatedBy = "Admin";
                   objBusinessUI.CreatedOn = DateTime.Now;
                   objBusinessUI.Active = "True";
                   string strMsg = objDataUserUI.InsertUsers(objBusinessUI);
                   if (strMsg=="SUCCESS")
                   {

                   }
                   else if (strMsg=="EMAILEXISTS")
                   {
                       lblMsg.Text = "Email already  exist";
                   }

                   else
                   {

                       lblMsg.Text = "Your registration could not be completed!Try again";

                   }

in this SP..after executing application ''Procedure or function INSERT_USER has too many arguments specified.'' excpetion

解决方案

You do not have a parameter called CreatedOn in your stored procedure, but you've created one in your parameter list.

you have remaining in your store procedure

@CreatedOn Datetime

Comment the follwing code from your DAL,

//SqlParameter CreatedOn = new SqlParameter("@CreatedOn", objUsers.CreatedOn);
//CreatedOn.Direction = System.Data.ParameterDirection.Input;
//CreatedOn.DbType = System.Data.DbType.DateTime;
//sqlParams[7] = CreatedOn;

//SqlParameter Active = new SqlParameter("@Active", objUsers.Active);
//Active.Direction = System.Data.ParameterDirection.Input;
//Active.DbType = System.Data.DbType.String;
//sqlParams[8] = Active;

As you are not passing @CreatedOn from parameter and also Active since you have commneted the @Active parameter.

Also change the index of @MSG 7 instead of 8.

Hope it works :)

这篇关于异常抛出'过程或函数INSERT_USER指定了太多参数。'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！