Question (DataGridView) - Delete successful inserts of rows and move rows up.

[Bro.BluPhi!]

How would i delete only the successful inserted rows for insert and then move the non successful rows up and allow the user to correct the data and get it ready for an insert. If i dont delete the successful rows then i will have multiple inserts of the same dataRow and we dont want that! AND if i dont move the data up then it will have blank rows uptop and will end the try and not insert the corrected data. You can better see what logic i am trying to perform at the bottom of my code, right after I insert into the database. Here is my code.

  Private Sub btnLaserGenerateTicket_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLaserGenerateTicket.Click

    'Function declarations
    'Object declarations
    Dim localOutputServer As SQLServer = Nothing

    'Variable declarations (Form) 
    Dim CustomerName As String = Nothing
    Dim passCustomerID As String
    Dim SpecialInstructions As String = Nothing

    'Variable declarations (DataGridView) LaserTabControl
    Dim JobNumber As String = Nothing
    Dim LotNumber As String = Nothing
    Dim FileName As String = Nothing
    Dim Quantity As String = Nothing
    Dim SequenceMinimum As String = Nothing
    Dim SequenceMaximum As String = Nothing
    Dim DueDate As Date = Nothing
    Dim ProductionType As String = Nothing

    Try
      For Each item As DataGridViewRow In Me.dgvLaser.Rows

        'Gives error if the Cell value is blank/empty and if wrong datatype exists
        If (item.Cells(0).Value = Nothing) Then
          MessageBox.Show("Blank field(s) exists for Lot #.")
          Exit For
        ElseIf (item.Cells(1).Value = Nothing) Then
          MessageBox.Show("Blank field(s) exists for File.")
          Exit For
        ElseIf (item.Cells(2).Value = Nothing) Then
          MessageBox.Show("Blank field(s) exist for Record Count!")
          Exit For
        ElseIf Not IsNumeric(item.Cells(2).Value) Then
          MessageBox.Show("Record Count is a numbers only field!")
          Exit For
        ElseIf (item.Cells(3).Value = Nothing) Then
          MessageBox.Show("Blank field(s) exists for Start #.")
          Exit For
        ElseIf (item.Cells(4).Value = Nothing) Then
          MessageBox.Show("Blank field(s) exists for End #.")
          Exit For
        ElseIf (item.Cells(5).Value = Nothing) Then
          MessageBox.Show("Blank field(s) exists for Form Code #.")
          Exit For
        ElseIf (item.Cells(6).Value = Nothing) Then
          MessageBox.Show("Blank field(s) exists for Date.")
          Exit For
        ElseIf Not DateTime.TryParseExact(item.Cells(6).Value, "MM/dd/yyyy", Nothing, DateTimeStyles.None, Nothing) Then
          MessageBox.Show("Check the date column. Date must be in the following format: MM/DD/YYYY")
          Exit For
        ElseIf (item.Cells(0).Value = Nothing And item.Cells(1).Value = Nothing And item.Cells(2).Value = Nothing And _
         item.Cells(3).Value = Nothing And item.Cells(4).Value = Nothing And item.Cells(5).Value = Nothing And _
         item.Cells(6).Value = Nothing) Then
          'Clear Rows
          Me.dgvLaser.Rows.Clear()

          'Creates 60 rows after the insert is successful and cleared.
          Me.dgvLaser.RowCount = 60

          'Clears TextBoxes on TabControl
          ClearTabCtrlTextBoxes()
          Exit For
        Else
          'Passes CustomerID
          passCustomerID = cboSelectCustomer.SelectedValue.ToString

          'Gets Username to insert
          CustomerName = cboSelectCustomer.Text
          JobNumber = txtLaserJobNumber.Text

          'Concatenates txtLaserWatch, txtLaserDesignID, txtLaserAppSpecialist, txtLaserAccountManager into one variable..."SpecialInstructions"
          SpecialInstructions = txtLaserWatch.Text + ", " + txtLaserDesignID.Text + ", " + txtLaserAppSpecialist.Text + ", " _
          + txtLaserAccountManager.Text

          ' Gets current date
          Dim dateMade As DateTime
          dateMade = DateTime.Now

          'Get a SQLServer Object...this relies on getting the database
          'connection from a ".config" file.
          localOutputServer = New SQLServer(ConfigurationManager.AppSettings("dbConnectionString"))

          'Creates new StringBuilder for each Insert
          Dim strSQL As New StringBuilder

          strSQL.Append("INSERT INTO tblJobTicketDataDirectMail(LotNumber, FileName, Quantity, SequenceMinimum, SequenceMaximum, ProductionType, OrderDate, CustomerName, CustomerID, JobNumber, SpecialInstructions, dateMade, PSIJobNumber, JobTicketDataID)")
          strSQL.Append("VALUES('" & localOutputServer.FixSQL(item.Cells(0).Value.ToString) & "',")
          strSQL.Append("'" & localOutputServer.FixSQL(item.Cells(1).Value.ToString) & "',")
          strSQL.Append("'" & localOutputServer.FixSQL(item.Cells(2).Value.ToString) & "',")
          strSQL.Append("'" & localOutputServer.FixSQL(item.Cells(3).Value.ToString) & "',")
          strSQL.Append("'" & localOutputServer.FixSQL(item.Cells(4).Value.ToString) & "',")
          strSQL.Append("'" & localOutputServer.FixSQL(item.Cells(5).Value.ToString) & "',")
          strSQL.Append("'" & localOutputServer.FixSQL(item.Cells(6).Value.ToString) & "',")
          strSQL.Append("'" & localOutputServer.FixSQL(CustomerName.ToString) & "',")
          strSQL.Append("'" & (passCustomerID.ToString) & "',")
          strSQL.Append("'" & localOutputServer.FixSQL(JobNumber.ToString) & "',")
          strSQL.Append("'" & localOutputServer.FixSQL(SpecialInstructions.ToString) & "',")
          strSQL.Append("'" & localOutputServer.FixSQL(dateMade.ToString) & "',")
          strSQL.Append("0,")

          'Creates new JobTicketDataID (GUID) for before each Insert
          Dim JobTicketDataID As Guid = Guid.NewGuid
          strSQL.Append("'" & (JobTicketDataID.ToString) & "')")

          'Run the strSQL statement
          localOutputServer.runSQL(strSQL.ToString)


