|Title:||Can't insert negative number into table|
|Last Modified:||2014-01-02 05:53:41|
|Version Found In:||18.104.22.168|
anonymous added on 2013-12-30 13:49:26:
When I updated my system.data.sqlite.dll from 3.63 to the latest one, I found I can't insert a negative number into my table as it used to be. Error message: System.OverflowException: Value was either too large or too small for a UInt64. at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo batchCommands, Int32 commandCount) at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo batchCommands, Int32 commandCount) at System.Data.Common.DbDataAdapter.Update(DataRow dataRows, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.Update(DataTable dataTable) My code looks like: SQLiteCommand cmd = myConn.CreateCommand(); cmd.CommandTimeout = SqliteLib.defTimeout; cmd.CommandText = sql; SQLiteDataAdapter da = new SQLiteDataAdapter(cmd); SQLiteCommandBuilder cb = new SQLiteCommandBuilder(da); da.InsertCommand = cb.GetInsertCommand(); da.DeleteCommand = cb.GetDeleteCommand(); da.UpdateCommand = cb.GetUpdateCommand(); DataSet ds = new DataSet(); da.Fill(ds); DataTable dt = ds.Tables; dt.AcceptChanges(); ... //insert a new record da.Update(dt); --> raise error here I found it can insert positive numbers while can't insert negative ones. Please help to solve it ASAP, thanks!
mistachkin added on 2013-12-30 21:30:23:
Without seeing the schema of the database, it's very difficult to determine what the exact problem is. Are you able to share the database schema?
anonymous added on 2013-12-31 00:06:28:
The table schema is : CREATE TABLE [ANA_CURSMH] ( [UNIQUENO] INTEGER(20), [OrderNO] INTEGER, [RECMONTH] INTEGER, [RECTYPE] CHAR, [DATE] DATETIME, [TTLACCTIME] INTEGER, [DAYACCTIME] INTEGER, [AREAID] INTEGER, [LONG] INTEGER, [LAT] INTEGER, [ACCSTATUS] INTEGER, [LOCKSTATUS] INTEGER, CONSTRAINT  PRIMARY KEY ([UNIQUENO], [RECTYPE], [RECMONTH])) I found that it can't insert data when the long & lat fields are negative, but it can work when the long and lat fields are positive
mistachkin added on 2013-12-31 01:35:28:
Are you able to execute the following command directly (i.e. without using a data adapter)?
INSERT INTO [ANA_CURSMH] (LONG, LAT) VALUES ( -1 ,-1 );
mistachkin added on 2013-12-31 01:37:00:
I'm currently unable to reproduce this issue here locally. Please feel free to add comments; however, please let me control the various status field of the ticket. Thanks.
mistachkin added on 2013-12-31 02:06:41:
Also, what is the CommandText property you are using? I've now tried using a data adapter to perform an INSERT with negative values here locally and it does not throw an exception.
anonymous added on 2013-12-31 12:59:48:
The number I am using is -1000000, could you please have a test, thanks a lot!
mistachkin added on 2013-12-31 21:57:03:
I've tried with much smaller (and bigger) numbers and it does not throw an exception. I have not seen any hint of the reported behavior. Furthermore, the type mapping for "INTEGER" is Int64, which is signed. I have no idea how the UInt64 type ended up getting used in your case. My theory is that the .NET Framework itself may be using UInt64 somewhere internally; however, I have not seen that behavior either. Are you using the standard System.Data.SQLite or a customized version? Can you produce a self-contained test case that demonstrates the issue?
anonymous added on 2014-01-01 01:31:50:
Anyhow, thanks for your efforts & replies. The issue is really weired: it didn't raise error when the numbers were all positive but it recurred steadily when some fields were negative. Because I don't have the database on hand. I will try what you have suggested tomorrow and keep you updated.
mistachkin added on 2014-01-01 02:17:53:
If possible, the following information would make this easier to track down: 1. The complete schema of the database involved (or the database itself). 2. Minimal C# code that demonstrates the issue. 3. The SQL queries involved, along with the required sample data.
anonymous added on 2014-01-02 03:52:17:
Hi Sir, I have attached a testBug project to this ticket, it contains not only the source, system.data.sqlite.dll and the database related. With them, you can reproduce the problem easily. Please help to fix it ASAP because my project is suspended now. Another question here, every time, I have to use 'Anonymous' as user name when logining and subsequently I can't get your status & feedback directly(if my understanding is correct). Considering that I am an active user(already reported several bugs recently), could you assign me a formal user name? thanks in advance! My email address is email@example.com. Looking forward to your update & reply!
anonymous added on 2014-01-02 05:25:59:
Sorry for the bothering. This afternoon, I found the reason. It is an internal error, I should use DbType.Int64 instead of DbType.UInt64 when generating Update Command. Sorry for the inconvenience caused. By the way, Still, Can I be a normal member of your forum instead one of "anonymous" users? Thanks~
mistachkin added on 2014-01-02 05:53:41:
Your analysis of the issue is correct, the Int64 type should be used by your code, not the UInt64 type. Closing this ticket.