Solved

VB.NET Query Dataset

Posted on 2004-08-30
16
1,035 Views
Last Modified: 2010-01-25
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
Comment
Question by:Vartana
  • 7
  • 6
  • 2
  • +1
16 Comments
 
LVL 7

Expert Comment

by:ramesh12
ID: 11936905
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
 
LVL 1

Author Comment

by:Vartana
ID: 11936922
the whole problem is i dont know how the syntex of my condition should be.
0
 
LVL 1

Author Comment

by:Vartana
ID: 11937372
I need to query and pull only duplicate rows
0
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
LVL 37

Expert Comment

by:gregoryyoung
ID: 11938019
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
 
LVL 18

Expert Comment

by:DotNetLover_Baan
ID: 11938059
0
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 11938257
baan: on a GROUP BY ?
0
 
LVL 18

Expert Comment

by:DotNetLover_Baan
ID: 11938309
not really... just in case he needs some more to do... these links are really useful... :)
-Baan
0
 
LVL 1

Author Comment

by:Vartana
ID: 11958528
I want to know if i have any duplicate rows
How can i do this ?????
0
 
LVL 18

Expert Comment

by:DotNetLover_Baan
ID: 11958555
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
 
LVL 1

Author Comment

by:Vartana
ID: 11958562
all column values
0
 
LVL 18

Accepted Solution

by:
DotNetLover_Baan earned 500 total points
ID: 11958887
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
 
LVL 1

Author Comment

by:Vartana
ID: 11958990
i need to be able to check if the table is empty first how would i do that ?
0
 
LVL 18

Expert Comment

by:DotNetLover_Baan
ID: 11959045
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
 
LVL 18

Expert Comment

by:DotNetLover_Baan
ID: 11959102
is everything alright now ??  did you use my latest post ?
-Baan
0
 
LVL 1

Author Comment

by:Vartana
ID: 11959250
no i modified your first one it works like a charm thank you.
0
 
LVL 18

Expert Comment

by:DotNetLover_Baan
ID: 11959284
good to hear that... good luck...
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

785 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