kinton
asked on
Visual Basic Net Datatable filter by column
Hi,
I am being passed a data table. The data table has 1 to n columns in it. I know that will have three specific columns, i..e. firename, Surname and address. I want to create a second dataset or limit the first data set to only include those columns. NOTE: I the dataset being passed to me I do not know how many or what the other columns are. What is the best way to create this row filter?
I am being passed a data table. The data table has 1 to n columns in it. I know that will have three specific columns, i..e. firename, Surname and address. I want to create a second dataset or limit the first data set to only include those columns. NOTE: I the dataset being passed to me I do not know how many or what the other columns are. What is the best way to create this row filter?
What is you datatable (sql, access, etc)? Or is a system datatable ?
ASKER
Its a datatable object is Visual Basic 2005
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Dim dt As New DataTable
Dim index As Integer = 0
' Copy from other datatable
dt = myDataSet.Tables(0)
Dim total As Integer = dt.Columns.Count
For x As Integer = 0 To dt.Columns.Count - 1
If x >= (total + index) Then Exit For
If dt.Columns(x - index).ColumnName <> "firename" And dt.Columns(x - index).ColumnName <> "Surname" And dt.Columns(x - index).ColumnName <> "address" Then
dt.Columns.Remove(dt.Colum ns(x - index))
total -= 1
index += 1
End If
Next
myobject.DataSource = dt.DefaultView
Dim index As Integer = 0
' Copy from other datatable
dt = myDataSet.Tables(0)
Dim total As Integer = dt.Columns.Count
For x As Integer = 0 To dt.Columns.Count - 1
If x >= (total + index) Then Exit For
If dt.Columns(x - index).ColumnName <> "firename" And dt.Columns(x - index).ColumnName <> "Surname" And dt.Columns(x - index).ColumnName <> "address" Then
dt.Columns.Remove(dt.Colum
total -= 1
index += 1
End If
Next
myobject.DataSource = dt.DefaultView
jpaulino
dt = myDataSet.Tables(0)
won't COPY from the other datatable. It IS the other datatable. Just to demonstrate, try this. One form, two datagridviews, one button, this code
Public Class Form1
Private dt1 As New DataTable
Private dt2 As New DataTable
Private Sub filltables()
Dim dc0 As New DataColumn("ID", GetType(Integer))
dt1.Columns.Add(dc0)
Dim dc1 As New DataColumn("Item", GetType(String))
dt1.Columns.Add(dc1)
dt2 = dt1
End Sub
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
filltables()
DataGridView1.DataSource = dt1
DataGridView2.DataSource = dt2
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
dt2.Columns.RemoveAt(0)
End Sub
End Class
You'll notice that when you click on the button to remove a column from dt2, it goes from dt1 as well.
It may be that the old datatable can be altered in this way, but if so there's no need for a second one. If there is to be a second one as a copy then it will be necessary to use DataTable.Copy.
But I still think
dim dv as new dataview(oldtable)
dim newtable as datatable = dv.ToTable("NewTable", False, New String() {"firename", "Surname", "Address"})
or some such would be neater ;-)
Roger
dt = myDataSet.Tables(0)
won't COPY from the other datatable. It IS the other datatable. Just to demonstrate, try this. One form, two datagridviews, one button, this code
Public Class Form1
Private dt1 As New DataTable
Private dt2 As New DataTable
Private Sub filltables()
Dim dc0 As New DataColumn("ID", GetType(Integer))
dt1.Columns.Add(dc0)
Dim dc1 As New DataColumn("Item", GetType(String))
dt1.Columns.Add(dc1)
dt2 = dt1
End Sub
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
filltables()
DataGridView1.DataSource = dt1
DataGridView2.DataSource = dt2
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
dt2.Columns.RemoveAt(0)
End Sub
End Class
You'll notice that when you click on the button to remove a column from dt2, it goes from dt1 as well.
It may be that the old datatable can be altered in this way, but if so there's no need for a second one. If there is to be a second one as a copy then it will be necessary to use DataTable.Copy.
But I still think
dim dv as new dataview(oldtable)
dim newtable as datatable = dv.ToTable("NewTable", False, New String() {"firename", "Surname", "Address"})
or some such would be neater ;-)
Roger
ASKER
Perfect. Thank you
ASKER