Visual Basic .NET Forums  

Go Back   Visual Basic .NET Forums > VB.NET > Winforms Data Access

Winforms Data Access VB.NET development for data access and back-end related areas

VB.NET Forums Newsletter Signup:
Email address:


Reply
 
LinkBack Thread Tools Display Modes
  #1 (permalink)  
Old 11-22-2008, 10:25 PM
VB.NET Forum Enthusiast
.NET Framework: .NET 2.0 (VS 2005)
 
Join Date: Nov 2008
Age: 45
Posts: 47
Reputation: 10
victor64 is on a distinguished programming path ahead
Default Help with 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,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
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #2 (permalink)  
Old 11-26-2008, 12:05 PM
VB.NET Forum Newbie
.NET Framework: .NET 1.1 (VS 2003)
 
Join Date: Nov 2008
Posts: 2
Reputation: 0
VBJohney is on a distinguished programming path ahead
Default

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.
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #3 (permalink)  
Old 11-28-2008, 9:16 PM
cjard's Avatar
VB.NET Forum All-Mighty
.NET Framework: .NET 2.0 (VS 2005)
 
Join Date: Apr 2006
Age: 65
Posts: 6,137
Reputation: 658
cjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond repute
Default

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
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #4 (permalink)  
Old 11-28-2008, 9:26 PM
cjard's Avatar
VB.NET Forum All-Mighty
.NET Framework: .NET 2.0 (VS 2005)
 
Join Date: Apr 2006
Age: 65
Posts: 6,137
Reputation: 658
cjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond repute
Default

Quote:
Originally Posted by VBJohney View Post
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
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
Reply

Bookmarks


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 On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On





All times are GMT -4. The time now is 9:28 PM.

Powered by vBulletin® Version 3.8.3
Copyright ©2000 - 2009, Jelsoft Enterprises Ltd.
Search Engine Optimization by vBSEO 3.3.0


For advertising opportunities click here.