Solved

Filter DataSet / DataTable

Posted on 2010-11-21
4
824 Views
Last Modified: 2012-05-10
I have an SP that returns a dataset. The query can take some time to execute so rather than rerunning the query for each row I want to return I need to filter the dataset.
Here is an example of the table results:

buck_i      sub_buck_i     buck_totl_a
1              1                      124126833.42
2              2                      4645542.05
2              3                      90399665.40
3              6                      32333891.28
4              6                      366683.44
5              6                      10.00
3              7                      28766437.68
4              7                      315178.29
5              7                      10.00
3              8                      3567453.60
4              8                      51505.15
5              8                      0.00
6              9                      -12956303.94
7              10                      507879.75

I pass in a filter, such as "buck_i = 3 AND sub_buck_i = 8" and will need to get back the value.

I have tried the following code but the filter does not seem to work:

Public Function SelectDataSetValue(ByVal dsDataSet As DataSet, _
                                       ByVal cmdFilter As String, _
                                       Optional ByVal sField As String = "", _
                                       Optional ByVal Index As Integer = -1, _
                                       Optional ByVal IsNumber As Boolean = False) As String
        Try
            SelectDataSetValue = CStr(IIf(IsNumber, 0, ""))
            If dsDataSet.Tables.Count > 0 Then
                'Dim theDataTable As DataTable = dsDataSet.Tables(0)
                'theDataTable.Select(cmdFilter)
                Dim theDataView As DataView
                theDataView = New DataView(dsDataSet.Tables(0))
                theDataView.RowFilter = cmdFilter
                If theDataView.Table.Rows.Count > 0 Then
                    Dim theDatarow As DataRow = theDataView.Table.Rows(0)
                    Return GetDBValue(theDatarow, sField, Index, IsNumber)
                End If
            End If
        Catch ex As Exception
            Debug.Print("Error in SelectDataSetValue: " & cmdFilter & vbCrLf & _
                        "Exception: " & ex.ToString)
            LogWrite("Error in SelectDataSetValue: " & cmdFilter & vbCrLf & _
                     "Exception: " & ex.ToString)
            Throw ex
        End Try
    End Function

   Public Function GetDBValue(ByVal theDataRow As DataRow, _
                                Optional ByVal sField As String = "", _
                                Optional ByVal Index As Integer = -1, _
                                Optional ByVal IsNumber As Boolean = False) As String
        Try
            GetDBValue = ""
            ' This procedure is used to catch null values
            If IsNumber Then GetDBValue = "0"
            If sField <> "" Then
                'If theDataRow(sField).ActualSize = 0 Then Exit Function ' Test for null
                GetDBValue = theDataRow(sField).ToString
                If IsNumber And theDataRow(sField).GetType = System.Type.GetType("System.Boolean") Then GetDBValue = CStr(Math.Abs(CInt(theDataRow(sField))))
            ElseIf Index <> -1 Then
                'If theDataRow(Index).ActualSize = 0 Then Exit Function ' Test for null
                GetDBValue = theDataRow(Index).ToString
                If IsNumber And theDataRow(Index).GetType = System.Type.GetType("System.Boolean") Then GetDBValue = CStr(Math.Abs(CInt(theDataRow(Index))))
            End If
            If IsNumber And GetDBValue = "" Then GetDBValue = "0"
            Exit Function
        Catch ex As Exception
            Debug.Print("Error getting DB Value : " & sField & " : " & Err.Description)
            LogWrite("Error getting DB Value  : " & sField & " : " & Err.Description)
            Throw ex
        End Try
    End Function

0
Comment
Question by:dbasplus
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 21

Assisted Solution

by:Alfred1
Alfred1 earned 500 total points
ID: 34184863
Try doing it something like this:
Dim dt1 As DataTable
        dt1 = dsDataSet.Tables(0).Clone

        Dim dr1 As DataRow() = dsDataSet.Tables(0).Select(cmdFilter)

        For Each d1 As DataRow In dr1
            dt1.ImportRow(d1)
        Next
       
        Dim theDataView As DataView = dt1.DefaultView

Open in new window

0
 

Accepted Solution

by:
dbasplus earned 0 total points
ID: 34184895
With the help of Alfred1 I worked out that it helps to read the MS Help a little more careful on the Select method.
This "filter" method does not quite work the same way as the old VB6 version.
Here is the working code:


    Public Function SelectDataSetValue(ByVal dsDataSet As DataSet, _
                                       ByVal cmdFilter As String, _
                                       Optional ByVal sField As String = "", _
                                       Optional ByVal Index As Integer = -1, _
                                       Optional ByVal IsNumber As Boolean = False) As String
        Try
            SelectDataSetValue = CStr(IIf(IsNumber, 0, ""))
            If dsDataSet.Tables.Count > 0 Then
                Dim theDataTable As DataTable = dsDataSet.Tables(0)
                Dim theDataRows() As DataRow = theDataTable.Select(cmdFilter)
                If theDataRows.Count > 0 Then
                    Return GetDBValue(theDataRows(0), sField, Index, IsNumber)
                End If
            End If
        Catch ex As Exception
            Debug.Print("Error in SelectDataSetValue: " & cmdFilter & vbCrLf & _
                        "Exception: " & ex.ToString)
            LogWrite("Error in SelectDataSetValue: " & cmdFilter & vbCrLf & _
                     "Exception: " & ex.ToString)
            Throw ex
        End Try
    End Function
0
 
LVL 21

Expert Comment

by:Alfred1
ID: 34185016
Hi dbasplus,

I was just wondering, would it be faster if you selected your comment #34184895 as the solution and my comment #34184863 as assisted solution through direct points rewarding?  I just don't know if you can do it.  It has been a decade since I awarded points.  :-)

Anyway, if you can't do it, doing it this way is fine. Glad to assist, mate!  :-)

 
0
 

Author Closing Comment

by:dbasplus
ID: 34216261
Excellent and quick turnaround. Thanks
0

Featured Post

Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
dao vs facade design patterns 2 71
Learning About The VB.NET TableLayoutPanel Control 5 42
Difference between Leaflet and MapBox? 5 46
Datagridview column resizing 8 28
The CRUD Functions CRUD, meaning "Create, Read, Update, Delete (http://en.wikipedia.org/wiki/Create,_read,_update_and_delete)" is a common term to data base developers.  It describes the essential functions of data base table maintenance.  This art…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question