I was trying to use a SQLite database to store some to-do data in my app. And it failed when I tried to put in some data. I have searched StackOverflow, changing database or table name didn't seem to work for my case. The error in logcat is
statement aborts at 5: [INSERT INTO Todos(Complete,Description,Title,Deadline) VALUES (?,?,?,?)] datatype mismatch
My SQLHelper class:
public class DatabaseHelper extends SQLiteOpenHelper {
private static final int DATABASE_VERSION = 1;
// Database name
private static final String DATABASE_NAME = "todoManager";
// Todos table name
private static final String TABLE_TODOS = "Todos";
// Todos table columns names
private static final String KEY_TITLE = "Title";
private static final String KEY_DDL = "Deadline";
private static final String KEY_DES = "Description";
private static final String KEY_COM = "Complete";
public DatabaseHelper (Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
//String for creating table TODOS.
String sql = "CREATE TABLE IF NOT EXISTS " + TABLE_TODOS + " ("
+ KEY_TITLE + " TEXT, "
+ KEY_DDL + " TEXT, "
+ KEY_DES + " TEXT, "
+ KEY_COM + " TEXT)";
//Run string
db.execSQL(sql);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
//Drop old table, if it existed.
db.execSQL("DROP TABLE IF EXISTS " + TABLE_TODOS);
//Create tables again.
onCreate(db);
}
public void delete(TodoItem item) {
SQLiteDatabase db = this.getWritableDatabase();
db.delete(TABLE_TODOS, KEY_TITLE + " = " + item.getTITLE(), null);
}
public void addTodo(TodoItem item) {
SQLiteDatabase db = this.getWritableDatabase();
//Prepare and save all values for insertion.
ContentValues values = new ContentValues();
values.put(KEY_TITLE, item.getTITLE());
values.put(KEY_DDL, item.getDEADLINE());
values.put(KEY_DES, item.getDESCRIPTION());
values.put(KEY_COM, item.getCOMPLETED());
db.insert(TABLE_TODOS, null, values);
db.close();
}
public void updateTodo(TodoItem item) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
//Update rows
values.put(KEY_TITLE, item.getTITLE());
values.put(KEY_DDL, item.getDEADLINE());
values.put(KEY_DES, item.getDESCRIPTION());
values.put(KEY_COM, item.getCOMPLETED());
db.update(TABLE_TODOS, values, KEY_TITLE + " = ?", new String[]{String.valueOf(item.getTITLE())});
}
public ArrayList<TodoItem> getAllTodos() {
ArrayList<TodoItem> list = new ArrayList<TodoItem>();
SQLiteDatabase db = this.getWritableDatabase();
//Select all query
String query = "SELECT * FROM " + TABLE_TODOS;
Cursor cursor = db.rawQuery(query, null);
//Add to list
if(cursor.moveToFirst()) {
do {
TodoItem todo = new TodoItem(cursor.getString(0), cursor.getString(1),
cursor.getString(2),cursor.getString(3),cursor.getString(4));
todo.setTITLE(cursor.getString(0));
list.add(todo);
} while (cursor.moveToNext());
}
return list;
}
}
My TodoItem class (I didn't put the last field in my database, is it OK?):
public class TodoItem {
private String TITLE = TodoListProvider.TaskEntry.COL_TITLE;
private String DESCRIPTION = TodoListProvider.TaskEntry.COL_DESCRIPTION;
private String DEADLINE = TodoListProvider.TaskEntry.COL_DEADLINE;
private String COMPLETED = TodoListProvider.TaskEntry.COL_COMPLETED;
private String TIME_CREATED = TodoListProvider.TaskEntry.COL_TIME_CREATED;
public TodoItem(String tt, String dl,String dt,String cm,String tc) {
this.TITLE= tt;
this.DESCRIPTION = dt;
this.DEADLINE = dl;
this.COMPLETED = cm;
this.TIME_CREATED = tc;
}
public String getTITLE() {
return TITLE;
}
public void setTITLE(String TITLE) {
this.TITLE = TITLE;
}
public String getDESCRIPTION() {
return DESCRIPTION;
}
public void setDESCRIPTION(String DETAILS) {
this.DESCRIPTION = DETAILS;
}
public String getDEADLINE() {
return DEADLINE;
}
public void setDEADLINE(String DEADLINE) {
this.DEADLINE = DEADLINE;
}
public String getCOMPLETED() {
return COMPLETED;
}
public void setCOMPLETED(String COMPLETED) {
this.COMPLETED = COMPLETED;
}
public String getTIME_CREATED() {
return TIME_CREATED;
}
public void setTIME_CREATED(String TIME_CREATED) {
this.TIME_CREATED = TIME_CREATED;
}
}
This is how I called the database in a fragment (through a button click):
public class EditItemFragment extends Fragment {
private View view;
@Override
public View onCreateView(LayoutInflater inflater, ViewGroup container,
Bundle savedInstanceState) {
view = inflater.inflate(R.layout.fragment_edit_item, container, false);
return view;
}
@Override
public void onActivityCreated(Bundle savedInstanceState) {
// TODO Auto-generated method stub
super.onActivityCreated(savedInstanceState);
EditText title = (EditText) view.findViewById(R.id.editTitle);
EditText deadline = (EditText) view.findViewById(R.id.editDDL);
EditText description = (EditText) view.findViewById(R.id.editDescription);
EditText complete = (EditText) view.findViewById(R.id.editComplete);
String Title = title.getText().toString();
String Deadline = deadline.getText().toString();
String Description = description.getText().toString();
String Complete = complete.getText().toString();
final TodoItem item = new TodoItem(Title, Deadline, Description, Complete, "now");
Button saveBtn = (Button) getActivity().findViewById(R.id.btnSave);
saveBtn.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
// TODO Auto-generated method stub
DatabaseHelper helper = new DatabaseHelper(getActivity());
helper.addTodo(item);
}
});
Thanks a million if you could give me some idea about this!
I have exected ur code, it works fine basically that getwritable db not working in ur applications
so i have made some changes in ur code see below
Add these codes DatabaseHelper.class:
private static DatabaseHelper instance;
public static synchronized DatabaseHelper getHelper(Context context) {
if (instance == null)
instance = new DatabaseHelper(context);
return instance;
}
****`Add these In Ur Fragment Activity:`****
//Declare in private
DatabaseHelper helper;
SQLiteDatabase database;
//In on createView:
if (helper == null) {
helper = DatabaseHelper.getHelper(getActivity());
database = helper.getWritableDatabase();
}
Permissions in manifest file:
<uses-permission android:name="android.permission.READ_EXTERNAL_STORAGE" />
<uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE" />
Note: Whenever ur adding / modying changes in ur code at dbhelper class: please uninstall and reinstall app .