存储过程参数插入错误值
大家下午好,
我遇到存储过程插入错误值的问题。 下面是我的存储过程的总结...
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 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
我相信,当您设置像这样的参数的默认值时,
您不需要将其添加到 SQL 命令中。
尝试仅使用此代码而不包含其他内容...
I believe that when you set the default value on a parameter like
You do not need to add it to your SQL Command.
Try the code with just this and do not include the else...
您是否确定差分列的默认值约束不为零?
Are you certian that the differential column does not have a Default Value Constraint on it of zero?
还要检查表上是否有触发器将空值设置为零。
ALso check to see if there is a trigger on the table setting null values to zero.