对象不能从 DBNull 转换为其他类型。
今天程序执行以下代码,时有时无的抛出错误:对象不能从 DBNull 转换为其他类型。
Database db = DatabaseFactory.CreateDatabase();
string sqlCommand =
"INSERT S_StorageInput (InputNo, WarehouseId, CreatedUser,BusinessDate, CreatedDateTime, UpdatedDateTime, StorageInputType, IsDeleted, IsCheck, DepartmentId, SupplierId,DealerId,StockNo, OrderNo,BoxUpNo, Remark,BillNo,UpdatedUser)" +
"VALUES (@InputNo, @WarehouseId, @CreatedUser,@BusinessDate, @CreatedDateTime, @UpdatedDateTime, @StorageInputType, @IsDeleted, @IsCheck, @DepartmentId, @SupplierId,@DealerId, @StockNo, @OrderNo,@BoxUpNo,@Remark,@BillNo,@UpdatedUser)";
sqlCommand += " ;SELECT SCOPE_IDENTITY()";
DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);
db.AddInParameter(dbCommand, "DepartmentId", DbType.Int32, storageInput.DepartmentId); //FK
db.AddInParameter(dbCommand, "InputNo", DbType.String, storageInput.InputNo);
db.AddInParameter(dbCommand, "WarehouseId", DbType.Int32, storageInput.WarehouseId);
db.AddInParameter(dbCommand, "CreatedUser", DbType.String, storageInput.CreatedUser);
db.AddInParameter(dbCommand, "BusinessDate", DbType.DateTime, storageInput.BusinessDate);
db.AddInParameter(dbCommand, "CreatedDateTime", DbType.DateTime, DateTime.Now);
db.AddInParameter(dbCommand, "UpdatedDateTime", DbType.DateTime, DateTime.Now);
db.AddInParameter(dbCommand, "StorageInputType", DbType.Int16, storageInput.StorageInputType);
db.AddInParameter(dbCommand, "IsDeleted", DbType.Boolean, storageInput.IsDeleted);
db.AddInParameter(dbCommand, "IsCheck", DbType.Boolean, storageInput.IsCheck);
db.AddInParameter(dbCommand, "SupplierId", DbType.Int32, storageInput.SupplierId);
db.AddInParameter(dbCommand, "DealerId", DbType.Int32, storageInput.DealerId);
db.AddInParameter(dbCommand, "StockNo", DbType.String, storageInput.StockNo);
db.AddInParameter(dbCommand, "OrderNo", DbType.String, storageInput.OrderNo);
db.AddInParameter(dbCommand, "BoxUpNo", DbType.Int32, storageInput.BoxUpNo);
db.AddInParameter(dbCommand, "Remark", DbType.String, storageInput.Remark);
db.AddInParameter(dbCommand, "BillNo", DbType.String, storageInput.BillNo);
db.AddInParameter(dbCommand, "UpdatedUser", DbType.String, storageInput.UpdatedUser);
storageInput.Id = Convert.ToInt32(db.ExecuteScalar(dbCommand));
此段代码执行了3-4个月无异常,怎么会突然报错呢,最好跟踪到是Object obj=db.ExecuteScalar(dbCommand);obj=null造成的。
将sqlCommand丢到数据库执行,参数@...没有替换执行不了,最后只好一个字段一个字段的替换,变成一个sql。
执行发现无错,返回null。去掉SELECT SCOPE_IDENTITY()再执行";错误终于出现了:已忽略重复行。原来在唯一约束列上插入了相同数据。而数据库中唯一约束的‘忽略重复键:是’,数据库不会抛出异常。将‘忽略重复键:是’调为‘否’。再执行Object obj=db.ExecuteScalar(dbCommand),此时数据库就抛出
‘消息 2601,级别 14,状态 1,第 1 行
不能在具有唯一索引 'IX_S_StorageOutput' 的对象 'dbo.S_StorageOutput' 中插入重复键的行。
语句已终止。
’ 程序中捕获2601错误,问题解决。