none
Finding The DataTable Row Index RRS feed

  • Question

  • Hi:

    I have a DataTable "dtSE" that supplies data to a form using a BindingSource.

    Each time that I move to a different row using the navigation buttons, I want 

    to retrieve the index number of the row in the DataTable, but not the position

    in the BindingSource. Can anyone tell me how to do this?

    Thanks

    Wednesday, July 10, 2019 8:22 PM

Answers

  • Okay, the following code sample loads data from MS-Access rather than mocked up as per my former replies.

    Full source is here

    https://github.com/karenpayneoregon/WorkingWithAccessDatabases/tree/master/RowIndexExampleSpecial

    Data class

    Imports System.Data.OleDb
    
    Public Class DatabaseOperations
        '
        Private ConnectionString As String =
                    "Provider=Microsoft.ACE.OLEDB.12.0;" &
                   $"Data Source={AppDomain.CurrentDomain.BaseDirectory}\Database1.accdb"
        Public Function LoadCustomers() As DataTable
    
            Using cn As New OleDbConnection(ConnectionString)
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText = <SQL>
                        SELECT 
                            C.Identifier, 
                            C.CompanyName, 
                            CT.ContactTitleId, 
                            CT.Title, 
                            C.Country
                        FROM 
                            ContactTitle AS CT 
                        INNER JOIN 
                            Customers AS C ON CT.ContactTitleId = C.ContactTitleId
                        ORDER BY 
                            CompanyName;
                        </SQL>.Value
    
                    Dim dt As New DataTable With {.TableName = "Customer"}
    
    
                    cn.Open()
                    dt.Load(cmd.ExecuteReader)
                    Return dt
    
                End Using
            End Using
        End Function
    End Class
    

    Form code

    Public Class Form1
        Private bs As New BindingSource
        Private Sub Form1_Shown(sender As Object, e As EventArgs) Handles Me.Shown
            Dim ops As New DatabaseOperations
            bs.DataSource = ops.LoadCustomers
    
            CompanyTextBox.DataBindings.Add("Text", bs, "CompanyName")
            CountryTextBox.DataBindings.Add("Text", bs, "Country")
        End Sub
        Private Sub MoveFirstButton_Click(sender As Object, e As EventArgs) _
            Handles MoveFirstButton.Click
    
            bs.MoveFirst()
    
        End Sub
        Private Sub MovePriorButton_Click(sender As Object, e As EventArgs) _
            Handles MovePriorButton.Click
    
            bs.MovePrevious()
    
        End Sub
        Private Sub MoveNextButton_Click(sender As Object, e As EventArgs) _
            Handles MoveNextButton.Click
    
            bs.MoveNext()
    
        End Sub
        Private Sub MoveLastButton_Click(sender As Object, e As EventArgs) _
            Handles MoveLastButton.Click
    
            bs.MoveLast()
    
        End Sub
        Private Function GetRowIndex() As Integer
            If bs.Current IsNot Nothing Then
                Dim row As DataRow = CType(bs.Current, DataRowView).Row
                Dim index As Integer = CType(bs.DataSource, DataTable).Rows.IndexOf(row)
                Return index
            Else
                Return -1
            End If
        End Function
    
        Private Sub CurrentIndexButton_Click(sender As Object, e As EventArgs) _
            Handles CurrentIndexButton.Click
    
            MessageBox.Show($"Current DataRow Index is {GetRowIndex()}")
    
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, July 10, 2019 11:50 PM
    Moderator

