-
Oct 29th, 2012, 12:38 AM
#1
Thread Starter
Member
keydown delete in dataGridView
Hello! I have a dataGridView that displays a data table pulled from mySQL.
I am trying to delete a user-selected row when the user hits the DELETE key.
What is happening is, the delete works (both in the DGV and the actual data table), but it deletes TWO rows (selected & the one above) from the dataGridView (but not from the actual table). When I refresh the DGV, the row above the selected row that wasn't supposed to get deleted re-appears. I want JUST the selected row to be deleted from the DGV.
Could someone tell me how I can fix my code? Thank you!
Code:
Private Sub dgv_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles DataGridView1.KeyDown
If e.KeyCode = Keys.Delete Then
setConnection()
deleteData()
End If
End Sub
Private Sub deleteData()
Try
Dim i As Integer
Dim tableName As String = TableList.Text
Dim mResult
mResult = MsgBox("Do you really want to delete the selected records?", vbYesNo + vbQuestion, "Removal confirmation")
If mResult = vbNo Then
Exit Sub
End If
command = New MySqlCommand
command.Connection = conn
If tableName = "persons" Then
For i = DataGridView1.SelectedRows.Count - 1 To 0 Step -1
command.CommandText = "delete from `" & tableName & "` where `Book ID`=" & DataGridView1.SelectedRows(i).Cells("Book ID").Value & ";"
command.ExecuteNonQuery()
DataGridView1.Rows.Remove(DataGridView1.SelectedRows(i))
Next
ElseIf tableName = "randomdata" Then
For i = DataGridView1.SelectedRows.Count - 1 To 0 Step -1
command.CommandText = "delete from `" & tableName & "` where dataid=" & DataGridView1.SelectedRows(i).Cells("dataid").Value & ";"
command.ExecuteNonQuery()
DataGridView1.Rows.Remove(DataGridView1.SelectedRows(i))
Next
End If
fetchData(tableName)
Catch ex As Exception
MsgBox("Could not delete record. " & ex.Message)
Finally
conn.Close()
End Try
End Sub
-
Oct 29th, 2012, 01:19 AM
#2
Re: keydown delete in dataGridView
You have made something that is very simple rather complex. You should be using a MySqlDataAdapter to Fill a DataTable. Bind that DataTable to a BindingSource and bind that to the grid. When you want to delete the selected record yo simply call RemoveCurrent on the BindingSource, which deletes the record in the DataTable. You then use the same MySqlDataAdapter Update the database from the DataTable.
-
Oct 29th, 2012, 02:58 AM
#3
Thread Starter
Member
Re: keydown delete in dataGridView
Do I still need a different SQL statement for each table in the database? or is there a way to do this without multiple SQL statements?
Here is what I changed my code to:
Code:
Dim tableName As String = TableList.Text
dt = BindingSource.DataSource
BindingSource.RemoveCurrent()
command = New MySqlCommand
adapter = New MySqlDataAdapter
command.Connection = conn
command.CommandText = ' ******** don't know what to put here *********
adapter.SelectCommand = command
reader = command.ExecuteReader
dt.Load(reader)
reader.Close()
Last edited by echoUser; Oct 29th, 2012 at 03:06 AM.
-
Oct 29th, 2012, 03:32 AM
#4
Re: keydown delete in dataGridView
Still not quite right I'm afraid. Follow the CodeBank link in my signature and check out my thread on Retrieving & Saving Data for an example. One data adapater = one DataTable.
-
Oct 29th, 2012, 08:51 PM
#5
Thread Starter
Member
Re: keydown delete in dataGridView
Is this the example you are talking about?
Copying & pasting your code:
Code:
Private connection As New SqlConnection("connection string here")
Private adapter As New SqlDataAdapter("SELECT ID, Name, Quantity, Unit FROM StockItem", _
connection)
Private table As New DataTable
Private Sub InitialiseDataAdapter()
Dim delete As New SqlCommand("DELETE FROM StockItem WHERE ID = @ID", Me.connection)
Dim insert As New SqlCommand("INSERT INTO StockItem (Name, Quantity, Unit) VALUES (@Name, @Quantity, @Unit)", Me.connection)
Dim update As New SqlCommand("UPDATE StockItem SET Name = @Name, Quantity = @Quantity, Unit = @Unit WHERE ID = @ID", Me.connection)
delete.Parameters.Add("@ID", SqlDbType.Int, 4, "ID")
insert.Parameters.Add("@Name", SqlDbType.VarChar, 100, "Name")
insert.Parameters.Add("@Quantity", SqlDbType.Float, 8, "Quantity")
insert.Parameters.Add("@Unit", SqlDbType.VarChar, 10, "Unit")
update.Parameters.Add("@Name", SqlDbType.VarChar, 100, "Name")
update.Parameters.Add("@Quantity", SqlDbType.Float, 8, "Quantity")
update.Parameters.Add("@Unit", SqlDbType.VarChar, 10, "Unit")
update.Parameters.Add("@ID", SqlDbType.Int, 4, "ID")
Me.adapter.DeleteCommand = delete
Me.adapter.InsertCommand = insert
Me.adapter.UpdateCommand = update
Me.adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
End Sub
Private Sub GetData()
'Retrieve the data.
Me.adapter.Fill(Me.table)
'The table can be used here to display and edit the data.
'That will most likely involve data-binding but that is not a data access issue.
End Sub
Private Sub SaveData()
'Save the changes.
Me.adapter.Update(Me.table)
End Sub
-
Oct 29th, 2012, 08:55 PM
#6
Re: keydown delete in dataGridView
That's the one. If you have multiple related tables then you'd use a DataSet to contain the DataTables with the appropriate DataRelation(s) between them.
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|