Seb J Seb J -4 years ago 91
Android Question

Error in creation of table

I am encountering an error for my app such that I can't even launch it.

SqliteDatabase.java

public class SqliteDatabase extends SQLiteOpenHelper {

private static final int DATABASE_VERSION = 5;
private static final String DATABASE_NAME = "myexpenditure";
private static final String TABLE_EXPENDITURE = "myexpenditures";
private static final String COLUMN_ID = "id";
private static final String COLUMN_PLACE = "place";
private static final String COLUMN_DATE = "date";
private static final String COLUMN_AMOUNT = "amount";
private static final String COLUMN_CATEGORIES = "categories";
private static final String COLUMN_ADDITIONAL_INFO = "additional_info";


public SqliteDatabase(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
String CREATE_EXPENDITURE_TABLE = "CREATE TABLE " + TABLE_EXPENDITURE + "(" + COLUMN_ID + " INTEGER PRIMARY," + COLUMN_PLACE + " TEXT," + COLUMN_DATE + " TEXT," + COLUMN_AMOUNT + " INTEGER," + COLUMN_CATEGORIES + " TEXT," + COLUMN_ADDITIONAL_INFO + " TEXT" + ")" ;
sqLiteDatabase.execSQL(CREATE_EXPENDITURE_TABLE);
}

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

public List<Expenditure> listExpenditure() {
String sql = "select * from " + TABLE_EXPENDITURE;
SQLiteDatabase sqLiteDatabase = this.getReadableDatabase();
List<Expenditure> storeExpenditure = new ArrayList<>();
Cursor cursor = sqLiteDatabase.rawQuery(sql, null);
if (cursor.moveToFirst()) {
do {
int id = Integer.parseInt(cursor.getString(0));
String place = cursor.getString(1);
String date = cursor.getString(2);
int amount = Integer.parseInt(cursor.getString(3));
String category = cursor.getString(4);
String additional_info = cursor.getString(5);
storeExpenditure.add(new Expenditure(id, place, date, amount, category, additional_info));
} while (cursor.moveToNext());
}
cursor.close();
return storeExpenditure;
}

public void addExpenditure(Expenditure expenditure){
ContentValues values = new ContentValues();
values.put(COLUMN_PLACE,expenditure.getPlace());
values.put(COLUMN_DATE,expenditure.getDate());
values.put(COLUMN_AMOUNT,expenditure.getAmount());
values.put(COLUMN_CATEGORIES,expenditure.getCategories());
values.put(COLUMN_ADDITIONAL_INFO,expenditure.getAdditional_Info());
SQLiteDatabase db = this.getWritableDatabase();
db.insert(TABLE_EXPENDITURE,null,values);
}

public void updateExpenditure(Expenditure expenditure){
ContentValues values = new ContentValues();
values.put(COLUMN_PLACE,expenditure.getPlace());
values.put(COLUMN_DATE,expenditure.getDate());
values.put(COLUMN_AMOUNT,expenditure.getAmount());
values.put(COLUMN_CATEGORIES,expenditure.getCategories());
values.put(COLUMN_ADDITIONAL_INFO,expenditure.getAdditional_Info());
SQLiteDatabase db = this.getWritableDatabase();
db.update(TABLE_EXPENDITURE,values,COLUMN_ID + " =?",new String[]{String.valueOf(expenditure.getId())});

}

public Expenditure findExpenditure(String place){
String query = "Select * FROM" + TABLE_EXPENDITURE + "WHERE"+ COLUMN_PLACE + " = " + "place";
SQLiteDatabase sqLiteDatabase = this.getWritableDatabase();
Expenditure mExpenditure =null;
Cursor cursor=sqLiteDatabase.rawQuery(query,null);
if (cursor.moveToFirst()){
int id = Integer.parseInt(cursor.getString(0));
String expenditurePlace = cursor.getString(1);
String date = cursor.getString(2);
int amount = Integer.parseInt(cursor.getString(3));
String category = cursor.getString(4);
String additional_info = cursor.getString(5);
mExpenditure = new Expenditure(id,expenditurePlace,date,amount,category,additional_info);

}
cursor.close();
return mExpenditure;
}

public void deleteExpenditure(int id){
SQLiteDatabase sqLiteDatabase = this.getWritableDatabase();
sqLiteDatabase.delete(TABLE_EXPENDITURE,COLUMN_ID + " = ?",new String[]{String.valueOf(id)});
}


}

MainActivity.Java

