Asked by:
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
- 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.
Friday, January 3, 2020 2:28 PM -
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.- Proposed as answer by Xingyu ZhaoMicrosoft contingent staff Friday, January 10, 2020 3:09 AM
Monday, January 6, 2020 9:13 AM