Click here to Skip to main content
15,889,867 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
C#
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
C#
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
Posted
Updated 8-Nov-13 18:28pm
v2
Comments
♥…ЯҠ…♥ 9-Nov-13 1:46am    
@Nric is in caps in your sp.... change that and try
Maideen Abdul Kader 9-Nov-13 5:32am    
praba

same error

pls

maideen
Maideen Abdul Kader 9-Nov-13 5:32am    
praba

same error, pls help me
maideen
♥…ЯҠ…♥ 9-Nov-13 5:38am    
Can you able to execute it in sql server using exec command? And make sure whether you are pointing to the correct server that you intent to?
Maideen Abdul Kader 9-Nov-13 20:31pm    
Hi
it is working. I have added cmd = New SqlCommand on the top of coding. Now no error. but cannot store
have any idea? pls

1 solution

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.

SQL
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.

VB
cmd = New SqlCommand
cmd.Connection = conn
cmd.CommandText = ("web_usp_empData_Update")
cmd.Parameters.Add(New SqlParameter("@id", Session("empID")))
cmd.CommandType = CommandType.StoredProcedure
...
 
Share this answer
 
v6
Comments
Maideen Abdul Kader 9-Nov-13 20:36pm    
hi mike
i have changed according to your suggestion. No error, but cannot update the data. working fine in mssql console using exec command.
working fine. But cannot update. any idea. I have posting my code once again

cmd = New SqlCommand
cmd.Connection = conn
cmd.CommandText = "web_usp_empData_Update"
cmd.Parameters.Add(New SqlParameter("@id", Me.txtID.Text))
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()
Session.Remove("empID")
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)
'Throw ex
Finally
cmd.Dispose()
conn.Close()


End Try
Mike Meinz 10-Nov-13 8:12am    
If there is no error message and an update is not occurring, then Me.txtID.Text is not matching an ID value in the empData table.

I suggest you change this statement:
cmd.Parameters.Add(New SqlParameter("@id", Me.txtID.Text))

to this:

cmd.Parameters.Add(New SqlParameter("@id", CLng(Me.txtID.Text.Trim)))

If this does not work, then the value in Me.txtID.Text is missing from the empData table.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900