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?

[dglienna]

Probably don't have permission to write to the F: drive

[pescadore]

Thanks for your input, dglienna. You might be right, but if you are, it has to be because of some quirk with VB. I write to the F: drive all the time; it is my files drive. I don't store any files on the C: drive if I can help it. That's saved me many, many times.

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.

In my 12/18/09 post, I had the line MsgBox ("Update failed"). I have replaced that with the following:

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

So, now if I run the app and delete a row/record, for instance, 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.TableAdapt erManager.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 in the sortBillsTableAdapter. I just don't know what. Do you know or can you point me to where I can find out?

[dglienna]

You need to supply the key when you insert or delete something, so the compiler doesn't delete EVERYTHING

[pescadore]

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.