DataSet auto_increment on update current_timestamp

[tsiqueira]

C# / ADO.NET version 4.0
Database: MySQL

What I'm working with:

I have a database with a client table. The columns on the client table are:

- client_id [int] (which is an auto_increment and PK)
- case number [varchar]
- first name [varchar]
- last name [varchar]-
- last_modified [timestamp] which is CURRENT_TIMESTAMP by default and also has the "ON UPDATE CURRENT_TIMESTAMP" attribute. None of these columns can be null.

On the application side i have a DataSet that is bound to this client table. I then have a DataGridView that is bound to the DataSet. When i start the application, the client list is correctly populated in the DataGridView.

What I'm trying to do:

I want my user to be able to use the DataGridView toolbar to add, edit, delete, and save records on the clients table.

What is my problem?

The problem is with the above approach, i have only been able to get it working if i display all the columns to the user, including client_id and last_formatted which the user does not really need to see. Those fields are automatically populated when a record is added to the DB, so i don't want the user to have to touch that.

However, i don't think the DataSet is able to deal with this problem. If you add a record on the ACTUAL DB, it knows to automatically add a client_id and either update or add the timestamp, but if it's still on the DataSet it cannot do this and triggers an exception.

I can create a form with text boxes that allow the user to input the new client, then using parameters, input that new record into the DB. But if it can be done directly on the DataGridView somehow, it would be easier for the user.

Is this possible?

Thanks in advance,

Tsiqueira

[Cimperiali]

You can do that. You can enable editing, deleting and adding on gridview.

You let users do changes via Gridview. Give it a button to Save modifications. When the user save, you send what is changed of the datasource of your grid to database via appropriate command.
YOu could also save any single row as immediately as the user leave it, and refresh the data each time.
THe autonumber autoincrement (or also said identity) field will take care of itself.
In any case, you're always on a potentially old photo of data, that's why you should look at how optimistic concurrency do work.

some example of grid code for editing using data adapters:

http://social.msdn.microsoft.com/Forums/en/csharplanguage/thread/8db841fc-ffa7-4519-b6f5-d054c7190948

http://www.codeguru.com/csharp/.net/net_data/datagrid/article.php/c13041

http://stackoverflow.com/questions/1518946/how-to-insert-delete-select-update-values-in-datagridview-in-c-sharp-using-mysql