Updating Access from Data Table - slightly confused

I'm using vb2005 to create a front end to an access database. Being new to this I'm slightly confused over what the wizards will create automatically as opposed to what I have to code. I've used the table adapter configuration wizard and specified that I want it to create methods to update a table called 'Categories' in the database. This is a simple table with a text field for the category name and an autonumber field (CategoryID) as the primary key. Now, I've read the supplied help info and a few newgroup posts about how to update a table row containing an autonumber field by
using the RowUpdated event of the adapter class. The code example that seems to get shown everywhere doesn't seem to 'fit' well with the class driven approach that the wizard generates so I've tried to implement such a handler for the TableAdapter that the wizard creates. As the wizard regenerates the DataSet.Designer.vb file whenever the config is changed via the graphical designer I didn't want to add a RowUpdated handler in there, so I've created a separate source file and added to the partial class definition for the table adapter as follows:

Namespace quizDataSetTableAdapters
Partial Public Class CategoriesTableAdapter

Private Sub OnRowUpdated(ByVal sender As Object, ByVal args As
System.Data.OleDb.OleDbRowUpdatedEventArgs) Handles _adapter.RowUpdated
' Include a variable and a command to retrieve the identity value
' from the Access database.
Dim newID As Integer = 0
Dim idCMD As System.Data.OleDb.OleDbCommand = New
System.Data.OleDb.OleDbCommand( _
"SELECT @@IDENTITY", _connection)

If args.StatementType = StatementType.Insert Then
' Retrieve the identity value and store it in the CategoryID column.
newID = CInt(idCMD.ExecuteScalar())
args.Row("CategoryID") = newID
args.Row.AcceptChanges()
End If
End Sub

End Class
End Namespace

Then at the point where I want to add a new category I have the following:

Private Sub OK_Button_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles OK_Button.Click
Dim sCategory = Me.tbNewCategory
Dim NewRow As DataRow = Form1.QuizDataSet.Categories.NewRow()

NewRow("Category") = Me.tbNewCategory.Text
Form1.QuizDataSet.Categories.Rows.Add(NewRow)

'Now call table adapter Update routine - causes write back to database
which will trigger event to get autonumber
Form1.CategoriesTableAdapter.Update(Form1.QuizDataSet.Categories)

Me.DialogResult = System.Windows.Forms.DialogResult.OK
Me.Close()
End Sub

This all compiles and executes without error; indeed I can set a breakpoint
on the RowUpdated handler which gets hit and shows a 'sensible' value for
the autonumber coming back from Access. However, the new record has not
actually been written to the database. I am assuming the Update method that
the wizard has created would be correct and as my RowUpdated handler gets
triggered I'm assuming something must be scanning the datatable for changes
and 'doing the necessary'. Can anyone enlighten me as to what I'm missing -
what else do I have to code or is there an obvious error in what I've done
here.

Answer this question

Updating Access from Data Table - slightly confused

Neil Dela Osa

What you are doing is correct -- I just tested it myself using the code snippets you have posted.

So you are never seeing the record appear in the MDB file When you click the button a second time, do you get a new AutoNumber back from the SELECT @@IDENTITY query, or does it keep giving you the same number If you close the app and run it again, what identity value do you get

Sometimes there is a small delay between the time that the application does the insert and the time that the record shows up in the database. This is due to an in-memory cache that the Jet engine maintains. However, it should only be a short period of time (several seconds at the most), and the value should definitely be there if you close the app, because the cache is in the process's memory and is flushed at that point.

Unfortunately the Jet engine doesn't have much in the way of tracing capabilities, so there's not a lot we can do to see what is actually going on under the covers. However, we do have some tracing built into ADO.NET 2.0. You could at least use this to confirm that the statements are being sent to the OLE DB provider. Here is a good article that explains how to use the tracing: http://msdn.microsoft.com/library/default.asp url=/library/en-us/dnadonet/html/tracingdataaccess.asp.

Thanks,

Sarah
dickP

Update:

kmitty and I took this offline and finally figured out what is going on. The key is what happens when you create a new Data Source based on a Data Connection that is considered "local", like Access MDB files. If the Data Source file is not already copied into your project, you will get a prompt like this:

