使用 adapter.update() 从数据表更新 SQL 时出错

本文关键字：更新 SQL 出错 数据表 adapter update 使用 | 更新日期: 2023-09-27 18:33:31

根据到目前为止的答案，我似乎没有正确创建参数，也没有正确传递值。 我想从数据表更新 SQL 表(数据表是从 SQL 表填充的，列名相同(当我创建参数时，我认为第二个参数表示数据表列。 如何在将数据表"DWFieldScale"的值传递给 SQL 列 DWFieldScale 的地方进行设置??? (对于我创建的所有其他参数相同(

命令。Parameters.AddWithValue("@DWFieldScale"， "DWFieldScale"(;

调用adapter.update((时，我收到SQL异常，因为我不明白如何正确设置适配器以用于主题标题。 我只用了几个月的C#，所以我仍然很环保。

无论如何，我已经尝试了十几件事，并且已经到了我"可能"在正确的轨道上的地步，但得到的是"无法将 NVARCHAR 转换为 INT"让我困扰的是，这是一个绑定到数据表的 DataGridView，其中列的类型为 INT。 (在 SQL 中，该列可为空并且存在空值(

  1. NVARCHAR 从何而来？
  2. 如果NVARCHAR来自DataGridView，为什么它不使用匹配类型自动生成？
  3. 我在哪里可以进行转换以允许我的 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;

使用 adapter.update() 从数据表更新 SQL 时出错

如果数据库字段为"int"，则以下行将给出错误： 

command.Parameters.AddWithValue("@DWFieldScale", "DWFieldScale");

它将给出一个错误，因为您将字符串"DWFieldScale"作为值传递给字段。命令背后的想法。参数是进行所需的任何转换的控件。

看到这个：

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparametercollection.addwithvalue%28v=vs.110%29.aspx

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;

所以，你必须传递一个 VALUE，可以 ba 一个常量、一个相同类型的变量、一个相同类型的函数的结果等。但实际上，一定是你想在sql命令中成为的女功夫。

但这是您要执行命令的时候。如果要将其绑定到数据网格或类似类型的东西，只需添加参数即可。不要传递值，因为更新数据网格时将设置这些值。

因此，只需使用如下行：

command.Parameters.Add("@DWFieldScale", SqlDbType.Int);

让视图为您处理值。

这里有一个关于如何使用DAtaSet(在内存中(的很好的例子

http://msdn.microsoft.com/en-us/library/system.data.dataset%28v=vs.110%29.aspx

该示例适用于"选择"语句，但您会得到:)

的想法

这里有一些关于SQLDataAdapter的信息：http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter%28v=vs.110%29.aspx

对于下一个刚开始的程序员，这是我在编辑绑定到数据表的 DataGridView 时自动更新 SQL 数据库/表的完整解决方案：

绑定：(将 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;
        }

创建要从中执行适配器更新的事件： private void bindingSource1_PositionChanged(Object sender， EventArgs e( { var config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None(; 应用设置部分应用设置部分 = (应用设置部分(配置。GetSection("cbSettings"(; SqlConnection vx130 = new SqlConnection(appSettingSection.Settings[cbRegion.SelectedItem.ToString((]。值(; SqlDataAdapter da = CreateSQLAdapter(vx130(; 呸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;
        }
    }
}
我认为

问题是因为您在代码 collection.in 向参数添加了错误的值，所有列，您添加了字符串值。也许有些列是 int 类型

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("@DWFieldLength", "DWFieldLength");
command.Parameters.AddWithValue("@SourceAttributeSID",  "SourceAttributeSID");

尝试将参数的值更改为 int 您还需要更改命令文本并将 varchar 参数放在单个 qoto 标记中;但是对于这种类型的数据库操作，最好使用存储过程而不是使用纯文本

相关文章：