SQL 数据源 - 存储过程和返回参数

发布于 2024-08-10 05:19:01 字数 1033 浏览 14 评论 0原文

我在 SQL Server 中有一个返回值的存储过程:

CREATE PROCEDURE [dbo].[insertProc]
    @value1 INT,
    @value2 INT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO table1(value1,value2) VALUES (@value1,@value2)

    RETURN SCOPE_IDENTITY();
END

我使用 SQL 数据源从 ASP.NET 连接到数据库,其配置如下:

InsertCommand="@insertedId = insertProc"
InsertCommandType="StoredProcedure"

oninserting="sqlDS_Inserting" 
oninserted="sqlDS_Inserted"

<InsertParameters>
    <asp:Parameter Name="value1" />
    <asp:Parameter Name="value2" />
    <asp:Parameter Name="insertedId" DbType="Int32" Direction="ReturnValue" />
</InsertParameters>

我想要做什么来获取返回值。在 sqlDS_Inserted 过程的主体中,我确实喜欢这样:

this.insertedId = Convert.ToInt32(e.Command.Parameters["insertedId"].Value);

但我收到错误:

Object cannot be cast from DBNull to other types.

但是,当我查看 SQL Server Profiler 并运行命令(添加 @insertedId 变量的声明)时,它运行良好。问题是什么?如何从 ASP.NET 获取存储过程的返回值?

I have a store procedure in SQL Server which returns a value:

CREATE PROCEDURE [dbo].[insertProc]
    @value1 INT,
    @value2 INT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO table1(value1,value2) VALUES (@value1,@value2)

    RETURN SCOPE_IDENTITY();
END

I connect to the DB from ASP.NET using SQL Data Source, which is configured like this:

InsertCommand="@insertedId = insertProc"
InsertCommandType="StoredProcedure"

oninserting="sqlDS_Inserting" 
oninserted="sqlDS_Inserted"

<InsertParameters>
    <asp:Parameter Name="value1" />
    <asp:Parameter Name="value2" />
    <asp:Parameter Name="insertedId" DbType="Int32" Direction="ReturnValue" />
</InsertParameters>

What I want to do it to get the returned value. In the body of sqlDS_Inserted procedure I do like this:

this.insertedId = Convert.ToInt32(e.Command.Parameters["insertedId"].Value);

but I get error:

Object cannot be cast from DBNull to other types.

However, when I look at SQL Server Profiler and run the command (adding declaration of @insertedId variable) it works good. What is the problem and how can I get the returned value of stored procedure from ASP.NET?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

长伴 2024-08-17 05:19:01

我认为这个说法是你的问题。

InsertCommand="@insertedId = insertProc"

您不需要将存储过程的返回值显式分配给返回值参数。只需指定存储过程的名称即可。

 InsertCommand = "InsertProc"

另一件事是,您必须在 OnInserted 事件处理程序中的参数名称前面加上“@”。

this.insertedId = Convert.ToInt32(e.Command.Parameters["@insertedId"].Value);

I think this statement is your problem.

InsertCommand="@insertedId = insertProc"

You don't need to explicitly assign the return value of the stored procedure to the return value parameter. Just specify the name of the stored procedure.

 InsertCommand = "InsertProc"

Another thing is that you will have to precede the parameter name in your OnInserted event handler with '@'.

this.insertedId = Convert.ToInt32(e.Command.Parameters["@insertedId"].Value);
静谧 2024-08-17 05:19:01

试试这个:

{ 
    SqlConnection conMyData = default(SqlConnection); 
    SqlCommand cmdInsert = default(SqlCommand); 
    SqlParameter parmReturnValue = default(SqlParameter); 
    long l = 0; 

    conMyData = new SqlConnection(_SQLDBConnString); 
    cmdInsert = new SqlCommand("insEmployee", conMyData); 

    { 
        cmdInsert.CommandType = CommandType.StoredProcedure; 
        parmReturnValue = cmdInsert.Parameters.Add("RETURN_VALUE", SqlDbType.BigInt); 
        parmReturnValue.Direction = ParameterDirection.ReturnValue; 
        cmdInsert.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = strFirstName; 
        cmdInsert.Parameters.Add("@MiddleInitial", SqlDbType.VarChar).Value = strMI; 
        conMyData.Open(); 
        cmdInsert.ExecuteNonQuery(); 
        l = (long)cmdInsert.Parameters("RETURN_VALUE").Value; 
    } 

    return l; 
} 

Try this:

{ 
    SqlConnection conMyData = default(SqlConnection); 
    SqlCommand cmdInsert = default(SqlCommand); 
    SqlParameter parmReturnValue = default(SqlParameter); 
    long l = 0; 

    conMyData = new SqlConnection(_SQLDBConnString); 
    cmdInsert = new SqlCommand("insEmployee", conMyData); 

    { 
        cmdInsert.CommandType = CommandType.StoredProcedure; 
        parmReturnValue = cmdInsert.Parameters.Add("RETURN_VALUE", SqlDbType.BigInt); 
        parmReturnValue.Direction = ParameterDirection.ReturnValue; 
        cmdInsert.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = strFirstName; 
        cmdInsert.Parameters.Add("@MiddleInitial", SqlDbType.VarChar).Value = strMI; 
        conMyData.Open(); 
        cmdInsert.ExecuteNonQuery(); 
        l = (long)cmdInsert.Parameters("RETURN_VALUE").Value; 
    } 

    return l; 
} 
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文