Tableadapter.Fill not working with manual query source

Question

I have a windows form app with a DataGridView populated by a TableAdapter. I'm using the Fill method to update the data for the UI in a looping Async sub like so.

Private Async Sub updateUI()
    Dim sw As New Stopwatch
    While True
        Await Task.Delay(3000)
        sw.Restart()
        myTableAdapter.Fill(getDataWithMySQL())
        'myTableAdapter.Fill(myDataSet.myTable)
        logger.Debug(sw.ElapsedMilliseconds)
    End While
End Sub

The getDataWithMySQL function is as follows:

Private Function getDataWithMySQL() As myDataSet.myDataTable
    Dim connStr As String = My.Settings.myConnectionString
    Dim sql As String = "SELECT ... LEFT JOIN ..."
    Dim dt As New myDataSet.myDataTable
    Using conn As New MySqlConnection(connStr)
        Using cmd As New MySqlCommand()
            With cmd
                .CommandText = sql
                .Connection = conn
            End With
            Try
                conn.Open()
                Dim sqladapter As New MySqlDataAdapter(cmd)
                sqladapter.Fill(dt)

            Catch ex As MySqlException
                MsgBox(ex.Message)
            End Try
        End Using
    End Using
    Return dt
End Function

myTableAdapter.Fill(myDataSet.myTable) works fine but performs poorly whereas myTableAdapter.Fill(getDataWithMySQL()) performs better as reported in my as yet unanswered question here.

For some reason, myTableAdapter.Fill(getDataWithMySQL()) does not work any more. It doesn't throw an error and dt is populated with the correct data but the DataGridView is not updating. It has worked previously and I don't think I've changed anything that would affect this. Any ideas why the DataGridView is not updating?

Answer 1

I believe your problem stems from the fact that you're creating a new datatable within the method call and returning that datatable. You've got a reference to the DataSet.DataTable that you want, just call Fill with that DataTable.

Your problem is that the datatable that you're filling isn't associated w/ the dataset that your grid is using.

Answer 2

Most likely this is because the .Fill command takes a datatable in using ByRef. I think what's happening is you are returning a DataTable with your getDataWithMySQL() but then you are taking that DataTable and refilling it with the .Fill method of the adapter and the DataTable you were returning with the getDataWithMySQL() method goes into lala land to never be seen again.

Making some assumptions here, but try giving this a shot:

Private Async Sub updateUI()
    Dim sw As New Stopwatch
    While True
        Await Task.Delay(3000)
        sw.Restart()
        Dim dt as myDataSet.myDataTable = getDataWithMySQL("SELECT ... LEFT JOIN ...")
        UpdateDGV(dt)
        'myTableAdapter.Fill(myDataSet.myTable)
        logger.Debug(sw.ElapsedMilliseconds)
    End While
End Sub

' You need to change `DataGridView` here to be the actual datagridview control on the form
Private Sub UpdateDGV(newSource as myDataSet.myDataTable)
    DataGridView.SuspendLayout()
    DataGridView.DataSource = Nothing
    DataGridView.DataSource = newSource 
    DataDridView.ResumeLayout()
End Sub

' Make the function easier to reuse by passing in the SQL command
Private Function getDataWithMySQL(sqlCmd as string) As myDataSet.myDataTable
    Dim connStr As String = My.Settings.myConnectionString
    Dim dt As New myDataSet.myDataTable
    Using conn As New MySqlConnection(connStr)
        Using cmd As New MySqlCommand()
            With cmd
                .CommandText = sqlCmd
                .Connection = conn
            End With
            Try
                conn.Open()
                Dim sqladapter As New MySqlDataAdapter(cmd)
                sqladapter.Fill(dt)

            Catch ex As MySqlException
                MsgBox(ex.Message)
            End Try
        End Using
    End Using
    Return dt
End Function