![]() |
|
|||||||
| Winforms Data Access VB.NET development for data access and back-end related areas |
![]() |
|
|
LinkBack | Thread Tools | Display Modes |
|
|||
|
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
Last edited by JohnH; 11-23-2008 at 5:21 AM. Reason: formatted post for readability |
|
|||
|
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. |
|
||||
|
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 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
__________________
DW1: Data Walkthroughs 1.1...DW2: Data Walkthroughs 2.0...DDS: The DataSet Designer Surface...ANO: ADO.NET2 Orientation...DAN: Deeper ADO.NET...DNU...PQ |
|
||||
|
Quote:
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
__________________
DW1: Data Walkthroughs 1.1...DW2: Data Walkthroughs 2.0...DDS: The DataSet Designer Surface...ANO: ADO.NET2 Orientation...DAN: Deeper ADO.NET...DNU...PQ |
![]() |
| Bookmarks |
| Currently Active Users Viewing This Thread: 1 (0 members and 1 guests) | |
| Thread Tools | |
| Display Modes | |
|
|