Access database update

[Joejoe79sa]

Hi guys, i have been strugling with this updating of records in a access database for a while now. I can update a microsoft SQL Database using this code... Me.Validate()
Me.StudentBindingSource.EndEdit()
Me.StudentTableAdapter.Update(Me.StudantDataSet.Student)

But this same code is generated when i drag and drop items from the data sources windows in my visual basic.net using a access database but i get this error:

System.InvalidOperationException was unhandled
Message="Update requires a valid UpdateCommand when passed DataRow collection with modified rows."
Source="System.Data"

This code s generated by visual basic but doesnt work. Any idee what i am to do next?

Thanks

 

[cjard]

The error is reasonably self explanatory when you know that:


SQL statements are needed to read from and write to database tables

If a table has a primary key then it is very easy to automatically deduce which columns are needed to exactly and uniquely identify precisely one row in the table - thats what a primary key is; a column or columns whose value(s) combined are never repeated anywhere in the table (and this is enforced)

It is thus very easy, for a table having a primary key, for the designer to make a guess at what the UPDATE command will be in order to take one row it holds locally, and update exactly one row remotely

SImilarly for delete; you want to identify just one row to delete


If your table doesnt have a primary key, then the designer wizard WILL NOT GENERATE AN UPDATE OR DELETE statement for that table because it cannot know which columns precisely identify one row.

These kinds of tables (without PKs) can be inserted to and selected from, no problems, but updates and deletes are supposed to be targeted. Because your table doesnt give the deisgner any clue how to target one row, it refuses to create the statement

There is nothing stopping you adding this statement in manually. Click on the tableadapter, add an Update command to the properties, and write the SQL. If you dont know how to write SQLs, visit www.w3schools.com