3

I am m making a timesheet application using Android. I am a beginner and don't have much knowledge about database programming.

Below is my code for the database I'm using:

public class TsData{

    private static final String TS_DATABASE = "timesheetdb";
    private static final int DATABASE_VERSION = 1;
    private static final String TS_TABLE = "database";

    public static final String ROW_ID = BaseColumns._ID;
    public static final String PROJ_NAME = "projname";
    public static final String START_TIME = "stime";
    public static final String DATE = "date";
    public static final String STOP_TIME="";
    public static final String ACT_NAME = "actname";
    public static final String TOTAL_TIME = "ttime";

    private DBhelper ourHelper;
    private final Context ourContext;
    private SQLiteDatabase ourDataBase;

    private static class DBhelper extends SQLiteOpenHelper{
        public DBhelper(Context context) {
            super(context, TS_DATABASE, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            String sql = "CREATE TABLE " + TS_TABLE + "(" + ROW_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
                + ACT_NAME + " TEXT NOT NULL, "
                + DATE + " TEXT NOT NULL, " 
                + START_TIME + " TEXT NOT NULL, "
                + STOP_TIME + " TEXT NOT NULL" + ");";
            db.execSQL(sql);
        }


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

    public TsData(Context c){
        ourContext=c;
    }

    public TsData open()throws SQLException{
        ourHelper = new DBhelper(ourContext);
        ourDataBase = ourHelper.getWritableDatabase();
        return this;
    }

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

    public long insertEntry(String act_name2, String startDate, String startTime, String stopTime) {
        ContentValues values = new ContentValues();
        values.put(ACT_NAME, act_name2);
        values.put(DATE, startDate);
        values.put(START_TIME, startTime);
        values.put(STOP_TIME, stopTime);
        System.out.println("record inserted");
        return ourDataBase.insert(TS_TABLE, null, values);
    }

    public String getData() {
        // TODO Auto-generated method stub
        String[] columns = new String[]{ROW_ID, ACT_NAME, DATE, START_TIME, STOP_TIME};
        Cursor cr = ourDataBase.query(TS_TABLE, columns, null, null, null, null, null);
        String result="";

        int iRow=cr.getColumnIndex(ROW_ID);
        int iName=cr.getColumnIndex(ACT_NAME);
        int istartTime=cr.getColumnIndex(START_TIME);
        int istopTime=cr.getColumnIndex(STOP_TIME);
        int iDate=cr.getColumnIndex(DATE);

        for(cr.moveToFirst();!cr.isAfterLast();cr.moveToNext()) {
            result=result+cr.getString(iRow)+"  "
                +cr.getString(iName)+"  "
                +cr.getString(iDate)+"  "
                +cr.getString(istartTime)+"  "
                +cr.getString(istopTime)+"\n";
        }
        return result;
    }

    public void deleteEntry(long rowNo) {
        // TODO Auto-generated method stub
        ourDataBase.delete(TS_TABLE, ROW_ID + "=" + rowNo, null);
    }
}

but I am getting the following error when I insert the record in the table:

10-21 20:33:11.044: I/Database(6597): sqlite returned: error code = 1, msg = near ",": syntax error

I have double checked all the syntax but not getting any solution.

laalto
  • 150,114
  • 66
  • 286
  • 303
user1763193
  • 41
  • 1
  • 3
  • Have you printed out the value of the sql variable for the CREATE statement? What query is it failing on? Do any of the parameters contain commas? – Smith Oct 21 '12 at 15:08
  • Composing SQL queries by hands is very error prone. Use ormlite instead: http://stackoverflow.com/questions/6645665/what-is-a-good-tutorial-for-using-ormlite-with-sqlite-and-android – alehro Oct 21 '12 at 15:13
  • I formatted your code, so it became a lot more readable. – Styxxy Oct 21 '12 at 21:00

1 Answers1

1

public static final String STOP_TIME=""; ??? Use some value for STOP_TIME.

or

CREATE TABLE   TS_TABLE  (  ROW_ID   INTEGER PRIMARY KEY AUTOINCREMENT, 
         ACT_NAME   TEXT NOT NULL, 
         DATE   TEXT NOT NULL,  
         START_TIME   TEXT NOT NULL );
rahul
  • 6,447
  • 3
  • 31
  • 42
  • changed to public static final String STOP_TIME="stptime"; but getting this error now 10-21 21:37:23.674: I/Database(6678): sqlite returned: error code = 1, msg = table database has no column named stptime – user1763193 Oct 21 '12 at 15:44
  • Is the table created successfully? what is the operation you were doing when you got this new problem read, update, create, delete? – rahul Oct 21 '12 at 16:16
  • Also, please try to give tables proper names. your table name "database" is a bad one. by definition, database is a set of tables. – rahul Oct 21 '12 at 16:18
  • the error is coming on the following line: return ourDataBase.insert(TS_TABLE, null, values); – user1763193 Oct 21 '12 at 17:49