none
datatable.Rows.Count shows only some rows RRS feed

  • 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


    • Edited by Pappa_1945 Friday, January 3, 2020 1:09 PM
    Friday, January 3, 2020 1:04 PM

All replies

  • 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
    profile for Karen Payne on Stack Exchange

    Friday, January 3, 2020 2:28 PM
    Moderator
  • 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 MSDNFSF@microsoft.com.

    Monday, January 6, 2020 9:13 AM
    Moderator