Question How to save datagridview data to the database file.

[Pescadore]

I'm using VB Express 2008 in Windows 7 64-bit. I'm working on a project with a datagridview bound to a MS Access 2007 database. I want to use the datagridview to add, delete and update the data in the MS Access database. When I added the database file to the project through the Datasource Configuration Wizard, I selected 'No' to copy the file to the project folder. The connection string automatically placed in the project settings is: Provider=Microsoft.ACE.OLEDB.12.0;Data Source="F:\My Money\DoBills\DoBills2009.accdb"* The 'Copy to Output Directory" property for the DoBills2009DataSet.xsd is set to '*Do not copy*'(and I've tried every other setting available). So, as I understand it, I am working with the original file, not a copy, and any changes (additions, deletions, field changes, etc.) are made directly to the original file. I have added the following code to the datagridview rowleave event:

    Private Sub dgvSortBills_RowLeave(ByVal sender As Object, ByVal e As
System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvSortBills.RowLeave
        Try
            Me.Validate()
            Me.SortBillsBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.DoBills_2009DataSet)
            Me.DoBills_2009DataSet.AcceptChanges()
            CalcTotals() 'sub procedure that doesn't affect the datafile.
        Catch ex As Exception
            MsgBox("Update failed")
        End Try
    End Sub

Later, I'll figure out a way to skip the update if nothing is changed, but for right now, it doesn't work. I've searched MSDN, the MS Community forum and several other forums for a solution and all I have found is what I already have and it doesn't work. I can make changes at run-time alright, but the exception it triggered when I change rows, and the data is not saved to the database file. When I close/re-run the app, the changes to the data are not there. There has to be a way to save changes to the datafile, but I'm just not finding it. Can someone please help?

 

[jmcilhinney]

The first thing to do is to not simply ignore your exception. If an exception is thrown it's thrown for a reason, but you're making no effort to determine what that reason is. The exception contains that information, so you need to actually interrogate the exception for its type, error message, stack trace, etc.

That said, because of your fairly thorough post I can be fairly certain that I know what your issue is. You say that you're running on an x64 system and you're using the ACE OLEDB provider. By default, .NET apps will run in 64-bit mode on x64 systems but there is no 64-bit version of ACE, so your app thinks ACE is not installed. You need to set your project to compile specifically for x86 so that it will run in 32-bit mode on x86 systems and be able to use the 32-bit version of ACE.

 

[jmcilhinney]

Something worth noting about databases and copying them: what are you going to do when it comes time to deploy your app? You've only got one database so you're going to have to clean it out and then make a copy manually to deploy. That's why you should generally add the database to your project and debug with a copy: your original database stays clean and a copy if created automatically when it comes time to deploy.

 

[Pescadore]

Thanks for your input jmcihenney. I have set my project to compile for x86. I had to do that to even get the datagridview to display the data, and I have shared in an earlier post in this forum the way I found to do that. However, I guess that could still have something to do with the problem.

Also, I don't know how interogate the exception for its type, error message, stack trace, etc. Can you point me somewhere to learn how to do that?

 

[Pescadore]

Also, jmcihenney, I have no intentions right now to ever deploy this app. It's just a small app I use to help make decisions in my personal bill paying process. I guess some day I might deploy it, but I'm a preacher. Programming is a hobby. I'm as clueless as to how to go about developing and marketing software as I am about how to get this database to update.

 

[jmcilhinney]

An exception is an object, just like any other. To get data from any object you use its members, i.e. call methods and get properties.

 

[Pescadore]

Thanks again, jmcilhinney. I created an exception string to return in a message box. Here is how the code for the rowleave even to my datagridview now looks:

Try
'Me.Validate()
Me.SortBillsBindingSource.EndEdit()
Me.TableAdapterManager.UpdateAll(Me.DoBills_2009DataSet)
Me.DoBills_2009DataSet.AcceptChanges()

'CalcTotals()
Catch ex As Exception
Dim exceptionstring As String
exceptionstring = "Type: " & ex.GetType.ToString & Chr(13) & _
"Message: " & ex.Message.ToString & Chr(13) & _
"Stack trace: " & ex.StackTrace.ToString
MsgBox(exceptionstring)

'MsgBox("Update failed")
End Try

Here is the message returned:

Type: System.ApplicationException

Message: TableAdapterManager contains no connection information. Set each TableAdapterManager TableAdapter property to a valid TableAdapter instance.

