Solved

Capturing a RETURN_VALUE from a stored procedure in an ASP.net 2.0 C# application

Posted on 2006-06-20
6
760 Views
Last Modified: 2012-08-13
Hi Experts,

I know there's got to be a way to do this.  I'm builing a web app using ASP.NET 2.0, C#, and SQL server 2005.  All I was trying to do was get the int return value from the stored procedure, so I could use it in some sort of algorithm.  If anyone could give me an example of how this could be done I would appriciate it.  Here are samples of my stored procedure, and application code:

ALTER PROCEDURE [dbo].[sp_PopSTArchive]
AS
SET ANSI_WARNINGS OFF
BEGIN

IF EXISTS (SELECT * FROM tbl_ArchiveBillingST WHERE BillDates = (SELECT TOP 1 BillDates
FROM vew_BillingSetType  ))

RETURN 6
ELSE

IF EXISTS (SELECT * FROM tbl_MergeExtnAccount WHERE (Account = ' ' OR AccountSupervisor = ' ' OR
      Dept = ' '))

RETURN 5
ELSE
      
      INSERT INTO tbl_ArchiveBillingST (Inst, NameOnDigitalDisplay, SetType,
      Bldg, Room, Jack, COR, COS, CoveragePath1, Extn, Account, AccountSupervisor, Dept, SetTypeCost,
      BillDates)
      SELECT Inst, NameOnDigitalDisplay, SetType,
      Bldg, Room, Jack, COR, COS, CoveragePath1, Extn, Account, AccountSupervisor, Dept, SetTypeCost,
      BillDates
      FROM  vew_BillingSetType
      
      INSERT INTO tbl_ArchiveBillingLI (Inst, Extn, Date, Time, Numberdialed, Location, Length, Cost, AuthCode, BillDates, Account)
      SELECT Inst, Extn, Date, Time, Numberdialed, Location, Length, Cost, AuthCode, BillDates, Account
      FROM vew_BillingLongDistanceI
      
      INSERT INTO tbl_ArchiveBillingLD (Inst, Extn, Date, Time, Numberdialed, Location, Length, Cost, AuthCode, BillDates, Account)
      SELECT Inst, Extn, Date, Time, Numberdialed, Location, Length, Cost, AuthCode, BillDates, Account
      FROM vew_BillingLongDistanceD
      
      INSERT INTO tbl_Payment (Account, SetTypeCost, InternationalCost, DomesticCost,
                                    TotalCost, BillDates)
SELECT DISTINCT Account, COALESCE(Expr1,0), COALESCE(Expr2,0), COALESCE(IntTotal,0), (COALESCE(Expr1,0) + COALESCE(Expr2,0) + COALESCE (IntTotal,0))AS TotalCost, BillDates
FROM vew_TotalPayment
      --DELETE tbl_AHECExtOwners, tbl_AHECLongDistanceD, tbl_AHECLongDistanceI
      --GO

      RETURN 0
      END


    protected void Button1_Click(object sender, EventArgs e)
       
    {
        SqlConnection objConn = new SqlConnection();
        objConn.ConnectionString = "Data Source=CCDADAPP01;Initial Catalog=PhoneBilling;Integrated Security=True;";
        objConn.Open();
        SqlCommand objCmd = new SqlCommand("sp_PopSTArchive", objConn);
        objCmd.CommandType = System.Data.CommandType.StoredProcedure;
        SqlParameter objpar = new SqlParameter("@RETURN_VALUE", SqlDbType.Int);
        objpar.Direction = ParameterDirection.ReturnValue;
        objCmd.Parameters.Add(objpar);
        objCmd.ExecuteNonQuery();
       

        //TextBox2.Text = objpar.ToString();
       

        //if (returnvalue == 6)
        //{
        //    TextBox2.Text = "look out";
        //}
        //else
        //{
        //    TextBox2.Text = "Hello";
        //}
        objConn.Close();

        //LinkButton1.Text = "Click to preview final bill";
    }



Thanks!!
0
Comment
Question by:rketterer
6 Comments
 
LVL 37

Expert Comment

by:gregoryyoung
Comment Utility
this code seems to be reasonably ok what exactly are you having a problem with?



Are you meaning to use objpar.Value?

Cheers,

Greg
0
 

Author Comment

by:rketterer
Comment Utility
Yea,

All I was trying to do is get the objpar.Value, and use it in a situtaion kinda like the one I have commented out above:

if (objpar.Value == 6)
{
  Label1.Text = "This don't work
}
else

if {objpar.Value == 5) etc. etc.

Currently when I get the value for objpar; as I did in  //TextBox2.Text = objpar.ToString() it only return "@RETURN_VALUE" in the textbox.

Thanks again!
Rod
0
 
LVL 1

Accepted Solution

by:
reisrf earned 500 total points
Comment Utility
Try this to get the return value

string sResult = objCmd.Parameters["@RETURN_VALUE"].Value.ToString();

Then you can use Convert.ToInt32(sResult,10) to get as an Int value.

good luck
Robson Reis
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 25

Expert Comment

by:dstanley9
Comment Utility
use

TextBox2.Text = objpar.Value.ToString()
0
 

Author Comment

by:rketterer
Comment Utility
Thanks to everyone!!
0
 
LVL 37

Expert Comment

by:gregoryyoung
Comment Utility
Funny this sure looked like he repeated my answer, to use the Value property.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Bit flags and bit flag manipulation is perhaps one of the most underrated strategies in programming, likely because most programmers developing in high-level languages rely too much on the high-level features, and forget about the low-level ones. Th…
Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now