none
Remove Datagridview binding RRS feed

  • Question

  • Hi

    I have a winform with Datgridview(DGV1)

    Then a Sql server Database Table and a DataAdapter that fills e DataTable (DT1)

    The DGV binds to Bindinsource (BS1) and BS1 binds to DT1

    Now I want to delete a DGV1 selected row  form DT1 (with a Delete button )

    With the delete button the row is deleted from DT1 and disappears in the DGV1, then I update the Database.

    If I get a sql exception I give a massage to the user and  I reject change on DT1, so the  row  appears agan in the DGV1.

    It is all ok.

    What I want is to avoid that the row disappears in the DGV1 before Sql exception message since if there is a sql exception the row must be still in the DGV.

    I though to clear the DGV binding to BS1 before update DT1 and bind it again after Database update so I used DGV1.databindings.clear . But it does not work.

    How to avoid that the selected row to delete disappears from DGV before checking sql exceptions ?

    I hope i was clear 

    Monday, February 12, 2018 10:26 AM

Answers

  • Karen,  in my unferstanding since i'm using DataAdapter between DataBase and DataTable I think I cannot delete a row from  Database if first i don't delete the row from DataTable

    Is it right ?

    Sure you can, the DataAdapter is unaware of the record being removed. I've done this countless times with SQL-Server, MS-Access, IBM-DB2 and Oracle. So don't think that way :-)

    Delete the row in the table, upon success delete the row in the project via the BindingSource.RemoveCurrent (if you are deleting the current row) or find the record and mark it for deletion. Either way the DataAdapter will know it needs to remove the record. Using a solid delete statement in the DataAdapter when it goes to remove the record the WHERE condition is not met so it does not remove anything, no different than writing a DELETE statement with a WHERE condition in SSMS (SQL-Server Management Studio) or in a .sql file in your project.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Claudio111 Wednesday, February 14, 2018 4:01 PM
    Monday, February 12, 2018 2:55 PM
    Moderator