Stack trace: at DoBills.DoBills_2009DataSetTableAdapters.TableAdapterManager.UpdateAll(DoBills_DataSet dataset) in f:\MyProgramming\DoBills\ver 6+\6-0-0\prjDobills\DoBills_2009DataSet.Designer.vb:line 1831 at DoBills.frmDoBills.dgvSortBills_RowLeave(Object sender, DataGridViewCellEventArgs e) in f:\My Programming\DoBills\ver 6+\6-0-0\frmDoBills.vb:line 114

I don't understand what this message is telling me much less why or how to fix it.

 

[jmcilhinney]

First up, get rid of that call to AcceptChanges. It's of no use.

As for the error, I'm not sure why you'd have a TableAdapterManager with no connection information. That said, where did 'Me.TableAdapterManager' come from? I've never actually used a TableAdapterManager but, from what I understand, shouldn't that be something like 'Me.myDataSet.TableAdapterManager'?

 

[Pescadore]

Rev

The TableAdapterManager was created when I first dragged the table from Data Sources onto my form. It sits in the tray area below the form design palette along with the DoBills_2009DataSet, sortBillsTableAdapter, sortBillsBindingSource, ToolStrip and ImageList. Only the TableAdapterManager has any update method and it is "UpdateAll", which takes DoBills_2009DataSet as an argument.

I did add sortBillsTableAdapter to the properties for the TableAdapterManager, ran the app and got the following error message:

Type: System.Data.OleDb.OleDbException

Message: Command test was not set for the command object.

Stack trace: at DoBills.DoBills_2009DataSetTableAdapters.TableAdapterManager.UpdateAll(DoBills_2009DataSet dataSet) in F:\My Programming\DoBills\ver\6+\6-0-0\prjDoBills\DoBills_2009DataSet.Designer.vb:line 1934 at DoBills.frmDoBills.dgvSortBills_RowLeave(Object sender, DataGridViewCellEventArgs e) in F:\My Programming\DoBills\ver\6+\6-0-0\prjDoBills\frmDoBills.vb:line 113​

In the DoBills_2009DataSet.xsd View Designer, the sortBillsTableAdapter has Insert Command, Delete Command, and Update Command listed as properties. Each of these call for an SQL command to insert/delete/update the data in the database. I understand SQL to query a database, but I have no clue as to SQL statements to insert/delete/update.

If I run the app and delete a row/record, I get the following error message:

Type: System.InvalidOperationException

Message: Update requires a valid DeleteCommand when passed DataRow collection with delete rows.

Stack trace: at DoBills.DoBills_2009DataSetTableManager.TableAdapterManager.UpdateAll(DoBills_2009DataSet dataset) in F:\My Programming\DoBills\ver 6+\6-0-0\prjDoBills\DoBills_2009DataSEt.Designer.vb:line 1934 at DoBills.frmDoBills.dgvSortBills_RowLeave(Object sender, DataGridViewCellEventArgs e) in F:\My Programming\DoBills\ver 6+\6-0-0\prjDoBills\frmDoBills.vb:line 115​

So, I think I need something in the Insert, Delete and Update SQL commands. I just don't know what. Do you know or can you point me to where I can find out?

Again, thanks so much for your help, jmcilhinney.

 

[Pescadore]

How to save changes of a datagridview to the database

I was finally able to solve this issue. When I originally added the datafile to the app, I dragged a query from the datasource onto the form to create the datagridview, but the delete, insert and update commands in the table adapter in the .xsd file were left empty. So I removed the datasource and started over. This time I added the table rather than the query based on the table. As a result, the delete, insert and update commands were automatically created and I was the able to save changes to the datasource made through the datagridview. There are still a few little bugs I need to work out yet, but if anyone's having trouble saving changes to the datasource of a datagridview, you might check to make sure you're using a table from the datafile and not a query.

 

[jmcilhinney]

You could have left it as the query, but you'd have had to write the SQL code to delete, insert and update yourself too.

 

[Pescadore]

I agree, jmcilhinney. I just didn't know how to write that kind of sql code and couldn't find any help to learn. The query I was trying to use was a left-over from MS Access 1.1 when I first started using this particular database and all it did was sort the data, which I found that I didn't really need because I can do that in VB. It was just easier to use the table, although I did try to copy/paste the sql commands from the table version to the query version just to see if that would work for future reference, but it didn't, so... Anyway, I really appreciate you input throughout this learning process for me.