Getting duplicated values....
Getting duplicated values....
Getting duplicated values....
Getting duplicated values....
Getting duplicated values....
Getting duplicated values.... Getting duplicated values.... Getting duplicated values.... Getting duplicated values.... Getting duplicated values.... Getting duplicated values.... Getting duplicated values.... Getting duplicated values....
Getting duplicated values.... Getting duplicated values....
Getting duplicated values....
Go Back  Xtreme Visual Basic Talk > > > Getting duplicated values....


Reply
 
Thread Tools Display Modes
  #1  
Old 07-03-2013, 04:42 AM
Glenvn Glenvn is offline
Regular
 
Join Date: Jun 2003
Location: South Africa
Posts: 57
Default Getting duplicated values....


Dear All,

I have the following:

Database: MobData
Number of Tables: 20

All of the tables have the field "MobileVesselNo"
I want to loop through each Table, only getting the values of "MobileVesselNo"
My code is as follows:

Code:
 Sub loopDB()

        con.Open()

        Dim da As New OleDbDataAdapter()
        Dim schemaTable As DataTable
        Dim r As DataRow

        schemaTable = con.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})

        Dim i As Integer

        For i = 0 To schemaTable.Rows.Count - 1

            Dim pTableName As String = schemaTable.Rows(i)!TABLE_NAME.ToString

            da.SelectCommand = New OleDbCommand("select MobileVesselNo from " & pTableName, con)

            da.Fill(schemaTable)

            For Each r In schemaTable.Rows

                ComboBox1.Items.Add(r("MobileVesselNo").ToString)

            Next

        Next

            con.Close()

    End Sub

I am getting the data BUT it is repeated 20 times with open spaces in between.

It loops every table and adds it separately to the combobox.

The very last grouping has all the data from above. I am only interested in this.

Any Ideas?
Reply With Quote
  #2  
Old 07-03-2013, 09:29 AM
DrPunk's Avatar
DrPunkGetting duplicated values.... DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

I'm not too sure what you're after.

Are you saying you only want unique MobileVesselNos in the combobox?

Depends how you want to do it, but you could do it in one big UNION query of all the tables. UNION will remove duplicates.
Code:
dim query as string = ""
For i = 0 To schemaTable.Rows.Count - 1
    
    Dim pTableName As String = schemaTable.Rows(i)!TABLE_NAME.ToString

    query &= "SELECT MobileVesselNo FROM " & pTableName

    ' If it's not the last table in the list then add UNION to the query
    if i < schemaTable.Rows.Count - 1 Then
        query &= vbcrlf & "UNION" & vbcrlf
    end if
Next

' Then run that query and you should have all the unique MobileVesselNos from all the tables.
Hopefully that's right. I might have got something wrong building the query.
__________________
There are no computers in heaven!
Reply With Quote
  #3  
Old 07-04-2013, 01:33 AM
Glenvn Glenvn is offline
Regular
 
Join Date: Jun 2003
Location: South Africa
Posts: 57
Cool Works!

Thank You DrPunk! - This is what I want. I am now getting the MobileVesselNo from each table added to the combobox!

I changed the code for adding to the ComboBox:

Code:
   Dim schemaTable As DataTable

        con.Open()

        schemaTable = con.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})

        Dim query As String = ""

        For i = 0 To schemaTable.Rows.Count - 1

            Dim pTableName As String = schemaTable.Rows(i)!TABLE_NAME.ToString

            query &= "SELECT MobileVesselNo FROM " & pTableName

            ' If it's not the last table in the list then add UNION to the query
            If i < schemaTable.Rows.Count - 1 Then
                query &= vbCrLf & "UNION" & vbCrLf
            End If

        Next


        Dim cmd As New OleDbCommand
        cmd.Connection = con
        cmd.CommandText = query


        Dim dr As OleDbDataReader = cmd.ExecuteReader

        While dr.Read
            ComboBox1.Items.Add(dr.Item(0))
        End While
        dr.Close()


        con.Close()

Seems to be working fine!
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Getting duplicated values....
Getting duplicated values....
Getting duplicated values.... Getting duplicated values....
Getting duplicated values....
Getting duplicated values....
Getting duplicated values.... Getting duplicated values.... Getting duplicated values.... Getting duplicated values.... Getting duplicated values.... Getting duplicated values.... Getting duplicated values....
Getting duplicated values....
Getting duplicated values....
 
Getting duplicated values....
Getting duplicated values....
 
-->