Migrating VB6 to VB.net recordsets and datatables

Question

I have a part of code in VB6 which i'm trying to convert to VB.net. Specifically recordsets.

This is a part of VB6 code:

Data19.RecordSource = "select id from headers where type=12"
Data19.Refresh
If Data19.Recordset.RecordCount > 0 Then
Data6.RecordSource = "select sum(left * right) from footers where type=12"
Data6.Refresh
ss = Format(Data6.Recordset.Fields(0), "0.00")
Data19.Recordset.MoveLast
a = Data19.Recordset.RecordCount - 1
Data19.Recordset.MoveFirst
For i = 0 To a
If i > 0 Then Data19.Recordset.MoveNext
   Data22.RecordSource = "select * from documents where type=12"
   Data19.Recordset.Fields(0)
   Data22.Refresh
   With Data22.Recordset
      If .RecordCount > 0 Then
      .MoveLast
      b = .RecordCount - 1
      .MoveFirst
        For j = 0 To b
          If j > 0 Then .MoveNext
              If .Fields("link1") < ra And .Fields("code") <> "00" Then
              .Edit
              .Fields("link1") = ra
              .Fields("pc") = Format(.Fields("dpc") * (100 - ra) / 100, "0.00")
              .Fields("total") = Format(.Fields("amount") * .Fields("dpc") * (100 - ra) / 100, "0.00")
              .Update
              End If
        Next
      End If
   End With
  Next
 End If 

I am a bit confused about .MoveLast, .MoveFirst,.Recordset.

In my VB.net code i have been using this function to get the table that i want from the database:

  Public Function returnTable(ByVal queryString As String)
    Dim query1 As String = queryString
    'Console.WriteLine(query1)
    Dim table As New DataTable
    Using connection As New MySqlConnection(connection)
        Using adapter As New MySqlDataAdapter(query1, connection)
            Dim cmb As New MySqlCommandBuilder(adapter)
            table.Clear()
            adapter.Fill(table)
            Return table
        End Using
    End Using
End Function

So the part with recordsources should go something like this if i'm not wrong:

Dim data19 as new datatable
Data19 = returnTable("select id from headers where type=12")

But later on, in the code, i cannot figure out how to write the equivalents to .MoveLast, .MoveFirst,.Recordset,.MoveNext, etc...

Answer 1

It looks like you're trying to write the VB.Net equivalents to VB6 .MoveLast, .MoveFirst, .Recordset, .MoveNext, etc.

.Recordset

You're very close to your solution with the returnTable function that you created. Datatables are just collections of DataRow objects, which is a bit different than recordset objects in VB6. You created a DataTable with your .Net function:

Dim data19 as new datatable
Data19 = returnTable("select id from headers where type=12")

In this case, Data19 is a DataTable that takes the place of a recordset in the VB6 example.

.MoveLast

In your VB6 example, the reason for using .MoveLast is to expose the count of records in the recordset. The count of records is not known until you move to the last record. After you move to the last record, then you can load the count into a variable.

With ADO.Net, you don't need to use .MoveLast to get the count. You can simply get the row count like this:

Dim row_count As Integer = Data19.Rows.Count

You'll see below that this variable is not needed when you convert to .Net. You use it in VB6 to know how many records to loop through (and when to stop). In .Net you will use For Each.. Next to achieve the same purpose.

.MoveFirst

For your example, .MoveFirst is used only because you used .MoveLast to get the record count. In order to walk through the recordset, you have to go back to the first record. Since you no longer need to use .MoveLast in .Net, you also don't need to use .MoveFirst.

.MoveNext

In your VB6 example, .MoveNext is used to walk through the recordset and do some actions on each row. To walk through the DataTable that you created, you can do something like this:

Dim my_row as DataRow
For Each my_row in Data19.Rows
    If my_row("link1") < ra And my_row("code") <> "00" Then
        .. do some actions
    End If
Next

This will walk through the recordset in a similar fashion. One thing to consider is that you are working with a disconnected recordset when you use your DataTable. When you get to the .Edit and .Update parts of your VB6 procedure, you might need to use a parameterized query to perform the actual update to any records. This will use a command object and .ExecuteNonQuery() method to perform an SQL update statement.