小贝子编程

从 C# 应用更新 MS Access 数据库中新添加的记录时"Concurrency violation: the UpdateCommand affected 0 of the expected



请帮忙解决我的问题。当试图从我用c#编写的应用程序更新MS Access(2000格式)数据库(.mdb)中新增的表记录时，我得到了一个错误"并发性违规:UpdateCommand影响了预期1条记录中的0条"。这个错误是相当普遍的，我尝试了不同论坛上建议的解决方案，但没有成功。

这是我一步一步做的:

我在mdb中有一个表'TRACKS'，其中包含以下列:

  • ID -类型'自动号码'(键列)
  • 标题-"文本"
  • FullTitle - 'Text'

  • 长度- '日期/时间'

    1. 我以这种方式建立数据库连接并获取表记录:

      public partial class MainForm : Form
{
    public OleDbConnection dbConn = new OleDbConnection();
    public DataSet dataset = new DataSet();
    protected OleDbDataAdapter adTracks = new OleDbDataAdapter();
    protected OleDbCommandBuilder cmb;
    ArrayList arrArtists = new ArrayList();
    public MainForm(string strFileName)
    {
        InitializeComponent();
        cmb = new OleDbCommandBuilder(adTracks);
    }
    private void OnLoad(object sender, EventArgs e)
    {
        dbConn.ConnectionString = Properties.Settings.Default.dbConnectionString;
        OleDbCommand cmTracks = new OleDbCommand("Select * from Tracks", dbConn);
        OleDbDataAdapter adapter = new OleDbDataAdapter();
        try
        {
            dbConn.Open();
            adTracks.SelectCommand = cmTracks;
            adTracks.Fill(dataset, "Tracks");
        }
        catch (Exception err)
        {
            MessageBox.Show(err.Message);
            return;
        }
        finally
        {
            dbConn.Close();
        }
        cboOriginal.DataSource = dataset.Tables["Tracks"];
        cboOriginal.DisplayMember = "FullTitle";
        cboOriginal.ValueMember = "ID";
        cboOriginal.SelectedIndex = -1;
        adTracks.RowUpdated += new OleDbRowUpdatedEventHandler(OnRowUpdated);
    }
}

    2. 然后我用以下代码向表添加一个新记录(txtTitle和txtGenTitle控件包含记录的值):

      DataTable dt;
DataRow dr;
int newID;
dt = dataset.Tables["Tracks"];
dr = dt.NewRow();
dr["Title"] = txtTitle.Text;
dr["FullTitle"] = txtGenTitle.Text;
dt.Rows.Add(dr);
try
{
    dbConn.Open();
    adTracks.Update(dt);
}
catch (Exception err)
{
    MessageBox.Show("Error adding new track '" + txtGenTitle.Text + "':n" + err.Message);
    return;
}
finally
{
    dbConn.Close();
}
res = dt.Select("FullTitle = '" + txtGenTitle.Text.Replace("'", "''") + "'");
if (res.Length != 0)
{
    newID = (int)res[0]["ID"];
    // continue with newID
}

      这段代码成功执行:新记录被添加到表中，包括本地数据表和mdb文件中的实际表。在此处理程序中接收到键列的新自动递增值:

      protected void OnRowUpdated(object sender, OleDbRowUpdatedEventArgs args)
{
    if (args.StatementType == StatementType.Insert)
    {
        OleDbCommand idCMD = new OleDbCommand("SELECT @@IDENTITY", dbConn);
        args.Row["ID"] = (int)(idCMD.ExecuteScalar());
    }
}

      这个ID的行现在有RowState == Unchanged，所以一切似乎OK。

    3. 现在我想更新这个新添加的记录中的一些值(来自txtLength控制):

      DataTable dt;
