none
更新数据集时出现System.Data.DBConcurrencyException: 违反并发性: UpdateCommand 影响了预期 1 条记录中的 0 条。 RRS feed

  • Pergunta

  • if (Conn.ExecuteQueryRow(Conn.GetDb, " update " + tablename + " set confirmid='" + Conn.loginname + "',confirmdate='" + Conn.ReturnDataTime(Conn.GetDb, false) + "' where " + columnname + "= '" + ID + "'") > 0)
                    { 
                        dr["confirmid"] = Conn.loginname;
                        saveitem.Enabled = false;
                        ConfirmBtn.Text = "解锁";
                        MessageBox.Show(lable + ID + "加锁成功", Language.ShowMessageHead(Cpublic.Version));
                    } 
                    
                }
                else
                {
                    if (Conn.ExecuteQueryRow(Conn.GetDb, " update  " + tablename + "  set confirmid='' ,confirmdate='" + Conn.ReturnDataTime(Conn.GetDb, false) + "' where " + columnname + "='" + ID + "'") > 0)
                    { 
                        dr["confirmid"] = " ";
                        ConfirmBtn.Text = "加锁"; 
                      saveitem.Enabled = true; 
                        MessageBox.Show(lable + ID + "解锁成功", Language.ShowMessageHead(Cpublic.Version));

                    }


    //这是查询出后,点击加解锁后通过sql语句更新,到解锁后修改数据后通过数据集更新,只更新了哪一个字段,我看网上说//什么读到脏数据了,我不知道该怎样修改了


                }
    用sql语句更新了数据表的某一个字段后,然后修改了数据库保存

           public static void UpdateDataset(SqlTransaction trans, SqlConnection conn, DataSet dataSet, string tableName)
            {
                string ls_sql = "select top 0 * from " + tableName;
                SqlDataAdapter da = new SqlDataAdapter(ls_sql, conn);
                da.SelectCommand.Transaction = trans;
                SqlCommandBuilder cb = new SqlCommandBuilder(da);
                DataSet ds = new DataSet();
                da.Fill(ds, tableName);
                ds = dataSet.Copy();
                try
                {
                    da.Update(ds, tableName.Trim()); //这里会抛出如标题的异常
                   
                }
                catch (System.Data.SqlClient.SqlException e)
                {
                    trans.Rollback();
                    throw new Exception(e.Message);
                }
                finally
                {
                    da.Dispose();
                    cb.Dispose();
                    ds.Dispose();
                }
            }
    sexta-feira, 11 de dezembro de 2009 02:21

Respostas

  •     public static int UpdateDataset(string ConnectionSql, DataSet dataset, string tablename)//更新数据集
            {
                int ii;
                string updatesql = "select  * from " + tablename +" where 0=1";
                using (SqlConnection conn = new SqlConnection(ConnectionSql))
                {
                    conn.Open();
                    SqlTransaction trans = conn.BeginTransaction();
                    SqlDataAdapter da = new SqlDataAdapter(updatesql, conn);              
                    SqlCommandBuilder cb = new SqlCommandBuilder();
                    da.SelectCommand.Transaction = trans;
    //加上以下三句就可以了
                    cb.ConflictOption = ConflictOption.OverwriteChanges;
                    cb.SetAllValues = false;
                    cb.DataAdapter = da;
                    DataSet ds = new DataSet();
                    da.Fill(ds, tablename);
                    ds = dataset.Copy();
                    //;//建立一个主键
                    try
                    {
                        ii = da.Update(ds, tablename.Trim());
                        ds.AcceptChanges();
                        trans.Commit();
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        trans.Rollback();
                        throw new Exception(e.Message);
                    }
                    finally
                    {
                        da.Dispose();
                        cb.Dispose();
                        ds.Dispose();
                        conn.Close();
                    }
                }
                return ii;
            }
    • Marcado como Resposta Mog Liang sexta-feira, 18 de dezembro de 2009 03:48
    sexta-feira, 11 de dezembro de 2009 08:03

