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
447 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
[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
  • 5
  • 2
8 Comments
 
LVL 3

Expert Comment

by:jmro20
ID: 34873805
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
ID: 34873937
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
ID: 34873945
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
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 3

Expert Comment

by:jmro20
ID: 34873972
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
 
LVL 9

Assisted Solution

by:sarabhai
sarabhai earned 250 total points
ID: 34874154
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
ID: 34874473
That's almost the same I said.
0
 
LVL 3

Expert Comment

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

Expert Comment

by:CodeCruiser
ID: 34874636
>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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

726 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