存储过程错误，参数过多 [英] Error in Stored procedure too many argument

问题描述

嗨
我有一个问题，当我更新记录时出现错误发生错误:过程..指定了太多参数".这是我的存储过程

 SET ANSI_NULLS ON
去
将QUOTED_IDENTIFIER设置为ON
去
ALTER过程[dbo].[web_usp_empData_Update]
@id bigint，
@Code nvarchar( 10 )，
@Name nvarchar( 50 )，
@Address nvarchar( 150 )，
@Tel nvarchar( 20 )，
@HP nvarchar( 20 )，
@email nvarchar( 50 )，
@NRIC nvarchar( 20 )，
@Jobtitle nvarchar( 50 )，
@Dept nvarchar( 20 )
作为
开始
更新empData
放
[代码] = @代码，
[名称] = @名称，
[地址] = @地址，
[电话] = @电话，
[HP] = @HP，
[email] = @email，
[NRIC] = @NRIC，
[Jobtitle] = @Jobtitle，
[部门] = @部门
在哪里
[id] = @id
结束

我的更新代码

 cmd.Connection = conn
   cmd.CommandText =(" )
   cmd.Parameters.Add(New SqlParameter(" ，会话(  empID")))
   cmd.CommandType = CommandType.StoredProcedure
   尝试

       cmd.Parameters.AddWithValue(" ，Me.txtCode.Text)
       cmd.Parameters.AddWithValue(" ，UCase(Me.txtName.Text))
       cmd.Parameters.AddWithValue(" ，Me.txtAddress.Text)
       cmd.Parameters.AddWithValue(" ，Me.txtTel.Text)
       cmd.Parameters.AddWithValue(" ，Me.txtHP.Text)
       cmd.Parameters.AddWithValue(" ，Me.txtEmail.Text)
       cmd.Parameters.AddWithValue(" ，Me.txtNRIC.Text)
       cmd.Parameters.AddWithValue(" ，Me.cboJobTitle.SelectedValue)
       cmd.Parameters.AddWithValue(" ，Me.cboDepartment.SelectedValue)

       conn.Open()
       cmd.ExecuteNonQuery()
       cmd.Dispose()
       conn.Close()
       Response.Redirect(" )
       ScriptManager.RegisterStartupScript(Page，Page.GetType()，" ， 警报('Updated！');"，True)

   异常捕获
       ScriptManager.RegisterStartupScript(Me，Me.[GetType]()，" ，" & ex.Message.ToString()&  ');"，True)
   最后
       cmd.Dispose()
       conn.Close()
   结束尝试

请帮助我

我想知道存储过程解析器是否被一行上的"SET"混淆了.尝试以下将UPDATE语句全部放在一行上的操作.

  SET   ANSI_NULLS   ON 
转到
设置  QUOTED_IDENTIFIER  >打开
转到
更改 过程 [dbo].[web_usp_empData_Update]
 @ id   bigint ，
 @ Code   nvarchar ( 10 )，
 @ Name   nvarchar ( 50 )，
 @ Address   nvarchar ( 150 )，
 @电话  nvarchar ( 20 )，
 @ HP   nvarchar ( 20 )，
 @电子邮件  nvarchar ( 50 )，
 @ NRIC   nvarchar ( 20 )，
 @ Jobtitle   nvarchar ( 50 )，
 @部门  nvarchar ( 20 )
目标
开始
更新 empData 设置 Code = @ Code，Name = @ Name，Address = @ Address，Tel = @ Tel ，HP = @ HP，email = @ email，NRIC = @ NRIC，Jobtitle = @ Jobtitle，Dept = @ Dept 其中 id = @ id
结束 

另外，在创建新的cmd对象而不是重新使用现有的cmd对象的情况下，尝试此操作.

 cmd = 新建 SqlCommand
cmd.Connection =连接
cmd.CommandText =(" )
cmd.Parameters.Add(新建 SqlParameter(" ，会话(  empID"))))
cmd.CommandType = CommandType.StoredProcedure
...

Hi
I have an issue that when i Update the record there is error "Error occured: Procedure .. has too many argument specified". It is my Store procedure

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[web_usp_empData_Update]
	@id bigint,
	@Code nvarchar(10),
	@Name nvarchar(50),
	@Address nvarchar(150),
	@Tel nvarchar(20),
	@HP nvarchar(20),
	@email nvarchar(50),
	@NRIC nvarchar(20),
	@Jobtitle nvarchar(50),
	@Dept nvarchar(20)
As
Begin
	Update empData
	Set
		[Code] = @Code,
		[Name] = @Name,
		[Address] = @Address,
		[Tel] = @Tel,
		[HP] = @HP,
		[email] = @email,
		[NRIC] = @NRIC,
		[Jobtitle] = @Jobtitle,
		[Dept] = @Dept
	Where		
		[id] = @id
End

My update code

cmd.Connection = conn
   cmd.CommandText = ("web_usp_empData_Update")
   cmd.Parameters.Add(New SqlParameter("@id", Session("empID")))
   cmd.CommandType = CommandType.StoredProcedure
   Try

       cmd.Parameters.AddWithValue("@Code", Me.txtCode.Text)
       cmd.Parameters.AddWithValue("@Name", UCase(Me.txtName.Text))
       cmd.Parameters.AddWithValue("@Address", Me.txtAddress.Text)
       cmd.Parameters.AddWithValue("@Tel", Me.txtTel.Text)
       cmd.Parameters.AddWithValue("@Hp", Me.txtHP.Text)
       cmd.Parameters.AddWithValue("@email", Me.txtEmail.Text)
       cmd.Parameters.AddWithValue("@Nric", Me.txtNRIC.Text)
       cmd.Parameters.AddWithValue("@Jobtitle", Me.cboJobTitle.SelectedValue)
       cmd.Parameters.AddWithValue("@Dept", Me.cboDepartment.SelectedValue)

       conn.Open()
       cmd.ExecuteNonQuery()
       cmd.Dispose()
       conn.Close()
       Response.Redirect("empVIEW.aspx")
       ScriptManager.RegisterStartupScript(Page, Page.GetType(), "alert", "alert('Updated!');", True)

   Catch ex As Exception
       ScriptManager.RegisterStartupScript(Me, Me.[GetType](), "Message", "alert('Error occured : " & ex.Message.ToString() & "');", True)
   Finally
       cmd.Dispose()
       conn.Close()
   End Try

Pls help me

maideen

解决方案

I wonder if the Stored Procedure parser is confused by the "SET" on a line by itself. Try the following where I put the UPDATE statement all on one line.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[web_usp_empData_Update]
	@id bigint,
	@Code nvarchar(10),
	@Name nvarchar(50),
	@Address nvarchar(150),
	@Tel nvarchar(20),
	@HP nvarchar(20),
	@email nvarchar(50),
	@NRIC nvarchar(20),
	@Jobtitle nvarchar(50),
	@Dept nvarchar(20)
As
Begin
Update empData Set Code=@Code,Name=@Name,Address=@Address,Tel=@Tel,HP=@HP,email=@email, NRIC=@NRIC,Jobtitle=@Jobtitle,Dept=@Dept Where id=@id
End

Also, try this where I create a new cmd object instead of re-using an existing cmd object.

cmd = New SqlCommand
cmd.Connection = conn
cmd.CommandText = ("web_usp_empData_Update")
cmd.Parameters.Add(New SqlParameter("@id", Session("empID")))
cmd.CommandType = CommandType.StoredProcedure
...

这篇关于存储过程错误，参数过多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！