sorting data in a row

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,CAN,BEL,CAN,DEU,USA" Can you please help me fix the code.

Thanks

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

I don't quite understand your question but if you want to sort the data coming from a database on certain field(s), why don't you just use "order by" in your query?

I agree. It seems only logical to put it into your SQL command, then you don't need to reorder the output.

I don't know the SQL to sort the rows' data at runtime. Do you know the SQL?

Thanks,

Victor

I don't think ORDER BY will work in this case. How about editing that first post and enclosing the code in CODE tags. That will make it much easier to read.

Also, unless you are using a version of .NET prior to 2005, you should get rid of the ArrayList in favor of List (of T), which is a strongly typed list, and much superior.

It appears that you are taking a string, which is a comma separated list of countries, splitting the string on the comma, sorting the resulting array, then re-packing them into a string. All that looks fine (though an odd way to set up a database...not that I haven't done the same once before).


As for the main question:You need to add just one line to your code. Right after the call to split, you need to add this line:

sortedList = ""

Otherwise, sortedList still holds the original list, and you just concatenate the new stuff onto it.

I still can't see why you can't 'ORDER BY country ASC'? Or whatever it actually is, my SQL is rusty, haven't touched it for a couple of years.

Unless I'm missing something you only need to amend your query to

Code:

---

SELECT ID, country FROM Identification_data ORDER BY country ASC

---

or

Code:

---

SELECT ID, country FROM Identification_data ORDER BY country DESC

---

if you want it in reverse alphabetical order.

Since the Country field is being split, then re-ordered, I understand the Country field not to hold a single country, but to hold something like this:

"USA,GER,FRA"

and he wants to end up with:

"FRA,GER,USA"

ORDER BY won't do that, because it will only order a collection of records, not a bunch of pieces within a string.

Yeah Shaggy, makes perfect sense if "BEL,CAN,DEU,USA" is the value of dr("COUNTRY").ToString for one record. It would have paid for me to have read the original post, but that code without syntax highlighting and indentation...hurts my eyes...and swells my brain! ;-)

It still comes down to adding only one line to fix the problem.