Adding rows to Access table using code

[bones]

I have a bound datagridview on a form, [access table bound]

I found this helpful video that explained the the binding process and the layer structure involved. https://www.youtube.com/watch?v=dRcFpqLrkTw&index=1&list=PL107E18F44E230A53

I found example code that is supposed update the data table [I thought at first this meant the actual access database table but I might be wrong about that] that I have tied to a command button. I found it here https://science.nature.nps.gov/im/units/arcn/data_management/code/81.htm

Sample Code

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim dsNewRow As DataRow
        dsNewRow = SheepMonitoringDataSet.tblSheepGroupPhotos.NewRow
        dsNewRow.Item("SheepGroupID") = 3
        dsNewRow.Item("Filename") = "Skeeter.jpg"
        SheepMonitoringDataSet.tblSheepGroupPhotos.Rows.Add(dsNewRow)
        TblSheepGroupPhotosTableAdapter.Update(SheepMonitoringDataSet)
    End Sub

My Code

[LEFT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] Button5_Click([/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] sender [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] System.[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af]Object[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2], [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] e [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] System.[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af]EventArgs[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]) [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] Button5.Click[/SIZE][/FONT][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] dsNewRow [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af]DataRow[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]dsNewRow = Items1DataSet.items.NewRow
[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]dsNewRow.Item([/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#a31515][FONT=Consolas][SIZE=2][COLOR=#a31515][FONT=Consolas][SIZE=2][COLOR=#a31515]"item1"[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]) = [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2].TextBox3.Text[/SIZE][/FONT][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]dsNewRow.Item([/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#a31515][FONT=Consolas][SIZE=2][COLOR=#a31515][FONT=Consolas][SIZE=2][COLOR=#a31515]"item2"[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]) = [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2].TextBox7.Text[/SIZE][/FONT][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]dsNewRow.Item([/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#a31515][FONT=Consolas][SIZE=2][COLOR=#a31515][FONT=Consolas][SIZE=2][COLOR=#a31515]"item3"[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]) = [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2].TextBox5.Text[/SIZE][/FONT][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]dsNewRow.Item([/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#a31515][FONT=Consolas][SIZE=2][COLOR=#a31515][FONT=Consolas][SIZE=2][COLOR=#a31515]"item4"[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]) = [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2].TextBox8.Text[/SIZE][/FONT][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]items1DataSet.items.Rows.Add(dsNewRow)
items1TableAdapter.Update(items1DataSet)
[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT]
[/LEFT]

The code throws no errors. The data does in fact show in the dataview grid.
In reality though the database table never gets the update.

The database is copied into the project rather than being accessed externally. I have checked both databases just to be sure.

Interestingly, if I manually enter data and click another command button with the same tableadapter.update code, that works fine....

I should add that the table is indexed with an ID field and it is set to auto for number assignment. When I run the code from button1, the field ID shows as a negative value eg. -1. If I close the form and reopen it from the main form, the updated data appears to be there and the id NUMBER IS NO LONGER A NEGATIVE NUMBER. I do not attempt to send an id number via the code...I just let the access back end handle that. BUT, If I quit the debugger and run it again, there is no updated data in the table..

I have read conflicting information on the .net as to which is the correct way to go with database connections, that is to say copied into the project vs accessed externally. I found a helpful video to watch where they did use the copy method and it all worked fine. Don't know if that's part of the issue or not but I suspect it it was, at least one copy of the db would have received the updates????

I may be confused on this process. I am thinking that writing it to the data table is not the same as writing it to the db table. Is that correct? If so, how to I get it that last step to home base "the table"?

Any ideas?

I'm new to VB10, have a fair bit of experience with VBA....but I"m trying to work my way though this learning curve without pestering anyone any more that I need to :embarrassed:

 

[jmcilhinney]

Calling NewRow creates a new DataRow with the appropriate schema, i.e. number and type of columns. Calling Add inserts that row into your DataTable, which is your local data cache. Calling Update saves all the changes (inserts, updates and deletes) from the DataTable back to the database.

One issue you have there is that you're mixing two different data access technologies. You're using some straight ADO.NET and some typed DataSet. You should pick one and stick to it.

If you want to use raw ADO.NET then, for an Access database, you'll create your own OleDbConnection to connect to the database, OleDbCommands to execute SQL code and OleDbDataAdapter to move data back and forth between the database and a DataTable. That DataTable will be just a plain old vanilla DataTable.

If you want to use a typed DataSet then you will run the Data Source Configuration Wizard. You must have already done that. That will generate a number of custom classes in your project that you use instead of the regular generic class. The wizard will generate a custom DataSet class that you use instead of a regular DataSet. Instead of just having a Tables collection that you access regular DataTables from by name, your custom DataSet has a property for each table and each of those is a custom type that inherits DataTable. Instead of using the Rows collection of the DataTable to get regular DataRows, you use the custom DataTable itself as a collection of custom DataRows. Each of those custom rows has a property of the appropriate type for each field instead of a collection of Object references that require casting. As an example, this is how you might use regular ADO.NET objects:

Dim name = CStr(myDataSet.Tables("Person").Rows(0)("Name"))

while the equivalent code using a typed DataSet would be:

Dim name = myDataSet.People(0).Name

Shorter, no need for "magic strings" to name tables and columns and no casting required.

Because a typed DataTable inherits the DataTable class, it inherits the NewRow method. Don't use it. There is another, similarly-named method that, instead of returning a standard DataRow object, will return a typed DataRow. You can then set the typed properties of that typed DataRow directly instead of having to use magic strings to name each column. Finally, there table itself will have a method to add the typed row that you use instead of adding the untyped row to the Rows collection.

You're already using a table adapter so you don't have to change that part. Instead of you creating a connection, command and data adapter, you simply create the table adapter and it creates those parts automatically, hidden inside where you don;t have to worry about them.

So, once you've sorted all that out, then you can look at your issue. Most likely there is no issue at all, but I'll explain how to determine that. Whether you use a data adapter or table adapter, you're going to be calling Update to save your changes. Update returns a number and that represents the number of records in the database affected by the operation. You can simply test that number to see whether it's zero or not. There are three possible outcomes:

1. The call fails and throws an exception.
2. The call succeeds and returns zero.
3. The call succeeds and returns a non-zero value.

If you get the first outcome then you need to deal with the specific exception that's thrown, whatever that may be. If you get the second outcome then there were no changes in your DataTable that could be saved by your adapter. If you get the third outcome then there were changes and they were saved. In that case, you should follow the first link in my signature to learn how local data files are managed.

 

[bones]

THAN YOU for that information and for answering another question I posted. I will sort carefully through the information you provided and see what I can accomplish. When I set out to learn Visual Basic 2010 I didn't realize there would be so many different flavors of code. I have miles to go before I sleep........

 

[jmcilhinney]

THAN YOU for that information and for answering another question I posted. I will sort carefully through the information you provided and see what I can accomplish. When I set out to learn Visual Basic 2010 I didn't realize there would be so many different flavors of code. I have miles to go before I sleep........

There are far more options than just that for data access too. You can use LINQ to SQL for SQL Server, Entity Framework for numerous data sources, NHibernate and more.

 

[bones]

There are far more options than just that for data access too. You can use LINQ to SQL for SQL Server, Entity Framework for numerous data sources, NHibernate and more.

I try to adhere to K.I.S.S. principles in everything I do. I'd like to apply it here as well...... Keep It Simple...

I read the link you suggested. I gather that none of the three copy options will retain the changes made to the db. I would think that at least one of the options would keep the changes???

If, when running running the app in debug, the changes are taking place, will they show up in the db copy in the bin/x86 folder where the db gets copied to?
Can I check that copy while debug is running or will I get access denied when I try to view the bin copy of the db using access?

If the changes are in fact showing up in the bin copy then can I assume that in the live application it will be fine?


HOLD THE PHONE..... I just ran the app in debug and looked at the bin copy of the db. The changes are showing up there. Do you think that all is well with the code as far as accomplishing the task, irregardless of the mix of code being used?

 

[jmcilhinney]

I read the link you suggested. I gather that none of the three copy options will retain the changes made to the db. I would think that at least one of the options would keep the changes???

Then you gather wrongly. "Copy always" will copy the source database over the working database every time the project is built. If you run the app, make changes, close the app, then run the app again, your changes will still be there because the project will not have been built. If you change any code between runs then the project is built and your changes are lost. If you use "Copy if newer" then the changes will not be lost even if the project is built, as long as you don't make any changes to the source database. All you have to do is change the "Copy to output directory" property from "Copy always" to "Copy if newer".

 

[bones]

Thank you again for clearing that up... I have that part all working to satisfaction now, thanks to you.