在VS2015中更新VB.NET中的访问数据库 [英] Update access database in VB.NET in VS2015

问题描述

我已经按照在线教程指导我连接到Access数据库，并使用数据适配器和数据集从数据库中获取数据。

我有一个Windows表单，首先得到一个我的数据库作者表中的作者列表使用dataadapter和数据集并将它们放入组合框中。当我在该组合框中选择作者时，我使用另一个dataadapter和数据集组合从数据库的 Books 表中获取该作者的所有书名列表并将它们放入第二个组合框。

当我从该组合框列表中选择一个特定的书时，我使用另一个适配器/数据集组合来获得所选书籍的（1）完整记录（ ID ）并根据数据类型在文本框和组合框中显示这些详细信息（例如，数据库有书籍格式表（精装版，平装本等）但是' 图书'表只保存格式的 ID ，所以我有一个包含所有格式类型的组合框设置为根据检索到的记录数据中的格式 ID 显示相关格式。所有上述工作都很好，但如果我编辑书的标题，说，并尝试将更改保存到数据库我得到一个错误。



教程建议使用： -

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")

所以我复制了这个并取代了我的' da '的Dataadapter名称和' ds '的数据集名称以及'我给了我的名字' ta（？）'for AddressBook 。分配'值'的代码仅指向行（0），但贯穿所有'项目 '在那一排： -



我的代码---

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")

当我点击保存更改按钮运行上面的代码时，我收到OleDbException错误： -

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.

我尝试了什么：



输入上面的内容之后，我想也许我需要使用整个'书籍'表的数据集所以我添加了代码来为整个'Books'表打开一个带有新适配器的新数据集..

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")

然后循环遍历该数据集，直到我有正确的行（值 - 'inc'）来引用所选书籍，然后为数据集中该行中的每个项目分配值。

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

等...



和chan使用'更新'行来引用'新'数据集和适配器..

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.

推荐答案

好吧，我建议阅读以下文章：

< a href =http://stackoverflow.com/questions/36678396/c-sharp-syntax-error-in-insert-into-statement-an-unhandled-exception-of-type>数据库 - C＃ - 语法错误INSERT INTO语句（类型&＃39; System.Data.OleDb.OleDbException&＃39;未处理的例外;发生在System.Data.dll中） - 堆栈溢出 [ ^ ]

FIX：使用CommandBehavior.SingleRow的ExecuteReader方法时出现未处理的异常错误消息 [ ^ ]

OleDbCommandBuilder类（System.Data.OleDb） [ ^ ]



也请遵循引用的文章。

Well, i'd suggest to read below articles:
database - C# - Syntax error in INSERT INTO statement (An unhandled exception of type &#39;System.Data.OleDb.OleDbException&#39; occurred in System.Data.dll) - Stack Overflow[^]
FIX: "Unhandled Exception" Error Message When You Use ExecuteReader Method with CommandBehavior.SingleRow [^]
OleDbCommandBuilder Class (System.Data.OleDb)[^]

Follow the referenced articles too.

这篇关于在VS2015中更新VB.NET中的访问数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！