dimanche 21 février 2016

How to run Sqlite Update Query in a while ExecuteReader loop

I have a unique situation in sqlite (vb.net), Where i am trying to update fileds within a ExecuteReader while loop.

scenario: db table columns: ID, Val1, Val2

task: have 2 arrays of characters.

   Array1("a","b","c","d"....)
   Array2("l","j","p","x"....)

now i need to:

  1. Read All Rows and take Val1
  2. replace multiple chars in Val1 // Character from Array1 converts to Character in Array2 at same index e.g: a->l, b->j,c->p etc.
  3. Put resulting string in Val2

my current code of the loop:

    ''character replacement map
  Dim strMap As New Dictionary(Of String, String)
    strMap.Add("A", "a")
    strMap.Add("B", "x")
    strMap.Add("C", "f")
    strMap.Add("D", "h")
    strMap.Add("E", "l")    
    If myReader.HasRows = True Then
        Do While myReader.Read()
            oldStr = myReader("Val1")
            newStr = oldStr
            For j As Integer = 0 To newStr .Length - 1
                nChar = newStr.Substring(j, 1)
                If strMap.ContainsKey(nChar) Then
                    newStr = newStr .Replace(nChar, strMap(nChar))
                End If
            Next j

            Qry = "UPDATE my_table set Val2='" & newStr "' WHERE ID=" & myReader("ID")

            cmd.ExecuteNonQuery(Qry) '''''THIS CODE GIVES ERROR: database corruption
            ii = ii + 1

        Loop
    End If

Problem: It is giving "database corruption" error. I've marked the code where error occurs. please advise

Aucun commentaire:

Enregistrer un commentaire