Link to home
Start Free TrialLog in
Avatar of mutu
mutuFlag for United States of America

asked on

how to save data in datagridview

I have a datagridview that users edit and when the Save button is pressed I want the data from the datagridview to be saved back to the database.  Whenever I press the Save button my application crashes and I get the following error message:

System execution failure
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)
...

Any ideas on why I'm getting this error?  An update command is defined in my code but not shown in the code snippet.  I've been stuck on this for a couple of days now so any help is appreciated and thank you in advance.


Private dap As New System.Data.OleDb.OleDbDataAdapter()
    Private es As New System.Data.DataSet()
    Private cmd As New System.Data.OleDb.OleDbCommand()
    Private bindingSource1 As New BindingSource()
    Private db As dbConnect = dbConnect.GetInstance()
    Private tblName As String
    Private sortColumn As String
 
    Private Sub frm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        displayGrid()
    End Sub
 
    Sub displayGrid()
            With gridView
                .ReadOnly = False
                .DataSource = eView(txtPartNo.Text).Tables(tblECO)
                .EditMode = DataGridViewEditMode.EditOnEnter
                .SelectionMode = DataGridViewSelectionMode.FullRowSelect
            End With
        End If
    End Sub
 
    Public Function eView(ByVal itemStr As String) As System.Data.DataSet
        cmd.CommandText = "Select * " + _
          " From " + tblName + _
          " WHERE " + partNo + _
          " LIKE '%" + itemStr + "%'" + _
          " Order by " + partNo
 
        dap = New OleDb.OleDbDataAdapter(cmd.CommandText, db.Connection)
 
        es.Clear()
        dap.TableMappings.Add("Table", tblName)
        dap.Fill(es, tblName)
        Return es
    End Function
 
 
    Public Sub saveGridChanges()
        dap.Update(es)
    End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Jorge Paulino
Jorge Paulino
Flag of Portugal image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mutu

ASKER

Thank you - that worked!!!

What does the OleDbCommandBuilder do?  I don't think I have seen seen this in my searches or I didn't pay attention.

mutu


From MSDN:
"Automatically generates single-table commands that are used to reconcile changes made to a DataSet with the associated database. This class cannot be inherited."  ... " To generate INSERT, UPDATE, or DELETE statements, the OleDbCommandBuilder uses the SelectCommand property to retrieve a required set of metadata automatically."

For more information:
http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommandbuilder.aspx

Glad I could help!