DataRow dr;
DataRow[] res;
dt = dataset.Tables["Tracks"];
res = dt.Select("FullTitle = '" + txtGenTitle.Text.Replace("'", "''") + "'");
if (res.Length != 0)
{
    TimeSpan tsNew = TimeSpan.Zero, tsOld = TimeSpan.Zero;
    if (txtLength.Text != String.Empty) tsNew = TimeSpan.Parse(txtLength.Text);
    if (!(res[0]["Length"] is DBNull))
    {
        DateTime date = (DateTime)res[0]["Length"];
        tsOld = date.TimeOfDay;
    }
    if (tsNew != TimeSpan.Zero && (tsOld == TimeSpan.Zero || tsOld.CompareTo(tsNew) < 0))
    {
        if (tsNew != TimeSpan.Zero && (tsOld == TimeSpan.Zero || tsOld.CompareTo(tsNew) < 0)) res[0]["Length"] = txtLength.Text;
        if (String.Compare((string)res[0]["Title"], txtTitle.Text, true) != 0)
        {
            res[0]["Title"] = txtTitle.Text;
            res[0]["FullTitle"] = txtGenTitle.Text;
        }
        try
        {
            dbConn.Open();
            adTracks.Update(dt);
        }
        catch (Exception err)
        {
            MessageBox.Show("Error updating track '" + txtGenTitle.Text + "':n" + err.Message);
            return;
        }
        finally
        {
            dbConn.Close();
        }
    }
}

      并在adTracks.Update(dt) 上得到一个错误"并发冲突:UpdateCommand影响了预期1条记录中的0条"。数据库未更新，DataTable也未更新

这可能意味着记录ID有一些错误-插入后没有更新为正确的值。但这里的情况并非如此:在步骤2的OnRowUpdated处理程序中使用正确的ID更新ID，并将此ID的记录添加到表mdb文件中。在步骤3调用adTracks.Update之前，res[0]也包含正确的ID值和RowState == Modified。但我还是会得到这个错误。我做错了什么?

建议在adTracks.Update(dt)之后添加dt.AcceptChanges() -没有帮助。

更新:

1。krish建议的尝试方法:

我在步骤3的try/catch块之前添加了以下行:

string cmd = "UPDATE TRACKS SET Length = '" + res[0]["Length"] + "' WHERE ID = " + res[0]["ID"];
adTracks.UpdateCommand = new OleDbCommand(cmd, dbConn);

它有效!更新数据库，对应的DataRow得到RowState == Unchanged。这是一个很好的变通方法。但我仍然想知道为什么"传统"方法在这里不起作用。当需要更新许多列时，变通方法并不十分方便。此外，它似乎只有在我更新单行时才可以接受，并且我需要一次更新几行的能力。

2。hynsey建议的尝试方法:

我用以下代码替换了步骤3中的adTracks.Update(dt);行:

using (OleDbDataAdapter da = new OleDbDataAdapter ("Select * from Tracks", dbConn))
{
    OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
    da.RowUpdated += new OleDbRowUpdatedEventHandler(OnRowUpdated);
    da.Update(dt);
}

遗憾的是，行为根本没有改变-同样的错误"并发冲突:UpdateCommand影响了预期1条记录中的0条"。

下面是我在所有3个步骤中使用的代码(与原始代码相比):

1. 
        public partial class MainForm : Form
        {
            public OleDbConnection dbConn = new OleDbConnection();
            public DataSet dataset = new DataSet();
            protected OleDbDataAdapter adTracks = new OleDbDataAdapter();
            ArrayList arrArtists = new ArrayList();
            public MainForm(string strFileName)
            {
                InitializeComponent();
            }
            private void OnLoad(object sender, EventArgs e)
            {
                dbConn.ConnectionString = Properties.Settings.Default.dbConnectionString;
                try
                {
                    dbConn.Open();
                    adTracks = new OleDbDataAdapter("Select * from Tracks", dbConn));
                    adTracks.Fill(dataset,"Tracks");    
                }
                catch (Exception err)
                {
                    MessageBox.Show(err.Message);
                    return;
                }
                finally
                {
                    dbConn.Close();
                }
                cboOriginal.DataSource = dataset.Tables["Tracks"];
                cboOriginal.DisplayMember = "FullTitle";
                cboOriginal.ValueMember = "ID";
                cboOriginal.SelectedIndex = -1;
                adTracks.RowUpdated += new OleDbRowUpdatedEventHandler(OnRowUpdated);
            }
        }
