Input string was not in a correct format.Couldn't store <> in SortOrder Column. Expected type is Int64.

[hatzisn]

Dear sirs,

today I started getting the error mentioned in the subject.
(Input string was not in a correct format.Couldn't store <> in SortOrder Column. Expected type is Int64.)

I have not changed anything in the code, neither in the database structure and it used to work perfectly.

Here is the code and where the error occures:

Code: Select all

Dim dsSave As New DataSet
Dim cmndSave As New SQLiteCommand("SELECT * FROM [Translations] WHERE lang='" & _
                            ds.Tables("trns").Rows(0).Item("lang") & "'", conn)
Dim daSave As New SQLiteDataAdapter(cmndSave)
Dim cmbld As New SQLiteCommandBuilder(daSave)
daSave.UpdateCommand = cmbld.GetUpdateCommand
daSave.DeleteCommand = cmbld.GetDeleteCommand
daSave.InsertCommand = cmbld.GetInsertCommand

Debug.Print(daSave.InsertCommand.CommandText)
Debug.Print(daSave.SelectCommand.CommandText)
Debug.Print(daSave.DeleteCommand.CommandText)
Debug.Print(daSave.UpdateCommand.CommandText)

daSave.Fill(dsSave, "sv")  <---- the error occurs in this line

The table structure is the following (I use SQLite Expert Personal for the DB):

Code: Select all

CREATE TABLE "Translations"(
  [key] TEXT, 
  [lang] TEXT, 
  [value] TEXT, 
  [lkey] INTEGER, 
  [SortOrder] INTEGER, 
  [LastUpdatedAt] LARGEINT, 
  PRIMARY KEY([key], [lang]) ON CONFLICT FAIL);

devart.data version 5.0.2296.0
devart.data.sqlite version 5.14.1504.0

I do not try to insert something in the DB with a wrong type because as you can see the error occures in the filling of the dataset and it used to work perfectly the last time I ran this code (before 4 months or so). What could it possible be wrong?

Thanks in advance

[Shalex]

The error is specific to the data stored in the SortOrder column of your Translations table. Please send us your database file for reproducing.

[hatzisn]

I did some diagnostics in this column and it has values from 1 to 200 and null values. I thought it was a matter of VACUUMING and I did so but nothing changed. I solved the problem by turning back to a previous version of the database and doing again all the work that was needed. Shouldn't VACUUM solve the problem since it recreates the database and inserts the old values in the new database?

[Shalex]

You mean the AutoVacuum connection string option, don't you? You can try it.

The problem is specific to your database. We need to reproduce the issue to start our investigation.