Delete multiple rows at once via TableAdapter[Answered] RSS

• • Reply
  SparX23

  Member

  52 Points

  161 Posts

  Delete multiple rows at once via TableAdapter

  Mar 18, 2015 10:26 AM|SparX23|LINK

  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 ?

  vb.net

• • Reply
  Fei Han - MS...

  All-Star

  40565 Points

  6233 Posts

  Microsoft

  Re: Delete multiple rows at once via TableAdapter

  Mar 19, 2015 02:07 AM|Fei Han - MSFT|LINK

  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

  vb.net

  .NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.
• • Reply
  SparX23

  Member

  52 Points

  161 Posts

  Re: Delete multiple rows at once via TableAdapter

  Mar 19, 2015 03:19 AM|SparX23|LINK

  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

  vb.net