-
Searhing a dataset
I have a dataset with employee names and projects they worked on. So an employee might have 10 rows with there name in it and all different project number in these rows
How do i filter the data to only include one employees rows?
Can I filter the rows using two different requirements?
Like all the rows where Bob is the first name and the Day of week was wednesday?
-
You can select distinct employee to the different dataset like this
Imports System.Data
Public Class Form1
Inherits System.Windows.Forms.Form
#Region " Windows Form Designer generated code "
#End Region
Dim dsSource As DataSet
Dim dsDest As DataSet
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
dsSource = New DataSet()
dsDest = New DataSet()
' Create source table
'
Dim dt As New DataTable("Orders")
dt.Columns.Add("EmployeeID", GetType(String))
dt.Columns.Add("OrderID", GetType(Integer))
dt.Columns.Add("Amount", GetType(Decimal))
dt.Rows.Add(New Object() {"Sam", 5, 25.0})
dt.Rows.Add(New Object() {"Tom", 7, 50.0})
dt.Rows.Add(New Object() {"Sue", 9, 11.0})
dt.Rows.Add(New Object() {"Tom", 12, 7.0})
dt.Rows.Add(New Object() {"Sam", 14, 512.0})
dt.Rows.Add(New Object() {"Sue", 15, 17.0})
dt.Rows.Add(New Object() {"Sue", 22, 2.5})
dt.Rows.Add(New Object() {"Tom", 24, 3.0})
dt.Rows.Add(New Object() {"Tom", 33, 78.75})
dsSource.Tables.Add(dt)
dg.DataSource = dsSource
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
dg.DataSource = Me.SelectDistinct("DistinctEmployees", dsSource.Tables("Orders"), "EmployeeID")
End Sub
'This method copies unique values of the field that you select into a new DataTable. If the field contains
' NULL values, a record in the destination table will also contain NULL values.
Public Function SelectDistinct(ByVal TableName As String, _
ByVal SourceTable As DataTable, _
ByVal FieldName As String) As DataTable
Dim dt As New DataTable(TableName)
dt.Columns.Add(FieldName, SourceTable.Columns(FieldName).DataType)
Dim dr As DataRow, LastValue As Object
For Each dr In SourceTable.Select("", FieldName)
If LastValue Is Nothing OrElse Not ColumnEqual(LastValue, dr(FieldName)) Then
LastValue = dr(FieldName)
dt.Rows.Add(New Object() {LastValue})
End If
Next
If Not dsSource Is Nothing Then dsDest.Tables.Add(dt)
Return dt
End Function
Private Function ColumnEqual(ByVal A As Object, ByVal B As Object) As Boolean
'
' Compares two values to determine if they are equal. Also compares DBNULL.Value.
'
' NOTE: If your DataTable contains object fields, you must extend this
' function to handle the fields in a meaningful way if you intend to group on them.
'
If A Is DBNull.Value And B Is DBNull.Value Then Return True ' Both are DBNull.Value.
If A Is DBNull.Value Or B Is DBNull.Value Then Return False ' Only one is DBNull.Value.
Return A = B ' Value type standard comparison
End Function
End Class