My "User" table (SQL Server) has a (int,null) column, ActiveWorkRequestId. I am trying to update the table using a SqlDataAdapter. This is set up with a clone of the default Update command based on an XSD strongly typed dataset definition (Visual Studio 2010). So the command looks like
我的“用户”表(SQL Server)有一个(int,null)列,ActiveWorkRequestId。我正在尝试使用SqlDataAdapter更新表。这是基于XSD强类型数据集定义(Visual Studio 2010)的默认Update命令的克隆设置的。所以命令看起来像
UPDATE [User] SET [Username] = @p1, [ActiveWorkRequestId] = @p2
WHERE (([Id] = @p3) AND ([Username] = @p4) AND ((@p5 = 1 AND
[ActiveWorkRequestId] IS NULL) OR ([ActiveWorkRequestId] = @p6)))
In this example the table "User" has 3 columns: Id, Username and ActiveWorkRequestId. Parameters p2, p5 and p6 all have an associated column of ActiveWorkRequestId.
在此示例中,表“User”有3列:Id,Username和ActiveWorkRequestId。参数p2,p5和p6都具有ActiveWorkRequestId的关联列。
The run time parameters for the update command (from the modified row) in this example are:
此示例中更新命令(来自修改行)的运行时参数为:
@p1 - "myusername"
@p2 - [DBNull]
@p3 - 126
@p4 - "myusername"
@p5 - [DBNull]
@p6 - [DBNull]
I get all the updated rows (there is only one returned), then update as follows. The ActiveWorkRequestId in the changed row is null (as it was in the original row, in fact). "dt" is the populated DataTable from my DataSet.
我得到所有更新的行(只返回一个),然后更新如下。更改的行中的ActiveWorkRequestId为null(实际上是在原始行中)。 “dt”是我的DataSet中填充的DataTable。
// This part is inline for convenience, only done once in fact
// Some irrelevant code omitted
string query = String.Format("SELECT * FROM [{0}]", dt.TableName);
string sqlCon = ""; // connection string actually got from settings
SqlDataAdapter da = new SqlDataAdapter(query, sqlCon);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
cb.QuotePrefix = "[";
cb.QuoteSuffix = "]";
da.UpdateCommand = CopyCommandObject(dt, cb.GetUpdateCommand());
// ... This part may execute more than once
DataRow[] foundRows = dt.Select("", "",
DataViewRowState.Added | DataViewRowState.ModifiedCurrent);
da.Update(foundRows); // Exception here
// ... defined elsewhere
private static SqlCommand CopyCommandObject(DataTable dt, SqlCommand cmd)
{
SqlCommand r = new SqlCommand(cmd.CommandText);
r.Connection = cmd.Connection;
foreach (SqlParameter p in cmd.Parameters)
{
DataColumn dc = dt.Columns[p.SourceColumn];
// Put this in since p.IsNullable always seems to be false
bool nullable = dc.AllowDBNull ? true : false;
SqlParameter newParam = new SqlParameter(
p.ParameterName,
p.SqlDbType,
p.Size,
p.Direction,
nullable,
p.Precision,
p.Scale,
p.SourceColumn,
p.SourceVersion,
p.Value);
r.Parameters.Add(newParam);
}
return (r);
}
When I call Update on the SqlDataAdapter, a FormatException is thrown:
当我在SqlDataAdapter上调用Update时,抛出FormatException:
Failed to convert parameter value from a String to a Int32.
无法将参数值从String转换为Int32。
Inner exception is:
内部例外是:
Input string was not in a correct format.
输入字符串的格式不正确。
I can't find out which column it is objecting to but the only int column (apart from the PK) is ActiveWorkRequestId. So it seems that the Update method is treating a null value for a parameter as an empty string and attempting to coerce this to an int32, even if the table column and the parameter are both nullable.
我找不到它反对的列,但唯一的int列(除了PK)是ActiveWorkRequestId。因此,似乎Update方法将参数的空值视为空字符串并尝试将其强制转换为int32,即使表列和参数都可以为空。
Why can't it just set the column to null (especially as it already is null in the database)? Can I do anything about this?
为什么不能只将列设置为null(特别是因为它在数据库中已经为null)?我可以对此做些什么吗?
I perhaps should mention that the project was just imported into VS2010 (.NET 4.0) from VS2003 (.NET 1.1) where it all worked fine. This is why the SqlDataAdapter is created in code rather than just using the TableAdapter created by the XSD designer - the .NET 1.1 designer didn't create TableAdapters. (I don't want to have to regenerate all the XSDs if possible).
我或许应该提一下,该项目刚刚从VS2003(.NET 1.1)导入VS2010(.NET 4.0),在那里一切正常。这就是为什么SqlDataAdapter是用代码创建的,而不是仅使用由XSD设计器创建的TableAdapter - .NET 1.1设计器没有创建TableAdapter。 (如果可能的话,我不想重新生成所有的XSD)。
1 个解决方案
#1
1
Instead of using the CopyCommandObject() method, use:
而不是使用CopyCommandObject()方法,使用:
da.UpdateCommand = cb.GetUpdateCommand().Clone();
This way is simpler, and less error-prone.
这种方式更简单,更不容易出错。
#1
1
Instead of using the CopyCommandObject() method, use:
而不是使用CopyCommandObject()方法,使用:
da.UpdateCommand = cb.GetUpdateCommand().Clone();
This way is simpler, and less error-prone.
这种方式更简单,更不容易出错。