Please Help.. Update MS Access using DataGridView in VB2005

[vbDiggerz]

Please Help.. Update MS Access using DataGridView in VB2005 I've seen so many post and tutorial about this subject.. and they are all most the same code logic and many of them appreciate it because it works for them?.. on form load the records load smoothly and it works but when everytime datagridview/datasets committ changes and i tyr to update the record and i always got an ERROR like this.. System.InvalidOperationException: Update requires a valid UpdateCommand when passed DataRow collection with modified rows. at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) at System.Data.Common.DbDataAdapter.Update(DataSet dataSet) at Employee_System.Form1.btnUpdate_Click(Object sender, EventArgs e) in C:\Employee_System\Form1.vb:line 31 **************** Here's my Code just like most of the forum i've visited Please anyone who had a good heart to help me with this difficulties.. Please... ******************** Imports System.Data.OleDb Public Class Form1 Dim theOleDbCommand As New OleDbCommand() Dim theOleDbDataAdapter As New OleDbDataAdapter(theOleDbCommand) Dim theDataSet As New DataSet() Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Try Me.theOleDbCommand = New OleDbCommand("SELECT * FROM SampleTable", New OleDbConnection(dbConnectionString)) Me.theOleDbDataAdapter = New OleDbDataAdapter(Me.theOleDbCommand) Me.theDataSet = New DataSet() Me.theOleDbCommand.Connection.Open() Me.theOleDbDataAdapter.Fill(Me.theDataSet) Me.theOleDbCommand.Connection.Close() Me.DataGridView1.DataSource = Me.theDataSet.Tables(0).DefaultView Catch ex As Exception MsgBox(ex.ToString()) End Try End Sub Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click Try Me.theOleDbCommand.Connection.Open() Me.theOleDbDataAdapter.Update(Me.theDataSet) Me.theOleDbCommand.Connection.Close() Catch ex As Exception MsgBox(ex.ToString()) End Try End Sub End Class

[Dave Kreskowiak]

You gave the DataAdapter the SQL SELECT to get the records, but you never gave it how to update the database with SQL UPDATE, INSERT, and DELETE statements. You can have these created automatically by using the OleDbCommandBuilder class.

Dim myDA As New OleDbDataAdpater(myCommand)
Dim cb As new OleDbCommandBuilder(myDA)

A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
     2006, 2007

[vbDiggerz]

It works! Thank You So Much.... I do appreciate... Your such a great help! Thanks again... :)

[vbDiggerz]

Do? OleDbDataAdapter.Update() supports (multiple or INNER JOIN) MS Access Table to be update?? or any other way to update two tables at the same time without making manual hardtype code or such a very long codes?? because in some instance of my application.. I have a DataGridView which its records came from an sql query which are two join table.. The Loading of record works fine.. but when i try to make changes into the DataGridView which also means dataset has also committ changes?? and i try to update the record using: OleDbDataAdapter.Update() then an ERROR occur just like this: System.InvalidOperationException: Dynamic SQL generation is not supported against multiple base tables. at System.Data.Common.DbDataAdapter.UpdatingRowStatusErrors(RowUpdatingEventArgs rowUpdatedEvent, DataRow dataRow) at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable) at System.Data.Common.DbDataAdapter.Update(DataSet dataSet) at Employee.frmDTR.UpdateRecord() in C:\Employee\frmDTR.vb:line 136 ****************** Heres my Code... ************************** Private Sub LoadRecord(ByVal strSQL As String) Try Me.Cursor = Cursors.WaitCursor oleConnection.ConnectionString = dbConnectionString oleConnection.Open() oleDataAdapter = New OleDbDataAdapter(strSQL, oleConnection) oleCommandBuilder = New OleDbCommandBuilder(oleDataAdapter) dbDataSet = New DataSet() oleDataAdapter.Fill(dbDataSet) DataGridView1.DataSource = dbDataSet.Tables(0).DefaultView Me.Cursor = Cursors.Default Catch ex As Exception MsgBox(ex.ToString()) Finally oleConnection.Close() End Try End Sub Public Sub UpdateRecord() Try oleConnection.Open() oleDataAdapter.Update(dbDataSet) Catch ex As Exception MsgBox(ex.ToString()) Finally oleConnection.Close() End Try End Sub *************************** heres my SQL Query ******************************* LoadRecord("SELECT p.IDno as [ID No],p.Lastname,p.Firstname,t.[Time In],t.[Time Out],t.[Date In],t.[Date Out],t.[Remarks] FROM tblPersonal as p inner j

[Dave Kreskowiak]

DataAdapters only work with single-table queries. Anything more complicated and you have to supply ALL the SQL statements and objects to update the database yourself.

A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
     2006, 2007

[vbDiggerz]

Thank your very much... I've already created a manual/hardtype code to do that... i'm just wondering if VB2005 has that kind of function so i can make the code much easier... ehehe.. anyway... thank so much again.. such a great help! :)