代码之家 › 专栏 › 技术社区 › Rich Andrews

SQL 2005的返回值sp返回dbnull-哪里出错了?

sql-server-2005 tsql vb.net sql asp.net

Rich Andrews · 技术社区 · 16 年前

这是SP…

USE [EBDB]
GO
/****** Object:  StoredProcedure [dbo].[delete_treatment_category]    Script Date: 01/02/2009 15:18:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
RETURNS 0 FOR SUCESS
        1 FOR NO DELETE AS HAS ITEMS
        2 FOR DELETE ERROR
*/

ALTER PROCEDURE [dbo].[delete_treatment_category]
(
    @id INT
)
AS
    SET NOCOUNT ON

    IF EXISTS
    (
        SELECT id
        FROM dbo.treatment_item
        WHERE category_id = @id
    )
    BEGIN
        RETURN 1
    END 
    ELSE
    BEGIN
        BEGIN TRY
            DELETE FROM dbo.treatment_category
            WHERE id = @id
        END TRY

        BEGIN CATCH
            RETURN 2
        END CATCH 

        RETURN 0                        
    END

我尝试使用下面的代码(vb.net中的sqldatasource&gridview组合)获取返回值

Protected Sub dsTreatmentCats_Deleted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles dsTreatmentCats.Deleted
    Select Case CInt(e.Command.Parameters(0).Value)
    Case 0
        'it worked so no action
        lblError.Visible = False
    Case 1
        lblError.Text = "Unable to delete this category because it still has treatments associated with it."
        lblError.Visible = True
    Case 2
        lblError.Text = "Unable to delete this category due to an unexpected error. Please try again later."
        lblError.Visible = True
End Select
End Sub

问题是行cint(e.command.parameters(0.value)返回dbnull而不是返回值,但仅在删除时返回-此方法在更新和插入时都可以正常工作。

希望我只是有点迟钝,错过了一些明显的东西——有什么想法吗?

编辑

我仍然有这个问题,并尝试了下面的所有选项,但没有任何效果-我很惊讶没有其他人有这个问题?

添加参数的代码:

<asp:SqlDataSource ID="dsTreatmentCats" runat="server" 
        ConnectionString="<%$ ConnectionStrings:EBDB %>" 
        DeleteCommand="delete_treatment_category" DeleteCommandType="StoredProcedure" 
        InsertCommand="add_treatment_category" InsertCommandType="StoredProcedure" 
        SelectCommand="get_treatment_categories" SelectCommandType="StoredProcedure" 
        UpdateCommand="update_treatment_category" 
        UpdateCommandType="StoredProcedure" ProviderName="System.Data.SqlClient">

    <DeleteParameters>
        <asp:Parameter Direction="ReturnValue" Name="RetVal" Type="Int32" />
        <asp:Parameter Name="id" Type="Int32" />
    </DeleteParameters>
    <UpdateParameters>
        <asp:Parameter Direction="ReturnValue" Name="RetVal" Type="Int32" />
        <asp:Parameter Name="id" Type="Int32" />
        <asp:Parameter Name="name" Type="String" />
        <asp:Parameter Name="additional_info" Type="String" />
    </UpdateParameters>
    <InsertParameters>
        <asp:Parameter Direction="ReturnValue" Name="RetVal" Type="Int32" />
        <asp:ControlParameter ControlID="txtCat" Name="name" PropertyName="Text" 
            Type="String" />
        <asp:ControlParameter ControlID="txtAddInfo" Name="additional_info" 
            PropertyName="Text" Type="String" />
    </InsertParameters>
</asp:SqlDataSource>

6 回复 | 直到 12 年前

Joe 15 年前

我在这里玩得有点晚,但为了那些偶然发现这个问题的人…

如果您在ADO.NET中使用ExecuteReader,则在关闭读卡器或数据库的基础连接之前,不会填充返回值。( See here )

这不起作用:

SqlConnection conn = new SqlConnection(myConnectionString);
 SqlCommand cmd = new SqlCommand(mySqlCommand, conn);

 //     Set up your command and parameters

 cmd.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

 SqlDataReader reader = cmd.ExecuteReader();
 while (reader.Read())
 {
       //     Read your data
 }

 int resultCount = (int)cmd.Parameters["@Return"].Value;
 conn.Close();
 return resultCount;

这将:

SqlConnection conn = new SqlConnection(myConnectionString);
 SqlCommand cmd = new SqlCommand(mySqlCommand, conn);

 //     Set up your command and parameters

 cmd.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

 SqlDataReader reader = cmd.ExecuteReader();
 while (reader.Read())
 {
       //     Read your data
 }

 conn.Close();
 int resultCount = (int)cmd.Parameters["@Return"].Value;
 return resultCount;

Mark Brackett Achilles Ram Nakirekanti 16 年前

添加参数时,是否将方向设置为 ReturnValue ?

Rich Andrews 16 年前

是的-我使用的是sqldatasource控件,它为我嗅出参数,包括具有正确方向设置的返回值。只是为了好玩,我也从零开始创建了带有返回值方向的参数,但没有乐趣:(

gbn 16 年前

在SQL工具中运行此命令,以确保存储过程的行为符合预期。

DECLARE @rtn int;
EXEC @rtn = dbo.delete_treatment_category /*insert valid id here > 2*/;
SELECT @rtn;

我提到“身份证2”是因为你可以正在读取错误的参数。也就是说,这个存储过程有两个参数…一个用于标识,另一个用于返回值。

IIRC:

cmd.CommandType = CommandType.StoredProcedure 'cmd is SqlCommand

Dim retValParam as New SqlParameter("@RETURN_VALUE", SqlDbType.Int)
retValParam.Direction = ParameterDirection.ReturnValue
cmd.Parameters.Add(retValParam)

'add the ID parameter here

'execute

'look at the @RETURN_VALUE parameter here

Marc Gravell 15 年前

不显示添加参数和执行命令的代码。两者都可能很关键。

我知道复制这个的一种方法-如果您的过程还返回行(例如,从一个删除触发器),并且您没有使用这些行…基本上,输出/返回参数值跟随 TDS流中的网格,因此如果您还没有读取网格(使用ExecuteReader时),则无法获取更新的参数/返回值。但是如果你用 ExecuteNonQuery 这不应该是一个因素。

Mark Brittingham 15 年前

为什么删除参数使用name=“return_value”,而更新和插入参数使用name=“retval”?如果后两种方法有效的话,那就是我要找的第一个地方。