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
439 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
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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

786 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