How to retrieve string out parameter of a stored procedure in aspx page

Question

3.33/5 (3 votes)

See more:

hello sir.
i have a stored procedure which returns string as a out parameter. that string is
print 'Records are successfully inserted'
i want to print this message on a label in aspx page.
how to do this. any body plz help me.

What I have tried:

my stored procedure is

SQL

ALTER proc [dbo].[sp_insert]
(
@eid int,
@ename nvarchar(50),
@image nvarchar(50)
)
as begin 
insert into tab (eid , ename , image )
values(
@eid,
@ename,
@image 
)
print 'Records are inserted successfully'
end

i want to print this print statement on my aspx page.
my aspx.cs page is below

C#

{
       DataSet ds = new DataSet();
       SqlParameter[] oparam = new SqlParameter[3];
       oparam[0] = new SqlParameter("@eid", Txteid.Text);
       oparam[1] = new SqlParameter("@ename", Txtename.Text);


       FU.SaveAs("E:\\BackUpWebsites\\sri\\images\\" + FU.FileName);
       Server.MapPath("images\\" + FU.FileName);

       oparam[2] = new SqlParameter("@image", "E:\\BackUpWebsites\\sri\\images\\" + FU.FileName );


       ds = BusinessLogic.InsertDetails(oparam );
       SqlParameter paramoutput=new SqlParameter ("@print", SqlDbType .NVarChar );
       paramoutput .Direction =ParameterDirection .Output ;
       paramoutput .Value =10;
       LblResult.Visible = true;
       LblResult.Text = paramoutput.Value.ToString();
   }

thanking you.

Posted 30-Dec-10 0:22am

sathish.jampala

Updated 6-May-23 14:57pm

merano99

v3

Add a Solution

2 solutions

Solution 1

change in your procedure

SQL

ALTER proc [dbo].[sp_insert]
(
@eid int,
@ename nvarchar(50),
@image nvarchar(50),
@print nvarchar(50) output 
)
as begin 
insert into tab (eid , ename , image )
values(
@eid,
@ename,
@image 
)
 select @print = 'Records are inserted successfully'
end

now execute code you get value of @print parameter

Posted 30-Dec-10 0:40am

Pranay Rana

Updated 30-Dec-10 7:00am

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

jerrykid · Accepted Answer · 2010-12-30T00:45:00

Hi,

print 'text statement' in SQL is not considered as an output parameter, it's like as Console.Write('Text') in .NET development environment.

So, please change your sp:

SQL

ALTER proc [dbo].[sp_insert]
(
@eid int,
@ename nvarchar(50),
@image nvarchar(50),
@outString nvarchar(100) output
)
as begin 
insert into tab (eid , ename , image )
values(
@eid,
@ename,
@image 
)
SET @outString = 'Records are inserted successfully'
end

In aspx:

public void InsertSP()
    {
        SqlConnection cnn = new SqlConnection("your connection string here");
        try
        {            
            SqlCommand cmd = new SqlCommand("sp_insert", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@eid", SqlDbType.Int);
            cmd.Parameters.Add("@ename", SqlDbType.NVarChar, 50);
            cmd.Parameters.Add("@image", SqlDbType.NVarChar, 50);
            cmd.Parameters.Add("@outString", SqlDbType.NVarChar, 100).Direction = ParameterDirection.Output;
            cnn.Open();
            cmd.ExecuteNonQuery();
            LblResult.Visible = true;
            LblResult.Text = cmd.Parameters["@outString"].Value.ToString();

        }
        catch (Exception ex)
        {
            //Process error
        }
        finally
        {
            if (cnn != null && cnn.State == ConnectionState.Open)
            {
                cnn.Close();
            }
        }
    }