Procedure or function has too many arguments specified

[Abbasi]

Hi everybody

I am a beginner and facing the problem in calling a Stored Procedure from within my VB.NET 2005, as below:

MY Stored Procedure goes like;
ALTER PROCEDURE ByRoyalty @percentage int, @avgprice float Output
AS
SELECT @avgprice= AVG(price) FROM titles
SELECT au_id FROM titleauthor
WHERE titleauthor.royaltyper = @percentage
DECLARE @numtitles Int
SELECT @numtitles=COUNT(*) FROM titles
RETURN @numtitles

My VB code is as below:
Dim cmd As New SqlCommand("ByRoyalty", Conxn)
With cmd
.CommandType = CommandType.StoredProcedure

.Parameters.Add("@numtitles", SqlDbType.Int)
.Parameters(0).Direction = ParameterDirection.ReturnValue

.Parameters.AddWithValue("@percentage", 100)
.Parameters(1).Direction = ParameterDirection.Input

.Parameters.Add("@avgprice", SqlDbType.Float)
.Parameters(2).Direction = ParameterDirection.Output
End With
Dim dr As SqlDataReader = cmd.ExecuteReader()

Running the above vb code reports the following error:
"Procedure or function ByRoyalty has too many arguments specified."

Please note that there are 2 parameters in the declaration, whereas third argument is declared inside the SP as;

DECLARE @numtitles Int​

Even if we remove this statement and put this argument along with the other two in the declaration, I receive the same error.

Please help

 

[cjard]

Youre using .net2.0. Do this:

make a new dataset item in your project (leave it as DataSet1 for now)
go through the wizard to connect to your database
right click the blue surface and click add>>query
choose that it's a stored procedure, follow the wizard (i've never done this because it doesnt work that way for oracle)
now, have a look at the properties of the query you just made.. in particular look at the number and the types of the arguments. if you want to see the code the designer generated, click SHOW ALL FILES button above the solution explorer tree (use tooltips to find it) and look at DataSet1.Designer.vb

if you cannot choose to add a storedprocedure type query, then choose a scalar (returns a single value) query and write any query.. SELECT 1 or whatever..
then when the wizard finished, click the query it made (in the list under the tableadapter heading) and change its properties to be CommandType StoredProcedure, CommandText (dropdown) select the name of the SProc, and press return. VS will fill in the other properties



I suspect the problem is that youre calling parameters.add when you dont need to. Debug the project, and halt immediately before you call ExecuteNonQuery() to run the sproc. Look at the parameters collection.. You might find there are up to 5 params in there (i.e. 2 VS made for you , and 2-3 you added yourself.) see if it helps you work out what is wrong.

 

[Abbasi]

THOUSANDS OF PARDONS to all the friends who tried to help.
The problem was somewhere else.
As a matter of fact, all the changes I had been making were commencing in the LOCAL COPY of my database. For this reason, my changes did not reflect in the result set.

I again aplogize everybody.

With gread regards.