如何在aspx页面中检索存储过程的字符串输出参数 [英] how to retrieve string out parameter of a stored procedure in aspx page

问题描述

你好先生.
我有一个存储过程，该过程返回字符串作为out参数.该字符串是
打印``记录已成功插入''
我想在aspx页面的标签上打印此消息.
这个怎么做.任何身体请帮助我.

我的存储过程是

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

我想在我的aspx页面上打印此打印声明.
我的aspx.cs页面在下面

{
       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();
   }

谢谢.

解决方案

SQL中的print``text statement''不被视为输出参数，就像.NET开发环境中的Console.Write(''Text'')一样.

因此，请更改您的sp:

  ALTER   proc  [dbo].[sp_insert]
(
 @ eid   int ，
 @ ename   nvarchar ( 50 )，
 @ image   nvarchar ( 50 )，
 @ outString   nvarchar ( 100 )输出
)
原为 开始
插入 插入标签(eid，ename，图像)
值(
 @ eid ，
 @ ename ，
 @ image 
)
 SET   @ outString  = ' 记录已成功插入'
结束 

在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();
            }
        }
    }

程序更改

  ALTER   proc  [dbo].[sp_insert]
(
 @ eid   int ，
 @ ename   nvarchar ( 50 )，
 @ image   nvarchar ( 50 )，
 @ print   nvarchar ( 50 )输出
)
原为 开始
插入 插入标签(eid，ename，图像)
值(
 @ eid ，
 @ ename ，
 @ image 
)
 选择  @ print  = ' 记录已成功插入'
结束 

现在执行代码，您将获得@print参数的值

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.

my stored procedure is

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

{
       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.

解决方案

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:

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();
            }
        }
    }

change in your procedure

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

这篇关于如何在aspx页面中检索存储过程的字符串输出参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！