0

In the column "TOTAL" is the result of calculations in sql so that the column is an alias and the column has descending in Sql. So how can I set the rank column datatable can create serial number sequence from number one onwards?

Public Sub filltable2(ByVal dtgrd As Object, ByVal design As String)
    Dim publictable As New DataTable

    Try
        da.SelectCommand = cmd
        publictable.Columns.Add(New DataColumn("Rank", GetType(Integer)))
        Dim count As Integer = 1
 da.Fill(publictable)
        dtgrd.DataSource = publictable

        Select Case design
            Case "trial"

        End Select

        da.Dispose()

    Catch ex As Exception
        MsgBox(ex.Message, MsgBoxStyle.Information)
    End Try
End Sub

Table 1

EMPID NAME TOTAL
10037 JACK 389.11
10046 JOHN 372.62

Desired Result table 1

EMPID NAME TOTAL RANK
10037 JACK 389.11 1
10046 JOHN 372.62 2
roy
  • 693
  • 2
  • 11
  • If `cmd` contains an SQL query, then you could do it there, e.g., see [How to add sequence number column into result data?](https://stackoverflow.com/a/13597041/1115360). – Andrew Morton May 11 '23 at 11:35
  • @AndrewMorton , My database uses ms access can use your solution? – roy May 11 '23 at 14:45
  • Not sure if you're maybe trying to over complicate this? You're already returning an ordered data table from your db based on Total, you're already adding a column to it for the Rank. Why not just iterate the publictable rows and populate the rank? Although I would suggest as rank id based on the record order of Total, having a specific field for Rank is probably in some ways asking for trouble – Hursey May 11 '23 at 20:32
  • @Hursey , `Why not just iterate the publictable rows and populate the rank? ` What you mean like how I can implement as per your recommendation – roy May 12 '23 at 15:08
  • Using a `For Each` loop you can iterate over the publictable.Rows collection and update the field values directly via row.Item("Rank"). Plenty of examples around for that – Hursey May 14 '23 at 20:28

0 Answers0