Ticket Hash: | 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. |