simple field selection from table question

[BernardB]

Hi all,

Simple question about tables here.

What is the simplest way of selecting a single field from a table based on row & column numbers?
In essence what I'm trying to do is use a table as if it were a multidimensional array only than with different variable types for some of the columns in the array.
Ideally I'm looking for something like this :

myvar = mytable(X,Y)

where X and Y refer to column and row numbers.

Sounds simple enough right?
I've googled the hell out of it but I can't find it anywhere.
So I joined this forum and hope some of you can guide me in the right direction.
Anyone out there with a simple answer?

 

[jmcilhinney]

First things first, what EXACTLY do you mean by "table". Are you talking about a database table, a DataTable, a DataGridView, something else, nothing in particular? It's not really a surprise that you couldn;t find any useful information when your question is so non-specific.

 

[BernardB]

Hi mod (not sure how to address you)

Thank you for your reply.
I'm currently using a datatable.
I'm not sure if this is the correct type to use, but the web tells me I can sort on columns with datatables. (I will try this later)
Here a little bit of test code I've made to practice my skills with datatables.
(I'll give you 10 bonus points if you can see from this code what language I programmed 25 years ago)

Module Module1
    Private MijnTabel As DataTable
    Public colVoornaam As DataColumn
    Private colAchternaam As DataColumn
    Private colJarenOud As DataColumn
    Private AantalRegels As Integer

    Sub Main()
        Dim Veld1 As String
        Dim Veld2 As String
        Dim Veld3 As Integer
        'definieer tabel
        MijnTabel = New DataTable("MijnTabel")

        'definieer twee kolommen
        colVoornaam = New DataColumn("Voornaam")
        colAchternaam = New DataColumn("Achternaam")
        colJarenOud = New DataColumn("Jaren Oud")
        'ken datatype toe aan kolom
        colVoornaam.DataType = System.Type.GetType("System.String")
        colAchternaam.DataType = System.Type.GetType("System.String")
        colJarenOud.Datatype = System.Type.GetType("System.Int32")

        'koppel kolommen aan tabel
        MijnTabel.Columns.Add(colVoornaam)
        MijnTabel.Columns.Add(colAchternaam)
        MijnTabel.Columns.Add(colJarenOud)

        'stop wat onzin regels in de kolom 2 strings en 1 integer
        Dim Regel() As Object = {"Klaas", "Apenbrood", 20}
        MijnTabel.Rows.Add(Regel)
        Regel = {"Pipo", "Clown", 99}
        MijnTabel.Rows.Add(Regel)
        Regel = {"Jaap", "Shoarmatent", 18}
        MijnTabel.Rows.Add(Regel)

        'even kijken hoeveel regeltjes we ook al weer hebben
        For Each Row In MijnTabel.Rows
            AantalRegels = AantalRegels + 1
        Next

' **************** my post concerns the next statements in the for loop ***************

        For i = 0 To AantalRegels - 1
            Veld1 = MijnTabel.Rows(i, 0)
            Veld2 = MijnTabel.Rows(i)(1)
            Veld3 = MijnTabel.Rows(i)(2)
            Console.WriteLine(Veld1, Veld2, Veld3)
        Next
        Console.ReadLine()
    End Sub
End Module

 

[jmcilhinney]

There is no way to index a DataTable by row and column in one go. First you need to index the Rows collection to get a DataRow and then index that to get a field value. Your expressions for Veld2 and Veld3 are correct but the one for Veld1 is invalid. If you're really want to be able to do it that way then you could write an extension method:

Imports System.Runtime.CompilerServices

Public Module DataTableExtensions

    <Extension>
    Public Function Field(source As DataTable, columnIndex As Integer, rowIndex As Integer) As Object
        Return source.Rows(rowIndex)(columnIndex)
    End Function

    <Extension>
    Public Function Field(source As DataTable, columnName As String, rowIndex As Integer) As Object
        Return source.Rows(rowIndex)(columnName)
    End Function

    <Extension>
    Public Function Field(Of T)(source As DataTable, columnIndex As Integer, rowIndex As Integer) As T
        Return DirectCast(source.Field(columnIndex, rowIndex), T)
    End Function

    <Extension>
    Public Function Field(Of T)(source As DataTable, columnName As String, rowIndex As Integer) As T
        Return DirectCast(source.Field(columnName, rowIndex), T)
    End Function

End Module

There's a method that takes a index for both column and row and another that takes a column name and row index that both return an Object reference and then a generic version of each that allows you to specify the data type to get that type back. Sample usage:

Dim id = CInt(myDataTable.Field(0, 10))
Dim name = myDataTable.Field(Of String)("Name", 10)