存储过程错误,参数过多 [英] 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 newcmd
object instead of re-using an existingcmd
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屋!
查看全文