View Single Post
  #3 (permalink)  
Old 11-28-2008, 9:16 PM
cjard's Avatar
cjard cjard is offline
VB.NET Forum All-Mighty
.NET Framework: .NET 2.0 (VS 2005)
 
Join Date: Apr 2006
Age: 65
Posts: 6,442
Reputation: 807
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
__________________
DW1 DW2 DW3 DW4 DNU PQ
Reply With Quote