Working with an Access DB in VB.Net

Part 4 - Committing your changes

Before we go any further we need to take a small step back, right back to the start of this guide.

Do you remember the stage where we created the data adapter and dataset?

Dim adapter As OleDbDataAdapter = New OleDbDataAdapter("Select * from Customers", con)

Notice the "Select * from Customers" select statement again. Basically, when ever you update, insert, delete or select from a database via a data adapter you have to give your adapter the SQL command that it needs to perform the task your asking of it. Now I don't mind SQL, I work with it quite a lot, but I'm also lazy :-)

If I don't have to work out what an SQL command should be then all the better, and luckily for me the chaps (and chapetes) at Microsoft think like I do. There is another class of object that we haven't touched on yet called the OleDBCommandBuilder. It will generate SQL statements for you for your data adapter. All you need to do is request the right one. Once you have created your Adapter as above insert the next line afterwards..

Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(adapter)

This creates a new command builder object called builder and attaches it to your adapter.

Before you use the data adapter to apply the changes in your dataset you must generate the relevant statement with your command builder object. You simply call the appropriate method of the command builder....

builder.GetDeleteStatement
builder.GetUpdateStatement
builder.GetInsertStatement

AApplying your changes - Updating the dataset Updating the dataset in itself is a simple process if the items above have been followed. Just use the update method of your adapter to write the changes back to the live database. adapter.update(ds,"Customers") then the last thing you need to do is accept changes on your dataset to bring it up to date..

ds.AcceptChanges()

Hopefully that has helped you get an understanding of how to work with a dataset in vb.net. For further help please check out the useful links section of the website

Back to 'Snippets and useful things'