CODEWITHSUNDEEP
sql-servervb.netdatagridviewdatatable
PowerMan2015
PowerMan2015

Reputation: 1418

Updating database from DataGridView

I have a datagrid view which loads data from a data table

This data table is filled with data from a SQL Server database

When the user loads an invoice, I fill a data grid view from this datatable

I then allow the user to modify the invoice, this will include adding/editing/deleting rows from the data table

This data table is then updated to the database when the user presses a save button

I need a way of detecting the row status so that it updates the database correctly

I am able to add a row no problem

However is the user tries to delete a row which was fetched from the database it removes it from the data table, but does not remove it from the database

Furthermore, I need to allow for users to add a row and then delete it without the row ever existing in a database

Here is the code I currently use

Dim boolAdded As Boolean = False
Dim boolDeleted As Boolean = False
Dim boolChanged As Boolean = False
Dim cB As SqlCommandBuilder = New SqlCommandBuilder(SQLAdaptor)

Con.ConnectionString = CropTrackMod.strConn
SQLAdaptor.SelectCommand = New SqlClient.SqlCommand("Select * FROM TicketDetailv2 where ticketref ='" & strEditTicketRef & "'", Con)

For Each row As DataRow In myTable.Rows
    If row.RowState = DataRowState.Added Then
        boolAdded = True
    End If
    If row.RowState = DataRowState.Deleted Then
        boolDeleted = True
    End If
    If row.RowState = DataRowState.Modified Then
        boolChanged = True
    End If
Next

If boolAdded = True Then
    Dim tmpAddedMyTable As DataTable = myTable.GetChanges(DataRowState.Added)


    If tmpAddedMyTable.Rows.Count >= 0 Then
        For Each row As DataRow In tmpAddedMyTable.Rows
            row.Item("TicketRef") = strEditTicketRef
        Next

        SQLAdaptor.InsertCommand = cB.GetInsertCommand
        SQLAdaptor.Update(tmpAddedMyTable)
    End If
End If

If boolChanged = True Then
    Dim tmpChangedMyTable As DataTable = myTable.GetChanges(DataRowState.Modified)

    SQLAdaptor.InsertCommand = cB.GetUpdateCommand
    SQLAdaptor.Update(tmpChangedMyTable)
End If

If boolDeleted = True Then
    Dim tmpDeletedMyTable As DataTable = myTable.GetChanges(DataRowState.Deleted)
    SQLAdaptor.InsertCommand = cB.GetDeleteCommand
    SQLAdaptor.Update(tmpDeletedMyTable)
End If

Any help would be appreciated

Thanks in advance guys

Upvotes: 0

Views: 94

Answers (1)

PowerMan2015
PowerMan2015

Reputation: 1418

I have now fixed the error of my ways

The issue was being caused because i was removing the row from the dataTable like this

myTable.Rows.RemoveAt(dgvTicketDetail.CurrentCell.RowIndex)

however if i delete the row like this it works

myTable.Rows(dgvTicketDetail.CurrentCell.RowIndex).Delete()

This works because it marks the row for deletion until an update occurs.

I did have to improve the logic when iterating through the rows in the table in an effort not to include the deleted rows. This was achieved by:

for each row as datarow in myTable.rows
if not row.rowstate = datarowstate.deleted then

end if
next

Upvotes: 1

Related Questions