Solved

How to sort data in a string?

Posted on 2011-02-13
10
279 Views
Last Modified: 2012-05-11
Hello,

I have an ACCESS table with duplicate data in a string, for example:

ROW1: USA,BEL,N;D,CZE,FRA
ROW2: GBR,CZE,BEL,USA,NOR
ROW3: BEL,FRA,USA,DNK,CAN

How do you loop through the rows and sort the countries?

Thanks,

Victor

0
Comment
Question by:vcharles
  • 4
  • 4
  • 2
10 Comments
 
LVL 42

Expert Comment

by:sedgwick
ID: 34882635
0
 

Author Comment

by:vcharles
ID: 34882668
Hello,

I found the example below from the link, but unfortunately I can't figure out how to integrate it with my existing project.

Further below is the code that I'm using to delete duplicate data in the row, can you help me include the example as part of my code?


For lLoop = 0 To UBound(MyArray)

       For lLoop2 = lLoop To UBound(MyArray)

            If UCase(MyArray(lLoop2)) < UCase(MyArray(lLoop)) Then

                str1 = MyArray(lLoop)

                str2 = MyArray(lLoop2)

                MyArray(lLoop) = str2

                MyArray(lLoop2) = str1

            End If

        Next lLoop2

    Next lLoop


Existing Code:

 Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\aopt2002org.mdb;Persist Security Info=True;Jet OLEDB:Database Password=testaopupdate"

        Dim objConnection As New OleDb.OleDbConnection(ConnectionString)
        objConnection.Open()

        'Dim da As New OleDb.OleDbDataAdapter("Select NIIN, code3 from IDENTIFICATION_DATA", objConnection)
        Dim da As New OleDb.OleDbDataAdapter("Select FIF, COUNTRYFUZEALLA from AOP5", objConnection)

        Dim dt1 As New DataTable

        da.Fill(dt1)

        da.Dispose()
        da = Nothing

        'da = New OleDb.OleDbDataAdapter("Select NIIN, RN from REFERENCE_NUMBER_DATA", objConnection)
        da = New OleDb.OleDbDataAdapter("Select FIF, COUNTRY from AOP5A", objConnection)


        Dim dt2 As New DataTable

        da.Fill(dt2)

        Dim dv2 As DataView

        dv2 = dt2.DefaultView

        Dim cmd As OleDbCommand

        Dim SQLStr As String


        For Each dr As DataRow In dt1.Rows

            '.RowFilter = "NIIN='" & dr.Item("NIIN") & "'"
            dv2.RowFilter = "FIF='" & dr.Item("FIF") & "'"

            If dv2.Count > 0 Then


                SQLStr = "Update AOP5 Set COUNTRYFUZEALLA='"

                ' For Each drv2 As DataRowView In dv2

                'SQLStr &= drv2.Item("COUNTRY") & ","
                'Next
                For Each drv2 As DataRowView In dv2
                    If SQLStr.Contains(drv2.Item("COUNTRY")) = False Then
                        SQLStr &= drv2.Item("COUNTRY") & ","
                    End If
                Next
                'strip off trailing comma
                SQLStr = SQLStr.Substring(0, SQLStr.Length - 1)
                'add closing apostrophe
                SQLStr &= "'"
                'add WHERE clause
                SQLStr &= " Where FIF='" & dr.Item("FIF") & "'"
                ' MsgBox(SQLStr)
                cmd = New OleDbCommand(SQLStr, objConnection)
                'MsgBox(SQLStr)
                cmd.ExecuteNonQuery()
                cmd.Dispose()
                cmd = Nothing
            End If
        Next
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 34882682
do you need to sort the data in each row or sort the rows of a single column?
for instance, if your row is:
BEL,FRA,USA,DNK,CAN

after sorting:
BEL,CAN,DNK,FRA,USA

is that what u mean?

0
 
LVL 42

Expert Comment

by:sedgwick
ID: 34882685
can we assume data is splitted by comma separator?
0
 

Author Comment

by:vcharles
ID: 34882977
Yes after sorting I would like to see:
BEL,CAN,DNK,FRA,USA
The countries will always be separate by a " , ".
Thanks.
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 42

Expert Comment

by:sedgwick
ID: 34882997
which line in your code contain the string of countries which need to be sorted?
0
 

Author Comment

by:vcharles
ID: 34883214
I believe this is that part of the code
.
 For Each drv2 As DataRowView In dv2
                    If SQLStr.Contains(drv2.Item("COUNTRY")) = False Then
                        SQLStr &= drv2.Item("COUNTRY") & ","
                    End If
                Next
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 500 total points
ID: 34883863
Try following code.

I added the following line (and cleaned up the code)


        dv2.Sort = "Country"




Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\aopt2002org.mdb;Persist Security Info=True;Jet OLEDB:Database Password=testaopupdate"

        Dim da As New OleDb.OleDbDataAdapter("Select FIF, COUNTRYFUZEALLA from AOP5", ConnectionString)
        Dim dt1 As New DataTable
        da.Fill(dt1)
        da.Dispose()
        da = Nothing
        da = New OleDb.OleDbDataAdapter("Select FIF, COUNTRY from AOP5A", ConnectionString)
        Dim dt2 As New DataTable
        da.Fill(dt2)
        Dim dv2 As DataView
        dv2 = dt2.DefaultView
        dv2.Sort = "Country"
        Dim cmd As OleDbCommand
        Dim SQLStr As String
        Dim dTable As New DataTable
        For Each dr As DataRow In dt1.Rows
            dv2.RowFilter = "FIF='" & dr.Item("FIF") & "'"
            If dv2.Count > 0 Then
                SQLStr = "Update AOP5 Set COUNTRYFUZEALLA='"
                For Each drv2 As DataRowView In dv2
                    If SQLStr.Contains(drv2.Item("COUNTRY")) = False Then
                        SQLStr &= drv2.Item("COUNTRY") & ","
                    End If
                Next
                'strip off trailing comma
                SQLStr = SQLStr.Substring(0, SQLStr.Length - 1)
                'add closing apostrophe
                SQLStr &= "'"
                'add WHERE clause
                SQLStr &= " Where FIF='" & dr.Item("FIF") & "'"
                ' MsgBox(SQLStr)
                cmd = New OleDbCommand(SQLStr, objConnection)
                'MsgBox(SQLStr)
                cmd.ExecuteNonQuery()
                cmd.Dispose()
                cmd = Nothing
            End If
        Next

Open in new window

0
 

Author Comment

by:vcharles
ID: 34883927
It Worked.
THANK YOU!
Victor
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 34883946
Glad to help :-)
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

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 …
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

864 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

20 Experts available now in Live!

Get 1:1 Help Now