JimmyJimm JimmyJimm - 6 months ago 18
Vb.net Question

Dataset has changes for new row

I just notice that if user modyfi something in datagridview and additionally select last new row in datagridview and will not fill it out but just select then when it goes to HasChanged this last newrow is included to be added to db and then i get error because i cannot put that record to db which is logical as nulls. I would like to exclude this row in that particural situation as it shouldn't be treaded to be added. Below my current code:

For Each row As DataGridViewRow In Grid.Rows
If Not row.IsNewRow Then
Dim cellValue As String = String.Empty
For i = 1 To row.Cells.Count - 1
cellValue = row.Cells(i).Value.ToString
If String.IsNullOrEmpty(cellValue) Then
MsgBox("All fields has to be filled out")
Exit Sub
End If
Next
End If
Next

marke.MakeChangesDS()





Public Sub MakeChangesDataSet() Implements IDAL.MakeChangesDataSet
If Not GetGeschaftDataSet.HasChanges Then
MessageBox.Show("No changes to be done", "Informacja", MessageBoxButtons.OK, MessageBoxIcon.Warning)
Else
Dim i As Integer
Try
Using MyConnection = New SqlConnection(strcon)
Using cmd As New SqlCommand("SELECT * FROM T_Marke", MyConnection)
MyConnection.Open()
' Create a data adapter in the method and throw it away afterwards
Using GetProjectsDataAdapter = New SqlDataAdapter(cmd)
Dim cmdbuilder As New SqlCommandBuilder(GetProjectsDataAdapter)
i = GetProjectsDataAdapter.Update(GetGeschaftDataSet, "trial1")
End Using
End Using
End Using
MessageBox.Show("Updated" & i & " marks", "Informacja", MessageBoxButtons.OK, MessageBoxIcon.Information)
Catch ex As Exception
MsgBox(ex.Message)
End Try
End If
End Sub


I think i should check and exclude it in MakeChangesDataSet function.

Answer

The SqlDataAdapter class has an event handler called RowUpdating that allows you to check if your row is valid or not and cancel the Update of that row if you choose to do so.

.....
Using GetProjectsDataAdapter = New SqlDataAdapter(cmd)
   Dim cmdbuilder As New SqlCommandBuilder(GetProjectsDataAdapter)
   AddHandler GetProjectsDataAdapter.RowUpdating, AddressOf onUpdate
   i = GetProjectsDataAdapter.Update(GetGeschaftDataSet, "trial1")
   RemoveHandler GetProjectsDataAdapter.RowUpdating, AddressOf onUpdate
End Using

This event is called for each row that need to be updated before the actual update/insert/delete occurs. The event handler code receives all the info required to decide what to do with the data. You get the StatementType that tells you if this is an INSERT, UPDATE or DELETE operation. Then you can decide what to do with the row setting the SqlRowUpdatingEventArgs.Status before any exception is raised by your database engine.

Sub OnUpdate(sender as Object, args As SqlRowUpdatingEventArgs)
     If args.StatementType = StatementType.Insert Then
        if Not IsYourRowValid(args.Row) Then
           args.Status = UpdateStatus.SkipThisRow
        End If
    End If
End Sub
Comments