Multirow delete function

Question

0.00/5 (No votes)

See more:

I am able to delete one row at a time using the cell value, but I tried to setup the same code for a mutlirow delete and I keep getting the error: unable to case object of type 'System.Int32' to type 'System.collections.IEnumerable'.

Here is the code:

Private Sub Datagridview_multiselct_delete() Handles DataGridView1.AllowUserToDeleteRowsChanged

       Try
           For Each dgvr In DataGridView1.SelectedCells.Item(0).Value

               Dim sqlcon As New SqlConnection("Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=c:\users\jj\documents\visual studio 2015\Projects\WindowsApplication3\WindowsApplication3\Database1.mdf;Integrated Security=True")
               Dim sqladapt = New SqlDataAdapter("Select * from " + "[" + TreeView1.SelectedNode.Text.ToString, sqlcon)

               sqlcon.Open()
               Dim cmd As SqlClient.SqlCommand
               Dim sql As String = "Delete from " + "[" + TreeView1.SelectedNode.Text.ToString + "]" + " where Id= " + dgvr
               cmd = New SqlClient.SqlCommand(sql, sqlcon)

               cmd.Parameters.AddWithValue("@id", dgvr)

               cmd.ExecuteNonQuery()
               sqlcon.Close()
           Next
           MessageBox.Show("Record deleted")
       Catch ex As Exception
           MsgBox(ex.Message)

       End Try

       Refresh_database()

   End Sub

What I have tried:

I have tried changing the selected cell to selected row and trying to string the results. When I do try to use the values in selected rows as a string I keep getting weird messages like invalid column name T or invalid column name S.
here are some of the other methods I have tried:

For Each dgvr In DataGridView1.SelectedRows.Item(0).DataGridView.MultiSelect.ToString
For Each dgvr In DataGridView1.SelectedRows.Item(0).DataBoundItem.ToString
For Each dgvr In DataGridView1.SelectedRows.Item(0).DataGridView.SelectedRows.ToString
For Each dgvr In DataGridView1.SelectedRows.Item(0).DataGridView.Rows.Item(0).Selected.ToString

just to name a few

Posted 6-Jan-17 22:02pm

Member 11856456

Updated 7-Jan-17 0:25am

Add a Solution

3 solutions

Solution 2

VB

For Each dgvr In DataGridView1.SelectedCells.Item(0).Value

You cannot execute a For Each clause on a single value. You must do it on some type of collection object. And in the above case I would expect it to be the rows attached to the SelectedCells.

Posted 6-Jan-17 22:13pm

Richard MacCutchan

Comments

Member 11856456 7-Jan-17 4:30am

I have tried to switch the code to this:
For Each dgvr As DataRowCollection In DataGridView1.SelectedCells.Item(0).Value

but in my SQL string code I have to change it to dgvr.tostring, but I am still getting the same error as before.

Richard MacCutchan 7-Jan-17 5:01am

Yes, because you are still doing it wrong. Please read my answer above once more and understand that you cannot use a For Each clause on a single value. If you really do not understand this basic concept then go back to your study guides and read the section on this clause.

Solution 4

Even though you have marked this as solved I would recommend a bit different approach.

Currently you execute a delete statement separately for each row. If the grid contains 100 rows to delete, you execute 100 separate SQL statement. This causes a lot of round-trips to the database and back and is both time and resource consuming.

Instead, why not gather all the keys inside the for each loop and then delete all the rows using a single statement using IN comparison.

Another thing is that you open and close the connection inside the loop. This causes two things:
- resource usage
- lack of transaction between row deletions

You should open the connection once, delete the rows, check that everything went smoothly and then close the connection

Third thing is that you're not using using blocks. They should be used in order to dispose resources properly.

What comes to the code suggestion, a pseudo-code would look like

for each grid row
   store the id value in a list of strings

using connection
   using command
      sql = "DELETE FROM TableName WHERE ID IN (" & String.Join(", ", stringlist.ToArray)  & ")
      try
         open connection
         execute statement
         close connection
      catch
         handle errors

An example delete statement would look like

SQL

DELETE FROM TableName WHERE ID IN (1, 5, 99, 12, 55)

For more information about utilizing the using blocks, transaction etc, have a look at Properly executing database operations[^]

Posted 7-Jan-17 0:25am

Wendelius

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Michael_Davies · Accepted Answer · 2017-01-06T22:20:00

Solution 3

The error message tells you the problem, you are trying to enumerate something that cannot be enumerated.

Enumerate the rows in the gridview and access each id in the row.

For Each dgvr As DataGridViewRow In DataGridView1.SelectedRows
...WHERE id="+dgvr.Cells(0).Value.ToString

You then try to add a parameter to an SQL string that does not contain a parameter place holder, you have actually concatenated the parameter into the string if you do it that way (not recommended) you'll need to .ToString the integer.

Posted 6-Jan-17 22:20pm

Michael_Davies

Updated 7-Jan-17 0:29am

v5

Comments

Member 11856456 7-Jan-17 4:40am

I just tried it this way and I got a new error: Public member 'Item' on type 'DataGridViewRow' not found

Michael_Davies 7-Jan-17 5:06am

Updated the solution.

Member 11856456 7-Jan-17 5:45am

your solution was close, after tinkering with it this was what I came up with

For Each dgvr As DataGridViewRow In DataGridView1.SelectedRows
the SQL statement worked fine once this portion was set up, thank you and Mr. Richard for your help.

Michael_Davies 7-Jan-17 6:32am

Your also opening and closing the database within the for loop, waste of time, open before the for and close after the next.

Also see Mika Wendelius solution for a single shot command to delete all selected in one go, more efficient and saves time.