使用adapter.update()从数据表更新SQL时出错
使用adapter.update()从数据表更新SQL时出错
提问于 2014-12-08 00:29:52
根据目前的答案，我似乎没有正确地创建参数，也没有正确传递值。当我创建参数时，我想从DataTable更新SQL表(datatable是从datatable填充的，列名是相同的)，当我创建参数时，我认为第二个参数表示datatable列。如何设置datatable "DWFieldScale“的值传递给DWFieldScale列的情况？(我创建的每个其他参数都是相同的)
command.Parameters.AddWithValue("@DWFieldScale"，"DWFieldScale")；
当调用adapter.update()时，我会得到一个subject，因为我不知道如何在主题标题中正确地设置适配器。我只和C#合作了几个月，所以我仍然很健康。
不管怎么说，我已经尝试了十几件事情，并已经达到了我“可能”在正确的轨道上，但我得到一个‘不能将NVARCHAR转换为INT’，这是一个DataGridView绑定到数据表，列是INT类型。(在SQL中，列是可空的，并且有空)
- NVARCHAR来自哪里？
- 如果NVARCHAR来自DataGridView，为什么不使用匹配类型自动生成？
- 在哪里可以进行转换以允许我的da.Update(tblvAttributes)；命令工作？在定义参数时，我已经尝试过几种方法，但我的下巴不能保持正确。帮助
我的绑定代码：
private void tvVX130_AfterSelect(object sender, TreeViewEventArgs e)
{
string t = tvVX130.SelectedNode.Text;
BindingSource bs1 = new BindingSource();
bs1.PositionChanged += bindingSource1_PositionChanged;
bs1.DataSource = tblvAttributes;
dgvVX130.DataSource = bs1;
string dwTN = tvVX130.SelectedNode.Text.Substring(0, tvVX130.SelectedNode.Text.IndexOf(" - "));
bs1.Filter = "DWPhysicalTableName = '" + dwTN + "' AND DWPhysicalSchemaName = '" + t.Substring(t.IndexOf(" - ") + 5) + "'";
dgvVX130.DataSource = bs1;
public static SqlDataAdapter CreateSQLAdapter(SqlConnection vx130)
{
SqlDataAdapter da = new SqlDataAdapter();
command = new SqlCommand(
"UPDATE [Meta].[AttributeMap] "+
"SET DatabaseName = @DatabaseName, DWPhysicalSchemaName = @DWPhysicalSchemaName, " +
"DWPhysicalTableName=@DWPhysicalTableName, DWFieldName=@DWFieldName, DataDomain=@DataDomain," +
"DWFieldDataType=@DWFieldDataType, DWFieldLength=@DWFieldLength, DWFieldScale=@DWFieldScale," +
"SourceAttributeSID=@SourceAttributeSID " +
"WHERE DWPhysicalSchemaName = @DWPhysicalSchemaName and DWPhysicalTableName= @DWPhysicalTableName and DWFieldName=@DWFieldName", vx130);
command.Parameters.AddWithValue("@DatabaseName", "DatabaseName");
command.Parameters.AddWithValue("@DWPhysicalSchemaName", "DWPhysicalSchemaName");
command.Parameters.AddWithValue("@DWPhysicalTableName", "DWPhysicalTableName");
command.Parameters.AddWithValue("@DWFieldName", "DWFieldName");
command.Parameters.AddWithValue("@DWFieldDataType", "DWFieldDataType");
command.Parameters.AddWithValue("@DWFieldLength", "DWFieldLength");
//command.Parameters.AddWithValue("@DWFieldScale", "DWFieldScale"); gives can't convert NVARCHAR to INT
//if (!String.IsNullOrWhiteSpace("DWFieldScale")) Doesn't recognize "DWFieldScale" as column
// command.Parameters.AddWithValue("@DWFieldScale", "DWFieldScale");
//else
// command.Parameters.AddWithValue("@DWFieldScale", DBNull.Value);
//command.Parameters.Add("@DWFieldScale", SqlDbType.Int);
//command.Parameters["@DWFieldScale"].Value = "DWFieldScale"; Doesn't recognize "DWFieldScale" as column
//command.Parameters.AddWithValue("@DWFieldScale", int.Parse("DWFieldScale".ToString())); gives input incorrect format
command.Parameters.AddWithValue("@SourceAttributeSID", "SourceAttributeSID"); //this is also integer
da.UpdateCommand = command;回答 4
Stack Overflow用户
回答已采纳
发布于 2014-12-08 00:40:56
如果数据库字段是“int”，下面的行将给出一个错误：
command.Parameters.AddWithValue("@DWFieldScale", "DWFieldScale"); 它将产生一个错误，因为您将字符串"DWFieldScale“作为值传递给字段。command.Parameters背后的思想是进行任何必要的转换所需的控件。
见此：
NVARCHAR是连接认为试图传递给参数的类型。这是一个数据库字段类型。
另外，下面这一行也很奇怪：
if (!String.IsNullOrWhiteSpace("DWFieldScale"))String.IsNullOrWhiteSpace的意思是与“变量”一起使用。您正在传递一个常量字符串。函数的结果将始终为true，因为有一个字符串，而if的结果将始终为FALSE，因为您正在否定该函数的结果。
最后，由于相同的原因，这两行将失败，在开始时，您将设置一个int参数，但将字符串作为值传递：
command.Parameters.Add("@DWFieldScale", SqlDbType.Int);
command.Parameters["@DWFieldScale"].Value = "DWFieldScale";使用这些参数的正确方法更像这样：
command.Parameters.Add("@DWFieldScale", SqlDbType.Int);
command.Parameters["@DWFieldScale"].Value = 10;因此，您必须传递一个值，可以是一个常量，一个具有相同类型的变量，一个具有相同类型的函数的结果，等等。但实际上，必须是您希望在sql命令中出现的valyue。
但这是您想要执行命令的时候。如果您要将其绑定到一个数据集或某种类型的内容，只需添加参数即可。不要传递值，因为在更新数据集时将设置值。
所以，只需使用这样的行：
command.Parameters.Add("@DWFieldScale", SqlDbType.Int);让视图照顾好你的价值观。
这里有一个很好的例子，说明如何使用DAtaSet (在内存中)
http://msdn.microsoft.com/en-us/library/system.data.dataset%28v=vs.110%29.aspx
该示例用于"Select“语句，但您将得到如下想法:)
下面是关于SQLDataAdapter：http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter%28v=vs.110%29.aspx的一些信息
Stack Overflow用户
发布于 2014-12-08 03:07:25
对于下一个开始编程的程序员，这是我在编辑绑定到DataGridView的DataTable时对自动访问solution /Table的完整解决方案：
绑定：(将dgView、dTable和PositionChange事件联系在一起)
private void tvVX130_AfterSelect(object sender, TreeViewEventArgs e)
{
string t = tvVX130.SelectedNode.Text;
BindingSource bs1 = new BindingSource();
bs1.PositionChanged += bindingSource1_PositionChanged;
bs1.DataSource = tblvAttributes;
dgvVX130.DataSource = bs1;
string dwTN = tvVX130.SelectedNode.Text.Substring(0, tvVX130.SelectedNode.Text.IndexOf(" - "));
bs1.Filter = "DWPhysicalTableName = '" + dwTN + "' AND DWPhysicalSchemaName = '" + t.Substring(t.IndexOf(" - ") + 5) + "'";
dgvVX130.DataSource = bs1;
}创建要执行适配器更新的事件:私有的void bindingSource1_PositionChanged(对象发送方，EventArgs e) { var config = (AppSettingsSection)config.GetSection("cbSettings")；AppSettingsSection appSettingSection =(AppSettingsSection)config.GetSection(“cbSettings”)；SqlConnection vx130 = new SqlConnection(appSettingSection.SettingscbRegion.SelectedItem.ToString().Value)；SqlDataAdapter da = CreateSQLAdapter(vx130)；da.Update(tblvAttributes)；}
设置SQL适配器：(所有的好东西都在这里。这也是冗长的。另一种方法是重新声明语句以调用SQL存储过程。我没有。)
public static SqlDataAdapter CreateSQLAdapter(SqlConnection vx130)
{
SqlDataAdapter da = new SqlDataAdapter();
// Create the SelectCommand.
SqlCommand command = new SqlCommand("Select DatabaseName, DWPhysicalSchemaName, DWPhysicalTableName, " +
"DWFieldName ,DataDomain, DWFieldDataType, DWFieldLength, DWFieldScale, SourceAttributeSID, "+
"ResolvedValue, PointedToField, MapComments, PrimaryKeyEntitySID, SpecialHandlingFlag, "+
"DWFieldTechnicalDescription, BuildStatus from meta.attributemap", vx130);
da.SelectCommand = command;
// Create the InsertCommand.
command = new SqlCommand(
"Insert Into [Meta].[AttributeMap] " +
"(DatabaseName, DWPhysicalSchemaName, DWPhysicalTableName, " +
"DWFieldName ,DataDomain, DWFieldDataType, DWFieldLength, DWFieldScale, SourceAttributeSID, " +
"ResolvedValue, PointedToField, MapComments, PrimaryKeyEntitySID, SpecialHandlingFlag, " +
"DWFieldTechnicalDescription, BuildStatus ) " +
"Values (@DatabaseName, @DWPhysicalSchemaName, @DWPhysicalTableName, " +
"@DWFieldName ,@DataDomain, @DWFieldDataType, @DWFieldLength, @DWFieldScale, @SourceAttributeSID, " +
"@ResolvedValue, @PointedToField, @MapComments, @PrimaryKeyEntitySID, @SpecialHandlingFlag, " +
"@DWFieldTechnicalDescription, @BuildStatus)" , vx130);
// Add the parameters for the InsertCommand.
command.Parameters.Add(new SqlParameter("@DatabaseName", SqlDbType.VarChar));
command.Parameters["@DatabaseName"].SourceVersion = DataRowVersion.Current;
command.Parameters["@DatabaseName"].SourceColumn = "DatabaseName";
command.Parameters.Add(new SqlParameter("@DWPhysicalSchemaName", SqlDbType.VarChar));
command.Parameters["@DWPhysicalSchemaName"].SourceVersion = DataRowVersion.Current;
command.Parameters["@DWPhysicalSchemaName"].SourceColumn = "DWPhysicalSchemaName";
command.Parameters.Add(new SqlParameter("@DWPhysicalTableName", SqlDbType.VarChar));
command.Parameters["@DWPhysicalTableName"].SourceVersion = DataRowVersion.Current;
command.Parameters["@DWPhysicalTableName"].SourceColumn = "DWPhysicalTableName";
command.Parameters.Add(new SqlParameter("@DWFieldName", SqlDbType.VarChar));
command.Parameters["@DWFieldName"].SourceVersion = DataRowVersion.Current;
command.Parameters["@DWFieldName"].SourceColumn = "DWFieldName";
command.Parameters.Add(new SqlParameter("@DataDomain", SqlDbType.VarChar));
command.Parameters["@DataDomain"].SourceVersion = DataRowVersion.Current;
command.Parameters["@DataDomain"].SourceColumn = "DataDomain";
command.Parameters.Add(new SqlParameter("@DWFieldDataType", SqlDbType.VarChar));
command.Parameters["@DWFieldDataType"].SourceVersion = DataRowVersion.Current;
command.Parameters["@DWFieldDataType"].SourceColumn = "DWFieldDataType";
command.Parameters.Add(new SqlParameter("@DWFieldLength", SqlDbType.VarChar));
command.Parameters["@DWFieldLength"].SourceVersion = DataRowVersion.Current;
command.Parameters["@DWFieldLength"].SourceColumn = "DWFieldLength";
command.Parameters.Add(new SqlParameter("@DWFieldScale", SqlDbType.Int));
command.Parameters["@DWFieldScale"].SourceVersion = DataRowVersion.Current;
command.Parameters["@DWFieldScale"].SourceColumn = "DWFieldScale";
command.Parameters.Add(new SqlParameter("@SourceAttributeSID", SqlDbType.Int));
command.Parameters["@SourceAttributeSID"].SourceVersion = DataRowVersion.Current;
command.Parameters["@SourceAttributeSID"].SourceColumn = "SourceAttributeSID";
command.Parameters.Add(new SqlParameter("@ResolvedValue", SqlDbType.VarChar));
command.Parameters["@ResolvedValue"].SourceVersion = DataRowVersion.Current;
command.Parameters["@ResolvedValue"].SourceColumn = "ResolvedValue";
command.Parameters.Add(new SqlParameter("@PointedToField", SqlDbType.VarChar));
command.Parameters["@PointedToField"].SourceVersion = DataRowVersion.Current;
command.Parameters["@PointedToField"].SourceColumn = "PointedToField";
command.Parameters.Add(new SqlParameter("@MapComments", SqlDbType.VarChar));
command.Parameters["@MapComments"].SourceVersion = DataRowVersion.Current;
command.Parameters["@MapComments"].SourceColumn = "MapComments";
command.Parameters.Add(new SqlParameter("@PrimaryKeyEntitySID", SqlDbType.Int));
command.Parameters["@PrimaryKeyEntitySID"].SourceVersion = DataRowVersion.Current;
command.Parameters["@PrimaryKeyEntitySID"].SourceColumn = "PrimaryKeyEntitySID";
command.Parameters.Add(new SqlParameter("@SpecialHandlingFlag", SqlDbType.VarChar));
command.Parameters["@SpecialHandlingFlag"].SourceVersion = DataRowVersion.Current;
command.Parameters["@SpecialHandlingFlag"].SourceColumn = "SpecialHandlingFlag";
command.Parameters.Add(new SqlParameter("@DWFieldTechnicalDescription", SqlDbType.VarChar));
command.Parameters["@DWFieldTechnicalDescription"].SourceVersion = DataRowVersion.Current;
command.Parameters["@DWFieldTechnicalDescription"].SourceColumn = "DWFieldTechnicalDescription";
command.Parameters.Add(new SqlParameter("@BuildStatus", SqlDbType.VarChar));
command.Parameters["@BuildStatus"].SourceVersion = DataRowVersion.Current;
command.Parameters["@BuildStatus"].SourceColumn = "BuildStatus";
da.InsertCommand = command;
// Create the UpdateCommand.
command = new SqlCommand(
"UPDATE [Meta].[AttributeMap] "+
"SET DatabaseName = @DatabaseName, DWPhysicalSchemaName = @DWPhysicalSchemaName, " +
"DWPhysicalTableName=@DWPhysicalTableName, DWFieldName=@DWFieldName, DataDomain=@DataDomain," +
"DWFieldDataType=@DWFieldDataType, DWFieldLength=@DWFieldLength, DWFieldScale=@DWFieldScale," +
"SourceAttributeSID=@SourceAttributeSID, ResolvedValue=@ResolvedValue, @PointedToField=@PointedToField," +
"MapComments=@MapComments, PrimaryKeyEntitySID=@PrimaryKeyEntitySID, SpecialHandlingFlag=@SpecialHandlingFlag," +
"DWFieldTechnicalDescription=@DWFieldTechnicalDescription, BuildStatus=@BuildStatus " +
"WHERE DWPhysicalSchemaName = @DWPhysicalSchemaName and DWPhysicalTableName= @DWPhysicalTableName and DWFieldName=@DWFieldName", vx130);
command.Parameters.Add(new SqlParameter("@DatabaseName", SqlDbType.VarChar));
command.Parameters["@DatabaseName"].SourceVersion = DataRowVersion.Current;
command.Parameters["@DatabaseName"].SourceColumn = "DatabaseName";
command.Parameters.Add(new SqlParameter("@DWPhysicalSchemaName", SqlDbType.VarChar));
command.Parameters["@DWPhysicalSchemaName"].SourceVersion = DataRowVersion.Current;
command.Parameters["@DWPhysicalSchemaName"].SourceColumn = "DWPhysicalSchemaName";
command.Parameters.Add(new SqlParameter("@DWPhysicalTableName", SqlDbType.VarChar));
command.Parameters["@DWPhysicalTableName"].SourceVersion = DataRowVersion.Current;
command.Parameters["@DWPhysicalTableName"].SourceColumn = "DWPhysicalTableName";
command.Parameters.Add(new SqlParameter("@DWFieldName", SqlDbType.VarChar));
command.Parameters["@DWFieldName"].SourceVersion = DataRowVersion.Current;
command.Parameters["@DWFieldName"].SourceColumn = "DWFieldName";
command.Parameters.Add(new SqlParameter("@DataDomain", SqlDbType.VarChar));
command.Parameters["@DataDomain"].SourceVersion = DataRowVersion.Current;
command.Parameters["@DataDomain"].SourceColumn = "DataDomain";
command.Parameters.Add(new SqlParameter("@DWFieldDataType", SqlDbType.VarChar));
command.Parameters["@DWFieldDataType"].SourceVersion = DataRowVersion.Current;
command.Parameters["@DWFieldDataType"].SourceColumn = "DWFieldDataType";
command.Parameters.Add(new SqlParameter("@DWFieldLength", SqlDbType.VarChar));
command.Parameters["@DWFieldLength"].SourceVersion = DataRowVersion.Current;
command.Parameters["@DWFieldLength"].SourceColumn = "DWFieldLength";
command.Parameters.Add(new SqlParameter("@DWFieldScale", SqlDbType.Int));
command.Parameters["@DWFieldScale"].SourceVersion = DataRowVersion.Current;
command.Parameters["@DWFieldScale"].SourceColumn = "DWFieldScale";
command.Parameters.Add(new SqlParameter("@SourceAttributeSID", SqlDbType.Int));
command.Parameters["@SourceAttributeSID"].SourceVersion = DataRowVersion.Current;
command.Parameters["@SourceAttributeSID"].SourceColumn = "SourceAttributeSID";
command.Parameters.Add(new SqlParameter("@ResolvedValue", SqlDbType.VarChar));
command.Parameters["@ResolvedValue"].SourceVersion = DataRowVersion.Current;
command.Parameters["@ResolvedValue"].SourceColumn = "ResolvedValue";
command.Parameters.Add(new SqlParameter("@PointedToField", SqlDbType.VarChar));
command.Parameters["@PointedToField"].SourceVersion = DataRowVersion.Current;
command.Parameters["@PointedToField"].SourceColumn = "PointedToField";
command.Parameters.Add(new SqlParameter("@MapComments", SqlDbType.VarChar));
command.Parameters["@MapComments"].SourceVersion = DataRowVersion.Current;
command.Parameters["@MapComments"].SourceColumn = "MapComments";
command.Parameters.Add(new SqlParameter("@PrimaryKeyEntitySID", SqlDbType.Int));
command.Parameters["@PrimaryKeyEntitySID"].SourceVersion = DataRowVersion.Current;
command.Parameters["@PrimaryKeyEntitySID"].SourceColumn = "PrimaryKeyEntitySID";
command.Parameters.Add(new SqlParameter("@SpecialHandlingFlag", SqlDbType.VarChar));
command.Parameters["@SpecialHandlingFlag"].SourceVersion = DataRowVersion.Current;
command.Parameters["@SpecialHandlingFlag"].SourceColumn = "SpecialHandlingFlag";
command.Parameters.Add(new SqlParameter("@DWFieldTechnicalDescription", SqlDbType.VarChar));
command.Parameters["@DWFieldTechnicalDescription"].SourceVersion = DataRowVersion.Current;
command.Parameters["@DWFieldTechnicalDescription"].SourceColumn = "DWFieldTechnicalDescription";
command.Parameters.Add(new SqlParameter("@BuildStatus", SqlDbType.VarChar));
command.Parameters["@BuildStatus"].SourceVersion = DataRowVersion.Current;
command.Parameters["@BuildStatus"].SourceColumn = "BuildStatus";
da.UpdateCommand = command;
// Create the DeleteCommand.
command = new SqlCommand(
"delete from vx130.Meta.AttributeMap " +
" where DWPhysicalSchemaName = @DWPhysicalSchemaName AND " +
" DWPhysicalTableName = @DWPhysicalTableName AND DWFieldName = @DWFieldName", vx130);
// Add the parameters for the DeleteCommand.
command.Parameters.Add(new SqlParameter("@DWPhysicalSchemaName", SqlDbType.VarChar));
command.Parameters["@DWPhysicalSchemaName"].SourceVersion = DataRowVersion.Current;
command.Parameters["@DWPhysicalSchemaName"].SourceColumn = "DWPhysicalSchemaName";
command.Parameters.Add(new SqlParameter("@DWPhysicalTableName", SqlDbType.VarChar));
command.Parameters["@DWPhysicalTableName"].SourceVersion = DataRowVersion.Current;
command.Parameters["@DWPhysicalTableName"].SourceColumn = "DWPhysicalTableName";
command.Parameters.Add(new SqlParameter("@DWFieldName", SqlDbType.VarChar));
command.Parameters["@DWFieldName"].SourceVersion = DataRowVersion.Current;
command.Parameters["@DWFieldName"].SourceColumn = "DWFieldName";
da.DeleteCommand = command;
return da;
}
}
}Stack Overflow用户
发布于 2014-12-08 00:39:34
在代码中的两行中，我认为您要将字符串值分配给int参数，我的意思是：
command.Parameters.AddWithValue("@DWFieldLength", "DWFieldLength");
command.Parameters.AddWithValue("@SourceAttributeSID", "SourceAttributeSID"); 尝试将参数的值更改为int，还需要更改命令文本，并将varchar参数放在单个qoto标记中；但是对于这种类型的数据库操作，最好使用存储过程而不是纯文本
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/27349622
复制相关文章
相似问题
腾讯云开发者
