none
Dataadapter.SelectCommand Property needs to be initialized error when da.update is called the second time RRS feed

  • Question

  • Hello folks,

    I have this form which contains datagridviews of two parent child tables. I have this code for deleting the parent and the child rows. The Trips table is the parent and TripDues is the child. I have setup a relation between the two.
    When I delete a row from Trips table the first time everything goes fine. But when I delete another row I get the error that DataAdapter.SelectCommand property needs to be initialized. The error occurs on the bold line:

    numTripDues = daTripDues.Update(DeletedTripDues)

    As I mentioned, this error occurs only when I delete a second row. It works fine the first time. Please help

    Private Sub DeleteButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles DeleteButton.Click
            If TripsDataGridView.SelectedRows.Count > 0 Then
                If MessageBox.Show("Delete Trip? All related TripOrders records will be deleted too!", "Delete Trip", MessageBoxButtons.OKCancel) = DialogResult.OK Then
                    Try
                        bsTrips.RemoveCurrent()
                        UpdateTripAndChildren()
                    Catch ex As Exception
                        ds.RejectChanges()
                        MessageBox.Show(ex.Message)
                    End Try
                End If
            Else
                MessageBox.Show("No rows in grid or no row selected.")
            End If
        End Sub
    
        Private Sub UpdateTripAndChildren()
            Dim DeletedTrips As DataTable = ds.Tables("Trips").GetChanges(DataRowState.Deleted)
            Dim DeletedTripDues As DataTable = ds.Tables("TripDues").GetChanges(DataRowState.Deleted)
    
            Dim numTrips As Integer
            Dim numTripDues As Integer
            Dim cb As SqlServerCe.SqlCeCommandBuilder
            Try
                If Not DeletedTripDues Is Nothing Then
                    cb = New SqlServerCe.SqlCeCommandBuilder(daTripDues)
                    numTripDues = daTripDues.Update(DeletedTripDues)
                    cb.Dispose()
                End If
                cb = New SqlServerCe.SqlCeCommandBuilder(daTrips)
                numTrips = daTrips.Update(DeletedTrips)
                cb.Dispose()
                ds.AcceptChanges()
                MessageBox.Show(numTrips.ToString + " Trip records deleted.")
            Catch ex As Exception
                MessageBox.Show(ex.Message)
                ' Update error, resolve and try again
    
            Finally
                If Not DeletedTripDues Is Nothing Then
                    DeletedTripDues.Dispose()
                End If
    
                If Not DeletedTrips Is Nothing Then
                    DeletedTrips.Dispose()
                End If
    
            End Try
        End Sub
        



    Peace, one day.


    • Edited by mystical5 Wednesday, April 18, 2012 1:34 AM highlighted the error line
    Wednesday, April 18, 2012 1:30 AM

Answers

  • You know what the dispose method does? 

    To explain it direct. In the way you use it it only makes your objects unusable. (nothing more). 

    If you want to clear the dataset then use 

    TheDataset.Clear


    Success
    Cor


    I am sorry I did not understand clearly. The dispose method is being called on cb (command builder object) and the temporary datatables DeletedTrips and DeletedTripDues. But all of these objects are being created in the function itself. So when the second time the UpdateTripAndChildren() is called they would be created anew, right?

    Peace, one day.

    Yea but your code contains so much code which is only valueable if you are paid per line of code that it does pain on my eyes.

    Also for a strange reason you try to reuse references. Why do you do that?.

    Try to clean up your program a little bit and use at least two seperated dataadapters and commandbuilders. It are only references so it save you even memory. 

    This should be enough, the rest in your code is humbug made once by persons and put on internet who did not know for what these methods where intended.

      Private Sub UpdateTripAndChildren()
    
            Dim DeletedTrips As DataTable = ds.Tables("Trips").GetChanges(DataRowState.Deleted)
            Dim DeletedTripDues As DataTable = ds.Tables("TripDues").GetChanges(DataRowState.Deleted)
            Try
                Dim cbTripsDues as New SqlServerCe.SqlCeCommandBuilder(daTripDues)
                daTripDues.Update(DeletedTripDues)
                Dim cbDues as New SqlServerCe.SqlCeCommandBuilder(daTrips)
                Dim numTrips = daTrips.Update(DeletedTrips)
                ds.AcceptChanges()
                MessageBox.Show(numTrips.ToString + " Trip records deleted.")
            Catch ex As Exception
                MessageBox.Show(ex.Message)
                ' Update error, resolve and try again
            End Try
        End Sub


    Success
    Cor


    • Edited by Cor Ligthert Thursday, April 19, 2012 7:23 AM even to much
    • Marked as answer by mystical5 Thursday, April 19, 2012 12:37 PM
    Thursday, April 19, 2012 7:21 AM

