Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Filter DataSet / DataTable

Posted on 2010-11-21
4
Medium Priority
?
827 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 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: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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

721 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