SQLiteException Error Code = 1 Syntax Error near 'columnName'

Question

I am new to android platform. I am working on a project in which I have to make a shared calender with two calendar interfaces on one activity. User can save an event with event details that is event title, event time, remainder, description, to and from etc. I have done notepad tutorial given on android website. I made my application on same pattern. But I am unable to locate error. Every time I run my code SQLite Exception with error code 1 hits. I have spent more than 20 hours on it but could not find any solution. I have tried to debug it too but no use at all. Kindly help me solving this error. It would be a great help. Thank you all in advance.

this where m trying to insert into the database

import android.app.ListActivity;
import android.content.Intent;
import android.database.Cursor;
import android.os.Bundle;
import android.view.View;
import android.widget.ArrayAdapter;
import android.widget.ListView;

    public class MyListView extends ListActivity {

        int j=0;
        private EventsDbAdapter mDbHelper;
        private Cursor EventsCursor;

        static final String[] hours = new String[]{
            "00:00",
            "01:00",
            "02:00",
            "03:00",
            "04:00",
            "05:00",
            "06:00",
            "07:00",
            "08:00",
            "09:00",
            "10:00",
            "11:00",
            "12:00",
            "13:00",
            "14:00",
            "15:00",
            "16:00",
            "17:00",
            "18:00",
            "19:00",
            "20:00",
            "21:00",
            "22:00",
            "23:00"
        };
        private int pos=0;
        private int ACTIVITY_CREATE=0;
        private ArrayAdapter<String> ar;
        /** Called when the activity is first created. */
        @Override
        public void onCreate(Bundle savedInstanceState) {
            super.onCreate(savedInstanceState);

            ar = new ArrayAdapter<String>(this,android.R.layout.simple_list_item_1, hours);
            setListAdapter(ar);
            getListView().setTextFilterEnabled(true);

            mDbHelper = new EventsDbAdapter(this);
            mDbHelper.open();

        }

        protected void onListItemClick(ListView l, View v, int position, long id) {
            super.onListItemClick(l, v, position, id);
            pos = position;

            String str = this.getListAdapter().getItem(position).toString();

            if(str.length()<6)
            {
                Intent myIntent = new Intent(MyListView.this, event.class);
                MyListView.this.startActivityForResult(myIntent,0);
            }
            else
            {
                Cursor c = EventsCursor;
                EventsCursor=mDbHelper.fetchAllNotes();
                startManagingCursor(EventsCursor);

                // Create an array to specify the fields we want to display in the list (only TITLE)
                String[] from = new String[]{EventsDbAdapter.KEY_TITLE};


                c.moveToPosition(position);

                Intent i = new Intent(this, event.class);

                i.putExtra(EventsDbAdapter.KEY_ROWID, id);

                i.putExtra(EventsDbAdapter.KEY_TITLE, c.getString(c.getColumnIndexOrThrow(EventsDbAdapter.KEY_TITLE)));

                i.putExtra(EventsDbAdapter.KEY_TO, c.getString(c.getColumnIndexOrThrow(EventsDbAdapter.KEY_TO)));

                i.putExtra(EventsDbAdapter.KEY_FROM, c.getString(c.getColumnIndexOrThrow(EventsDbAdapter.KEY_FROM)));

                i.putExtra(EventsDbAdapter.KEY_DESCRIPTION, c.getString(c.getColumnIndexOrThrow(EventsDbAdapter.KEY_DESCRIPTION)));

                startActivityForResult(i, 1);
            }
     }



        @Override
        protected void onActivityResult(int requestCode, int resultCode, Intent intent) {
               super.onActivityResult(requestCode, resultCode, intent);

               Bundle extras = intent.getExtras();

                if(requestCode==0 && resultCode==-1)
                {
                        String title = extras.getString(EventsDbAdapter.KEY_TITLE);
                        String to = extras.getString(EventsDbAdapter.KEY_TO);
                        String from = extras.getString(EventsDbAdapter.KEY_FROM);
                        String description = extras.getString(EventsDbAdapter.KEY_DESCRIPTION);
                        mDbHelper.createEvent(title, to,from,description);
                        hours[pos]=hours[pos]+title;
                        ar.notifyDataSetChanged();

                }
                else if(requestCode==1)
                {
                    Long rowId = extras.getLong(EventsDbAdapter.KEY_ROWID);
                    if (rowId != null) {
                        String editTitle = extras.getString(EventsDbAdapter.KEY_TITLE);
                        String to = extras.getString(EventsDbAdapter.KEY_TO);
                        String from = extras.getString(EventsDbAdapter.KEY_FROM);
                        String description = extras.getString(EventsDbAdapter.KEY_DESCRIPTION);
                        mDbHelper.updateEvent(rowId, editTitle, to,from,description);

                        hours[pos]=hours[pos]+editTitle;
                        ar.notifyDataSetChanged();

                    }
                }
            }
    }

