How do I stop tableadapter filling

[John Cassell]

Hi,

I have a report form which loads a tableadapter and then shows the report. I have been trying (using the background worker) to give the user an option of cancelling the Fill of the TB but it's not working and I was hoping someone could point out where I am going wrong.

I
My code is below, any help would be greatly appreciated..
'Run report

  Private Sub Run_Report_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Run_Report.Click
        BackgroundWorker1.RunWorkerAsync()
        Me.ReportViewer1.RefreshReport()
    End Sub

'Fill Tableadapter

  Private Sub BackgroundWorker1_DoWork(ByVal sender As Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles BackgroundWorker1.DoWork
        Me.TBL_WS_JobsTableAdapter.Fill 
    End Sub

'Cancel if needed

    Private Sub CancelRep_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CancelRep.Click
        BackgroundWorker1.CancelAsync()
    End Sub

Also since using this backgroundworker. My report is showing less records than it should and every time I hit 'refresh' it displays extra records until it reaches the proper number..

Can anyone help pls?
Thanks

John

 

[JohnH]

Fill can't be cancelled, but the command it runs may. To do this you have to dissect the Fill; what it does is open connection, run the Select command to get a reader (ExecuteReader), load the Dataset from the reader, close connection. Since tableadapter doesn't expose the command objects you have to create one and replicate the procedure. Check the Cancel method of your command type in documentation for more information about its behaviour.

 

[John Cassell]

Thanks John,

I'll take a good look at that.

Cheers

 

[JohnH]

I forgot an easier way to do it, just extend the generated tableadapter class to add a CancelFill method, for example:

Namespace testdbDataSetTableAdapters
    Partial Public Class Table1TableAdapter
        Public Sub CancelFill()
            Me.CommandCollection(0).Cancel()
        End Sub
    End Class
End Namespace

The backgroundworker code could then look like below, when cancelled the Fill command now throws SqlException "Operation Cancelled by user":

Private Sub BackgroundWorker1_DoWork(ByVal sender As System.Object, _
ByVal e As System.ComponentModel.DoWorkEventArgs) _
Handles BackgroundWorker1.DoWork
    Try
        Me.Table1TableAdapter.Fill(Me.TestdbDataSet.Table1)
    Catch ex As SqlClient.SqlException
        MsgBox(ex.Message) '"Operation Cancelled by user"
    End Try
End Sub

Private Sub BackgroundWorker1_RunWorkerCompleted(ByVal sender As Object, _
ByVal e As System.ComponentModel.RunWorkerCompletedEventArgs) _
Handles BackgroundWorker1.RunWorkerCompleted
    Me.Table1BindingSource.ResetBindings(False)
End Sub

Same example cancel:

Me.Table1TableAdapter.CancelFill()