'AFTER THE SUCCESSFUL ROW IS INSERTED DELET THE ROW 
'AND MOVE THE OTHER ROWS UP. GET READY TO INSERT  
'THE REST OF THE DATA USE SOME LOGIC LIKE THIS:
          '''''''ME.DGVLASER.ROW.ROMVEAFTERINSERT'''''''''

          'Count Successful INSERTS
          lblCounter.Text = Counter.ToString
          Counter += 1

          'Clean, close and dispose
          If Not localOutputServer Is Nothing Then
            localOutputServer.Dispose()
            localOutputServer = Nothing
          End If
          'Object disposal
          strSQL = Nothing
        End If

      Next
    Catch ex As Exception
      MsgBox(ex.Message)
      EventLogHelper.LogException(ex, _moduleName, "Error occurred in " & _moduleName & "/" & currentMethod & ControlChars.CrLf & ex.StackTrace, _
                   EventLogEntryType.Error, 55555)

    End Try
    MessageBox.Show("Your insert was successful!")

  End Sub

 

[cjard]

I'd probably loop over the datatable in reverse order, using an integer based index, and update the rows one by one. If it works, remove the row from the datatable. It will disappear from the grid automatically. You could even capture the error mesage for each failed row so the user knows what to do to fix it

For i as Integer = yourDatatable.Count To 0 Step -1
  Try 
    yourTableadapter.Update(yourDataTable.Rows(i))
    yourDatatable.Rows.RemoveAt(i)
  Catch ex as Exception
    yourdatatable.Rows(i).RowError = ex.Message
  End Try
Next i

or similar (untested code)