Todas as Respostas

  •  你加上这一行
     da.UpdateCommand = cb .GetUpdateCommand();

    使用
    注意点: SqlCommandBuilder要注意以下
    1.只能更新一个表,如果此数据集是从两个或者两个以上的表关联而来的,则不能用此方法自动更新
    2.表中必须设置主键
    3.更新的表中字段不能有image类型的
    努力+方法=成功
    sexta-feira, 11 de dezembro de 2009 03:43
  •  public static int UpdateDataset(string ConnectionSql, DataSet dataset, string tablename)//更新数据集
            {
                int ii;
                string updatesql = "select  * from " + tablename +" where 0=1";
                using (SqlConnection conn = new SqlConnection(ConnectionSql))
                {
                    conn.Open();
                    SqlTransaction trans = conn.BeginTransaction();
                    SqlDataAdapter da = new SqlDataAdapter(updatesql, conn);
                    da.SelectCommand.Transaction = trans;
                    SqlCommandBuilder cb = new SqlCommandBuilder(da);
                    da.InsertCommand = cb.GetInsertCommand();
                    da.UpdateCommand = cb.GetUpdateCommand();
                    da.DeleteCommand = cb.GetDeleteCommand();

                    MessageBox.Show(cb.GetUpdateCommand().CommandText);
    //这里发现where是把表中字段全部都出来了,怎样可以做到根据主键列做为条件
                    DataSet ds = new DataSet();
                    da.Fill(ds, tablename);
                    ds = dataset.Copy();
                    //;//建立一个主键
                    try
                    {
                        ii = da.Update(ds, tablename.Trim());
                        ds.AcceptChanges();
                        trans.Commit();
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        trans.Rollback();
                        throw new Exception(e.Message);
                    }
                    finally
                    {
                        da.Dispose();
                        cb.Dispose();
                        ds.Dispose();
                        conn.Close();
                    }
                }
                return ii;
            }
    注意点: SqlCommandBuilder要注意以下
    1.只能更新一个表,如果此数据集是从两个或者两个以上的表关联而来的,则不能用此方法自动更新
    2.表中必须设置主键
    3.更新的表中字段不能有image类型的
    这几个都有,我是的步骤是   新增记录---保存成功--- 加锁(用sql语句udpate ExecuteNonQuery方法)--解锁(可以修改记录了,更新一个字段为空也用ExecuteNonQuery 方法)--修改记录保存就提示如上

    sexta-feira, 11 de dezembro de 2009 03:49
  • 你好!
          每次执行的插入、更新或删除操作所影响的行数;如果行数等于零,则引发此异常。此异常通常由并发冲突引起。你检查一下相关的方面!
         希望对你有帮助!

    周雪峰
    sexta-feira, 11 de dezembro de 2009 03:59
    Moderador
  •  MessageBox.Show(cb.GetUpdateCommand().CommandText);
    //这里发现where是把表中字段全部都出来了,怎样可以做到根据主键列做为条件
    因为前面要对订单做加锁操作,用sql语用update table set confirmid ='user' where id='变量'
    导到生成GetUpdateCommand() 后面的where 变量了,找不到以前的条件的记录所起的

    都不知道怎样改了

    sexta-feira, 11 de dezembro de 2009 07:48
  •     public static int UpdateDataset(string ConnectionSql, DataSet dataset, string tablename)//更新数据集
            {
                int ii;
                string updatesql = "select  * from " + tablename +" where 0=1";
                using (SqlConnection conn = new SqlConnection(ConnectionSql))
                {
                    conn.Open();
                    SqlTransaction trans = conn.BeginTransaction();
                    SqlDataAdapter da = new SqlDataAdapter(updatesql, conn);              
                    SqlCommandBuilder cb = new SqlCommandBuilder();
                    da.SelectCommand.Transaction = trans;
    //加上以下三句就可以了
                    cb.ConflictOption = ConflictOption.OverwriteChanges;
                    cb.SetAllValues = false;
                    cb.DataAdapter = da;
                    DataSet ds = new DataSet();
                    da.Fill(ds, tablename);
                    ds = dataset.Copy();
                    //;//建立一个主键
                    try
                    {
                        ii = da.Update(ds, tablename.Trim());
                        ds.AcceptChanges();
                        trans.Commit();
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        trans.Rollback();
                        throw new Exception(e.Message);
                    }
                    finally
                    {
                        da.Dispose();
                        cb.Dispose();
                        ds.Dispose();
                        conn.Close();
                    }
                }
                return ii;
            }
    • Marcado como Resposta Mog Liang sexta-feira, 18 de dezembro de 2009 03:48
    sexta-feira, 11 de dezembro de 2009 08:03
  •      我发现缺少了

    cb.ConflictOption = ConflictOption.OverwriteChanges;

    以上语句后 GetUpdateCommand 返回的 SQL 语句中 WHERE 后非常长, 但我表中有唯一主键, 加上以上语句后 WHERE 后的条件就只有更新那行的主键值了!

         所以 "更新数据集时出现System.Data.DBConcurrencyException: 违反并发性: UpdateCommand 影响了预期 1 条记录中的 0 条。" 这个问题可能是由于缺少设置 "ConflictOption" 属性引起的 :)

    terça-feira, 8 de agosto de 2017 07:18
  • cb.ConflictOption = ConflictOption.OverwriteChanges

    指定 GetUpdateCommand 生成 WHERE 后的条件 :)

    terça-feira, 8 de agosto de 2017 07:19