Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

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
?
768 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Introduction Although it is an old technology, serial ports are still being used by many hardware manufacturers. If you develop applications in C#, Microsoft .NET framework has SerialPort class to communicate with the serial ports.  I needed to…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

722 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