All replies

  • One way around this is to create a class for working with operations such as this that in this case has a delete method. The delete method accepts a primary key, using SqlClient connection and command object. For the command object set up the DELETE statement, add a parameter for the primary key. This is wrapped in a try/catch, if Executing the DELETE is successful return true from the method, otherwise return false.

    In the calling button click event, if the method returned true, remove the current row which can be done via the BindingSource.RemoveCurrent method, otherwise present the user with a message indicating there was an issue removing the record.

    Yes all of the above breaks away from reasons you may have selected the DataAdapter yet this is a viable option. Sometimes we need to work around a chosen path. The following is how I might handle this which has been taken from a C# article I'm currently writing. There are parts such as ViewCommand (which is about custom code I've written) or variables starting with 'm' that may not makes sense yet the concept is sound. If this method returns true we delete from the DataGridView via the BindingSource and if false don't remove from the DataGridView.

    Public Function RemovePerson(ByVal pIdentifier As Integer) As Boolean
    	mHasException = False
    	Dim succcess As Boolean = False
    
    	Using cn As New SqlConnection() With {.ConnectionString = ConnectionString}
    		Using cmd As New SqlCommand() With {.Connection = cn}
    			cmd.CommandText = "DELETE FROM People WHERE Id = @Id"
    			cmd.Parameters.AddWithValue("@Id", pIdentifier)
    
    			If ViewCommand Then
    				Console.WriteLine(cmd.ActualCommandText())
    			End If
    
    
    			Try
    				cn.Open()
    				succcess = (cmd.ExecuteNonQuery() = 1)
    			Catch sqlException As SqlException
    				mHasException = True
    				mLastException = sqlException
    				Throw sqlException
    			Catch generalException As Exception
    				mHasException = True
    				mLastException = generalException
    			End Try
    		End Using
    	End Using
    
    	Return succcess
    End Function

    The following is C# yet we can do the same in vb.net which is to create unit test to test method such as the one above.

    Here we test the delete method, a caveat, before each test is executed I truncate the table and re populate from another database. First remove the person, then check they were removed via (in this case) another method PersonExists.

    In closing sorry about using C# yet one does not have to understand C# to see what is being done here.  

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, February 12, 2018 11:11 AM
    Moderator
  • Thank you Cor but I think that even with the extra boolean column if I delete row from DataTable the row desappears from DGVAnd if I get a sql exceptio Is will appears again.

    I want to avoid thatt the rows disappears from DGV before sql exception error and show it again in DGV after reject.changes 

    Monday, February 12, 2018 11:12 AM
  • Thank you Cor but I think that even with the extra boolean column if I delete row from DataTable the row desappears from DGVAnd if I get a sql exceptio Is will appears again.

    I want to avoid thatt the rows disappears from DGV before sql exception error and show it again in DGV after reject.changes 

    Claudio, there is zero reasons to have to use RejectChanges, see my reply. You are thinking about this the wrong way which (and no disrespect here) comes from not working with data for a long time.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, February 12, 2018 11:17 AM
    Moderator
  • Hi Karen 

    I already have a DAL Class ( I work only with n-tier application) and in the DAL Class I can get sql exception thank you to your suggestions of several weeks ago.

    The problem is I want to have experience with dataadapter and datatable 

    So I have a dataTable and in the form I can add, modify and delete rows in the dataTable by different button.

    The DGV in the form  has a bind to Datatable 

    If I want to delete a row I have first to delete the row in the datatable and then try to delete from DataBase and if I get a Sql exception I have to rejcet changes to datatable.

    This is my code for delete command

    If TipoOperazione = "Elimina" Then
    
      Dim tempDTRow As DataRow = DTRowSelected
    
      DTRowSelected.Delete()
    			
      BLL.SaveRowToDB(UI_DataSet_Nazioni)
    
      If BLL.SqlError = True Then
        tempDTRow.RejectChanges()
        DGV.Refresh()
    
      End If
    			
    End If

    Monday, February 12, 2018 1:10 PM
  • everything works well

    I want just to avoid that the row disappears from DGV before I check for a sql exception.

    For now when i delete a row, it disappears from DGV (ok) then I get sql Exception and I give user a message, and the the row appears again.

    Monday, February 12, 2018 1:14 PM
  • Cor, i'm using a dataadapter and how can I delete a Row in Database if I don't delete that row FIRST form dataTable ?


    Monday, February 12, 2018 1:20 PM
  • Cor, i'm using a dataadapter and how can I delete a Row in Database if I don't delete that row FIRST form dataTable ?


    The DataTable is only a container that reflects what is in the database table. As I said before you should delete the record first in the database table then delete the row in the DataTable via the BindingSource.RemoveCurrent.

    If you want to know what goes on under the covers see my DataTable primer for events.

    Working with DataTable events primer.

    Though it's not a DataAdapter the following shows what I mean for ms-access yet the same holds true for sql-server.

        Private Sub cmdRemoveCurrentRow_Click(ByVal sender As System.Object, ByVal e As EventArgs) _ 
            Handles ToolStripButton1.Click 
     
            If My.Dialogs.Question("Remove current customer?") Then 
                If ops.RemoveCurrentCustomer(bsCustomers.CurrentRow.Field(Of Int32)("Identifier")) Then 
                    bsCustomers.RemoveCurrent() 
                    bsCustomers.DataTable.AcceptChanges() 
                Else 
                    If Not ops.IsSuccessFul Then 
                        MessageBox.Show($"Removal failed: {ops.LastExceptionMessage}") 
                    End If 
                End If 
            End If 
        End Sub 
        Private Sub cmdClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdClose.Click 
            Close() 
        End Sub 


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, February 12, 2018 2:24 PM
    Moderator
  • Karen,  in my unferstanding since i'm using DataAdapter between DataBase and DataTable I think I cannot delete a row from  Database if first i don't delete the row from DataTable

    Is it right ?

    Monday, February 12, 2018 2:49 PM
  • Karen,  in my unferstanding since i'm using DataAdapter between DataBase and DataTable I think I cannot delete a row from  Database if first i don't delete the row from DataTable

    Is it right ?

    Sure you can, the DataAdapter is unaware of the record being removed. I've done this countless times with SQL-Server, MS-Access, IBM-DB2 and Oracle. So don't think that way :-)

    Delete the row in the table, upon success delete the row in the project via the BindingSource.RemoveCurrent (if you are deleting the current row) or find the record and mark it for deletion. Either way the DataAdapter will know it needs to remove the record. Using a solid delete statement in the DataAdapter when it goes to remove the record the WHERE condition is not met so it does not remove anything, no different than writing a DELETE statement with a WHERE condition in SSMS (SQL-Server Management Studio) or in a .sql file in your project.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Claudio111 Wednesday, February 14, 2018 4:01 PM
    Monday, February 12, 2018 2:55 PM
    Moderator
  • I would like to delete database before DataTable but with this code i cannot

    DAL Class

    Public Sub SaveRowToDB(ByVal DAL_DS_Nazioni As DataSet) mHasException = False mHasSqlException = False mHasCurrException = False ‘ Dim QueryI = <sql> ‘ insert query …. ‘ Dim QueryU = <sql> ‘ update query Dim QueryD = <sql> DELETE FROM Tabelle.[TabNazioni] WHERE [NazioneID]= @ID </sql>.Value Try Using CONN As New SqlConnection With {.ConnectionString = VG.FullCS} Using CMD As New SqlCommand With {.Connection = CONN, .CommandType=CommandType.Text} Using DAL_DA_Nazioni As New SqlDataAdapter With {.InsertCommand = New SqlCommand With {.Connection =CONN}, .DeleteCommand = New SqlCommand With {.Connection = CONN}, .UpdateCommand = New SqlCommand With {.Connection = CONN} } ' INSERT COMMAND --------------------------------- ' UPDATE COMMAND --------------------------------- ' DELETE COMMAND --------------------------------- DAL_DA_Nazioni.DeleteCommand.CommandText = QueryD DAL_DA_Nazioni.DeleteCommand.Parameters.Add(New SqlParameter With {.ParameterName = "@ID", .SqlDbType = SqlDbType.Int, .SourceColumn = "NazioneID"}) CONN.Open()

    DAL_DA_Nazioni.Update(DAL_DS_Nazioni, "Nazioni") CO CONN.Close() End Using End Using End Using Catch sqlex As SqlException mHasSqlException = True mLastSqlException = sqlex Catch currex As DBConcurrencyException mHasCurrException = True mLastCurrException = currex Catch ex As Exception mHasException = True mLastException = ex End Try End Sub


    I understood that the DataAdapter updateCommand looks, in the DataTable, all the rows with Rowstate = deleted (in our case) . So if I don't delete the row from DataTable the DataAdapter has nothing to delete.

    (Just for details , the sql exception I want to handle is the SqlException.Number = 547 ) Impossible to delete row as the PK is a FK in another Table. But This does not change the my problem at all


    Monday, February 12, 2018 4:19 PM
  • yes of course Cor, but i would like to avoid to loop the DataTable.

    In any case thank you

    Claudio

    Monday, February 12, 2018 4:21 PM
  • Cor, maybe i was not clear.

    The problem is that I use a DataAdapter.Update command to delete row in database.

    If I do not delete the row first  from datatable,  the DataAdapter update command does not find any row to delete. See my code above.

    (The DataAdapter update command delete all row with rowstate = Deleted )

    Monday, February 12, 2018 5:41 PM
  • Your code just delete a row form datatable and then delete the database table

    Since I know the row to delete ( DTRowselected  = row selected in DGV in my code above) i do not need to loop the data table with boolean column. 

    I really do not understand you. My problem is different 

    Monday, February 12, 2018 5:52 PM
  • I try to explain the problem again

    This is my form 

    I select a row in DGV then press Delete Command (ELimina) then I ask to confirm 

    Then I confirm and so I delete the selected row from DataTable (so the datarow has rowstate = deleted)  ( DGV bound to DataTable so the row in not more in DGV). 

    Then I try to delete the row in Database. I get sql exception so I get a error message and reject the changes to datarow.

    This is the form with sql exception  message

    The selected row is not in the DGV but after message it will appear againg as it should be


    Every thing is ok. So I do no need boolean column .

    I want just the row showed in DGV during sql error message

    I understood that this can be done only if I try to delete the row from database BEFORE i delete it form DataTable, and this cannot be done with dataadapter Uodate command as show in my code above.

    Monday, February 12, 2018 6:09 PM
  • Sorry Cor, but i don't want to delete the row if it has a PK  used as FK Key in another Table. So I do not need to load the Regioni Table.

    And the problem I have is still there.

    I know that in 15 minutes I can do the same project with TableAdapter but without understand what is going in the back.

    So I prefer to go deeper and deeper in the hole and to be able to handle all monsters in the hole.

    In any case you suggest me to go ahead and use Table Adapter and Karen suggest me to not use any kind of adapter (she hates them)

    So,     soon,  i will go back to ...........COBOL   

    hihihihihihihihihihih 

    Monday, February 12, 2018 7:06 PM
  • Hi Cor,

    your example does not solve my problem ( please read again my first question) 

    You delete Row form DataTable with a loop then at the end you update the DataBase with da.update(datatable)

    When you delete the row in DataTable the row diasppears from DGV that bounds to DataTable.

    If I get sql exception I have to sohow again the row in DGV.

    So I have to delete first the row in Database (not with DataAdaper command) and then delete the row in DataTable if I donìt get any Sql Exception.

    Tuesday, February 13, 2018 5:40 PM
  • Hi Cor,

    your example does not solve my problem ( please read again my first question) 

    You delete Row form DataTable with a loop then at the end you update the DataBase with da.update(datatable)

    When you delete the row in DataTable the row diasppears from DGV that bounds to DataTable.

    If I get sql exception I have to sohow again the row in DGV.

    So I have to delete first the row in Database (not with DataAdaper command) and then delete the row in DataTable if I donìt get any Sql Exception.

    Hi Claudio,

    Yes my messages don't help you. In the case if there is for instance an SQL exception (that means a problem at the Server side, client side errors are standard exceptions), I try to change the  code in a way it cannot happen. 

    You simply tell the client the row may never been removed. But we have a different kind of view that is so wide spread that I cannot help you with that. I simply don't help with in my perception wrong solutions. 




    Success
    Cor


    • Edited by Cor Ligthert Wednesday, February 14, 2018 5:34 PM
    Wednesday, February 14, 2018 5:34 PM