Hi, I'm have a problem of not receiving the correct return value from a SP when using C# code. Please see the C# code below:


using System;
using System.Data;
using System.Collections;
using System.Data.Common;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.ComponentModel;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Microsoft.SqlServer.Server;
using CAPS.BO;
using CAPS.BLL;

namespace CAPS.DAL
{

/// <summary>
/// Summary description for ActvGarbageCanDB
/// </summary>

public class ActvGarbageCanDB
{
    #region Public Methods

    /// <summary>
    /// Deletes CAPS_PARTICIPATION_ACTIVITY records(by year date) from the database.
    /// </summary>
    /// <param name="iYearID">The ID of the year to delete.</param>
    /// <returns>Returns the number of records effected when the object was deleted successfully.</returns>



    public static int Delete(ActvGarbageCanBO myYear)
   {

       int result;

        using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrin gs["CAPSConnectionString"].ConnectionString))
        {
            SqlCommand myCommand = new SqlCommand("DELETE_GARBAGE", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;

            myCommand.Parameters.Add("@STARTDATE",SqlDbType.Da teTime);
            myCommand.Parameters["@STARTDATE"].Value = myYear.STARTDATE;

            myCommand.Parameters.Add("@ENDDATE",SqlDbType.Date Time );
            myCommand.Parameters["@ENDDATE"].Value = myYear.ENDDATE;

            myCommand.Parameters.Add("@Count", SqlDbType.Int);
            //myCommand.Parameters = ParameterDirection.ReturnValue;
            myCommand.Parameters["@Count"].Direction = ParameterDirection.Output;

            myConnection.Open();
            myCommand.ExecuteNonQuery();
            //int returnValue;
            int returnValue = (int)myCommand.Parameters["@Count"].Value;

            myConnection.Close();
            result = returnValue;
        }
        return result;

    }
     #endregion
}
}



I was hopping that you could give me some ideas as to what is the problem. I get the correct value from the SP when Exec from query analyzer, however, when called from the C# code I get a value of "0". Also, when I use a literal value in the SP, I get the correct return value. Please help me if you can! Thanks.

How are you setting the output param in your stored procedure?

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor :.
Wrox Books 24 x 7
================================================== =========

There is a big difference between ParameterDirection.Output and ParameterDirection.ReturnValue. Have you tried both?

/- Sam Judson : Wrox Technical Editor -/

Propably the easiest way is to just SELECT the correct value in SP like this:

CREATE PROCEDURE Foo(...)
BEGIN
  -- Do your stuff
  SELECT 1;
END

And then you retrieve the value by calling SqlCommand.ExecuteScalar and cast it's value to correct type (int in this case).

int value = Convert.ToInt32(myCommand.ExecuteScalar());