Hi everybody

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

[u]MY Stored Procedure goes like</u>;
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

[u]My VB code is as below</u>:
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 help

Raby

hi there..

try to specify exactly every parameter, name, type, len. you are not specifying all the data of the parameters...

HTH

Gonzalo

I tried but failed. I think I am not doing it in a proper way.
Should I modify the Call to the Stored Procedure, in VB, or have to make some changes in the Stored Procedure?
Please suggest me the exact parameters sequence. Thanks in anticipation.

I'm not an expert.. but let's try a couple of things..

order your parameters, should be in the same order at they appear in the SP..
a couple of things...
 

• your SP does 3 things, but neither one of them are relateds
• why execute a Select that doesn't do anything inside the SP ( SELECT au_id FROM titleauthor WHERE titleauthor.royaltyper = @percentage)
• to try it better can you split it to try every part by itself???

HTH

Gonzalo

Unfortunately, the re-ordering did not work.
Also;
the AddWithValue does not allow the data type.
The other two parameters are Int and Float, which do not require the len arguments.

Any other idea, please!

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.

look at the following code. Make sure to set the sqlcommand to connection.CreateCommand()

CREATE PROCEDURE GetDocumentSummary
(
    @DocumentID int,
    @DocumentSummary nvarchar(MAX) OUTPUT
)
AS
SET NOCOUNT ON
SELECT @DocumentSummary=Convert(nvarchar(MAX), DocumentSummary)
FROM Production.Document
WHERE DocumentID=@DocumentID

VB
Private Function GetDocumentSummary( _
  ByVal documentID As Integer) As String

    ' Assumes GetConnectionString returns a valid connection string.
    Using connection As New SqlConnection(GetConnectionString())
        connection.Open()
        Dim command As SqlCommand = connection.CreateCommand()

        ' Setup the command to execute the stored procedure.
        command.CommandText = "GetDocumentSummary"
        command.CommandType = CommandType.StoredProcedure

        ' Set up the input parameter for the DocumentID.
        Dim paramID As SqlParameter = _
            New SqlParameter("@DocumentID", SqlDbType.Int)
        paramID.Value = documentID
        command.Parameters.Add(paramID)

        ' Set up the output parameter to retrieve the summary.
        Dim paramSummary As SqlParameter = _
            New SqlParameter("@DocumentSummary", _
               SqlDbType.NVarChar, -1)
        paramSummary.Direction = ParameterDirection.Output
        command.Parameters.Add(paramSummary)

        ' Execute the stored procedure.
        command.ExecuteNonQuery()
        Console.WriteLine(paramSummary.Value)
        Return paramSummary.Value.ToString
    End Using
End Function

C#
static private string GetDocumentSummary(int documentID)
{
    //Assumes GetConnectionString returns a valid connection string.
    using (SqlConnection connection =
               new SqlConnection(GetConnectionString()))
    {
        connection.Open();
        SqlCommand command = connection.CreateCommand();
        try
        {
            // Setup the command to execute the stored procedure.
            command.CommandText = "GetDocumentSummary";
            command.CommandType = CommandType.StoredProcedure;

            // Set up the input parameter for the DocumentID.
            SqlParameter paramID =
                new SqlParameter("@DocumentID", SqlDbType.Int);
            paramID.Value = documentID;
            command.Parameters.Add(paramID);

            // Set up the output parameter to retrieve the summary.
            SqlParameter paramSummary =
                new SqlParameter("@DocumentSummary",
                SqlDbType.NVarChar, -1);
            paramSummary.Direction = ParameterDirection.Output;
            command.Parameters.Add(paramSummary);

            // Execute the stored procedure.
            command.ExecuteNonQuery();
            Console.WriteLine((String)(paramSummary.Value));
            return (String)(paramSummary.Value);
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
            return null;
        }
    }
}