All replies

  • Maybe add a special column to Data Table that contains the ordinal number, and adjust the code that fills the table?

    Wednesday, July 10, 2019 8:34 PM
  • Hi:

    I have a DataTable "dtSE" that supplies data to a form using a BindingSource.

    Each time that I move to a different row using the navigation buttons, I want 

    to retrieve the index number of the row in the DataTable, but not the position

    in the BindingSource. Can anyone tell me how to do this?

    Thanks

    Hi

    Give this a try. It will return the actaul index as per initiall filling of data source.

    Function GetTrueIndex() As Integer
    	Return CType(DataGridView1.DataSource, DataTable).Rows.IndexOf(CType(DataGridView1.CurrentRow.DataBoundItem, DataRowView).Row)
    End Function
    


    Regards Les, Livingston, Scotland

    Wednesday, July 10, 2019 8:46 PM
  • Hello,

    The following is devoid of a primary key but works the same with a primary key. Sort the DataGridView and we get the ordinal as defined in the DataTable not the sort nor the BindingSource position.

    Public Class Form1
        Private bs As New BindingSource
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            Dim row As DataRow = CType(bs.Current, DataRowView).Row
            Dim index As Integer = CType(bs.DataSource, DataTable).Rows.IndexOf(row)
            MessageBox.Show(index.ToString())
        End Sub
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim dt As New DataTable
            dt.Columns.Add(New DataColumn() With
                              {.ColumnName = "FirstName", .DataType = GetType(String)})
            dt.Columns.Add(New DataColumn() With
                              {.ColumnName = "LastName", .DataType = GetType(String)})
    
            dt.Rows.Add("Jim", "Dean")
            dt.Rows.Add("Mike", "Smith")
            dt.Rows.Add("Bill", "Adams")
    
            bs.DataSource = dt
            DataGridView1.DataSource = bs
        End Sub
    End Class


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, July 10, 2019 8:49 PM
    Moderator
  • I am using textboxes rather than a datagridview.
    Wednesday, July 10, 2019 9:04 PM
  • I am using textboxes rather than a datagridview.
    I am out of here .................

    Regards Les, Livingston, Scotland

    Wednesday, July 10, 2019 9:15 PM
  • I am using textboxes rather than a datagridview.

    Here you go, TextBoxes only

    Public Class Form1
        Private bs As New BindingSource
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            Dim row As DataRow = CType(bs.Current, DataRowView).Row
            Dim index As Integer = CType(bs.DataSource, DataTable).Rows.IndexOf(row)
            MessageBox.Show(index.ToString())
        End Sub
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim dt As New DataTable
            dt.Columns.Add(New DataColumn() With
                              {.ColumnName = "FirstName", .DataType = GetType(String)})
            dt.Columns.Add(New DataColumn() With
                              {.ColumnName = "LastName", .DataType = GetType(String)})
    
            dt.Rows.Add("Jim", "Dean")
            dt.Rows.Add("Mike", "Smith")
            dt.Rows.Add("Bill", "Adams")
    
            bs.DataSource = dt
            BindingNavigator1.BindingSource = bs
            FirstNameTextBox.DataBindings.Add("Text", bs, "FirstName")
            LastTextBox.DataBindings.Add("Text", bs, "LastName")
    
        End Sub
    End Class


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, July 10, 2019 9:16 PM
    Moderator
  • For with buttons not a BindingNavigator

    Public Class Form1
        Private bs As New BindingSource
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            Dim row As DataRow = CType(bs.Current, DataRowView).Row
            Dim index As Integer = CType(bs.DataSource, DataTable).Rows.IndexOf(row)
            MessageBox.Show(index.ToString())
        End Sub
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim dt As New DataTable
            dt.Columns.Add(New DataColumn() With
                              {.ColumnName = "FirstName", .DataType = GetType(String)})
            dt.Columns.Add(New DataColumn() With
                              {.ColumnName = "LastName", .DataType = GetType(String)})
    
            dt.Rows.Add("Jim", "Dean")
            dt.Rows.Add("Mike", "Smith")
            dt.Rows.Add("Bill", "Adams")
    
            bs.DataSource = dt
    
            FirstNameTextBox.DataBindings.Add("Text", bs, "FirstName")
            LastTextBox.DataBindings.Add("Text", bs, "LastName")
    
        End Sub
    
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            bs.MoveFirst()
        End Sub
    
        Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
            bs.MoveNext()
        End Sub
    
        Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
            bs.MovePrevious()
        End Sub
    
        Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
            bs.MoveLast()
        End Sub
    End Class


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, July 10, 2019 9:46 PM
    Moderator
  • Hi:

    My DataTable is "dtSE" and my BindindSource is "bsSE" but I cannot get it to work.

    The datatable is created from an MS Access table as opposed to being created on

    the fly like the sample code. Would that make a difference?

     Dim row As DataRow = CType(bsSE.Current, DataRowView).Row
     Dim index As Integer = CType(bsSE.DataSource, DataTable).Rows.IndexOf(row)





    Wednesday, July 10, 2019 9:50 PM
  • Thanks Les.
    Wednesday, July 10, 2019 9:51 PM
  • Okay, the following code sample loads data from MS-Access rather than mocked up as per my former replies.

    Full source is here

    https://github.com/karenpayneoregon/WorkingWithAccessDatabases/tree/master/RowIndexExampleSpecial

    Data class

    Imports System.Data.OleDb
    
    Public Class DatabaseOperations
        '
        Private ConnectionString As String =
                    "Provider=Microsoft.ACE.OLEDB.12.0;" &
                   $"Data Source={AppDomain.CurrentDomain.BaseDirectory}\Database1.accdb"
        Public Function LoadCustomers() As DataTable
    
            Using cn As New OleDbConnection(ConnectionString)
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText = <SQL>
                        SELECT 
                            C.Identifier, 
                            C.CompanyName, 
                            CT.ContactTitleId, 
                            CT.Title, 
                            C.Country
                        FROM 
                            ContactTitle AS CT 
                        INNER JOIN 
                            Customers AS C ON CT.ContactTitleId = C.ContactTitleId
                        ORDER BY 
                            CompanyName;
                        </SQL>.Value
    
                    Dim dt As New DataTable With {.TableName = "Customer"}
    
    
                    cn.Open()
                    dt.Load(cmd.ExecuteReader)
                    Return dt
    
                End Using
            End Using
        End Function
    End Class
    

    Form code

    Public Class Form1
        Private bs As New BindingSource
        Private Sub Form1_Shown(sender As Object, e As EventArgs) Handles Me.Shown
            Dim ops As New DatabaseOperations
            bs.DataSource = ops.LoadCustomers
    
            CompanyTextBox.DataBindings.Add("Text", bs, "CompanyName")
            CountryTextBox.DataBindings.Add("Text", bs, "Country")
        End Sub
        Private Sub MoveFirstButton_Click(sender As Object, e As EventArgs) _
            Handles MoveFirstButton.Click
    
            bs.MoveFirst()
    
        End Sub
        Private Sub MovePriorButton_Click(sender As Object, e As EventArgs) _
            Handles MovePriorButton.Click
    
            bs.MovePrevious()
    
        End Sub
        Private Sub MoveNextButton_Click(sender As Object, e As EventArgs) _
            Handles MoveNextButton.Click
    
            bs.MoveNext()
    
        End Sub
        Private Sub MoveLastButton_Click(sender As Object, e As EventArgs) _
            Handles MoveLastButton.Click
    
            bs.MoveLast()
    
        End Sub
        Private Function GetRowIndex() As Integer
            If bs.Current IsNot Nothing Then
                Dim row As DataRow = CType(bs.Current, DataRowView).Row
                Dim index As Integer = CType(bs.DataSource, DataTable).Rows.IndexOf(row)
                Return index
            Else
                Return -1
            End If
        End Function
    
        Private Sub CurrentIndexButton_Click(sender As Object, e As EventArgs) _
            Handles CurrentIndexButton.Click
    
            MessageBox.Show($"Current DataRow Index is {GetRowIndex()}")
    
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, July 10, 2019 11:50 PM
    Moderator
  • Hi:

    Once again thank you so much for your help.

    Call me Columbo, but I have one more question. My form has a tab control with just two tab pages.

    There are labels and textboxes on both tab pages that are bound to a datatable. On occasion I need

    to use  a value from one of the textboxes on tabpage #2 when tabpage #1 is active. The value is not

    accessible as tabpage #2 is not active. My question: Is there any way  to make the tabpage #2 values

    available without making tabpage #2 active, that is, loading it into a string or integer variable?

    Thanks

    Thursday, July 11, 2019 6:54 PM
  • Hi:

    Once again thank you so much for your help.

    Call me Columbo, but I have one more question. My form has a tab control with just two tab pages.

    There are labels and textboxes on both tab pages that are bound to a datatable. On occasion I need

    to use  a value from one of the textboxes on tabpage #2 when tabpage #1 is active. The value is not

    accessible as tabpage #2 is not active. My question: Is there any way  to make the tabpage #2 values

    available without making tabpage #2 active, that is, loading it into a string or integer variable?

    Thanks

    Hello,

    Please close out this question on the row index, mark the reply the resolved this then ask the new question in a new post. 


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, July 11, 2019 7:03 PM
    Moderator
  • Sorry had a brain fart.
    Thursday, July 11, 2019 7:05 PM