Exception at changing a row

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Exception at changing a row

Rtm Rbtsk
Hy,

If I change the data in the first row with the 'id'='1' in the table
'leslie', I get a DBConcurrencyException at the Update-command:

System.Data.DBConcurrencyException: 'Concurrency violation: the
UpdateCommand affected 0 of the expected 1 records.'

By deleting the row it raises in Program.cs in the Main-Class:

System.Data.DeletedRowInaccessibleException: 'Deleted row information
cannot be accessed through the row.'

It is only this one row, the other rows can be changed/deleted without
any problem. The database is attached.


Microsoft Visual Studio Community 2017 version 15.7.5    (.NET Framework
4.7.1)

Best,

Artem

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Exception at changing a row in .NET FW 4.7.1

Rtm Rbtsk
Hy,

If I change the data in the first row with the 'id'='1' in the table
'leslie', I get a DBConcurrencyException at the Update-command:

System.Data.DBConcurrencyException: 'Concurrency violation: the
UpdateCommand affected 0 of the expected 1 records.'

By deleting the row it raises in Program.cs in the Main-Class:

System.Data.DeletedRowInaccessibleException: 'Deleted row information
cannot be accessed through the row.'

It is only this one row, the other rows can be changed/deleted without
any problem. The database is here: https://www.dropbox.com/s/tqepj4ni806xa68/SQLITE.db?dl=0


Microsoft Visual Studio Community 2017 version 15.7.5    (.NET Framework
4.7.1)

Best,

Artem


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Exception at changing a row in .NET FW 4.7.1

Joe Mistachkin-3

Rtm Rbtsk wrote:
>
> If I change the data in the first row with the 'id'='1' in the table
> 'leslie', I get a DBConcurrencyException at the Update-command:
>
> System.Data.DBConcurrencyException: 'Concurrency violation: the
> UpdateCommand affected 0 of the expected 1 records.'
>

It's hard to say what exactly is happening without seeing the C# code
involved; however, in the past, these types of issues have been caused
by a mismatch between how the .NET Framework and SQLite treat typing
of column values and/or a mismatch between the data type used in the
query and the one actually stored in the database file.

--
Joe Mistachkin

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Exception at changing a row in .NET FW 4.7.1

Rtm Rbtsk
In reply to this post by Rtm Rbtsk
 > Rtm Rbtsk wrote:
 > >
 > > If I change the data in the first row with the 'id'='1' in the table
 > > 'leslie', I get a DBConcurrencyException at the Update-command:
 > >
 > > System.Data.DBConcurrencyException: 'Concurrency violation: the
 > > UpdateCommand affected 0 of the expected 1 records.'
 > >
 >
 > It's hard to say what exactly is happening without seeing the C# code
 > involved; however, in the past, these types of issues have been caused
 > by a mismatch between how the .NET Framework and SQLite treat typing
 > of column values and/or a mismatch between the data type used in the
 > query and the one actually stored in the database file.
 >
 > --
 > Joe Mistachkin

The source is below.
The cell column 'birthdate' of type NUMERIC has the value
'63487539487534432'. I found out that if you only set this value to
NULL, the Concurrency Exception will not appear anymore.

Further if I enter a new value, it suddenly seems to round this:

If I enter
75987492387429362,
75987492387429400 is displayed after load

If I enter
64230380287363183,
64230380287363200 is displayed after load

If I enter
92376465128590338,
92376465128590400 is displayed after load and so on. But the first value
is actually saved in DB.

The data type of the column in DataGridView is either Int64 or Decimal.
But both can contain higher values than those mentioned.

I will probably not use NUMERIC in my projects, but I'm concerned that
such failures may happen in other situations.

Best,
Artem


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SQLite;
using System.Text.RegularExpressions;

namespace SQLite_Test
{
     public partial class Form1 : Form
     {
         public static readonly string nl = Environment.NewLine;
         SQLiteConnection PubConLite;
         public static BindingSource _BS_Source2 = new BindingSource();
         public static SQLiteDataAdapter sql_ada2; public static DataSet
Datenset = new DataSet();
         public static DataTable dtLeslie = new DataTable("ZweiteTabelle");

         public Form1()
         {
             InitializeComponent();
         }

         private void Form1_Load(object sender, EventArgs e)
         {
             dtLeslie.Clear();
             Datenset.Tables.Add(dtLeslie);
             _BS_Source2.DataSource = Datenset.Tables["ZweiteTabelle"];
             dataGridView1.DataSource = _BS_Source2;
         }

