Deleting from datagrid

[Rakstip]

I made a little app for a friend with a very large movie library(DVD,VHS,etc.). He uses it to catalog his movies and to keep track of which movie he lent out to which person. The app uses a datagrid with 5 columns(ID#,Title,Genre,Comments,Who has it.). The datagrid is sortable. When first viewed the datagrid is sorted by the ID#. When the datagrid is sorted by any other column and he needs to delete a movie it does not delete the correct row. For example, we will say the movie # 1 is Star Wars and # 2 is Spaceballs. When I click to sort by tilte Spaceballs is first and Star Wars is second. If I want to delete Star Wars it actually deletes Spaceballs. What could be causing this?
I have it setup that you double click the row you want to delete and it fills a set of textboxes. Then you click a Delete button to delete the movie from the database.
I have only been playing around with programming for a short time. I am using VS.net 2003 and VB.net.

 

[jmcilhinney]

What code are you using to actually delete the row?

 

[Rakstip]

Here is the code that I am using.

Private Sub btnDeleteExecute_Click(ByVal sender As System.Object, _ByVal e As System.EventArgs) Handles btnDeleteExecute.Click
Dim i As Integer
rtd = dgMovies.CurrentRowIndex
Try
DsMovies1.Tables(0).Rows(rtd).Delete()
i = daMovies.Update(DsMovies1)
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
MessageBox.Show("The movie " & tbDeleteTitle.Text & _
" has been deleted.")
DsMovies1.Clear()
daMovies.Fill(DsMovies1)
DeleteTextBoxClear()
trc = DsMovies1.Tables(0).Rows.Count
tbCount.Text = trc
End Sub

 

[jmcilhinney]

I think you'll find that sorting the DataGrid does not alter the actual order of the rows in the underlying DataTable, so the you should delete from the table by ID rather than row index. Try retrieving the ID from the selected row and then use DataTable.Rows.Find(ID).Delete() to delete the row from the table.

 

[Rakstip]

I would like to say thanx for the help. Where would I put the code that you suggested in your last post?

 

[jmcilhinney]

I have very little experience with DataGrids, so there may be a better way to do this, but it should work. I am assuming that the column index for the primary key is held in a variable called keyColumnIndex. Change this line:

[/size][size=2]rtd = dgMovies.CurrentRowIndex

to something like this:

Dim rowKeyToDelete As Long = Me.dgMovies.Item(Me.dgMovies.CurrentRowIndex, keyColumnIndex)

which gets the primary key value for the current row. Then replace this line:

[/size][size=2]DsMovies1.Tables(0).Rows(rtd).Delete()

with something like this:

Me.DsMovies1.Tables(0).Rows.Find(rowKeyToDelete).Delete()

which finds the row with the specified key in the table and deletes it.

 

[Rakstip]

I am not quite sure what you mean by

I am assuming that the column index for the primary key is held in a variable called keyColumnIndex.

Why would I need to do this? And is there any examples that you know of rather than you giving me all of the answers. I like to read up and understand what I am doing.
Thx

 

[jmcilhinney]

The DataRowCollection.Find() function requires the primary key of the row you want to find. To get the primary key for the row that is selected in the DataGrid, you have to know which column the primary key is contained in. Then you can use the DataGrid.Item property with the indeces of the currently selected row and the column that contains the primary key to get the primary key value.

As I said, I have very little experience with DataGrids myself. I have almost never used them myself and worked out the above solution by reading the help topic for the DataGrid class. I'm afraid I don't know of any examples other than what is in the help, but I'm sure others would.

 

[Rakstip]

What do you use in lieu of datagrids?

 

[jmcilhinney]

This may sound silly, but I find DataGrids to be fairly clinical. In most cases where I want to display multiple records, I prefer to use a ListView and provide a facility to edit each record individually by double-clicking it and displaying it in a dialogue box with each value displayed in its own field with a label to describe it. Personally, I think this makes for a friendlier UI. It does make it more click-intensive, so I certainly believe the DataGrid is better in certain situations, like where the user is likely to be more technically minded, a lot of fields are likely to need to be updated at a time or it is desirable to show a parent-child relationship between tables.

 

[Rakstip]

Ok. I think I found what you were reading on the Datagrid class help. I am still not sure how to assign the primary key to a variable. The thing is that when I write the code to do this and then insert your code VS keeps telling me that "value of type System.Data.Datarow cannot be converted to integer. I appreciate all of the help you have given me so far. Thx.

 

[jmcilhinney]

Let's say you have a DataTable with two columns called ID and Name. The column index of the primary key is thus 0, i.e. the primary key, ID, is in column 0. This column index is the second argument to the Item property of the DataGrid. This line:

Dim rowKeyToDelete As Long = Me.DataGrid1.Item(Me.DataGrid1.CurrentRowIndex, 0)

gets the key value of the currently selected row in a DataGrid called DataGrid1 that is displaying the abovementioned table, and assigns it to a variable called rowKeyToDelete. This key value can then be passed to DataRowCollection.Find() to get the actual DataRow from the DataTable that has that key value. If this still does not make sense to you, post your code and I'll tell you how to change it.

 

[Rakstip]

Thank you very much for all the help. The way you explained everything was alittle easier to understand than the help files. The code you suggested worked perfect.
Thx again.