public class MainActivity extends AppCompatActivity {

private static final String TAG = ExpenditureSummary.class.getSimpleName();
private SqliteDatabase mDatabase;


@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);

FrameLayout frameLayout=(FrameLayout)findViewById(R.id.activity_expenditure_summary);
RecyclerView expenditureView =(RecyclerView)findViewById(R.id.expenditure_list);
LinearLayoutManager linearLayoutManager = new LinearLayoutManager(this);
expenditureView.setLayoutManager(linearLayoutManager);

expenditureView.setHasFixedSize(true);
mDatabase = new SqliteDatabase(this);

List<Expenditure> allExpenditure = mDatabase.listExpenditure();

if (allExpenditure.size() > 0){
expenditureView.setVisibility(View.VISIBLE);
ExpenditureAdapter mAdapter = new ExpenditureAdapter(this,allExpenditure);
expenditureView.setAdapter(mAdapter);
} else {
expenditureView.setVisibility(View.GONE);
Toast.makeText(this, "There is no entries.Start adding now", Toast.LENGTH_SHORT).show();
}

FloatingActionButton fab =(FloatingActionButton)findViewById(R.id.fab);

fab.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
addTaskDialog();
}
});
}

private void addTaskDialog(){
LayoutInflater inflater = LayoutInflater.from(this);
View subview = inflater.inflate(R.layout.activity_edit_expenditure_details,null);

final EditText placeField = (EditText)subview.findViewById(R.id.enter_place);

//date picker
final EditText dateField =(EditText)subview.findViewById(R.id.enter_date);
final Calendar myCalendar = Calendar.getInstance();
final DatePickerDialog.OnDateSetListener datePickerListener = new DatePickerDialog.OnDateSetListener() {
@Override
public void onDateSet(DatePicker view, int year, int monthOfYear, int dayOfMonth) {
myCalendar.set(Calendar.YEAR,year);
myCalendar.set(Calendar.MONTH,monthOfYear);
myCalendar.set(Calendar.DAY_OF_MONTH,dayOfMonth);
String myFormat ="dd/MM/yyyy";
SimpleDateFormat simpleDateFormat =new SimpleDateFormat(myFormat, Locale.UK);
dateField.setText(simpleDateFormat.format(myCalendar.getTime()));
}
};
dateField.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
new DatePickerDialog(MainActivity.this,datePickerListener,myCalendar.get(Calendar.YEAR),myCalendar.get(Calendar.MONTH),myCalendar.get(Calendar.DAY_OF_MONTH)).show();
}
});

final EditText amountField =(EditText)subview.findViewById(R.id.enter_amount);

//Spinner
final Spinner spinnerCategories=(Spinner)subview.findViewById(R.id.enter_category);
ArrayAdapter<CharSequence> adapter = ArrayAdapter.createFromResource(this,R.array.categories, android.R.layout.simple_spinner_item);
adapter.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);
spinnerCategories.setAdapter(adapter);

final EditText additional_infoField = (EditText)subview.findViewById(R.id.enter_additional_info);

AlertDialog.Builder builder = new AlertDialog.Builder(this);
builder.setTitle("Add new expense");
builder.setView(subview);
builder.create();

builder.setPositiveButton("ADD EXPENSE", new DialogInterface.OnClickListener() {
@Override
public void onClick(DialogInterface dialogInterface, int i) {
final String place = placeField.getText().toString();
final String date = dateField.getText().toString();
final int amount = Integer.parseInt(amountField.getText().toString());
final String category = spinnerCategories.getSelectedItem().toString();
final String additional_info = additional_infoField.getText().toString();

if (TextUtils.isEmpty(place)|| amount <= 0 || TextUtils.isEmpty(category)||TextUtils.isEmpty(date)){
Toast.makeText(MainActivity.this, "Please input values", Toast.LENGTH_SHORT).show();
}
else {
Expenditure newExpenditure = new Expenditure(place,date,amount,category,additional_info);
mDatabase.addExpenditure(newExpenditure);
finish();
startActivity(getIntent());
}
}
});
builder.setNegativeButton("CANCEL",new DialogInterface.OnClickListener(){

@Override
public void onClick(DialogInterface dialogInterface, int i) {
Toast.makeText(MainActivity.this, "Task Cancelled", Toast.LENGTH_SHORT).show();
}
});
builder.show();
}

@Override
protected void onDestroy(){
super.onDestroy();
if (mDatabase != null){
mDatabase.close();
}
}


}

