?
Solved

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

Posted on 2006-06-20
6
Medium Priority
?
772 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
ID: 16946099
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
ID: 16946183
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 2000 total points
ID: 16948241
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 25

Expert Comment

by:dstanley9
ID: 16951235
use

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

Author Comment

by:rketterer
ID: 16952287
Thanks to everyone!!
0
 
LVL 37

Expert Comment

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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Najam
Having new technologies does not mean they will completely replace old components.  Recently I had to create WCF that will be called by VB6 component.  Here I will describe what steps one should follow while doing so, please feel free to post any qu…
Introduction This article series is supposed to shed some light on the use of IDisposable and objects that inherit from it. In essence, a more apt title for this article would be: using (IDisposable) {}. I’m just not sure how many people would ge…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
How to fix display issue, screen flickering issue when I plug in power cord to the machine. Before I start explaining the solution lets check out once the issue how it looks like after I connect the power cord. most of you also have faced this…
Suggested Courses

621 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