`

public class EventsDbAdapter 
{
    public static final String KEY_TITLE = "title";
    public static final String KEY_TO = "tochecking";
    public static final String KEY_FROM = "fromchecking";
    public static final String KEY_DESCRIPTION = "description";
        public static final String KEY_ROWID = "_id";

        private static final String TAG = "EventsDbAdapter";
        private DatabaseHelper mDbHelper;
        private SQLiteDatabase mDb;

/**
 * Database creation sql statement
 */
    private static final String DATABASE_CREATE =
            " create table " + " DATABASE_TABLE " + " ("
            + KEY_ROWID + " integer primary key autoincrement,      "
            + KEY_TITLE + " text not null, "
            + KEY_TO + " text not null, "
            + KEY_FROM + " text not null,"+KEY_DESCRIPTION+" text not null);";


       private static final String DATABASE_NAME = "data";
       private static final String DATABASE_TABLE = "events";
       private static final int DATABASE_VERSION = 2;

       private final Context mCtx;

       private static class DatabaseHelper extends SQLiteOpenHelper {

       DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
       }
       @Override
       public void onCreate(SQLiteDatabase db) {

        System.out.print("testing");
        db.execSQL(DATABASE_CREATE);
       }

       @Override
       public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
           Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                   + newVersion + ", which will destroy all old data");
           db.execSQL("DROP TABLE IF EXISTS notes");
           onCreate(db);
       }
    }

    /**
     * Constructor - takes the context to allow the database to be
     * opened/created
     * 
     * @param ctx the Context within which to work
     */
      public EventsDbAdapter(Context ctx) {
        this.mCtx = ctx;
      }

    /**
     * Open the notes database. If it cannot be opened, try to create a new
     * instance of the database. If it cannot be created, throw an exception to
     * signal the failure
     * 
     * @return this (self reference, allowing this to be chained in an
     *         initialization call)
     * @throws SQLException if the database could be neither opened or created
     */
    public EventsDbAdapter open() throws SQLException {
        mDbHelper = new DatabaseHelper(mCtx);
        mDb =mDbHelper.getWritableDatabase();
        return this;
    }

    public void close() {
        mDbHelper.close();
    }


    /**
     * Create a new note using the title and body provided. If the note is
     * successfully created return the new rowId for that note, otherwise return
     * a -1 to indicate failure.
     * 
     * @param title the title of the note
     * @param body the body of the note
     * @return rowId or -1 if failed
     */
    public long createEvent(String title, String to , String from , String description) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_TITLE, title);
        initialValues.put(KEY_TO, to);
        initialValues.put(KEY_FROM, from);
        initialValues.put(KEY_DESCRIPTION, description);

        return mDb.insert(DATABASE_TABLE, null, initialValues);


    }

    /**
     * Delete the note with the given rowId
     * 
     * @param rowId id of note to delete
     * @return true if deleted, false otherwise
     */
    public boolean deleteNote(long rowId) {

        return mDb.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
    }

    /**
     * Return a Cursor over the list of all notes in the database
     * 
     * @return Cursor over all notes
     */
    public Cursor fetchAllNotes() {

        return mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_TITLE,KEY_TO,KEY_FROM,
                KEY_DESCRIPTION}, null, null, null, null, null);
    }

    /**
     * Return a Cursor positioned at the note that matches the given rowId
     * 
     * @param rowId id of note to retrieve
     * @return Cursor positioned to matching note, if found
     * @throws SQLException if note could not be found/retrieved
     */
    public Cursor fetchEvent(long rowId) throws SQLException {

        Cursor mCursor =

            mDb.query(true, DATABASE_TABLE, new String[] {KEY_ROWID, KEY_TITLE,KEY_TO,KEY_FROM,
                    KEY_DESCRIPTION}, KEY_ROWID + "=" + rowId, null,
                    null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;

    }

    /**
     * Update the note using the details provided. The note to be updated is
     * specified using the rowId, and it is altered to use the title and body
     * values passed in
     * 
     * @param rowId id of note to update
     * @param title value to set note title to
     * @param body value to set note body to
     * @return true if the note was successfully updated, false otherwise
     */
    public boolean updateEvent(long rowId, String title, String to ,String from,String description) {
        ContentValues args = new ContentValues();
        args.put(KEY_TITLE, title);
        args.put(KEY_TO, to);
        args.put(KEY_FROM, from);
        args.put(KEY_DESCRIPTION, description);

        return mDb.update(DATABASE_TABLE, args, KEY_ROWID + "=" + rowId, null) > 0;
    }
}

`

which create are you talking about ? database helper onCreate has been created — Muhamamd Awais
– Muhamamd Awais, Commented Nov 6, 2011 at 14:45
... Where you create the DB. Looks like rushman has it, though. The point was that you can't just say "I get this error" without providing any info about when/where/how you get the error. — Dave Newton
– Dave Newton, Commented Nov 6, 2011 at 14:50
i have seen the error code of sqlite and it says that when error code is 1 then its means that database is not created, yes rushman is right i have been trying on it from many last hours that why i think i forget this but i appreciat and also changed it as rushman said but still i got the expection — Muhamamd Awais
– Muhamamd Awais, Commented Nov 6, 2011 at 15:00
How are you using this class? In other words, at what point is the database supposed to be created, and how? — Dave Newton
– Dave Newton, Commented Nov 6, 2011 at 15:04

rushman · Accepted Answer · 2011-11-06 14:45:54Z

1

Is this the problem line?

" create table " + " DATABASE_TABLE " + " ("

Here, DATABASE_TABLE is a string i.e. you issue CREATE TABLE DATABASE_TABLE

Later you refer to DATABASE_TABLE the variable i.e. "events"

answered Nov 6, 2011 at 14:45

rushman

5722 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Muhamamd Awais Over a year ago

thanks rushman for you suggestion i have changed the "DATABASE_TABLE" to DATABASE_TABLE but still there is an execption

rushman Over a year ago

OK. Another suggestion. Is it that you have columns named "from" and "to"? These are SQL reserved words.

Muhamamd Awais Over a year ago

hello rushman i have edited the question but still its not working! :(

rushman Over a year ago

Hi. Looking at the updated code I still see DATABASE_TABLE as a string. Is this the case? i.e. private static final String DATABASE_CREATE = " create table " + " DATABASE_TABLE " + " ("

Atul Bhardwaj Over a year ago

@rushman thanks, you solved my problem. I was using "set" in my table that is SQL reserved word and because of that it was giving Exception. :)

Collectives™ on Stack Overflow

SQLiteException Error Code = 1 Syntax Error near 'columnName'

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related