Solved

How do I execute and return one output parameter from a stored procedure which returns two output parameters  in ASP.NET?

Posted on 2011-02-11
8
436 Views
Last Modified: 2012-08-13
I have a stored procedure which returns two output parameters.How do I execute and return one output parameter from a stored procedure which returns two output parameters  in ASP.NET?
0
Comment
Question by:AppDevs
  • 5
  • 2
8 Comments
 
LVL 3

Expert Comment

by:jmro20
Comment Utility
When you create the parameters there is a property called Direction and set it to Output

After executing evaluate that parameter value.
0
 
LVL 9

Expert Comment

by:sarabhai
Comment Utility
can you show the code both in asp.net and store procedure for simplification?
0
 
LVL 3

Accepted Solution

by:
jmro20 earned 250 total points
Comment Utility
You can do something like this:

'Sql Command
mSqlCommand = New SqlCommand
mSqlCommand.Connection = YourConnection
mSqlCommand.CommandTimeout = iTimeout
mSqlCommand.CommandText = yourSP
mSqlCommand.CommandType = CommandType.StoredProcedure

Dim param As SqlParameter() = { _
            New SqlParameter("@ParameterInput", SqlDbType.VarChar, 2), _
            New SqlParameter("@ParameterOutput1", SqlDbType.VarChar, 4), _
            New SqlParameter("@ParameterOutput2", SqlDbType.VarChar)
        }

param(0).Value = yourInputValue
param(1).Direction = ParameterDirection.Output
param(2).Direction = ParameterDirection.Output

'Add Parameters
For Each param In paramArr
   mSqlCommand.Parameters.Add(param)
 Next

'Exec NonQuery
 mSqlCommand.ExecuteNonQuery()
OutputValue1 = Convert.ToString(mSqlCommand.Parameters"("@ParameterOutput1).Value)
OutputValue2 = Convert.ToString(mSqlCommand.Parameters"("@ParameterOutput2).Value)

Open in new window

0
 
LVL 3

Expert Comment

by:jmro20
Comment Utility
There was an error in the loop to add parameters
'Sql Command
mSqlCommand = New SqlCommand
mSqlCommand.Connection = YourConnection
mSqlCommand.CommandTimeout = iTimeout
mSqlCommand.CommandText = yourSP
mSqlCommand.CommandType = CommandType.StoredProcedure

Dim paramArr As SqlParameter() = { _
            New SqlParameter("@ParameterInput", SqlDbType.VarChar, 2), _
            New SqlParameter("@ParameterOutput1", SqlDbType.VarChar, 4), _
            New SqlParameter("@ParameterOutput2", SqlDbType.VarChar)
        }

param(0).Value = yourInputValue
param(1).Direction = ParameterDirection.Output
param(2).Direction = ParameterDirection.Output

'Add Parameters
For Each param As SqlParameter In paramArr
   mSqlCommand.Parameters.Add(param)
 Next

'Exec NonQuery
 mSqlCommand.ExecuteNonQuery()
OutputValue1 = Convert.ToString(mSqlCommand.Parameters"("@ParameterOutput1).Value)
OutputValue2 = Convert.ToString(mSqlCommand.Parameters"("@ParameterOutput2).Value)

Open in new window

0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 9

Assisted Solution

by:sarabhai
sarabhai earned 250 total points
Comment Utility
following is just sample for you...
what error come at your code.

SqlCommand cmd = new SqlCommand("CustOrderOne", cn);
cmd.CommandType=CommandType.StoredProcedure ;
SqlParameter parm=new SqlParameter("@CustomerID",SqlDbType.NChar) ;
parm.Value="ALFKI";
parm.Direction =ParameterDirection.Input ;
cmd.Parameters.Add(parm);
SqlParameter parm2=new SqlParameter("@ProductName",SqlDbType.VarChar);
parm2.Size=50;
parm2.Direction=ParameterDirection.Output;
cmd.Parameters.Add(parm2);
SqlParameter parm3=new SqlParameter("@Quantity",SqlDbType.Int);
parm3.Direction=ParameterDirection.Output;
cmd.Parameters.Add(parm3);
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
0
 
LVL 3

Expert Comment

by:jmro20
Comment Utility
That's almost the same I said.
0
 
LVL 3

Expert Comment

by:jmro20
Comment Utility
Correcting another error:
OutputValue1 = Convert.ToString(mSqlCommand.Parameters("@ParameterOutput1").Value)
OutputValue2 = Convert.ToString(mSqlCommand.Parameters("@ParameterOutput2").Value)
0
 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
>How do I execute and return one output parameter from a stored procedure which returns two output parameters  in ASP.NET?

Provide it two parameters but ignore the return value of one parameter.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

743 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

8 Experts available now in Live!

Get 1:1 Help Now