Timeout problem on big tables

communi · Post by **communi** » Tue 04 Jul 2006 09:25

Hello,
on some of my forms (VB2005) I use a tableadapter and a dataset for filling a datagrid. On this forms is no MySQLConnection-component used. If I open the form there is the following line in the form_load:

Code: Select all

Me.MyDataTableAdapter3.Fill(Me.DataSet.MyData)

This table contains over 600 000 rows and all data should be loaded. This works fine on localhost, if I try it over LAN there comes the following timeout error:
CoreLab.MySql.MySQLException: {"Lost connection to MySQL server during query"}

Why this? On other forms with a query and a MySqlConnection object I set the MySqlSelectCommand.CommandTimeOut to 180 and it works there but where can I set the timeout on this forms where no MySqlCommand is on?

Hope you can help me...! Thank you!!

Alexey · Post by **Alexey** » Tue 04 Jul 2006 10:01

Try to use MySqlDataAdapter.SelectCommand.CommandTimeout property.

communi · Post by **communi** » Tue 04 Jul 2006 18:17

Sorry, but I have no idea where to place/use this property. Can you tell me where to place it? Here is the complete code of this form:

Code: Select all

Public Class frmAuftragalle

    Private Sub frmAuftragalle_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim Result As DialogResult
        frmMain.lblStatus.Text = "Lade Daten aus der Datenbank, bitte warten...!"

        Result = MessageBox.Show(Me, "Wollen Sie alle Daten laden?", "Bestätigung", MessageBoxButtons.YesNo, _
                    MessageBoxIcon.Question, MessageBoxDefaultButton.Button1, MessageBoxOptions.RightAlign)

        If Result = Windows.Forms.DialogResult.Yes Then
            frmMain.lblStatus.Text = "Lade Daten aus der Datenbank, bitte warten...!"
            Call laden()
        Else
            frmMain.lblStatus.Text = "Laden abgebrochen. Bereit."
            Me.Close()
            Exit Sub
        End If
    End Sub
    Private Sub frmAuftragalle_Activated(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Activated
        frmMain.lblStatus.Text = "Gesamtdatenliste Aufträge aktiv"
    End Sub

    Private Sub frmAuftragalle_Deactivate(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Deactivate
        frmMain.lblStatus.Text = ""
    End Sub
    Private Sub laden()
        Me.Auftragsdaten_komplettTableAdapter3.Fill(Me.MvaDataSet4.auftragsdaten_komplett)
        
        frmMain.lblStatus.Text = "bereit"
    End Sub
End Class

Thanks for your help!

Alexey · Post by **Alexey** » Wed 05 Jul 2006 07:12

Assuming Me.Auftragsdaten_komplettTableAdapter3 is a MySqlDataAdapter component add the following string:

Code: Select all

Me.Auftragsdaten_komplettTableAdapter3.SelectCommand.CommandText = ""

before this line:

Code: Select all

Me.Auftragsdaten_komplettTableAdapter3.Fill(Me.MvaDataSet4.auftragsdaten_komplett)

communi · Post by **communi** » Wed 05 Jul 2006 08:33

It seems not to be a MySqlTableAdapter, I think it's from VS2005 a integrated TableAdapter - but the error message comes from the MySql-Component.
Whats the reason for this?

Alexey · Post by **Alexey** » Wed 05 Jul 2006 08:57

CommandTimeOut property cannot be used with TableAdapter. You can use DataAdapter instead.
Also you can use reflection. This is the way we are not going to support.
Take a look at this code:

Code: Select all

DataSet1TableAdapters.deptTableAdapter adapter = new WindowsApplication213.DataSet1TableAdapters.deptTableAdapter();
      System.Reflection.PropertyInfo _adapterField = adapter.GetType().GetProperty("Adapter", System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.NonPublic);
      IDbDataAdapter dataAdapter = (IDbDataAdapter)_adapterField.GetValue(adapter, null);

For more information about reflection read documentation.

communi · Post by **communi** » Wed 05 Jul 2006 09:21

Alexey & team,
thanks for your quickly help!!!

Alexey · Post by **Alexey** » Wed 05 Jul 2006 09:45

You are welcome.