invalid operation exception was unhandled Update requires a valid UpdateCommand

Question

From these codes, I want to edit, add and save data from VB to MS Access permanently. I created dozens of Visual Basic projects but no progress at all.

Public Class Form1

    Private Sub ProductDescBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ProductDescBindingNavigatorSaveItem.Click

        Me.Validate()
        Me.ProductDescBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.INVSYSDataSet)
    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        'TODO: This line of code loads data into the 'INVSYSDataSet.ProductDesc' table. You can move, or remove it, as needed.
        Me.ProductDescTableAdapter.Fill(Me.INVSYSDataSet.ProductDesc)
    End Sub
End Class

The problem is that "invalid operation exception was unhandled" appears, Update requires a valid UpdateCommand from code Me.TableAdapterManager.UpdateAll(Me.INVSYSDataSet)

If you need the DataSource, I can provide code from another VB project.

*updated second code, help for sql please *updated srry bout that

Public Class Add_Products

Private myConString As String
Private con As OleDb.OleDbConnection = New OleDb.OleDbConnection
Private Dadapter As OleDb.OleDbDataAdapter
Private DSet As DataSet
Private DSet2 As DataSet
Private ConCMD As OleDb.OleDbCommand
Dim strSql As String
Dim inc As Integer
Dim MaxRows As Integer

Private Sub Add_Products_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    myConString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\larca\Documents\Visual Studio 2010\Projects\march16\march16\obj\x86\Debug\INVSYS.mdb"
    con.ConnectionString = myConString
    con.Open()
    Dadapter = New OleDb.OleDbDataAdapter("select * from ProductDesc", con)
    DSet = New DataSet
    Dadapter.Fill(DSet, "ProductDesc")
    DataGridView1.DataSource = DSet.Tables("ProductDesc")

    con.Close()
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Try
        Using con = New OleDb.OleDbConnection(myConString)
            con.Open()
            Dim cmd As OleDb.OleDbCommand
            cmd = New OleDb.OleDbCommand("UPDATE ProductDesc", con)
            Dadapter.UpdateCommand = cmd
            Dadapter.Update(DSet, "ProductDesc")
        End Using
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End Sub

End Class

Did you define an Update command for the DataAdapter? [OleDbDataAdapter.UpdateCommand Property](http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbdataadapter.updatecommand.aspx) — Tim, Mar 16 '13 at 07:03
You need to provide the DataAdapter with the SQL for the Update command. That's what the error message is telling you. Take a look at the link I provided in my previous comment. — Tim, Mar 16 '13 at 07:08
Open MS Access, paste in the sql from your update. You will see that it does not work. You must use valid sql. — Fionnuala, Mar 16 '13 at 10:48

score 1 · Accepted Answer · answered Mar 16 '13 at 07:15

1

The error message is telling you that you have not defined an Update command for the DataAdapter. From DbDataAdapter.Update Method DataSet, String: If INSERT, UPDATE, or DELETE statements have not been specified, the Update method generates an exception.

To resolve this, assign the UpdateCommand an OleDbCommand object with your update logic, like this:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

   Using con = New OleDb.OleDbConnection(myConString)
       con.Open()
       Dim cmd As OleDbCommand
       cmd = New OleDbCommand("<your update SQL goes here>", con)
       DAdapter.UpdateCommand = cmd 
       Dadapter.Update(DSet, "ProductDesc")
   End Using

End Sub

Simply put your SQL in the OleDbCommand and assign it to the UpdateCommand property.

Look at this link for a detailed example (and be sure to use parameterized queries like in the example to avoid SQL Injection attacks): OleDbDataAdapter.UpdateCommand Property

answered Mar 16 '13 at 07:15

Tim

28,212
8
63
76

i can't do it, can you help me plss – larca Mar 16 '13 at 07:44
Can't do what? Get the code to work? Write the SQL update? Post what you've tried (edit your question so it will format properly). – Tim Mar 16 '13 at 08:34
here i edited the 2nd codes, can u help me please, stuck in my system analysis project 1 days to go – larca Mar 16 '13 at 08:52
can't parameterized because im was confuse in parameterizing gridview and i don't know parameterizing yet – larca Mar 16 '13 at 08:56
Your SQL command isn't an update - it's a simple SELECT. An update is something like UPDATE tablename SET columnname = value WHERE someothercolumname = someothervalue. – Tim Mar 16 '13 at 09:54
srry bout that, i changed it again,do you mean that,please reply if im wrong again – larca Mar 16 '13 at 10:05
It's still not an update command - it won't even run. Have you worked with SQL before? – Tim Mar 16 '13 at 18:09

invalid operation exception was unhandled Update requires a valid UpdateCommand

1 Answers1