Solved

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

Posted on 2006-06-20
6
762 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 500 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
Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

This article describes a simple method to resize a control at runtime.  It includes ready-to-use source code and a complete sample demonstration application.  We'll also talk about C# Extension Methods. Introduction In one of my applications…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project

832 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