SQL 数据源 - 存储过程和返回参数
我在 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 技术交流群。

绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
更多
发布评论
评论(2)
我认为这个说法是你的问题。
您不需要将存储过程的返回值显式分配给返回值参数。只需指定存储过程的名称即可。
另一件事是,您必须在
OnInserted
事件处理程序中的参数名称前面加上“@”。I think this statement is your problem.
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.
Another thing is that you will have to precede the parameter name in your
OnInserted
event handler with '@'.试试这个:
Try this: