Go Premium for a chance to win a PS4. Enter to Win

x
?
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
?
770 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

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…
Hello there! As a developer I have modified and refactored the unit tests which was written by fellow developers in the past. On the course, I have gone through various misconceptions and technical challenges when it comes to implementation. I would…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

885 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