         private void btnConnect_Click(object sender, EventArgs e)
         {
             PubConLite = new SQLiteConnection("Data
Source=sqlite.db;Version=3;");
             PubConLite.Open();
         }

         private void fillTables()
         {
             SQLiteCommand cmd = PubConLite.CreateCommand();
             cmd.CommandText = $"CREATE TABLE IF NOT EXISTS campbell
([flights] INT NULL, [email] VARCHAR (255) NULL, [tour] VARCHAR (255)
NULL, [id] BIGINT NOT NULL, CONSTRAINT [pk_campbell] PRIMARY KEY
([id])); " +
                    $"CREATE TABLE IF NOT EXISTS leslie ([strGeltend]
VARCHAR(4000),[dtmAusgangsZ] DATETIME, [dtmEndgZ] DATETIME,
[dblGerechnet] REAL, [ztEndgueltig] VARCHAR(4000) , [birthdate] NUMERIC,
[bitGeltendAktiv] BIT, [id] INTEGER PRIMARY KEY ASC)";
             cmd.ExecuteNonQuery();
         }

         private void btnLoadToDGV_Click(object sender, EventArgs e)
         {
             if (PubConLite.State == ConnectionState.Closed)
PubConLite.Open();

             SQLiteCommand sql_command2 = new SQLiteCommand
             {
                 Connection = PubConLite,
                 CommandText = "SELECT * FROM leslie"
             };
             sql_ada2 = new SQLiteDataAdapter(sql_command2);

             sql_ada2.SelectCommand = sql_command2;
             sql_ada2.FillSchema(dtLeslie, SchemaType.Source);
             dtLeslie.Columns["id"].AutoIncrement = true;
             dtLeslie.Columns["id"].AutoIncrementSeed = 1;
             dtLeslie.Columns["id"].AutoIncrementStep = 1;
             dtLeslie.Columns["id"].Unique = true;
             sql_ada2.Fill(dtLeslie);

             dataGridView1.Columns["id"].Visible = false;

dataGridView1.Columns["dtmAusgangsZ"].DefaultCellStyle.Format = "HH:mm";
dataGridView1.Columns["dtmEndgZ"].DefaultCellStyle.Format = "HH:mm";

             PubConLite.Close();
         }

         private void btnWrite_Click(object sender, EventArgs e)
         {
             SQLiteCommandBuilder builder = new
SQLiteCommandBuilder(sql_ada2);

             if (sql_ada2 != null)
             {
                 try
                 {
                     sql_ada2.Update(dtLeslie);
                 }
                 catch (DBConcurrencyException exc1)
                 {
                     MessageBox.Show("Error DBConcurrencyException." +
nl + nl + exc1.Message);
                 }
                 catch (Exception exc2)
                 {
                     MessageBox.Show("Error." + nl + nl + exc2.Message);
                 }
             }
             else MessageBox.Show("sql_ada2 is null");
         }
     }
}

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Exception at changing a row in .NET FW 4.7.1

Joe Mistachkin-3

Using the type name “DATETIME” with System.Data.SQLite activates some special handling, which is quite complex, since it has many options dealing with formats, time zone handling, etc.  In your case, it may be better to use INTEGER instead.  Alternatively, you could create a custom type handler, but that seems like overkill.

Sent from my iPhone

