TableAdapter.Update problem
-
Sorry for the long text... I'm using a dataset for the link between my application and the MS Access database. The problem I have is with the Update function of the table adapter object. The code is really simple and it works very well except in one situation. Here's the code (generated by dragging from the data source to the windows form): this.tblRequestBindingSource.EndEdit(); this.tblRequestTableAdapter.Update(this.forms_Work_LogDataSet.tblRequest); The situation when it doesn't work is very specific and I've tried it on several test applications and it seems to be caused by the dataset. When I create a new record and save right away, everything is fine. Then, if I make a modification to the record and save again, an Concurrency Violation exception is thrown. It only happens on new records. I can save as many times as I wish on already existing records, but not on new records. Usually, Concurrency violation exception is due to the fact that more than one user is modifying the record, but in that case, only one user is modifying the information. It seems as if the first update didn't "EndEdit" even though it was clearly done if you look at my code. That's not good because when I call the update method of the table adapter, it's trying to update every changes made in that table of the dataset, meaning I can never save changes in the application data unless I restart the application because it's always throwing the exception. I read many things on bypassing concurrency violation exceptions, and for the dataset, it seemed pretty logical to just remove the "optimistic concurrency" option. It didn't work, because well, that's probably not even the reason why it's throwing that exception. I was wondering if this problem can be solved, without switching to a disconnected mode. I want a solution or a reason as to why we cannot insert a new record and save more than once without leaving the application. This problem can easily be reproduced by dragging a data source on a form and trying exactly what I wrote that isn't working fine. If someone has a solution for that, I'd really appreciate the help. BTW, I won't accept "Don't use datasets" as an answer unless there really isn't an other solutions...
-
Sorry for the long text... I'm using a dataset for the link between my application and the MS Access database. The problem I have is with the Update function of the table adapter object. The code is really simple and it works very well except in one situation. Here's the code (generated by dragging from the data source to the windows form): this.tblRequestBindingSource.EndEdit(); this.tblRequestTableAdapter.Update(this.forms_Work_LogDataSet.tblRequest); The situation when it doesn't work is very specific and I've tried it on several test applications and it seems to be caused by the dataset. When I create a new record and save right away, everything is fine. Then, if I make a modification to the record and save again, an Concurrency Violation exception is thrown. It only happens on new records. I can save as many times as I wish on already existing records, but not on new records. Usually, Concurrency violation exception is due to the fact that more than one user is modifying the record, but in that case, only one user is modifying the information. It seems as if the first update didn't "EndEdit" even though it was clearly done if you look at my code. That's not good because when I call the update method of the table adapter, it's trying to update every changes made in that table of the dataset, meaning I can never save changes in the application data unless I restart the application because it's always throwing the exception. I read many things on bypassing concurrency violation exceptions, and for the dataset, it seemed pretty logical to just remove the "optimistic concurrency" option. It didn't work, because well, that's probably not even the reason why it's throwing that exception. I was wondering if this problem can be solved, without switching to a disconnected mode. I want a solution or a reason as to why we cannot insert a new record and save more than once without leaving the application. This problem can easily be reproduced by dragging a data source on a form and trying exactly what I wrote that isn't working fine. If someone has a solution for that, I'd really appreciate the help. BTW, I won't accept "Don't use datasets" as an answer unless there really isn't an other solutions...
Which DataAdapter class are you using? If you're using the
OdbcXxxfamily, ensure you're using the Access ODBC driver directly by specifyingDriver={Microsoft Access Driver (*.mdb)}
If using OLE DB (OleDbConnection etc) the situation is reversed - ensure that you're not trying to use the OLE DB wrapper around ODBC (MSDASQL). If you're using a DSN you will be using this implicitly, likewise if you don't specify a provider. Use this for OleDbConnection:
Provider=Microsoft.Jet.OLEDB.4.0
People have reported that although Jet is COM-based internally, ODBC connections can be faster because the P/Invoke overhead is lower than the COM Interop overhead. As an experiment, you could see if the problem also occurs if using the other type of adapter, or with a different provider and database engine, e.g. SQL Server Compact Edition[^] (which is also an in-process database engine).
DoEvents: Generating unexpected recursion since 1991
-
Which DataAdapter class are you using? If you're using the
OdbcXxxfamily, ensure you're using the Access ODBC driver directly by specifyingDriver={Microsoft Access Driver (*.mdb)}
If using OLE DB (OleDbConnection etc) the situation is reversed - ensure that you're not trying to use the OLE DB wrapper around ODBC (MSDASQL). If you're using a DSN you will be using this implicitly, likewise if you don't specify a provider. Use this for OleDbConnection:
Provider=Microsoft.Jet.OLEDB.4.0
People have reported that although Jet is COM-based internally, ODBC connections can be faster because the P/Invoke overhead is lower than the COM Interop overhead. As an experiment, you could see if the problem also occurs if using the other type of adapter, or with a different provider and database engine, e.g. SQL Server Compact Edition[^] (which is also an in-process database engine).
DoEvents: Generating unexpected recursion since 1991
-
Sorry for the long text... I'm using a dataset for the link between my application and the MS Access database. The problem I have is with the Update function of the table adapter object. The code is really simple and it works very well except in one situation. Here's the code (generated by dragging from the data source to the windows form): this.tblRequestBindingSource.EndEdit(); this.tblRequestTableAdapter.Update(this.forms_Work_LogDataSet.tblRequest); The situation when it doesn't work is very specific and I've tried it on several test applications and it seems to be caused by the dataset. When I create a new record and save right away, everything is fine. Then, if I make a modification to the record and save again, an Concurrency Violation exception is thrown. It only happens on new records. I can save as many times as I wish on already existing records, but not on new records. Usually, Concurrency violation exception is due to the fact that more than one user is modifying the record, but in that case, only one user is modifying the information. It seems as if the first update didn't "EndEdit" even though it was clearly done if you look at my code. That's not good because when I call the update method of the table adapter, it's trying to update every changes made in that table of the dataset, meaning I can never save changes in the application data unless I restart the application because it's always throwing the exception. I read many things on bypassing concurrency violation exceptions, and for the dataset, it seemed pretty logical to just remove the "optimistic concurrency" option. It didn't work, because well, that's probably not even the reason why it's throwing that exception. I was wondering if this problem can be solved, without switching to a disconnected mode. I want a solution or a reason as to why we cannot insert a new record and save more than once without leaving the application. This problem can easily be reproduced by dragging a data source on a form and trying exactly what I wrote that isn't working fine. If someone has a solution for that, I'd really appreciate the help. BTW, I won't accept "Don't use datasets" as an answer unless there really isn't an other solutions...
Are you using an autonumber as the primary key? If so the adapter.update will not return the number to the data table. to correct for this add a rowchanged event to the adapter and use the code shown below to retrieve the IDENTITY value. This may fix your problem.
// To the DataAdapter Add adapter.RowUpdated += new OleDbRowUpdatedEventHandler(OnRowUpdateed); // The event handler assuming "Id" is the name of the Identity column private void OnRowUpdated(object sender, OleDbRowUpdatedEventArgs e) { if (e.StatementType == StatementType.Insert) { using (OleDbCommand cmd = new OleDbCommand("SELECT @@IDENTITY", e.Command.Connection)) { e.Row\["Id"\] = cmd.ExecuteScalar(); e.Row.AcceptChanges(); } } }William T
modified on Wednesday, June 4, 2008 4:47 AM