Solved

Filter DataSet / DataTable

Posted on 2010-11-21
4
819 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
  • 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 Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Before You Read The Article Please make sure you understand these two concepts: Variable Scope (http://www.php.net/manual/en/language.variables.scope.php) and Property Visibility (http://www.php.net/manual/en/language.oop5.visibility.php).  And to …
Software development teams often use in-memory caches to improve performance. They want to speed up access to, or reduce load on, a backing store (database, file system, etc.) by keeping some or all of the data in memory.   You should implement a …
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
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…

895 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now