Wrox Programmer Forums
Go Back   Wrox Programmer Forums > C# and C > C# 1.0 > C#
|
C# Programming questions specific to the Microsoft C# language. See also the forum Beginning Visual C# to discuss that specific Wrox book and code.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the C# section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 29th, 2007, 01:17 PM
Registered User
 
Join Date: Nov 2007
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Stored Procedure return value

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.

 
Old November 29th, 2007, 01:22 PM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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
================================================== =========
 
Old November 30th, 2007, 03:16 AM
samjudson's Avatar
Friend of Wrox
 
Join Date: Aug 2007
Posts: 2,128
Thanks: 1
Thanked 189 Times in 188 Posts
Default

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

/- Sam Judson : Wrox Technical Editor -/
 
Old December 6th, 2007, 05:20 AM
Authorized User
 
Join Date: Sep 2007
Posts: 92
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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());






Similar Threads
Thread Thread Starter Forum Replies Last Post
Stored Procedure return value is NULL kshort ASP.NET 2.0 Basics 16 March 14th, 2017 12:45 PM
[Resolved] Get return value from stored procedure snufse ASP.NET 2.0 Basics 1 August 8th, 2008 11:05 AM
how can i return a recrdset in a stored procedure? arathy ASP.NET 1.0 and 1.1 Basics 0 December 12th, 2006 04:24 AM
Can i return a recordset in a Stored procedure chiefouko VB Databases Basics 6 December 12th, 2006 03:34 AM
Can i return a recordset in a Stored procedure chiefouko SQL Server 2000 1 June 23rd, 2003 06:13 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.