Get specific row value from DataTable

Question

Hey there, I have a problem trying to get an specific row from a table. I'm using a DataSet that has several nums of tables created in runtime, but I need to get an specific row from each table, I'm using this code:

Dim TablesResult As New DataSet

'then I call the function to store the tables into the dataset

For Each Table As DataTable In TablesResult.Tables

Dim Generic As Integer = CInt(Int((Table.Rows.Count * Rnd()) + 1))

'And then here is where I need to select the specific row and get the value,

'the tables from the dataset just have one column!,

Next

Please help! Thanks!

 

Answer 1

Can you be a bit more specific about what these tables are containing.  

How are you intending to identify the specific row to get the value.   Does it have an Unique ID in a specific field ?.  

Are all the tables the same ?

 

If your simply talking about an ordinal position row then the following sample might work or give you an idea.  The function is in bold and will return and object but you can modify this to return back the specific dataType.

Public Class Form1
    Dim dt As New DataTable
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        dt.Columns.Add("ID")
        dt.Rows.Add(1)
        dt.Rows.Add(2)
        dt.Rows.Add(19)
        dt.Rows.Add(4)
        dt.Rows.Add(5)
        dt.Rows.Add(99)
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        MsgBox(CType(GetSpecificRow(3, dt, "ID"), Integer).ToString)
        MsgBox(CType(GetSpecificRow(6, dt, "ID"), Integer).ToString)

        MsgBox(CType(GetSpecificRow(100, dt, "ID"), Integer).ToString)

    End Sub

    ''' <summary>
    ''' Return a field at a specific ordinal row in a datatable
    ''' </summary>
    ''' <param name="x"></param>
    ''' <param name="dt"></param>
    ''' <param name="Field"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetSpecificRow(ByVal x As Integer, ByVal dt As DataTable, ByVal Field As String) As Object
        Dim i As Integer = 1
        For Each obj As Object In dt.Rows
            If i = x Then
                Return obj(Field)
            End If
            i = i + 1
        Next
        Throw New Exception("Invalid Row")
    End Function
End Class

 

Answer 2

Hi there.

What are you using the dataset for.

To reference a particular row for a table its

Table.row(rownumber).item(itemnumber)

I'm really rusty on datasets but I think it's like this:

Dataset.Table(tablenumber).row(rownumber).item(itemnumber)

The number can also be strings resprenting names but my guess is that would be a little slower than indexes.

Answer 3

Hey there, thanks for the reply, I used your code before but the problem is that I'm trying to convert it to string and it shows an error, the tables I'm using done have a key column so I can't get the row using the

datatable.rows.find(

so I have to get the value from the row with other method, all the tables has the same columnName and the tablesname are deferent, the the within the tables are simple strings, so I need to get a ramdon row for each table, I hope this helps, I have tried everything and anything works!!. thanks!!.

the other way is creating a column key that I think is easier and then just apply the find method but I don't know how to create it.

Answer 4

Sure makes things simpler, I'd had a complete brain freeze there.  

The x-1 is because the rows is a zero based collection and I'm using a 1 based index.   So when I want first item it will get element 0.   Thats just my implementation - you could keep it all zero based if you want to.

 

 

    ''' <summary>
    ''' Return a field at a specific ordinal row in a datatable
    ''' </summary>
    ''' <param name="x"></param>
    ''' <param name="dt"></param>
    ''' <param name="Field"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetSpecificRow(ByVal x As Integer, ByVal dt As DataTable, ByVal Field As String) As Object
        Try
            Return dt.Rows(x - 1).Item(Field)
        Catch ex As Exception
            Throw New Exception("Invalid Row")
        End Try
    End Function

Answer 5

So your telling me your using strings.  I've changed the column so that its storing string and returns a string - so you get a random integer number and then use this in the call to the method.   This requires no use of find method and would simply retrieve the string on the row that you request.

 

Public Class Form1
    Dim dt As New DataTable
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        dt.Columns.Add("ID")
        dt.Rows.Add("test1")
        dt.Rows.Add("test2")
        dt.Rows.Add("testabc")
        dt.Rows.Add("4")
        dt.Rows.Add("item5")
        dt.Rows.Add("item 6 but show 99")
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        MsgBox(GetSpecificRow(1, dt, "ID").ToString) '1st value
        MsgBox(GetSpecificRow(4, dt, "ID").ToString) '4th value

        'MsgBox(GetSpecificRow(100, dt, "ID").ToString) '//Intentional Exception

    End Sub

''' <summary>
''' Return a field at a specific ordinal row in a datatable
''' </summary>
''' <param name="x"></param>
''' <param name="dt"></param>
''' <param name="Field"></param>
''' <returns></returns>
''' <remarks></remarks>
    Function GetSpecificRow(ByVal x As Integer, ByVal dt As DataTable, ByVal Field As String) As String
        Try
            Return dt.Rows(x - 1).Item(Field)
        Catch ex As Exception
            Throw New Exception("Invalid Row")
        End Try
    End Function
End Class

Answer 6

I got your point, and it's pretty helpfull but my problem is now that I need to convert the value to a string cause I need the text witthin the field selected

thanks

Answer 7

Hey there, I got your point then  have another problem, the tables has more than 100 rows!!

 

Answer 8

Ccercone

I can't tell who you are asking...

Answer 9

Hey there, how can I create an autonumeric column in a database?

I know it's like

DataTable.Columns.Add("ColumnName", "and here the type")

is there any type that I could use for an autonumeric one?

thanks!!.

Answer 10

OK this ones a completely different issue.    I would suggest creating a separate post for this issue so as to not confuse issues and to make the title relevent to the actual post.

 

Answer 11

And....

The table has more than 100 rows.  What issue does that cause - tables can have millions of rows depending upon your database.

 

Answer 12

The examples show one which uses string and the value returned are strings.   The original intention with using Object was I could return any data from any field type as and object and cast it to an appropriate type when it was returned from the function.

 

Answer 13

Hey there, I got the answer thanks for all you Help!!, the code is something like this:

 

For Each Table as DataTable in DataTableCollection.Tables

        Dim GenericValue as Integer = CInt(Int(Table.Rows.Count * Rnd()))    

        MsgBox(cType(Table.Rows(GenericValue).Item(0), Object).toString)

Next

 

Thanks for all !!!   :D