SQLiteDataTable, Grid binding, Autoincrement and NULL

MitchV · Post by **MitchV** » Sat 16 Apr 2011 16:57

I have this table :

CREATE TABLE "accounts" ("account_id" INTEGER PRIMARY KEY  AUTOINCREMENT, "account_name" VARCHAR, "username" VARCHAR, "password" VARCHAR);

I created an SQLiteDataTable for the accounts table and generated the commands using the internal tool. I modified the INSERT command to leave out the account_id completely since it should be auto-assigned.

Here is the INSERT command text :

Code: Select all

INSERT INTO main.accounts (account_name, username, password) VALUES (:account_name, :username, :password)

I bound that to a grid but when I try to add a new row through the grid I get this error :

Column 'account_id' does not allow nulls. Do you want to correct the value ?

I can figure out why it sending *any* value for account_id, or why SQLite won't work with it even if it did. I can execute this SQL directly into the database without a problem and the account_id is assigned as it should be:

Code: Select all

INSERT INTO accounts (account_id,username) VALUES (NULL,'test');

Is it doing some sort of parameter checking internally? How can I make this work?

MitchV · Post by **MitchV** » Sat 16 Apr 2011 18:28

I seem to have it working but I'm pretty sure it isn't using the built-in SQLite autoincrement feature. In the datatable I set the id field to autoincrement=true. Am I correct in that it is not letting SQLite do the incrementing?

Shalex · Post by **Shalex** » Mon 18 Apr 2011 17:06

We have reproduced the "Column 'account_id' does not allow nulls." issue. We will investigate it and notify you about the results as soon as possible.

MitchV · Post by **MitchV** » Mon 18 Apr 2011 18:37

Thank, Guys. Please let me know if there is anything I can do to help.. I've worked around the problem for development but I definitely need the "real" autoincrement for production..

Shalex · Post by **Shalex** » Thu 21 Apr 2011 15:20

1. Yes, you can use the SQLiteTable column's AutoIncrement property to workaround the issue.

2. As for the "real" autoincrement, you should set AllowDbNull=true and AutoIncrement=false for the database autoincrement column. We have fixed the current bug with the "Column 'account_id' does not allow nulls." error for this case. As a workaround, please remove the constraint from your SQLiteDataTable. I will post here when the corresponding build of dotConnect for SQLite is available for download.

Be aware about the SQLite engine's issue with refreshing result set with the values generated in the database for the autoincrement columns: http://www.devart.com/forums/viewtopic.php?t=20294.

Shalex · Post by **Shalex** » Thu 19 May 2011 10:21

Shalex wrote:2. As for the "real" autoincrement, you should set AllowDbNull=true and AutoIncrement=false for the database autoincrement column. We have fixed the current bug with the "Column 'account_id' does not allow nulls." error for this case. As a workaround, please remove the constraint from your SQLiteDataTable. I will post here when the corresponding build of dotConnect for SQLite is available for download.

New version of dotConnect for SQLite 3.30.160 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/sqlite/download.html (trial version) or from Registered Users' Area (for users with valid subscription only): http://secure.devart.com/ .