Solved

Filter DataSet / DataTable

Posted on 2010-11-21
4
825 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

How To Reduce Deployment Times With Pre-Baked AMIs

Even if we can't include all the files in the base image, we can sometimes include some of the larger files that we would otherwise have to download, and we can also sometimes remove the most time-consuming steps. This can help a lot with reducing deployment times.

Question has a verified solution.

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

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…
The Fluent Interface Design Pattern You can use the Fluent Interface (http://en.wikipedia.org/wiki/Fluent_interface) design pattern to make your PHP code easier to read and maintain.  "Fluent Interface" is an object-oriented design pattern that r…
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…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

626 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