Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > C# and C > C# 1.0 > C#
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old November 29th, 2007, 01:17 PM
Registered User
 
Join Date: Nov 2007
Location: Austin, Texas, USA.
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.

Reply With Quote
  #2 (permalink)  
Old November 29th, 2007, 01:22 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
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
================================================== =========
Reply With Quote
  #3 (permalink)  
Old November 30th, 2007, 03:16 AM
samjudson's Avatar
Friend of Wrox
Points: 8,687, Level: 40
Points: 8,687, Level: 40 Points: 8,687, Level: 40 Points: 8,687, Level: 40
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2007
Location: Newcastle, , United Kingdom.
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 -/
Reply With Quote
  #4 (permalink)  
Old December 6th, 2007, 05:20 AM
Authorized User
 
Join Date: Sep 2007
Location: , , Finland.
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());

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 03:00 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.