2.
        DataTable dt;
        DataRow dr;
        int newID;
        dt = dataset.Tables["Tracks"];
        dr = dt.NewRow();
        dr["Title"] = txtTitle.Text;
        dr["FullTitle"] = txtGenTitle.Text;
        dt.Rows.Add(dr);
        try
        {
            dbConn.Open();
            using (OleDbDataAdapter da = new OleDbDataAdapter ("Select * from Tracks", dbConn)) 
            {
                OleDbCommandBuilder cb = new OleDbCommandBuilder(da); 
                da.RowUpdated += new OleDbRowUpdatedEventHandler(OnRowUpdated);
                da.Update(dataset, "Tracks"); 
            }
        }
        catch (Exception err)
        {
            MessageBox.Show("Error adding new track '" + txtGenTitle.Text + "':n" + err.Message);
            return;
        }
        finally
        {
            dbConn.Close();
        }
        res = dt.Select("FullTitle = '" + txtGenTitle.Text.Replace("'", "''") + "'");
        if (res.Length != 0)
        {
            newID = (int)res[0]["ID"];
            // continue with newID
        }        
3.
        DataTable dt;
        DataRow dr;
        DataRow[] res;
        dt = dataset.Tables["Tracks"];
        res = dt.Select("FullTitle = '" + txtGenTitle.Text.Replace("'", "''") + "'");
        if (res.Length != 0)
        {
            TimeSpan tsNew = TimeSpan.Zero, tsOld = TimeSpan.Zero;
            if (txtLength.Text != String.Empty) tsNew = TimeSpan.Parse(txtLength.Text);
            if (!(res[0]["Length"] is DBNull))
            {
                DateTime date = (DateTime)res[0]["Length"];
                tsOld = date.TimeOfDay;
            }
            if (tsNew != TimeSpan.Zero && (tsOld == TimeSpan.Zero || tsOld.CompareTo(tsNew) < 0))
            {
                if (tsNew != TimeSpan.Zero && (tsOld == TimeSpan.Zero || tsOld.CompareTo(tsNew) < 0)) res[0]["Length"] = txtLength.Text;
                if (String.Compare((string)res[0]["Title"], txtTitle.Text, true) != 0)
                {
                    res[0]["Title"] = txtTitle.Text;
                    res[0]["FullTitle"] = txtGenTitle.Text;
                }
                try
                {
                    dbConn.Open();
                    using (OleDbDataAdapter da = new OleDbDataAdapter ("Select * from Tracks", dbConn)) 
                    {
                        OleDbCommandBuildercb = new OleDbCommandBuilder(da); 
                        da.Update(dataset , "Tracks"); 
                    }
                }
                catch (Exception err)
                {
                    MessageBox.Show("Error updating track '" + txtGenTitle.Text + "':n" + err.Message);
                    return;
                }
                finally
                {
                    dbConn.Close();
                }
            }
        }

3。为了调查为什么我的初始代码不能工作，我提供了一些关于错误的更多细节:

OnRowUpdated处理程序被调用，尽管有错误，我能够检查传递到处理程序的args参数。args.Row有RowState == Modified, args.Command有以下CommandText(为了可读性，我添加了换行符):