Error is as follows :


03-24 10:10:16.844 19709-19709/com.nebula.sebjohn.myexpenses E/AndroidRuntime: FATAL EXCEPTION: main
Process: com.nebula.sebjohn.myexpenses, PID: 19709
java.lang.RuntimeException: Unable to start activity ComponentInfo{com.nebula.sebjohn.myexpenses/com.nebula.sebjohn.myexpenses.MainActivity}: android.database.sqlite.SQLiteException: near ",": syntax error (code 1): , while compiling: CREATE TABLE myexpenditures(id INTEGER PRIMARY,place TEXT,date TEXT,amount INTEGER,categories TEXT,additional_info TEXT)
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2439)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2499)
at android.app.ActivityThread.access$900(ActivityThread.java:166)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1360)
at android.os.Handler.dispatchMessage(Handler.java:102)
at android.os.Looper.loop(Looper.java:148)
at android.app.ActivityThread.main(ActivityThread.java:5468)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:726)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:616)
Caused by: android.database.sqlite.SQLiteException: near ",": syntax error (code 1): , while compiling: CREATE TABLE myexpenditures(id INTEGER PRIMARY,place TEXT,date TEXT,amount INTEGER,categories TEXT,additional_info TEXT)
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:887)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:498)
at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
at android.database.sqlite.SQLiteProgram.(SQLiteProgram.java:58)
at android.database.sqlite.SQLiteStatement.(SQLiteStatement.java:31)
at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1674)
at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1605)
at Database.SqliteDatabase.onCreate(SqliteDatabase.java:39)
at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:251)
at android.database.sqlite.SQLiteOpenHelper.getReadableDatabase(SQLiteOpenHelper.java:187)
at Database.SqliteDatabase.listExpenditure(SqliteDatabase.java:51)
at com.nebula.sebjohn.myexpenses.MainActivity.onCreate(MainActivity.java:50)
at android.app.Activity.performCreate(Activity.java:6556)
at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1108)
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2392)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2499) 
at android.app.ActivityThread.access$900(ActivityThread.java:166) 
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1360) 
at android.os.Handler.dispatchMessage(Handler.java:102) 
at android.os.Looper.loop(Looper.java:148) 
at android.app.ActivityThread.main(ActivityThread.java:5468) 
at java.lang.reflect.Method.invoke(Native Method) 
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:726) 
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:616) 


I am new to Android app development.Please help me to solve this issue.I am trying to store the data within my phone itself using SQLiteDatabase.

Build.gradle(Module:app)

android {
compileSdkVersion 25
buildToolsVersion '25.0.0'
defaultConfig {
applicationId "com.nebula.sebjohn.myexpenses"
minSdkVersion 14
targetSdkVersion 25
versionCode 1
versionName "1.0"
testInstrumentationRunner "android.support.test.runner.AndroidJUnitRunner"
}
buildTypes {
release {
minifyEnabled false
proguardFiles getDefaultProguardFile('proguard-android.txt'), 'proguard-rules.pro'
}
}
}

dependencies {
compile fileTree(dir: 'libs', include: ['*.jar'])
androidTestCompile('com.android.support.test.espresso:espresso-core:2.2.2', {
exclude group: 'com.android.support', module: 'support-annotations'
})
testCompile 'junit:junit:4.12'
compile 'com.android.support:appcompat-v7:25.0.1'
compile 'com.intuit.sdp:sdp-android:1.0.3'
compile 'com.android.support:design:25.0.1'
compile 'com.android.support:cardview-v7:25.0.1'
compile 'com.android.support:recyclerview-v7:25.0.1'
compile 'com.android.support:support-v4:25.0.1'
compile 'com.google.code.gson:gson:2.6.1'
compile 'io.realm:android-adapters:1.4.0'
compile 'com.android.support.constraint:constraint-layout:1.0.2'
testCompile 'junit:junit:4.12'
}

Answer Source

There is syntax error in your case as well primary key one so i have fixed it so see difference

  String CREATE_EXPENDITURE_TABLE = "CREATE TABLE " + TABLE_EXPENDITURE + "(" + COLUMN_ID + " INTEGER PRIMARY KEY, " + COLUMN_PLACE + " TEXT, " +
        COLUMN_DATE + " TEXT, " + COLUMN_AMOUNT +
        " INTEGER, " + COLUMN_CATEGORIES + " TEXT, " + COLUMN_ADDITIONAL_INFO + " TEXT" + ")";
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download