Help with Sorting data in a row

victor64 · #1 (**permalink**) 11-22-2008, 11:25 PM

Hello,

I'm getting the right order but the old data is still in the row, for example the row shows duplicate data with the old and new sort order. "USA,BEL,DEU,CANBEL,CAN,DEU,USA" Can you please help me fix the code.

Thanks

Code:

Code:

        Try 
            'conn.Open() 
            Dim cmd As New OleDbCommand("SELECT ID, country FROM Identification_data", objConnection) 
            Dim dr As OleDbDataReader = cmd.ExecuteReader 
            Dim intIndex As Integer 
            Dim arrFields As New ArrayList 
            Dim arrIds As New ArrayList 
            Dim sortedList As String 

            While dr.Read 
                intIndex = dr("id").ToString 
                sortedList = dr("COUNTRY").ToString 
                ' myString = dr("country").ToString() 
                Dim spliter() As String = sortedList.Split(",") 
                Array.Sort(spliter) 
                For i As Integer = 0 To spliter.Length - 1 
                    If i < spliter.Length - 1 Then 
                        sortedList &= spliter(i).ToString & "," 
                    Else 
                        sortedList &= spliter(i).ToString 
                    End If 
                    arrFields.Add(sortedList) 
                    arrIds.Add(intIndex) 
                Next 
            End While 
            dr.Close() 
            For i As Integer = 0 To arrFields.Count - 1 
                Dim updCmd As New OleDbCommand("UPDATE identification_data SET country ='" & arrFields(i).ToString & "' WHERE id=" & arrIds(i).ToString & "", objConnection) 
                updCmd.ExecuteNonQuery() 
                updCmd.Dispose() 
            Next 
        Catch ex As Exception 
            MsgBox("LLLL") 
            MessageBox.Show(ex.Message) 
        Finally 
            objConnection.Close() 
            MsgBox("END") 
        End Try

VBJohney · #2 (**permalink**) 11-26-2008, 1:05 PM

Looks like you are continuing to use the variable sortedList to hold the sorted list without clearing it out.
put in the line
sortedList = String.Empty before the for loop.

cjard · #3 (**permalink**) 11-28-2008, 10:16 PM

Let's simplify your code, and add in some nice new things like .Net 2.0 generics.. Makes things a lot easier and nicer:

Code:

Try 
            'conn.Open() 
            Dim cmd As New OleDbCommand("SELECT ID, country FROM Identification_data", objConnection) 
            Dim dr As OleDbDataReader = cmd.ExecuteReader 
            Dim amends as New Dictionary(Of Integer, String) 'system.collections.generics namespace

            While dr.Read 
                Dim bits() as String = sortedList.Split(","c)
                Array.Sort(bits)

                Dim id as Integer = DirectCast(dr("id"), Integer)
                amends(id) = String.Join(",", bits)
            End While 
            dr.Close() 

            Dim updCmd As New OleDbCommand("UPDATE identification_data SET country = ? WHERE id= ?", objConnection) 
            'dummy parameters of the correct type (string, int)
            updCmd.Parameters.AddWithValue("ct", "Dummy String")
            updCmd.Parameters.AddWithValue("id", 0)
            
   
            ForEach i As Integer In amends.Keys

                updCmd.Parameters("id").Value = i
                updCmd.Parameters("ct").Value = amends(i)

                updCmd.ExecuteNonQuery() 

            Next 
        Catch ex As Exception 
            MsgBox("LLLL") 
            MessageBox.Show(ex.Message) 
        Finally 
            objConnection.Close() 
            MsgBox("END") 
        End Try

Note the use of parameterized queries in the update. Read the PQ link in my signature. Note this code was written without testing, on a machine without VS. Some syntax errors may be present

Note I deliberately dim vars inside loops to give them their proper scope. The framework is smart enough not to dim a whole new one on every loop pass. "Dont dim vars in loops" is an obsolete performance mantra

cjard · #4 (**permalink**) 11-28-2008, 10:26 PM

Quote:

Originally Posted by VBJohney

Looks like you are continuing to use the variable sortedList to hold the sorted list without clearing it out.
put in the line
sortedList = String.Empty before the for loop.

Should work, BUT.. I have a problem with his original code, actually.. the entire post reads like he wants to alter the sort order of a comma separated list of values stored in a single db field, but the placement of the arraylist adds INSIDE the for loop means that:

1:USA,BEL,DEU,CAN

Will become 4 records:
1:BEL,
1:BEL,CAN,
1:BEL,CAN,DEU,
1:BEL,CAN,DEU,USA

Thus 4 database updates will be done, 3 of them pointless. My version of the code doesnt perform these meaningless database operations

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

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