Android Question SQLite INSERT INTO does not write to table

stevew · Dec 1, 2016

I have created a database with SQLite Browser and copied it into the files of my application. The tables have data in them. I can load the tables in views, sort, search and manipulate the contents with sql queries.

I have tried to write new records into tables (sql1.ExecNonQuery("INSERT INTO tblRoom VALUES(NULL,'Test1')") I get no errors. I used this same query in SQLite Browser on my computer to test the string and the record was added. When I run the app on a Samsung Tab A I get a return value from using x = sql1.ExecQuerySingleResult("SELECT last_insert_rowid() FROM tblRoom"), and no errors, but nothing has been written to the table.

After the insert I try to read the table in my app on the tablet, and the new record is not there. I can find the table stored on the tablet and view the table using SQLite Manager, and the record has not been added.

Do I need to add some permissions? I have tried everything I can think of; it should be simple to write new records and I feel I am missing something obvious. I have searched for an answer but can find nothing.

Would appreciate any help someone could provide.

Mahares · Dec 1, 2016

Did you try to INSERT a record and then see if the record count has increased by 1 by doing this::

B4X:

x = sql1.ExecQuerySingleResult("SELECT count(*) FROM tblRoom")

If the record count went up, then the INSERT is working. The problem could be in the way you display your data. Maybe, you are not positioning the Cursor, like: Cursor1.Position =i in your SELECT statement.

stevew · Dec 2, 2016

I hadn't but I did have the row_id returned after the insert and it increased by 1. I just tried the count as you have suggested - I got the count before the insert (20), and the count after the insert (21) and it increased by 1.

This indicates that the new record was added.

However, when I reload the listview the new record is not visible. Also, I open the table on the tablet and look at the records a (I use aSQLiteManager I installed on the tablet for this purpose) and the record does not appear.

If I close down the app, and re-start, and add a record again - the same same counts come up 20, and 21. I am baffled!

The database was created on my desktop and is called bldb.db

I copy it to the Files folder in B4a IDE project/app.

I have not tried to modify an existing record but I will try that also later today.

Thanks for replying!

If you have any suggestions they are greatly appreciated.

Mahares · Dec 2, 2016

As @klaus would say Without your code it is very hard to help you. If you can zip your project, someone in the forum will figure out the problem, which does not appear to be complex. You could very well be having 2 databases with the same name clashing somewhere.
In the IDE, 'File', 'Export as ZIp'

rboeck · Dec 2, 2016

Maybe you copy your db each time when the app starts from the assets folder. So you allways fall back to the 20 records...

stevew · Dec 2, 2016

rboeck

I think you are right! I feel really stupid. I'm not sure if I have this totally resolved, but I think I'm on my way.

I thank you both for your help!

rboeck · Dec 3, 2016

Guess, why i had this idea?

stevew · Dec 6, 2016

You did the same thing?

Anyway, finally got it working ok. Thanks for the 'hint'. For everyon's info, I got confused by where the db file(s) actually were. I created a database on my computer. Copied it into the DirAssets file (the Files tab in the UID). I found out eventually this is a read only file. Any changes I made on my device were not saved anywhere.

I was hoping to place the db and tables on the SD card but found out that Google (Android) doesn't allow this as of KITKAT!! I tried to find a solution - found some but haven't got them to work yet.

I copied the file from File.DirAssets to File.DirInternal, and turned off DBUtils.CopyDBFromAssets so I didn't keep reloading the unaltered File.DirAssets copy

If anyone has a tried and trusted solution ot writting to a SD card, I would love to hear about it.

Erel · Dec 7, 2016

stevew said:
I copied the file from File.DirAssets to File.DirInternal, and turned off DBUtils.CopyDBFromAssets so I didn't keep reloading the unaltered File.DirAssets copy

DBUtils.CopyDBFromAssets will never overwrite an existing database (see the code).

You can use RuntimePermissions.GetAllSafeDirsExternal to find the path to the accessible folder on the SD card.

DursunBolat · Oct 9, 2018

I am updating my database connection, adding delete operations. but it does not write to the database.
I can extract data from the database, but I can't write

DonManfred · Oct 9, 2018

DursunBolat said:
I am updating my database connection, adding delete operations. but it does not write to the database.
I can extract data from the database, but I can't write

You should always start a new thread for your questions in the questionsforum. Never post to an existing thread. Also make sure to help us help you. Upload a example project which shows the issue. From the IDE: File-Export as zip.

Without seeing your code we hardly can help. You are hiding ALL relevant infos.

Tamer El-Shimy · Dec 7, 2021

stevew said:
You did the same thing?

Anyway, finally got it working ok. Thanks for the 'hint'. For everyon's info, I got confused by where the db file(s) actually were. I created a database on my computer. Copied it into the DirAssets file (the Files tab in the UID). I found out eventually this is a read only file. Any changes I made on my device were not saved anywhere.

I was hoping to place the db and tables on the SD card but found out that Google (Android) doesn't allow this as of KITKAT!! I tried to find a solution - found some but haven't got them to work yet.

I copied the file from File.DirAssets to File.DirInternal, and turned off DBUtils.CopyDBFromAssets so I didn't keep reloading the unaltered File.DirAssets copy

If anyone has a tried and trusted solution ot writting to a SD card, I would love to hear about it.

stevew said:
I have created a database with SQLite Browser and copied it into the files of my application. The tables have data in them. I can load the tables in views, sort, search and manipulate the contents with sql queries.

I have tried to write new records into tables (sql1.ExecNonQuery("INSERT INTO tblRoom VALUES(NULL,'Test1')") I get no errors. I used this same query in SQLite Browser on my computer to test the string and the record was added. When I run the app on a Samsung Tab A I get a return value from using x = sql1.ExecQuerySingleResult("SELECT last_insert_rowid() FROM tblRoom"), and no errors, but nothing has been written to the table.

After the insert I try to read the table in my app on the tablet, and the new record is not there. I can find the table stored on the tablet and view the table using SQLite Manager, and the record has not been added.

Do I need to add some permissions? I have tried everything I can think of; it should be simple to write new records and I feel I am missing something obvious. I have searched for an answer but can find nothing.

Would appreciate any help someone could provide.

Unfortunately You can't see your updates on SQLite Manager due to different file versions of files (File-Based DB) , You wrote File.Copy statement to let your smartphone or tablet see the DB while the other is in your Application Directory (DirAssets) not (DirInternal).?

Android Question SQLite INSERT INTO does not write to table

stevew

Member

Mahares

Expert

stevew

Member

Mahares

Expert

rboeck

Well-Known Member

stevew

Member

rboeck

Well-Known Member

stevew

Member

Erel

B4X founder

DursunBolat

Member

DonManfred

Expert

Tamer El-Shimy

Member

Similar Threads

Android Question SQLite INSERT INTO does not write to table

Member

Expert

Member

Expert

Well-Known Member

Member

Well-Known Member

Member

B4X founder

Member

Expert

Member

Similar Threads

Privacy & Transparency

Privacy & Transparency