• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1073
  • Last Modified:

VB.NET Query Dataset

I have a dataset called "MyDataset"
I have a table in the dataset called "RamTable"

i need to run a query  that will group the columns names ( "Account" , "PaymentType", "PaymentAmount", "CheckNumber" )
and then anything that has a count greater than 1...

so then i will have a  Dim TempTable as DataTable = value of query


Then i want to loop thru it

loop temptable
    msgbox Field Account
0
Vartana
Asked:
Vartana
  • 7
  • 6
  • 2
  • +1
1 Solution
 
ramesh12Commented:
Use dataview like this


dim dv as  Dataview =new dataview(Mydataset.tables("RamTable"))

now apply yor filter like this

dv.rowfilter= your condition goes here


now you will have the dv with filtered rows. you can loop through the dv like this

for counter= 0 to dv.count -1
  s=dv.item(counter).item(your field name here).tostring
next
0
 
VartanaAuthor Commented:
the whole problem is i dont know how the syntex of my condition should be.
0
 
VartanaAuthor Commented:
I need to query and pull only duplicate rows
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
gregoryyoungCommented:
I believe this explains it all http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q326145 (C#) http://support.microsoft.com/default.aspx?scid=kb;EN-US;325685 (vb.net)

it also gives well tested code you can plugin ... the helpers described throughout the series are quite usefull.
0
 
gregoryyoungCommented:
baan: on a GROUP BY ?
0
 
DotNetLover_BaanCommented:
not really... just in case he needs some more to do... these links are really useful... :)
-Baan
0
 
VartanaAuthor Commented:
I want to know if i have any duplicate rows
How can i do this ?????
0
 
DotNetLover_BaanCommented:
do you have a unique key data field? if yes... just check for that if it repeats....  or do you want to check if all the column values are repeated ??
-Baan
0
 
VartanaAuthor Commented:
all column values
0
 
DotNetLover_BaanCommented:
Hi again...
well there is no direct method to do that.... have to loop through the rows in a table and remove... here is a method I just wrote... you may try this...

    Sub RemoveDulicateRow(ByVal ds As DataSet)
        Dim Found As Boolean = False
        Dim CheckingRowIndx, CurrRowIndx As Int32
        Dim CheckingRow, CurrRow As String
        For CheckingRowIndx = 0 To ds.Tables(0).Rows.Count - 2
            CheckingRow = ""
            CurrRow = ""
            Dim i As Int32
            For i = 0 To ds.Tables(0).Columns.Count - 1
                CheckingRow &= RTrim(ds.Tables(0).Rows(CheckingRowIndx)(i).ToString)
            Next
            For CurrRowIndx = CheckingRowIndx + 1 To ds.Tables(0).Rows.Count - 1
                For i = 0 To ds.Tables(0).Columns.Count - 1
                    CurrRow &= RTrim(ds.Tables(0).Rows(CurrRowIndx)(i).ToString)
                Next
                If CheckingRow = CurrRow Then
                    Found = True
                    Exit For
                Else
                    Found = False
                End If
            Next
            If Found = True Then
                ds.Tables(0).Rows.RemoveAt(CurrRowIndx)
                RemoveDulicateRow(ds)    'Recursive calling
            End If
        Next
    End Sub

Call it and pass your dataset into it. If the table is not Table(0), change it accordingly... :))

-Baan
0
 
VartanaAuthor Commented:
i need to be able to check if the table is empty first how would i do that ?
0
 
DotNetLover_BaanCommented:
the last post was not currect.... need to change...

    Sub RemoveDulicateRow(ByVal ds As DataSet)
        Dim Found As Boolean = False
        Dim CheckingRowIndx, CurrRowIndx As Int32
        Dim CheckingRow, CurrRow As String
here:   For CheckingRowIndx = 0 To ds.Tables(0).Rows.Count - 2
            CheckingRow = ""
            CurrRow = ""
            Dim i As Int32
            For i = 0 To ds.Tables(0).Columns.Count - 1
                CheckingRow &= RTrim(ds.Tables(0).Rows(CheckingRowIndx)(i).ToString)
            Next
            For CurrRowIndx = (CheckingRowIndx + 1) To ds.Tables(0).Rows.Count - 1
                For i = 0 To ds.Tables(0).Columns.Count - 1
                    CurrRow &= RTrim(ds.Tables(0).Rows(CurrRowIndx)(i).ToString)
                Next
                If CheckingRow = CurrRow Then
                    Found = True
                    Exit For
                Else
                    CurrRow = ""
                End If
            Next
            If Found = True Then
                ds.Tables(0).Rows.RemoveAt(CurrRowIndx)
                Found = False
                GoTo here
            End If
        Next
    End Sub

then to check if the table is not empty...
      If ds.Tables(0).Rows.Count = 0 Then
          'Table has no data
      Else
          'Call the method...
          RemoveDulicateRow(ds)
      End If


-Baan
0
 
DotNetLover_BaanCommented:
is everything alright now ??  did you use my latest post ?
-Baan
0
 
VartanaAuthor Commented:
no i modified your first one it works like a charm thank you.
0
 
DotNetLover_BaanCommented:
good to hear that... good luck...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 7
  • 6
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now