Solved

VB.NET Query Dataset

Posted on 2004-08-30
16
1,028 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

932 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now