Delete multiple rows at once via TableAdapter

Question

User-1108865520 posted

Hello!

 I have a simple task, but I cannot get this task to work I have a TableAdapter. In that table adapter I've added a query like this:

DELETE FROM table WHERE id IN (@MyListOfID)

In code, I create a string with all my ids that I need to delete. Here is the code:

Dim StringOfIDs As New StringBuilder

For b = 0 To CheckEmployeesWithJobID.Rows.Count - 1
	StringOfIDs.Append(CheckEmployeesWithJobID(b).id)
	StringOfIDs.Append(",")
Next

' Delete not needed trainings
                Try
                    EmployeesTrainingsAdapter.DeleteTrainingWithIDs(StringOfIDs)
                Catch ex As Exception
                    LBL_ERR.ForeColor = Drawing.Color.Red
                    LBL_ERR.Text = "Failed to delete not needed trainings from employees with this job"
                    GC.Collect()
                    Exit Sub
                End Try

I receive an error because I provide a string , instead of an integer

Is there a way to do this via table adapter, or should I use the tableadapter individually for every row ?

Answer 1

User61956409 posted

Hi SparX23,

Thanks for your post.

You could refer to the following sample to delete records.

Protected Sub btndel_Click(sender As Object, e As EventArgs) Handles btndel.Click
        Dim StringOfIDs As New StringBuilder

        For b = 0 To 3
            StringOfIDs.Append(b)
            StringOfIDs.Append(",")
        Next

        Dim ids As String = StringOfIDs.ToString().Substring(0, StringOfIDs.ToString().Length - 1)

        Dim sqlConn As New SqlConnection("connectiongstring")

        Dim queryString As String = "DELETE FROM YourTable WHERE Id IN (" + ids + ")"

        Dim cmd As New SqlCommand(queryString, sqlConn)

        sqlConn.Open()
        cmd.ExecuteNonQuery()
        sqlConn.Close()


End Sub 

Best Regards,

Fei Han

Answer 2

User-1108865520 posted

Hello Fei Han,

Your example works great (in your example you also remove the last coma from my StringOfIDs, I forgot about that), but I'm looking to do this via a table adapter.

It's there a way to do this, via tableadapter ?

If not, your solution will do the job :)

----------- Later edit

I used this code, and now it accepts the ids string as a parameter ....

                Dim StringOfIDs As New StringBuilder

                If CheckEmployeesWithJobID.Rows.Count > 0 Then
                    For b = 0 To CheckEmployeesWithJobID.Rows.Count - 1
                        StringOfIDs.Append(CheckEmployeesWithJobID(b).id)
                        StringOfIDs.Append(",")
                    Next
                End If

                ' Remove the last comma
                Dim ids As String = StringOfIDs.ToString().Substring(0, StringOfIDs.ToString().Length - 1)

                ' Delete not needed trainings
                Try
                    EmployeesTrainingsAdapter.DeleteTrainingWithIDs(ids)
                Catch ex As Exception
                    LBL_ERR.ForeColor = Drawing.Color.Red
                    LBL_ERR.Text = "Failed to delete not needed trainings from employees with this job"
                    GC.Collect()
                    Exit Sub
                End Try