SQLite: SQL error or missing database when inserting data into table

7
votes

I am trying to insert new data into a SQLite table.

  • I know for sure that the insertion code is correct because I use it for different tables and it works fine.

  • I know for sure that the database and the table are created and exist because I can see them in the SQLite Browser.

But something in the SQLite syntax of creation or insertion is wrong because I receive this exception:

SQL error or missing database

Here is my code:

CREATE TABLE IF NOT EXISTS Theory_answers ("questionID" INTEGER,"answerID" INTEGER,"answerText" TEXT, "isTrue" INTEGER);

INSERT INTO Theory_answers (questionID, answerID, answerText,isTrue) VALUES ("1","1",text,"0");
4
sqlite
Please check that if the questionId is a primary key??? - Rahul Tripathi
FWIW, you can get the same error in some versions of sqlite when you insert multiple values at once. - Jess

4 Answers

9
votes

should this be....

VALUES ("1","1",text,"0") -> VALUES (1,1,'text',0)

Or

VALUES ("1","1","text","0") <-- if you need to double quote all values?

Looking at this link, it suggests inserts are done with single quote around the text and no quotes around the numbers...

sqlite> INSERT INTO Books(Id, Title, Author, ISBN) ...> VALUES(1, 'War and Peace', 'Leo Tolstoy', '978-0345472403');

4
votes

There are two options for the error that I can see, if it's actually in the SQL you're showing;

  • You're not terminating each line with a ;, which makes SQLite not understand that it's two separate statements.

  • You forgot to quote 'text' in your insert statement.

Normally, the error you're stating is due to SQLite not finding the database you're asking it to open though, something that is very hard to debug without seeing any code.

1
votes

If you are getting this error from adding a new column to/or modifying your SQLite table, it's most likely because the table is still in your app cache. The easiest fix, if you are in development phase is to go Settings->Applications->Your App->Storage->Delete Data. And this will clear all cached memories.

Otherwise, programmatically, you can choose to DELETE TABLE YOUR_TABLE and recreate a new one.

Hope this helps the others as well.

0
votes

This problem can occur when the cursor is not closed. in android and several language , all cursor should be closed before run another query.

you should to find all "select" query before new query and close all curser

    final Cursor cur = mDb.rawQuery(
        "select * from mytable;",
.
.
.
cur.close();

if all cursor is closed and your query is correct , Your code will work properly.