All replies

  • You know what the dispose method does? 

    To explain it direct. In the way you use it it only makes your objects unusable. (nothing more). 

    If you want to clear the dataset then use 

    TheDataset.Clear


    Success
    Cor

    Thursday, April 19, 2012 4:14 AM
  • You know what the dispose method does? 

    To explain it direct. In the way you use it it only makes your objects unusable. (nothing more). 

    If you want to clear the dataset then use 

    TheDataset.Clear


    Success
    Cor


    I am sorry I did not understand clearly. The dispose method is being called on cb (command builder object) and the temporary datatables DeletedTrips and DeletedTripDues. But all of these objects are being created in the function itself. So when the second time the UpdateTripAndChildren() is called they would be created anew, right?

    Peace, one day.

    Thursday, April 19, 2012 4:25 AM
  • You know what the dispose method does? 

    To explain it direct. In the way you use it it only makes your objects unusable. (nothing more). 

    If you want to clear the dataset then use 

    TheDataset.Clear


    Success
    Cor


    I am sorry I did not understand clearly. The dispose method is being called on cb (command builder object) and the temporary datatables DeletedTrips and DeletedTripDues. But all of these objects are being created in the function itself. So when the second time the UpdateTripAndChildren() is called they would be created anew, right?

    Peace, one day.

    Yea but your code contains so much code which is only valueable if you are paid per line of code that it does pain on my eyes.

    Also for a strange reason you try to reuse references. Why do you do that?.

    Try to clean up your program a little bit and use at least two seperated dataadapters and commandbuilders. It are only references so it save you even memory. 

    This should be enough, the rest in your code is humbug made once by persons and put on internet who did not know for what these methods where intended.

      Private Sub UpdateTripAndChildren()
    
            Dim DeletedTrips As DataTable = ds.Tables("Trips").GetChanges(DataRowState.Deleted)
            Dim DeletedTripDues As DataTable = ds.Tables("TripDues").GetChanges(DataRowState.Deleted)
            Try
                Dim cbTripsDues as New SqlServerCe.SqlCeCommandBuilder(daTripDues)
                daTripDues.Update(DeletedTripDues)
                Dim cbDues as New SqlServerCe.SqlCeCommandBuilder(daTrips)
                Dim numTrips = daTrips.Update(DeletedTrips)
                ds.AcceptChanges()
                MessageBox.Show(numTrips.ToString + " Trip records deleted.")
            Catch ex As Exception
                MessageBox.Show(ex.Message)
                ' Update error, resolve and try again
            End Try
        End Sub


    Success
    Cor


    • Edited by Cor Ligthert Thursday, April 19, 2012 7:23 AM even to much
    • Marked as answer by mystical5 Thursday, April 19, 2012 12:37 PM
    Thursday, April 19, 2012 7:21 AM
  • Thanks for helping Cor. Although I am still not clear as to why the error was coming but removing the dispose() lines does remove the error. Thanks a lot.

    Peace, one day.

    Thursday, April 19, 2012 12:37 PM