The connection you selected uses a local data file that is not in the current project. Would you like to copy the file to your project and modify the connection

If you copy the data file to your project, it will be copied to the project's output directory each time you run the application. Press F1 for information on controlling this behavior.

If you choose Yes, it copies the file to your project. The key is that this also automatically sets the “Copy to Output Directory” property on the file to “Copy always”. What this means is that every time you build, run, or debug your application, Visual Studio copies the project file over to the output directory, and then that’s what the app connects to.

When you choose to let VS copy the database, it also gives you a different connection string than if you don’t let it copy. With the copy option, it gives you this: “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\db1.mdb”. Without it, you get the full path to the MDB file that you specified, instead of using the special DataDirectory keyword. In order to make sure your database really does exist in the DataDirectory, it does the copy each time you run the app (or doesn't, depending on the setting I mentioned above).

The problem here is not that the MDB wasn't being updated, but that a copy in the bin\debug folder is where the updates were going, and each time the app was run from the VS IDE, the MDB was copied over.

Notice that if you don’t run the application in VS, and instead just run the EXE directly from windows explorer, you will not see the copy happen, and the MDB file in the output directory (bin\debug by default) will continue to be updated. This is how the application will be run in a normal production setting, not under VS, so this is what you would typically want for a real app.

Here are some references:

Visual Studio behavior with local data files: http://msdn2.microsoft.com/en-us/library/ms233817(VS.80).aspx
DataDirectory keyword: http://msdn2.microsoft.com/en-us/library/ms254499.aspx

Another tool that could be used here is FileMon, from http://www.sysinternals.com. That would show you the actual MDB file that is being used by the application.

Thanks,

Sarah
Kousay

ah ok,

have you tried omitting the AcceptChanges call

not sure at what point the tableadapter actually writes to the database, i can't see anything wrong with the code you posted
Soheil Saadat

First of all your idea about inserting the OnRowUpdated event on the partial class of TableAdapter was really great. So after doing a lot of code testing trying a lot of stupid coding I find a solution which may not be the perfect but it is working. The only thing you have to do is to write the OnRowUpdated function like this (it is written in C#)

// Event procedure for OnRowUpdated

protected void OnRowUpdated(object sender, OleDbRowUpdatedEventArgs args)

{

// Include a variable and a command to retrieve the identity value from the Access database.

int newID = 0;

OleDbCommand idCMD = new OleDbCommand("SELECT @@IDENTITY", args.Command.Connection);

if (args.StatementType == StatementType.Insert)

{

// Retrieve the identity value and store it in the proper column.

newID = (int)idCMD.ExecuteScalar();

args.Row["CustomerID"] = newID;

args.Row.AcceptChanges();

OleDbDataAdapter da=new OleDbDataAdapter("SELECT * FROM Customers WHERE CustomerID="+newID,args.Command.Connection);

DataTable dt=new DataTable();

da.Fill(dt);

args.Row.Table.Merge(dt);

args.Row.Table.AcceptChanges();

}

}

I think you get the point what I am doing. It's a little stupid but it works. Of course you can do some improvements.
Richard Berg MSFT
Yes, started off without that and only added it when I saw the mdb wasn't being updated. Didn't make any difference!
ku19832001

It doesnt look like you have set the UpdateCommand property of your Tableadapter object

You need to set this to a stored procedure or SQl statement that will be called when the Update() methdod is called on the table adapter
Dubouku
The second time the button is clicked the handler is again correctly entered and the identity value returned is 1 more than the first time through which seems to imply that it is working correctly! If I then stop the app and restart (all within the VB IDE) then the first time through the identity value reverts to what I got on the first run.

What is slightly annoying is that all the auto-generated methods have an attribute that prevents the debugger tracing in to them - is there a mechanism for globally disabling that

I looked at the link on tracing and it would take a while to get my head around all that - rather more than I wanted to get into on a first dip into all this!

I've also tied getting the wizrad to create an 'Add' method for the category table adapter (taking a string as the parameter) - the generated sql looks correct, but when invoked that doesn't even trigger my handler - mightily confused
Claudio Rossi

I haven't received the trace in e-mail yet -- did it bounce back to you You can also try my first name dot my last name @microsoft.com. (e.g. first.last@microsoft.com)

Thanks,

Sarah
zonehenge

Hmm, very weird. I can't think of why this is happening. It sounds like the inserts are at least being recognized by the Jet engine, because the SELECT @@IDENTITY value is coming from there, not from ADO.NET. If you are getting increasing values while the app is running, it sounds like the INSERTS are definitely making it to Jet (in memory, not necessary in the MDB file). However, for some reason the writes are not being flushed out to the database. It almost sounds like you are running inside of a transaction somehow, and the data is not being committed. Other than the code snippets you have included in your post, did you add anything to the app besides using the wizard to generate the TableAdapter Is this just a new Windows Application that you have added the TableAdapter to If no, can you try to create a new simple app and verify that you are still seeing the behavior there

Have you tried to run your code on another machine The Jet engine has some machine-wide settings in the registry, but I don't know of any that would cause this behavior.

Regarding the debugger attribute on the auto-generated methods, I think you are talking about this:

<System.Diagnostics.DebuggerNonUserCodeAttribute()>

Yes, you can allow debugging into these methods by going to Tools->Options->Debugging, then uncheck "Enabled Just My Code (Managed Only)". This will allow you to set breakpoints in those generated methods, so you can do some more debugging.

Regarding the tracing, I can boil it down for you in a few simple steps, if you want to try:

(1) In the registry go to the existing key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\BidInterface. Under this, add a new key named Loader. In the Loader key, add a new String Value called :Path (note the colon in front of Path), with the value c:\WINNT\Microsoft.NET\Framework\v2.0.50727\adonetdiag.dll (change to the correct .NET install folder for your machine).

(2) From a command prompt, run the following:

Logman start MyTrace -p {914ABDE2-171E-C600-3348-C514171DE148} -o out.etl -ets

(3) Run your application and perform the necessary steps to produce the problem.

(4) From the command prompt again, run the following:

Logman stop MyTrace -ets

This will produce a .etl file in whatever folder is current in your command prompt. If you want to e-mail me that file, I would be glad to take a look at it. My e-mail address is in my profile, remove the "online" from the address.

****Note that these steps (particularly #2) are for a limited set of tracepoints that will be fine for the purposes of troubleshooting this particular problem. The whitepaper provides more details for general tracing scenarios.****

If you want to look at the file yourself, run the following two commands from the command prompt:

(1) mofcomp c:\WINNT\Microsoft.NET\Framework\v2.0.50727\adonetdiag.mof

(2) tracerpt -y out.etl -o out.csv

The .csv file is now a text file that you can view.

Thanks,

Sarah
gjc_vp
Not in code that I've written, but in the DataSet.Designer.vb file that the wizard generates there is the following in the partial class definition for the CategoriesTableAdapter

Me._adapter.UpdateCommand.CommandText = "UPDATE `Categories` SET `Category` = WHERE ((`CategoryID` = ) AND (( = 1 AND "& _

"`Category` IS NULL) OR (`Category` = )))"

Which I assume is what you mean and looks ok.

Am using Access so stored procedure not an option
mindshoe
I've tried the app on my laptop and it exhibits the same behaviour. I've done the trace and emailed it to you.

Other than the code posted I've got a Treeview which I've populated from the Categories table and one other in trhe DB - that all works fine.

Thinking back to when I started playing with this the only 'strange' thing I've done was to get the tableadapters auto generated - I dragged a fieldname from the Data Sources Window onto the form; that created the tableadapter, a BindingSource and a Binding Navigator - I then deleted the latter two and the associated controls and methods. I have just tried re-creating and leaving the BindingSource and it makes no difference.

I'll try a single table database and see what happens
Andeezle

Here is a workaround for this situation:

Create a second data connection that points to the file in the \bin\debug directory (I named mine <project>-Debug). Queries can be run on tables in this data connection to see the results of program operations.
Updating Access from Data Table - slightly confused

Answer this question