CODEWITHSUNDEEP
androidsqliteandroid-studioandroid-sqlite
user7613788
user7613788

Reputation: 27

android.database.sqlite.SQLiteException: near "1": syntax error (code 1) when creating a new table to store data

package com.example.android.minerals;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import com.example.android.minerals.QuizContract.*;

import java.util.ArrayList;
import java.util.List;

public class QuizDbHelper extends SQLiteOpenHelper {

    private static final String DATABASE_NAME = "minerals.db";
    private static final int DATABASE_VERSION = 2;
    //private static final SQLiteDatabase.CursorFactory factory = null;

    private SQLiteDatabase db;
    public QuizDbHelper(Context context) {
        super( context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        this.db = db;

        final String SQL_CREATE_QUESTIONS_TABLE = "CREATE TABLE " +
                QuestionsTable.TABLE_NAME + " ( "+
                QuestionsTable._ID + " INTEGER PRIMARY KEY AUTOINCREMENT " +
                QuestionsTable.COLUMN_QUESTION + " TEXT, " +
                QuestionsTable.COLUMN_OPTION1 + " TEXT, " +
                QuestionsTable.COLUMN_OPTION2 + " TEXT, " +
                QuestionsTable.COLUMN_OPTION3 + " TEXT, " +
                ")";


        db.execSQL( SQL_CREATE_QUESTIONS_TABLE );
        fillQuestionsTable();
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL( "DROP TABLE IF EXISTS " + QuestionsTable.TABLE_NAME);
        onCreate(db);

    }

}

I got a error.

Caused by: android.database.sqlite.SQLiteException: near "1": syntax error (code 1): , while compiling: CREATE TABLE quiz_questions ( _id INTEGER PRIMARY KEY AUTOINCREMENT, question TEXT, option 1 TEXT, option 2 TEXT, option 3 TEXT, )

I get a syntax error (code 1) when I try to create the questions table.

Upvotes: 2

Views: 2538

Answers (4)

sticky bit
sticky bit

Reputation: 37472

The comma at the end of the definition of the last column has to be removed.

But the actual error you posted is generated because you have column names with spaces in them, option 1. The parser thinks the column name is option and doesn't know what to do with the 1. I strongly advise not to have column names (or names of any object) with spaces (or any other characters than alphanumerics and an underscore), that only leads to trouble. If you however insist using such names, you must enclose them in double quotes.

...
final String SQL_CREATE_QUESTIONS_TABLE = "CREATE TABLE " +
        "\"" + QuestionsTable.TABLE_NAME + "\" ( "+
        "\"" + QuestionsTable._ID + "\" INTEGER PRIMARY KEY AUTOINCREMENT " +
        "\"" + QuestionsTable.COLUMN_QUESTION + "\" TEXT, " +
        "\"" + QuestionsTable.COLUMN_OPTION1 + "\" TEXT, " +
        "\"" + QuestionsTable.COLUMN_OPTION2 + "\" TEXT, " +
        "\"" + QuestionsTable.COLUMN_OPTION3 + "\" TEXT " +
        ")";
...

But be aware, if you do so, you have to enclose the object name in double quotes in any query you use it afterwards.

Upvotes: 0

Akash Patel
Akash Patel

Reputation: 3141

Do not use empty space for column name.

error: Caused by: android.database.sqlite.SQLiteException: near "1": syntax error (code 1): , while compiling: CREATE TABLE quiz_questions ( _id INTEGER PRIMARY KEY AUTOINCREMENT, question TEXT, option 1 TEXT, option 2 TEXT, option 3 TEXT, )

solution: Remove space from column name.

Use option_1, option_2, option_3 instead of option 1, option 2, option 3.

And also remove last comma from query.

Your query should be as below,

CREATE TABLE quiz_questions ( _id INTEGER PRIMARY KEY AUTOINCREMENT, question TEXT, option_1 TEXT, option_2 TEXT, option_3 TEXT)

Upvotes: 0

IntelliJ Amiya
IntelliJ Amiya

Reputation: 75788

Caused by: android.database.sqlite.SQLiteException: near "1": syntax error (code 1): , while compiling:

A SQLite exception that indicates there was an error with SQL parsing or execution.

You should remove last comma from your statement , " + ")";

Try with

private static final String SQL_CREATE_QUESTIONS_TABLE = "CREATE TABLE IF NOT EXISTS "
        + QuestionsTable.TABLE_NAME + "(" + QuestionsTable._ID
        + " INTEGER PRIMARY KEY AUTOINCREMENT, "
        + QuestionsTable.COLUMN_QUESTION + " TEXT NOT NULL, "
        + QuestionsTable.COLUMN_OPTION1 + " TEXT NOT NULL, "
        + QuestionsTable.COLUMN_OPTION2 + " TEXT NOT NULL, "
        + QuestionsTable.COLUMN_OPTION3 + " TEXT NOT NULL )";

Then Clean-Rebuild-Run

Upvotes: 3

dev
dev

Reputation: 260

final String SQL_CREATE_QUESTIONS_TABLE = "CREATE TABLE " +
                QuestionsTable.TABLE_NAME + " ( "+
                QuestionsTable._ID + " INTEGER PRIMARY KEY AUTOINCREMENT " +
                QuestionsTable.COLUMN_QUESTION + " TEXT, " +
                QuestionsTable.COLUMN_OPTION1 + " TEXT, " +
                QuestionsTable.COLUMN_OPTION2 + " TEXT, " +
                QuestionsTable.COLUMN_OPTION3 + " TEXT, " +
                ")";

change this to:

final String SQL_CREATE_QUESTIONS_TABLE = "CREATE TABLE " +
                QuestionsTable.TABLE_NAME + " ( "+
                QuestionsTable._ID + " INTEGER PRIMARY KEY AUTOINCREMENT " +
                QuestionsTable.COLUMN_QUESTION + " TEXT, " +
                QuestionsTable.COLUMN_OPTION1 + " TEXT, " +
                QuestionsTable.COLUMN_OPTION2 + " TEXT, " +
                QuestionsTable.COLUMN_OPTION3 + " TEXT " +
                ")";

remove comma after last column name which is TEXT QuestionsTable.COLUMN_OPTION3 + " TEXT " + ")";

Upvotes: 0

Related Questions