Update access database in VB.NET in VS2015

See more:

VB

Windows

MS-Access

database

, +

I have followed an online tutorial to guide me through connecting to an Access database and using data adapters and datasets to get data from the database.
I have a single Windows form which starts by getting a list of Authors from my database 'Authors' table using a dataadapter and dataset and putting them into a combo box. When I select an author in that combo box I then use another dataadapter and dataset combination to get a list of all book titles for that author from the 'Books' table of the database and put them into a second combo box.
When I select a particular book from that combo box list then i use another adapter/dataset combination to get the (1) full record for the selected book (ID) and display those details in text boxes and combo boxes depending on the type of data (eg the database has a table of book formats (hardback, paperback, etc) but the 'Books' table just holds the ID of the format so I have a combo box which contains all the format types and this is set to show the relevant format based on the format ID in the retrieved record data). All of the above works fine but if I edit the title of the book, say, and try to save the change to the database I get an error.

The tutorial advises to use:-

Dim cb As New OleDb.OleDbCommandBuilder(da)

ds.Tables("AddressBook").Rows(inc).Item(1) = txtFirstName.Text
ds.Tables("AddressBook").Rows(inc).Item(2) = txtSurname.Text

da.Update(ds, "AddressBook")

So I have copied this and substituted my Dataadapter name for 'da' and my dataset name for 'ds' and the 'name I gave my data(?)' for "AddressBook". My Code to assign 'values' to be saved refers only to row (0) but runs through all the 'items' in that row:-

My Code---

Dim cb As New OleDb.OleDbCommandBuilder(dsetadaptSelectedBook)

'Assign Values to Dataset record for database update
dsetSelectedBook.Tables("SelectedBook").Rows(0).Item(1) = BookTitle
dsetSelectedBook.Tables("SelectedBook").Rows(0).Item(2) = SelectedAuthorID
etc....
dsetSelectedBook.Tables("SelectedBook").Rows(0).Item(14) = LocationID


dsetadaptSelectedBook.Update(dsetSelectedBook, "SelectedBook")

MessageBox.Show("Data updated")

When I click the 'save changes' button to run the above code I get an OleDbException error:-

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

Additional information: Syntax error in UPDATE statement.

What I have tried:

After I'd typed the above I thought that maybe I needed to use a dataset of the whole 'Books' table so I added code to open a new dataset with a new adapter for the whole of the 'Books' table..

'Set The SQL String
SQLText = "Select * From Books"

'Pass The SQL String and CONNECTION object to the Data_Adapter
dsetadaptALLBooks = New OleDb.OleDbDataAdapter(SQLText, con)

'Fill the Dataset with records from the database table
dsetadaptALLBooks.Fill(dsetALLBooks, "Books")

then looped through that dataset until I had the correct row (value - 'inc')to refer to the selected book then assigned values for each item in that row in the dataset..

dsetALLBooks.Tables("Books").Rows(inc).Item(1) = BookTitle

etc...

and changed the 'Update' line to refer to the 'new' dataset and adapter..

dsetadaptALLBooks.Update(dsetALLBooks, "Books")

but I still get the same error.

What am I missing here?

Please help, it's driving me mad.