datatable.Rows.Count shows only some rows

Question

Respected Friends,
I have a MySQL table containing 2138 rows. But my code shows only 1639 rows and update is not taking effect after the row number of 1639. And also DatagridView is displaying 2138 rows. Please help me. My code is given bellow. Thanks.

My code to show the number of Rows:

mysqlconn = New MySqlConnection
mysqlconn.ConnectionString = "server=localhost;user id=root;password=1234;database=Share"
mysqlconn.Open()
Dim adapter As New MySqlDataAdapter("SELECT * FROM name_list;", mysqlconn)
Dim datatable As New DataTable()
Dim dataset As New DataSet
adapter.Fill(datatable)
adapter.Fill(dataset)

TextBox1.Text = datatable.Rows.Count

DataGridView1.DataSource = datatable

And my code to Update is

        Dim mysqlconn As New MySql.Data.MySqlClient.MySqlConnection 
        mysqlconn.ConnectionString = "server=localhost;user id=root;password=1234;database=Share"
        mysqlconn.Open()
        Dim adapter As New MySqlDataAdapter("SELECT * FROM name_list;", mysqlconn)
        Dim datatable As New DataTable()
        adapter.Fill(datatable)
        Dim cmd As New MySqlCommand
        cmd.Connection = mysqlconn
        Dim sql As String
        Dim ii As Integer = 0

        For Each rw As DataRow In datatable.Rows
            sql = "UPDATE Name_list Set P_Numerology = NULL , Remark = NULL WHERE ID =" & (ii)
            cmd.CommandText = sql
            cmd.ExecuteNonQuery()
            ii = ii + 1
        Next
        adapter.Fill(datatable)
        DataGridView1.DataSource = datatable

Answer 1

Hello,

Why are you doing two Fill calls

adapter.Fill(datatable)
adapter.Fill(dataset)

Only need one fill.

Try the following, replace SqlConnection with MySqlConnection and SqlCommand with MySqlCommand and replace the SELECT with your SELECT and see what the total is. If either is not reporting an expected row count then there are issues such has hitting the wrong database/table or there is corruption in the table or a field.

Note:This is not to replace what you are doing, only for diagnostics.

Public Class Operations
    ' ignore the next line
    Inherits BaseConnectionLibrary.ConnectionClasses.SqlServerConnection
    Public Function GetCount() As Integer
        DefaultCatalog = "NorthWindAzure2"
        DatabaseServer = ".\SQLEXPRESS"
        Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
            Using cmd As New SqlCommand With {.Connection = cn}
                cmd.CommandText = "SELECT COUNT(id) AS total FROM dbo.Countries;"
                cn.Open()
                Return CInt(cmd.ExecuteScalar())
            End Using
        End Using
    End Function
End Class

Usage

Dim operations = New Operations
MessageBox.Show($"Total records {operations.GetCount()}")

Then try

Public Class Operations
    ' ignore the next line
    Inherits BaseConnectionLibrary.ConnectionClasses.SqlServerConnection
    Public Function CountryDataTable() As DataTable
        DefaultCatalog = "NorthWindAzure2"
        DatabaseServer = ".\SQLEXPRESS"
        Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
            Using cmd As New SqlCommand With {.Connection = cn}
                cmd.CommandText = "SELECT id, CountryName FROM dbo.Countries;"
                cn.Open()
                Dim dt As New DataTable
                dt.Load(cmd.ExecuteReader())
                Return dt
            End Using
        End Using
    End Function
End Class

Usage

Dim operations = New Operations
Dim dt As DataTable = operations.CountryDataTable()
MessageBox.Show(dt.Rows.Count.ToString())

---

Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

NuGet BaseConnectionLibrary for database connections.

StackOverFlow

Answer 2

Hi Pappa_1945, 

Thank you for posting here.

According to your code,  you can refer to the following code to update data in your database.

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim lst As List(Of Integer) = New List(Of Integer)()

        Using mysqlconn As MySqlConnection = New MySqlConnection()
            mysqlconn.ConnectionString = "connection string"
            mysqlconn.Open()
            Dim commandTxt As String = "SELECT Id FROM TableNames"

            Using cmd As MySqlCommand = New MySqlCommand()
                cmd.Connection = sqlconn
                cmd.CommandText = commandTxt
                Dim reader = cmd.ExecuteReader()

                While reader.Read()
                    Dim result = Convert.ToInt32(reader(0))
                    lst.Add(result)
                End While

                reader.Close()

                For Each id In lst
                    commandTxt = "UPDATE TableNames Set Names = null WHERE Id = " & id
                    cmd.CommandText = commandTxt
                    cmd.ExecuteNonQuery()
                Next
            End Using

            Dim adapter As MySqlDataAdapter = New MySqlDataAdapter("SELECT * FROM TableNames;", mysqlconn)
            Dim dataset As DataSet = New DataSet()
            adapter.Fill(dataset)
            TextBox1.Text = dataset.Tables(0).Rows.Count.ToString()
            DataGridView1.DataSource = dataset.Tables(0)
        End Using
    End Sub

I insert 2138 rows in my database, and here’s my result:

Data in my database:

Hope it can help you.

Best Regards,

Xingyu Zhao

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].