存储过程参数插入错误值

发布于 2024-07-13 13:11:43 字数 1529 浏览 7 评论 0原文

大家下午好,

我遇到存储过程插入错误值的问题。 下面是我的存储过程的总结...

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE PROCEDURE [dbo].[InsertDifferential]
    @differential int = null
AS
    BEGIN TRY
        BEGIN TRANSACTION
            UPDATE
                DifferentialTable
            SET
                differential = @differential
        COMMIT
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
        ROLLBACK
        DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int, @ErrorState INT
        SELECT @ErrMsg = ERROR_MESSAGE(), 
               @ErrSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();
        RAISERROR(@ErrMsg, @ErrSeverity, @ErrorState);
    END CATCH

下面是我用来调用存储过程的代码...

SqlConnection dbEngine = new SqlConnection(connectionString);
SqlCommand dbCmd = new SqlCommand("InsertDifferential", dbEngine);
SqlDataAdapter dataAdapter = new SqlDataAdapter(dbCmd);

dbCmd.CommandType = CommandType.StoredProcedure;

if (myobject.differential.HasValue)
{ dbCmd.Parameters.AddWithValue("@differential", myobject.differential); }
else
{ dbCmd.Parameters.AddWithValue("@differential", DBNull.Value); }

dbCmd.ExecuteNonQuery();

在数据库表中,差异列是一个可以为空的 int,没有默认值。

“myobject”的微分属性是一个 int? 数据类型默认设置为 null。

问题是当我运行存储过程时,差分列最终会出现 0。 即使“myobject.differential”为 null 并且我传入 DBNull.Value,该列最终仍会出现 0。 我尝试过不将 @difference 传递到存储过程中,但它仍然将该列设置为 0。

我尝试了许多不同的解决方案,但没有任何效果。

预先感谢您提供的任何帮助,

斯科特·维库斯基

Good afternoon everyone,

I am having an issue with a stored procedure inserting an incorrect value. Below is a summarization of my stored procedure ...

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE PROCEDURE [dbo].[InsertDifferential]
    @differential int = null
AS
    BEGIN TRY
        BEGIN TRANSACTION
            UPDATE
                DifferentialTable
            SET
                differential = @differential
        COMMIT
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
        ROLLBACK
        DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int, @ErrorState INT
        SELECT @ErrMsg = ERROR_MESSAGE(), 
               @ErrSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();
        RAISERROR(@ErrMsg, @ErrSeverity, @ErrorState);
    END CATCH

Below is the code I use to call the stored procedure ...

SqlConnection dbEngine = new SqlConnection(connectionString);
SqlCommand dbCmd = new SqlCommand("InsertDifferential", dbEngine);
SqlDataAdapter dataAdapter = new SqlDataAdapter(dbCmd);

dbCmd.CommandType = CommandType.StoredProcedure;

if (myobject.differential.HasValue)
{ dbCmd.Parameters.AddWithValue("@differential", myobject.differential); }
else
{ dbCmd.Parameters.AddWithValue("@differential", DBNull.Value); }

dbCmd.ExecuteNonQuery();

In the database table, the differential column is a nullable int with no default value.

The differential property of "myobject" is an int? data type set to null by default.

The issue is when I run the stored procedure, the differential column winds up with a 0 in place. Even if "myobject.differential" is null and I pass in the DBNull.Value the column still winds up with a 0 in place. I've tried not passing the @differential into the stored procedure and it still sets the column to 0.

I've tried a number of different solutions and nothing has worked.

Thank you in advance for any assistance,

Scott Vercuski

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

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

发布评论

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

评论(3

长不大的小祸害 2024-07-20 13:11:43

我相信,当您设置像这样的参数的默认值时,

@differential int = null

您不需要将其添加到 SQL 命令中。

尝试仅使用此代码而不包含其他内容...

if (myobject.differential.HasValue)
{ 
   dbCmd.Parameters.AddWithValue("@differential", myobject.differential); 
}

I believe that when you set the default value on a parameter like

@differential int = null

You do not need to add it to your SQL Command.

Try the code with just this and do not include the else...

if (myobject.differential.HasValue)
{ 
   dbCmd.Parameters.AddWithValue("@differential", myobject.differential); 
}
懒的傷心 2024-07-20 13:11:43

您是否确定差分列的默认值约束不为零?

Are you certian that the differential column does not have a Default Value Constraint on it of zero?

故事未完 2024-07-20 13:11:43

还要检查表上是否有触发器将空值设置为零。

ALso check to see if there is a trigger on the table setting null values to zero.

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