CODEWITHSUNDEEP
vb.netdataset
user978698
user978698

Reputation: 11

Error when updating a table adapter in Visual Basic

I'm trying to progress my programming abilities by coding in Visual Basic forms. I've created a database and linked that to a VB form, I'm now coding a way to write into the database instead of reading.

I'm filling an array which is in-turn put into the dataset row by row however when attempting to 'update' the table adapter I get the following error:

Update requires a valid UpdateCommand when passed DataRow collection with modified rows.

I believe this has something to do with primary keys, the table that my adapter is updating does NOT have a primary key as it's involved in a relationship with another table, containing a primary key. How can I update my table adapter without getting this error? Thanks.

Upvotes: 1

Views: 2234

Answers (1)

lee-m
lee-m

Reputation: 2267

The error message you've mentioned has nothing to do with primary keys on the table you are attempting to update, instead it's complaining because you haven't given your adapter a valid command which it can use to update the underlying table.

You'd hit the same error if you attempted to insert or delete new rows without specifying an insert or delete command on your adapter.

To solve the problem, initialise your UpdateCommand property to something meaningful, for example:

'Open connection
Using Conn as new OleDbConnection("connection string")

  'This is the command that will update your table in the database
  Using UpdateCmd = Conn.CreateCommand()

    UpdateCmd.CommandText = "update yourtable set col1 = ?, col2 = ?"

    'Create a parameter to pass in the value of the "col1" column on "yourtable"
    Dim ColOneParam = UpdateCmd.Parameters.Add("@Col1", OleDbType.Integer)
    ColOneParam.SourceColumn = "Name of column in DataTable which corresponds to col1"

    'Create a parameter to pass in the value of the "col2" column on "yourtable"
    Dim ColTwoParam = UpdateCmd.Parameters.Add("@Col2", OleDbType.Integer)
    ColTwoParam.SourceColumn = "Name of column in DataTable which corresponds to col2"

    'Data adapter which will perform the specified update command for each 
    'newly inserted row
    Using Adapter As New OleDbDataAdapter

      'Set the update command on the adapter, if you omit this line you'll
      'encounter the original error you mentioned
      Adapter.UpdateCommand = UpdateCmd

      'This is the data table containing the rows you wish to update
      Dim NewRows As New DataTable("SomeTable")

      'For each modified row in NewRows, update it in the database
      Adapter.Update(NewRows)

    End Using

  End Using

End Using

The above code assumes a table in the database called yourtable with two columns col1 and col2 which are numeric.

The Using blocks simply ensure that the database objects are properly disposed off so you don't end up leaking resources.

Upvotes: 1

Related Questions