Delete selected rows in datagridview from database

You're getting slightly more involved than you need to. The set of steps you'd ideally take would be:

0) rename your table so it doesn't have an & character in it - it's just asking for trouble

1) Add your table to your dataset with something like this process: right click the design surface, new tableadapter, configure the connection string, set the query as SELECT * FROM Users WHERE Username LIKE @username, ensure other things are set, like whether you want generate dbdirect methods, and whether you want the dataset to refresh new values

2) In your data sources window (might not be showing by default, find it in on of visual studio's menus) drag and drop the Users node from the data soruces window, out onto the form. This will create a datagridview bound to the typed datatable, and also create a dataset, tableadapter, tableadaptermanager (not strictly needed; can delete), a bindingnavigator tool strip (again not strictly needed but has a handy pre-wired Save button on it) and a bindingsource. It will also pre-fill some code into the form_load event handler to fill the datatable

3) That's it - your form has a grid, bound to a datatable. That grid is capable of deleting rows - click the row header and press the delete button on the keyboard, row disappears. Click the save icon in the toolstrip, and the change is persisted to the database. The only thing you have to do is get data into the table in the first place. I gave a SQL that allows you to choose some usernames to load, but you can easily make it load the whole lot by changing the line of code in Form_Load to:

yourTableAdapterName.Fill(yourDatasetname.YourDatatableName, "%")

Passing a percent as the name is like a * wildcard in DOS. LIKE % will select all records. You can also, of course, leave the default provision (it will reference a textbox on the toolstrip) and instead run the program, put a % in the textbox on the toolstrip and click Fill. Or you can put JOHNSMITH, or JOHN% in th textbox and fill, to load that user/those users respectively

Hopefully you already did 1 and 2..

A bit of a background story on how all this stuff works:

DataTables are client side representations of tables in the database. It isn't intended that they contain all the data in the database, only a portion of this that you want to work with. When you load a row from the DB you can edit it, delete it (mark as deleted) and when you call tableAdapter.Update(yourtable) th changes you made are persisted to the DB. Note that even though it's called Update, the method will save new rows (by doing an INSERT), deleted rows (SQL DELETE) and make updates (SQL UPDATE). If your datatable has 4 rows laodd from the DB, and you then add 2 new rows, make changes to 3 of the loaded rows and delete the 4th row, then calling tableadapter.Update will save all these changes to the DB. You aren't required to pick through your datatable, calling tableadapter.insert/update/delete accordingly, to manually persist these changes. The flow of using a tableadapter is thus:

tableAdapter.Fill(datatable, maybe,parameters,to,control,the,fill,here)  

'work with the datatable here, change, insert, delete. Use loops/code, use the UI, etc

tableAdapter.Update(datatable) 'save changes

 I suggest putting an id column in your datagridview and just hide it so that you can easily delete the record you want to remove easily without conflict, because if you will use a username, what if theres a duplicate username exist.



   Private Sub BTNDELETE_Click(sender As Object, e As EventArgs) Handles BTNDELETE.Click
                Public result As Integer = 0
                Public cmd As New MySqlCommand
                strcon.Open() 'your connection string here
                With cmd
                    .Connection = strcon 'your connection string
                    .CommandText = "DELETE FROM `users` WHERE `id` = '" & DTGLIST.CurrentRow.Cells(0).Value.ToString() & "';"
                    result = cmd.ExecuteNonQuery
                    If result > 0 Then
                       MsgBox("Successfully Removed.", MsgBoxStyle.Information, "Info")
                       DTGLIST.Rows.Remove(DTGLIST.SelectedRows(0))
                    End If
                End With
                strcon.Close() 'close the connection string
        End Sub