Question How to insert into Access db?

gbalcom

Member
Joined
Oct 20, 2016
Messages
12
Location
Georgia
Programming Experience
Beginner
Hi Everyone,
I'm brand new to this forum, and to VB.NET. I've been an intermediate Access VBA programmer for a few years, and decided to try and improve my skills by learning VB.NET. I'm running visual studio community 2015 if it matters.

I figured I could start by duplicating one of my simplest Access applications. my .accdb has three tables; tblEvent, tblJunction, and tblTags. All of the controls on my form except one are bound to fields in the tblEvent table. That other control is a data grid view that is bound to the tblTags table. This data grid view shows just one field (the tags description), but has ID fields hidden to match up with the ID on the parent form, as well as the one for the Tag. This all seems to work when ran. I can flip through the data of existing records fine.

But, I cannot insert new records. My code breaks around the following:

Code:
Public Class frmRecord    Private Sub TblEventBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs) Handles TblEventBindingNavigatorSaveItem.Click
        Me.Validate()
        Me.TblEventBindingSource.EndEdit()
'code breaks on line below....
        TableAdapterManager.UpdateAll(Me.ACS_Engineering_Changes_beDataSet)


    End Sub

I know I need to do something to this, but I'm unclear as to what. Thanks for any insight.

Also, if anyone has a good way to learn this language and/or visual studio, please share. I've been watching videos on Lynda.com, which have been helpful, but I'm exploring all avenues.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,111
Location
Sydney, Australia
Programming Experience
10+
When you say "the code breaks", presumably you mean that an exception is thrown. The IDE gives you plenty of information about any exceptions in order to help you diagnose the issue. If you want us to diagnose the issue for you then it makes sense to pass that information on to us.
 

gbalcom

Member
Joined
Oct 20, 2016
Messages
12
Location
Georgia
Programming Experience
Beginner
Yes, sorry about that. I wasn't sure what information would be helpful. I'll know next time. Here is the exception that was thrown:

Exception thrown: 'System.Data.OleDb.OleDbException' in System.Data.dll


Additional information: An INSERT INTO query cannot contain a multi-valued field.

Exception.PNG


Here is the stack trace if it helps:

at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows)
at ChangeManagementApp.ACS_Engineering_Changes_beDataSetTableAdapters.tblEventTableAdapter.Update(DataRow[] dataRows) in S:\Gary\Visual Studio\ChangeManagementApp\ChangeManagementApp\ACS_Engineering_Changes_beDataSet.Designer.vb:line 2224
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,111
Location
Sydney, Australia
Programming Experience
10+
That error message suggests that one of your columns is type Attachment in Access. ADO.NET doesn't support Attachment columns. You can retrieve them with a bit of jiggery-pokery but there's no way to save them. You have to use DAO for that.
 

gbalcom

Member
Joined
Oct 20, 2016
Messages
12
Location
Georgia
Programming Experience
Beginner
Jmcilhimmey,
Thank you for your response. Yes, I do have one field in that table that is an attachment field. But I'm not referencing it in this form. I'll have to find out why I still have this error. I did drag it over to the form, but then I deleted it.


What is the best way to handle attachments to a form? I'll research the DAO method surrounding attachments. Thanks again.
 

gbalcom

Member
Joined
Oct 20, 2016
Messages
12
Location
Georgia
Programming Experience
Beginner
Top Bottom