Max Size for DataTable

6 Apr 2005 12:29 PM

Bruce D

Don't ask why...
But I want to loop through all my records in the MySQL table (5 million
records) and do a data integrity check (along with some other update
functions). I'm wondering if there is a max size for the datatable. Or if
there is a better way. It seems I loose my connection if I try to get 1
million rows...
Here's the code...

                Dim _connMySQL As New MySqlConnection(_strConn)
                _connMySQL.Open()

                If Me.txtJob.Text.Length > 0 Then
                    Me.lblStatus.Visible = True
                    strSQL = "SELECT * " & _
                                    " FROM Import"
                    'dtImport = DataClass.GetMySQLDataTable(strSQL,
"Import")
                    Dim dtImport As New DataTable("Import")
                    Dim _da1 As New MySqlDataAdapter(strSQL, _connMySQL)
                    _da1.Fill(dtImport)
                    _da1 = Nothing
                    _connMySQL.Close()

                    For Each dr In dtImport.Rows
                        ' blah blah
                    Next
                End If
                _connMySQL.Close()

-bruce duncan

6 Apr 2005 12:49 PM

Cor Ligthert

Bruce,

It is in my opinion certainly not the best way, I would just loop through it
using the MySqldatareader (I don't know if it exist however should be
because that is the basic for the Fill in the SQLDataadapter).

There is as well a SQLConnection.Timeout that can be set, what I don't know
as well for mySQL. In SQL you should never set that to zero, because that
means forever.

I hope this helps something,

Cor