System.Data.SQLite
View Ticket
Not logged in
Ticket UUID: 072c4dd05db3fb639711fbf07c0e38803004afa9
Title: How to resolve Concurrency violation: the UpdateCommand affected 0 of the expected 1 records with boolean data type
Status: Closed Type: Question
Severity: Important Priority: Medium
Subsystem: Data_Adapter Resolution: Not_A_Bug
Last Modified: 2017-04-03 23:16:14
Version Found In: 1.0.104.0
User Comments:
anonymous added on 2017-02-04 04:10:33:
I had below code to test SQLite with boolean data type.

>>> Code start
Imports System.Data.SQLite

Module Module1

    Sub Main()
        ' Create tables and test data
        Dim Conn_str As String = "Data source=test.db;Foreign Keys=True;"
        Dim Test_table As String = "Test_boolean"
        Dim Test_type As String = "boolean"
        'Dim Test_data As String = "True"
        'Above line will generate command: insert into Test_boolean values (1, True, 'Initial');
        'And the command will cause error: Sql logic error Or missing database no such column:  True
        Dim Test_data As String = "'True'"
        Dim Conn As New SQLiteConnection(Conn_str)
        Conn.Open()
        Dim Comm As SQLiteCommand = Conn.CreateCommand()
        Dim Comm_list = New String() {
            Replace("drop table %1;", "%1", Test_table),
            Replace(Replace("create table %1(ID integer primary key, Not_change %2, Change text);", "%1", Test_table), "%2", Test_type),
            Replace(Replace("insert into %1 values (1, %2, 'Initial');", "%1", Test_table), "%2", Test_data)
            }
        For Each Comm_text As String In Comm_list
            Comm.CommandText = Comm_text
            Try
                Comm.ExecuteNonQuery()
            Catch ex As Exception
                InputBox("Error executing command", "Debug", Comm_text & vbCrLf & ex.Message)
            End Try
        Next
        Conn.Close()

        ' Read data in to memory
        Dim Adap As New SQLiteDataAdapter("select * from " & Test_table, Conn_str)
        Dim Tabl As New DataTable(Test_table)
        Adap.Fill(Tabl)

        ' Change data and write back to db
        Tabl.Rows(0)("Change") = "Changed"
        Dim Bldr As New SQLiteCommandBuilder(Adap)
        Adap.Update(Tabl)   ' Causes the error: Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.
    End Sub

End Module
<<< Code end

As commented in the Adap.Update(Tabl) code, the exception is risen at the point. Before this, I have issue with date data type and can resolve it following solution in ticket [343d392b51].

My question is how to resolve the exception with boolean data type? I am thinking about using int data type instead but it is not natural. Thanks in advance for your advice.

mistachkin added on 2017-02-04 04:20:39:
This type of question is better handled on the mailing list:

[/doc/trunk/www/support.wiki]

anonymous added on 2017-02-04 04:46:51:
Thanks again for your advice. After reading mailing list about the error and http://sqlite.org/datatype3.html, I acknowledged that instead of using boolean data type, integer need to be used.