Multiple Connection MYSQL VB.NET RowCount

Question

I Have stack Here and does not go I have a loop that makes connection to database but last 2 days have problem with counting row number. And nowere to find Solution

    Dim row as Integer
    For i = 100 To 120
        Dim DBconn As MySql.Data.MySqlClient.MySqlConnection = New MySql.Data.MySqlClient.MySqlConnection(My.Settings.DBConnStr)
        Dim da As MySql.Data.MySqlClient.MySqlDataAdapter
        Dim dt As New DataTable
        Dim sqlcmd As New MySql.Data.MySqlClient.MySqlCommand
        sql = "SELECT Sum(D), Sum(P) FROM fin  WHERE K like '" & i.ToString & "%' "
        DBconn.Open()
        da = New MySql.Data.MySqlClient.MySqlDataAdapter(sql, DBconn)
        da.Fill(dt)
        DBconn.Close()
        row = dt.Rows.Count
        MsgBox("i=" & i & "    rows=" & row)
        dt.Clear()
    Next

MSG Box Alway tells me that the RowCount is 1, when manualy i make the query to MYSQL Diferent i Gives me Diferent Rows.

Answer 1

I am sure how you were able to get the dataadapter to fill a datatable. It should be filling a dataset. It should be

   DIM ds as New Dataset()
   da = New MySql.Data.MySqlClient.MySqlDataAdapter(sql, DBconn) 
   da.Fill(ds)
   DIM rowCount = ds.Table(0).Rows.Count

Try this

   DIM rowCount = ds.Tables(0).Select().Count