Stored Procedure fails to see parameters or returns a convert data type error

Question

I've run into a rather annoying problem which I cannot seem to get to the root of. I've searched the internet for similar problems - and I've found a few unanswered in other forums - so I thought I'd give it a go here.

The following WebMethod of a webservice contacts a database, which works fine, and runs a stored procedure.

The problem is, that when I pass the parameters as single characters it tells me it cannot find either of the parameters and when I pass a full length "ean" parameter it tells me the following error message:

System.Data.OleDb.OleDbException: Error converting data type varchar to int.
   at System.Data.OleDb.OleDbDataReader.ProcessResults(OleDbHResult hr)
   at System.Data.OleDb.OleDbDataReader.NextResult()
   at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
   at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.OleDb.OleDbCommand.ExecuteReader()
   at Service.GetOthersRatings(Int32 personID, String ean)

And now here's the Webmethod:

[WebMethod(Description = "GetRatings")]
public string GetRatings(int personID, string ean)
{
    string ratings = "";
    OleDbConnection connection = new OleDbConnection(connectionString);
    OleDbCommand command = new OleDbCommand("GetRatings", connection);
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.Add("@personID",OleDbType.Integer,10).Value = personID;
    command.Parameters.Add("@ean",OleDbType.VarChar,30).Value = ean;
    try
    {
        connection.Open();
        myReader = command.ExecuteReader();
        if (myReader.HasRows)
        {
            while (myReader.Read())
            {
                ratings = myReader.GetString(0);
            }
        }
        else
        {
            ratings = "Null";
        }
    }
   catch (Exception e)
    {
        ratings = "Error - " + e.ToString();
    }
    finally
    {
    }
    return ratings;
}

One thing that's worth mentioning is that if I remove the OUTPUT part of the SP, it runs fine down to and returns

    ratings = "Null";

But seeing that I try to read a String from the reader, I don't see why it wouldn't work with a varchar output from the SP.

In the Database we have the corresponding Stored Procedure, which works fine if just executed in SQL Server Management Studio:

IF ( OBJECT_ID('GetRatings') IS NOT NULL ) 
DROP PROCEDURE GetRatings
GO

CREATE PROCEDURE GetRatings
    @ratingschars           varchar(36) = NULL OUTPUT,
    @personID               int,
    @ean                    varchar(30)

AS
BEGIN TRAN
SET NOCOUNT ON;
    BEGIN
    DECLARE @pris varchar(2)
    DECLARE @forventet varchar(2)
    DECLARE @smag varchar(2)
    DECLARE @count varchar(30)

    IF EXISTS(SELECT * FROM feedback where personID = @personID AND ean = @ean)
        BEGIN 
            SELECT @pris = (SELECT CAST(pris AS varchar(2)) FROM feedback   where personID = @personID AND ean = @ean)
            SELECT @forventet = (SELECT CAST(forventet AS varchar(2)) FROM feedback where personID = @personID AND ean = @ean)
            SELECT @smag = (SELECT CAST(smag AS varchar(2)) FROM feedback where personID = @personID AND ean = @ean)
            SELECT @ratingschars = @pris + @forventet + @smag
        END
    ELSE
        BEGIN
            SELECT @pris = (SELECT CAST(avg(pris) AS varchar(2)) FROM feedback WHERE ean = @ean)
            SELECT @forventet += (SELECT CAST(avg(forventet) AS varchar(2)) FROM feedback WHERE ean = @ean)
            SELECT @smag += (SELECT CAST(avg(smag) AS varchar(2)) FROM feedback WHERE ean = @ean)
            SELECT @count += (SELECT CAST(count(*) AS varchar(30)) FROM feedback WHERE ean = @ean)
            SELECT @ratingschars = @pris + @forventet + @smag + @count
        END
    END
COMMIT TRAN

Which I've tried to change to output of to int, with the same error results. I'm stumped - I need help.

Answer 1

Stored-procedure has three parameters where as in your code, you've added only two parameters. So add a third parameter as OutPut type.

EDIT:

Stored Procedure:

ALTER PROCEDURE SampleProc

@no1 int,
@no2 int,
@result int OUTPUT
AS
  set @result=@no1+@no2
RETURN

Code to execute the Stored-procedure:

cmd.CommandText = "SampleProc"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = cn

Dim no1 as New OleDbParameter("@no1", OleDbType.Integer)
Dim no2 as New OleDbParameter("@no2", OleDbType.Integer)
Dim resultas New OleDbParameter("@result", OleDbType.Integer)
result.Direction = ParameterDirection.Output

no1.Value = 10
no2.Value = 20

cmd.Parameters.Add(no1)
cmd.Parameters.Add(no2)
cmd.Parameters.Add(result)

cn.Open()
cmd.ExecuteNonQuery()
cn.Close()

Dim returnResult as Integer 
returnResult=CType(result.Value,Integer)