DataGridView bound to DataTable (display & update problem with checkbox column type)

[exterminator]

( Visual C# 2008, .Net framework 3.5 sp1)

The title line should be explanatory but still the details:

1. I have a DataGridView control on a Window Form.
2. AutoGenerateColumns property set to False.
3. Columns added in design time and each columns' DataPropertyName set to specific column name in DataTable.
4. DataTable populated as:

DataTable dataTable = new DataTable();
OleDbConnection conn = DBConnection.getConnection();
string ct = @"SELECT column1, column2 FROM table";
OleDbDataAdapter da = new OleDbDataAdapter(ct, conn);
OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
da.Fill(dataTable);
(Feel free to suggest any corrections in code above, if something looks dodgy)
5. column 2 is supposed to be a boolean/a flag. So, I choose 2nd column in datagridview to be of check-box type type (at design time).

There are 2 problems:
a) What do I need to do so that a value true in DB for column 2 shows check-box as ticked (it isn't as of now while displaying data)?
b) What do I need to do to have the data updated into DB?

a) might help me find answer to b) but currently, I am doing the following for b) which doesn't seem to be working. I came across variety of expcetions that I searched for and one thing led to another but still I seem far from the solution. I will just throw it infront of you to help me how to achieve what I want to. This is just displaying a two column table on the Form in DataGridView and saving any updates on 2nd column value into the DB.

string ct = @"SELECT column1, column2 FROM table";
OleDbConnection conn = DBConnection.getConnection();
OleDbDataAdapter da = new OleDbDataAdapter(ct, conn);
OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
/*
da.UpdateCommand = new OleDbCommand();
da.UpdateCommand.CommandText =@"update table set column2 = ? where column1 = ?";
da.UpdateCommand.Connection = conn;
OleDbParameter p1 = da.UpdateCommand.Parameters.Add("@p1", OleDbType.Integer);
p1.SourceColumn = "column2";
p1.SourceVersion = DataRowVersion.Original;
OleDbParameter p2 = da.UpdateCommand.Parameters.Add("@p2", OleDbType.VarChar);
p2.SourceColumn = "column1";
p2.SourceVersion = DataRowVersion.Original;
*/
da.Update(dataTable);
I commented out some of the code above since it didn't seem to work (propagate the updates to the db). I had added it since otherwise I was getting the exception: "Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information." (There are no keys in table at the moment, and I don't need one too).

Any suggestions how to achieve the above?

[dglienna]

"Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information."

If you don't have any key, that might be a problem, but you need to use a WHERE so it know which record(s) to update.

You could load the grid manually, and format it however you want.

[JonnyPoet]

For working with datatables, editing and updating them you will need to have any sort of unique key. to identify the data that needs an update. This doesn't mean that you need to add any unique ID, it can be a combined key in some cases combining two or more columns together will result in a unique key
For example think you are storing customers and you have to keep timetrack of them even when there are some changes in his personal data like an adress change. Then your custonmer Recrd would have a CustomerID but this is not unique because when the customer chnges his data we will not change his ID So we additional have a versionNo and the customerID together with the version No is unique. I hope to give a simple examle )If the name itself is unoque, no problem the name can be the key.
Simple think to what will happen when your set of data is not unique in any way. It would update all of the records with the same signature.
You can do this but then you need to do a correct sql WHERE Clause which which sets all the fields like

"SELECT * FROM Table WHERE Column1=firstBut if you are working with Datatablespecification AND Column2= secondspecification";
But if you are working with datatable and your recordset is bound to the datagrid it should be able to update the correct DataRow automatically, because DataTable keeps track on each DataRow
If you dont have bound your datatable to the grid, you yourself need to keep track which datarow should be updated.

[exterminator]

If you don't have any key, that might be a problem, but you need to use a WHERE so it know which record(s) to update.Ok, thanks, I'll try that.You could load the grid manually, and format it however you want.That I was thinking to work-around. Did not want to go the long way. :) I thought this shouldn't have been that complicated. Anyways, I'll try your first suggestion and if it troubles me much, I will go the old way.

[exterminator]

For working with datatables, editing and updating them you will need to have any sort of unique key. to identify the data that needs an update.Could you please point me to a relevant MSDN documentation that reveals this?

But if you are working with datatable and your recordset is bound to the datagrid it should be able to update the correct DataRow automatically, because DataTable keeps track on each DataRow.That's what I thought. It should have understood that without creating a key. Afterall, it was a simplistic table with just 2 columns that I have in my select query to fill the data table. And it is bound to the grid with the following:

dataGridView.AutoGenerateColumns = false;
dataGridView.DataSource = dataTable; //this got populated the way I showed in first post

[exterminator]

Update: In fact, the code in my first post worked as is by just making a unique index on the DB table. And I did not need to add any support to make it translate the boolean to the checkboxes state too, which is fantastic!

Thanks to both of you for your suggestions. It helped.

[JonnyPoet]

Could you please point me to a relevant MSDN documentation that reveals this?
This is not documented anywhere. This is simple logically.
If you have records and no key how should the database decide which record is that one you wanted to get changed.
How would you try to find out which record it was, Maybe you count the rows where the change has happened. This would mean that you add foryourself a sort of unique key - the rowNumber
But this can get into troubles for example simple by sorting the rows
So you cannnot use this, You can only store all the rows in its original values and then compare them with the changed values.
Here you would need to compare each field original and changed value. But you cannot be sure if the user maybe has two oor more rows with the same data. Nothing is defined to be unique. So which column should be changed then. If you do it manual by code its yours what you are deciding to store. If the DataTable has to decide which row is to be used and it is not granted that there is no possibility for having two or more Records with the same data in all its columns, he has to throw an exception, because the condition is undefined and by that unsecured results would happen.If you do it manual using a 'where' clause in inserting data using ADO and the data in the 'where clause' fits to different rows, then simple all of them are updated with your data.

As you have seen it works as soon as it is able to exactly identify the row. If you add a unique identifier the dataRow naturally this ID, even if you do not show or use this key in your program anywhere.

But if you are working with datatable and your recordset is bound to the datagrid it should be able to update the correct DataRow automatically, because DataTable keeps track on each DataRow
Sorry there was one information missing in this sentence. The addition should have been something like " But it needs to be able to differ between rows which maybe would or would not contain the same information " Which again shows that a unique key is necessary.
BTW: The errormessage itself says exactly that. So maybe MSDN gives more information about that theme if you are tracking back that errormessage. :wave:
Anyway, Great you got it solved :)