[RESOLVED] delete in datagrid-update database

[sunshine123]

Using a datatable I have filled a datagrid.

Now i want to be able to delete a particular row from the grid (say the currently selected row in the grid) and these changes should be reflected back in the table in the database.

Could someone guide me on this...

[sunshine123]

Ok to be more specific, my table in the database does not have any particular unique data in a column to call a sql statement such as

Delete from tablename where fieldname = 'somevalue'

when i click on a delete button the currently selected row showuld be deleted. Now this this from view, I have no problem, how do I actually delete that row in the database.

I used adapter and datatable to fill the grid. Can i make use of it.

[jmcilhinney]

To delete a record from a database you have to be able to identify it. Usually that is done with the primary key. If there is no primary key then you can use any other combination of columns, including all columns. Having said that, if those columns don't uniquely identify a record then you cannot delete just one record. That's why primary keys exist. I suggest that you add one to your table if possible.

[rjv_rnjn]

I doubt DataAdapter can be of any help here, as it requires the datatables to have a unique key for it to auto-generate the command for Update etc. I think you'll need to figure a way out to do this if there is no unique key. What I would suggest (wrt what the problem appears to me) is in the "where" clause include all the fields to uniquely identify it (I know this sounds pretty stupid).

eDIT: The window was open for a bit longer time.

[jmcilhinney]

I doubt DataAdapter can be of any help here, as it requires the datatables to have a unique key for it to auto-generate the command for Update etc. I think you'll need to figure a way out to do this if there is no unique key. What I would suggest (wrt what the problem appears to me) is in the "where" clause include all the fields to uniquely identify it (I know this sounds pretty stupid).

A DataAdapter doesn't auto-generate anything. That's a CommandBuilder. The DataAdapter will delete whatever you tell it to delete. It doesn't care whether its DeleteCommand is generated by a CommandBuilder or created explicitly by you. All it needs is a valid SQL DELETE statement. If there is no primary key then you can use any combination of columns to identify a record, including all columns. Having said that, if there are two rows that have the same values in all those columns then it's impossible to delete one and not the other.

For example, consider a room full of people. That's your database. Now someone writes down the first and last name of everyone in the room. That's your DataTable. Now someone else looks at that list and gets to John Smith. They cross John Smith off the list. They just deleted a record from the DataTable. Now the first person goes back to the room and says "John Smith can leave". What happens? Ten people all named John Smith stand up and walk out. Do you see what I'm saying?

If you can't guarantee that your combination of columns uniquely identifies a single record then there's no way to be sure that you will only delete a single record. That's why ID columns are added to tables. If each person in that room was given a unique number and then someone said "Number 10 can leave" what would happen? Only the one person with the number 10 would leave. Software development is most common sense applied to technical situations.

[sunshine123]

hi,

ok so from what I understand I have to use the colum names. So since there's no primary key I'll use a combination. That will not be a problem.

so how do get the data values from the currently selected row in the datagrid.

[rjv_rnjn]

The following piece of code would help you to build upon the required functionality

DataGridViewSelectedRowCollection rows = dataGridView1.SelectedRows;
            foreach (DataGridViewRow row in rows)
            {
                string cellValue = row.Cells[0].Value.ToString();
                Console.WriteLine(cellValue);
            }

What it does is iterate through all the selected rows of the grid and print the value contained in the first column.

[sunshine123]

ok thanks.....isnt there anything to select a single row. instead of using the selected row collection

I want to select only a single row and then loop thru the columns to obtain the values.

thanks I'ce resolved the problem