How to run Sqlite Update Query in a while ExecuteReader loop

Question

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 input: have 2 arrays of characters. task:

 to update ALL ROWS:
 1. Read VAL1  
 2. Replace CHARACTERS as defined in strMapp e.g. (A->a,B->x,D->f) etc
 3. UPDATE this new String to column 'Val2'

MY CODE SO FAR:

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")    
conn1 = New SQLiteConnection("Data Source=test.s3db;Read Only=True")
conn2 = New SQLiteConnection("Data Source=test.s3db")
Dim cmd As New SQLiteCommand(conn1)
Dim cmd2 As New SQLiteCommand(conn2)
conn1.open  ''read only
conn2.open  ''to update
Dim ii As Integer
Dim query as String = "SELECT ID,Val1,Val2 FROM my_table"
cmd.commandText=query
Dim myReader As SQLiteDataReader

myReader = cmd.ExecuteReader

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")
        cmd2.CommandText=Qry
        cmd2.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

ok there was a typo in code above. actual code is: cmd.CommandText = Qry cmd.ExecuteNonQuery() ''here the error occurs — Ranhot
– Ranhot, Commented Feb 22, 2016 at 8:19
Does sqllite even allow CRUD actions while a data reader is open? — Alex B.
– Alex B., Commented Feb 22, 2016 at 8:40
update code for better understanding of the issue. as @Alex suggested, the issues seems i can not update db while reader is open. is it possible at all.? — Ranhot
– Ranhot, Commented Feb 22, 2016 at 9:56

Ranhot · Accepted Answer · 2016-02-22 10:57:36Z

Ok, it seems that i can not update db while datareader is open and active. so, i took a different approach to accomplish this by first storing records in a DataSet and then doing the UPDATE part later. Thus i avoided DataReader all together. Here is my new and working code :) It could also be read as HOW TO AVOID SQLiteDataReader :)

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")    

    Dim DS As New DataSet
    Dim DT As New DataTable
    Dim cmd As New SQLiteCommand
    Dim query As String = "SELECT ID,Val1,Val2 FROM my_table"
    Dim DB As New SQLiteDataAdapter(query, connection)
    DS.Reset()
    DB.Fill(DS)
    DT = DS.Tables(0)
    cmd.Connection = connection
    Dim nStr, oStr, Qry As String
    Dim nChar, oChar As String
    For Each row As DataRow In DT.Rows
        oStr = row("FirstLetterStr")
        nStr = ""
        For j As Integer = 0 To oStr.Length - 1
            oChar = oStr.Substring(j, 1)
            If strMap.ContainsKey(oChar) Then
                nChar = strMap(oChar)
                nStr += nChar
            End If
        Next j
        Qry = "UPDATE my_table set Val2='" & nStr & "' WHERE ID=" & row("ID")
        cmd.CommandText = Qry
        cmd.ExecuteNonQuery()
    Next row

Collectives™ on Stack Overflow

How to run Sqlite Update Query in a while ExecuteReader loop

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related