UPDATE Tracks SET Length = ? WHERE ((ID = ?) AND
((? = 1 AND Title IS NULL) OR (Title = ?)) AND
((? = 1 AND FullTitle IS NULL) OR (FullTitle = ?)) AND
((? = 1 AND GenreID IS NULL) OR (GenreID = ?)) AND
((? = 1 AND StyleID IS NULL) OR (StyleID = ?)) AND
((? = 1 AND SubStyleID IS NULL) OR (SubStyleID = ?)) AND
((? = 1 AND Length IS NULL) OR (Length = ?)) AND
((? = 1 AND UseOriginal IS NULL) OR (UseOriginal = ?))
AND ((? = 1 AND Version IS NULL) OR (Version = ?)) AND
((? = 1 AND TrackID IS NULL) OR (TrackID = ?)) AND
((? = 1 AND SpecPresConjunctor IS NULL) OR (SpecPresConjunctor = ?)) AND
((? = 1 AND SpecFeatConjunctor IS NULL) OR (SpecFeatConjunctor = ?)) AND
((? = 1 AND FreeRecord IS NULL) OR (FreeRecord = ?)))

有人能告诉我这个生成的命令有什么问题吗?'GenreID'、'StyleID'等是'TRACKS'表中的其他列。我不知道这些是什么?"的意思。

同样，当异常发生时，下面是对堆栈的最新调用:

at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
at *my code as above*

将OnLoad方法替换为:

    private void OnLoad(object sender, EventArgs e)
    {
        dbConn.ConnectionString = Properties.Settings.Default.dbConnectionString;
    try
    {
        dbConn.Open();
        adTracks = new OleDbDataAdapter("Select * from Tracks", dbConn));
        adTracks.Fill(dataset,"Tracks");    
    }
    catch (Exception err)
    {
        MessageBox.Show(err.Message);
        return;
    }
    finally
    {
        dbConn.Close();
    }
    cboOriginal.DataSource = dataset.Tables["Tracks"];
    cboOriginal.DisplayMember = "FullTitle";
    cboOriginal.ValueMember = "ID";
    cboOriginal.SelectedIndex = -1;
    adTracks.RowUpdated += new OleDbRowUpdatedEventHandler(OnRowUpdated);
}

然后是更新数据集的代码:(注意，我纠正了上面评论中对adTracks的错误引用)

using (OleDbDataAdapter da = new OleDbDataAdapter ("Select * from Tracks", dbConn)) 
{
OracleCommandBuilder cb = new OracleCommandBuilder(da); 
da.Update(dataset , "Tracks"); 
}

有相同的并发问题。毫无效果。2天后，我看到了这篇关于Ms Access和数据类型decimal的文章。本文指出应该避免使用这种数据类型。

所以我使用Double数据类型重新设计了Table。用新名字保存。复制数据，最后用旧名称重命名新表。

在Visual Studio中，我删除了Dataset。并添加了一个新的Dataset

终于成功了。

请阅读以下内容:https://www.fmsinc.com/microsoftaccess/database-design/decimal_data_type/index.htm

我在管理一个从Excel复制粘贴值的Access表时遇到了这个问题。由于某些原因，从Excel复制的DateTime字段可能具有Access无法很好处理的精度。

我通过OleDbDataAdapter.Update(DataTable)更新记录的另一个字段时遇到错误。

阅读@AnthonyG的回答后，我决定使用如下的Access查询直接在原始Access表中更新DateTime字段:

UPDATE [MYTABLE] SET [MYDATETIMEFIELD] = ROUND([MYDATETIMEFIELD],5)

我检查了5是工作的最大精度。

这个问题可以在运行时检查捕获DBConcurrencyException，但在那个时候，它不能解决舍入记录的DateTime值。

我不得不保存记录而不使用OleDbDataAdapter，但手动创建UPDATE SQL语句并使用OleDbCommand.ExecuteNonQuery()与OleDbParameter集合，重写包括DateTime字段在内的所有值，在这种情况下即使不应用任何四舍五入也能正确写入。

相关内容

  • 没有找到相关文章

最新更新


热门标签：

javascript python java c# php android html jquery c++ css ios sql mysql arrays asp.net json python-3.x ruby-on-rails .net sql-server django objective-c excel regex ruby linux ajax iphone xml vba spring asp.net-mvc database wordpress string postgresql wpf windows xcode bash git oracle list vb.net multithreading eclipse algorithm macos powershell visual-studio image forms numpy scala function api selenium