Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Filter DataSet / DataTable

Posted on 2010-11-21
4
Medium Priority
?
829 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:Alfred A.
Alfred A. earned 2000 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:Alfred A.
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Dependencies in Software Design In software development, the idea of dependencies (http://en.wikipedia.org/wiki/Coupling_%28computer_programming%29) is an issue of some importance. This article seeks to explain what dependencies are and where they …
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…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

886 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