> On Aug 8, 2018, at 7:06 PM, Rtm Rbtsk <[hidden email]> wrote:
>
> > Rtm Rbtsk wrote:
> > >
> > > If I change the data in the first row with the 'id'='1' in the table
> > > 'leslie', I get a DBConcurrencyException at the Update-command:
> > >
> > > System.Data.DBConcurrencyException: 'Concurrency violation: the
> > > UpdateCommand affected 0 of the expected 1 records.'
> > >
> >
> > It's hard to say what exactly is happening without seeing the C# code
> > involved; however, in the past, these types of issues have been caused
> > by a mismatch between how the .NET Framework and SQLite treat typing
> > of column values and/or a mismatch between the data type used in the
> > query and the one actually stored in the database file.
> >
> > --
> > Joe Mistachkin
>
> The source is below.
> The cell column 'birthdate' of type NUMERIC has the value '63487539487534432'. I found out that if you only set this value to NULL, the Concurrency Exception will not appear anymore.
>
> Further if I enter a new value, it suddenly seems to round this:
>
> If I enter
> 75987492387429362,
> 75987492387429400 is displayed after load
>
> If I enter
> 64230380287363183,
> 64230380287363200 is displayed after load
>
> If I enter
> 92376465128590338,
> 92376465128590400 is displayed after load and so on. But the first value is actually saved in DB.
>
> The data type of the column in DataGridView is either Int64 or Decimal. But both can contain higher values than those mentioned.
> > Rtm Rbtsk wrote:
> > >
> > > If I change the data in the first row with the 'id'='1' in the table
> > > 'leslie', I get a DBConcurrencyException at the Update-command:
> > >
> > > System.Data.DBConcurrencyException: 'Concurrency violation: the
> > > UpdateCommand affected 0 of the expected 1 records.'
> > >
> >
> > It's hard to say what exactly is happening without seeing the C# code
> > involved; however, in the past, these types of issues have been caused
> > by a mismatch between how the .NET Framework and SQLite treat typing
> > of column values and/or a mismatch between the data type used in the
> > query and the one actually stored in the database file.
> >
> > --
> > Joe Mistachkin
>
> The source is below.
> The cell column 'birthdate' of type NUMERIC has the value '63487539487534432'. I found out that if you only set this value to NULL, the Concurrency Exception will not appear anymore.
>
> Further if I enter a new value, it suddenly seems to round this:
>
> If I enter
> 75987492387429362,
> 75987492387429400 is displayed after load
>
> If I enter
> 64230380287363183,
> 64230380287363200 is displayed after load
>
> If I enter
> 92376465128590338,
> 92376465128590400 is displayed after load and so on. But the first value is actually saved in DB.
>
> The data type of the column in DataGridView is either Int64 or Decimal. But both can contain higher values than those mentioned.
>
> I will probably not use NUMERIC in my projects, but I'm concerned that such failures may happen in other situations.
>
> Best,
> Artem
>
>
> using System;
> using System.Collections.Generic;
> using System.ComponentModel;
> using System.Data;
> using System.Drawing;
> using System.Linq;
> using System.Text;
> using System.Threading.Tasks;
> using System.Windows.Forms;
> using System.Data.SQLite;
> using System.Text.RegularExpressions;
>
> namespace SQLite_Test
> {
>     public partial class Form1 : Form
>     {
>         public static readonly string nl = Environment.NewLine;
>         SQLiteConnection PubConLite;
>         public static BindingSource _BS_Source2 = new BindingSource();
>         public static SQLiteDataAdapter sql_ada2; public static DataSet Datenset = new DataSet();
>         public static DataTable dtLeslie = new DataTable("ZweiteTabelle");
>
>         public Form1()
>         {
>             InitializeComponent();
>         }
>
>         private void Form1_Load(object sender, EventArgs e)
>         {
>             dtLeslie.Clear();
>             Datenset.Tables.Add(dtLeslie);
>             _BS_Source2.DataSource = Datenset.Tables["ZweiteTabelle"];
>             dataGridView1.DataSource = _BS_Source2;
>         }
>
>         private void btnConnect_Click(object sender, EventArgs e)
>         {
>             PubConLite = new SQLiteConnection("Data Source=sqlite.db;Version=3;");
>             PubConLite.Open();
>         }
>
>         private void fillTables()
>         {
>             SQLiteCommand cmd = PubConLite.CreateCommand();
>             cmd.CommandText = $"CREATE TABLE IF NOT EXISTS campbell ([flights] INT NULL, [email] VARCHAR (255) NULL, [tour] VARCHAR (255) NULL, [id] BIGINT NOT NULL, CONSTRAINT [pk_campbell] PRIMARY KEY ([id])); " +
>                    $"CREATE TABLE IF NOT EXISTS leslie ([strGeltend] VARCHAR(4000),[dtmAusgangsZ] DATETIME, [dtmEndgZ] DATETIME, [dblGerechnet] REAL, [ztEndgueltig] VARCHAR(4000) , [birthdate] NUMERIC, [bitGeltendAktiv] BIT, [id] INTEGER PRIMARY KEY ASC)";
>             cmd.ExecuteNonQuery();
>         }
>
>         private void btnLoadToDGV_Click(object sender, EventArgs e)
>         {
>             if (PubConLite.State == ConnectionState.Closed) PubConLite.Open();
>
>             SQLiteCommand sql_command2 = new SQLiteCommand
>             {
>                 Connection = PubConLite,
>                 CommandText = "SELECT * FROM leslie"
>             };
>             sql_ada2 = new SQLiteDataAdapter(sql_command2);
>
>             sql_ada2.SelectCommand = sql_command2;
>             sql_ada2.FillSchema(dtLeslie, SchemaType.Source);
>             dtLeslie.Columns["id"].AutoIncrement = true;
>             dtLeslie.Columns["id"].AutoIncrementSeed = 1;
>             dtLeslie.Columns["id"].AutoIncrementStep = 1;
>             dtLeslie.Columns["id"].Unique = true;
>             sql_ada2.Fill(dtLeslie);
>
>             dataGridView1.Columns["id"].Visible = false;
>
> dataGridView1.Columns["dtmAusgangsZ"].DefaultCellStyle.Format = "HH:mm";
> dataGridView1.Columns["dtmEndgZ"].DefaultCellStyle.Format = "HH:mm";
>
>             PubConLite.Close();
>         }
>
>         private void btnWrite_Click(object sender, EventArgs e)
>         {
>             SQLiteCommandBuilder builder = new SQLiteCommandBuilder(sql_ada2);
>
>             if (sql_ada2 != null)
>             {
>                 try
>                 {
>                     sql_ada2.Update(dtLeslie);
>                 }
>                 catch (DBConcurrencyException exc1)
>                 {
>                     MessageBox.Show("Error DBConcurrencyException." + nl + nl + exc1.Message);
>                 }
>                 catch (Exception exc2)
>                 {
>                     MessageBox.Show("Error." + nl + nl + exc2.Message);
>                 }
>             }
>             else MessageBox.Show("sql_ada2 is null");
>         }
>     }
> }
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users