R.Alla R.Alla - 5 months ago 47
Android Question

Caused by: android.database.sqlite.SQLiteException: no such column: TITLE (code 1): , while compiling: SELECT TITLE, STORY FROM MY_TABLE

I'm working on a simple application that contains short stories and I'm trying to use a database but I got an error:


Caused by: android.database.sqlite.SQLiteException: no such column: TITLE (code 1): , while compiling: SELECT TITLE, STORY FROM MY_TABLE


SQLiteActivity.java

package com.scriptos.testsqlit;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class SQLiteActivity {
private static String DBNAME = "Sqlite_simple.db";
private static String TABLE = "MY_TABLE";
private static String COLUMN_1 = "TITLE";
private static String COLUMN_2 = "STORY";


private SQLiteDatabase database;
private SQLiteOpenHelper helper_database;

private Context context;

public SQLiteActivity(Context c){
context = c;
}

public SQLiteActivity Read() throws android.database.SQLException {
helper_database = new SQLiteHelper(context, DBNAME, null, 1);
database = helper_database.getReadableDatabase();
return this;
}

public SQLiteActivity Write() throws android.database.SQLException {
helper_database = new SQLiteHelper(context, DBNAME, null, 1);
database = helper_database.getWritableDatabase();
return this;
}

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

public long insert(String title, String story){
ContentValues contentValues = new ContentValues();
contentValues.put(COLUMN_1, title);
contentValues.put(COLUMN_2, story);

return database.insert(TABLE, null, contentValues);
}

public int deleteAll(){
return database.delete(TABLE, null, null);
}

public String[] getAll(){
String[] columns = new String[]{COLUMN_1,COLUMN_2};
Cursor cursor = database.query(TABLE, columns,
null, null, null, null, null);

String [] result = new String[cursor.getCount()];
int title = cursor.getColumnIndex(COLUMN_1);
int story = cursor.getColumnIndex(COLUMN_2);

int a = 0;
for(cursor.moveToFirst(); !(cursor.isAfterLast()); cursor.moveToNext()){
result [a] =
cursor.getString(title) + " " +
cursor.getString(story) ;
a++;
}

return result;
}

public class SQLiteHelper extends SQLiteOpenHelper {

public SQLiteHelper(Context context, String name,
SQLiteDatabase.CursorFactory factory, int version) {
super(context, name, factory, version);
}

@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE + " (ID INTEGER KEY, "+COLUMN_1+ "TEXT, "+COLUMN_2 +"TEXT);");
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
}
}
}


MainActivity.java

package com.scriptos.testsqlit;

import android.app.Activity;
import android.content.Intent;
import android.net.Uri;
import android.os.Bundle;
import android.view.View;
import android.widget.AdapterView;
import android.widget.ArrayAdapter;
import android.widget.ListView;

public class MainActivity extends Activity {
ListView listView;
SQLiteActivity db;

@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.content_main);

listView = (ListView) findViewById(R.id.list);
String[] adapter = new String[] { "a", "b", "c",};

ArrayAdapter<String> i = new ArrayAdapter<String>(this,
android.R.layout.simple_list_item_1, adapter);

listView.setAdapter(i);

listView.setOnItemClickListener(new AdapterView.OnItemClickListener() {

@Override
public void onItemClick(AdapterView<?> parent, View view,
int position, long id) {
if (position == 0) {
Intent i = new Intent(Intent.ACTION_VIEW, Uri.parse("http://andrody.com/"));
startActivity(i);
}
if (position == 1) {
Intent i = new Intent(Intent.ACTION_VIEW, Uri.parse("https://m.facebook.com/andrody2015"));
startActivity(i);
}
if (position == 2) {
Intent i = new Intent(Intent.ACTION_VIEW, Uri.parse("https://m.youtube.com/channel/UCAGUgnnL47fU3TMRhKhkQwQ"));
startActivity(i);
}
}
});


db = new SQLiteActivity(this);
db.Write();
db.deleteAll();
db.insert("title1", "story1");
db.insert("title2","story2");
db.insert("title3","story3");
db.close();


SHOW();
}

public void SHOW() {
db.Read();
String [] x = db.getAll();
db.close();
}
}


content_main.xml

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="fill_parent"
android:layout_height="fill_parent"
android:orientation="vertical">

<ScrollView
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:background="#fff5ffcf">

<LinearLayout
android:layout_width="match_parent"
android:layout_height="fill_parent"
android:orientation="vertical">

<ListView
android:id="@+id/list"
android:layout_width="match_parent"
android:layout_height="wrap_content">
</ListView>
</LinearLayout>
</ScrollView>
</LinearLayout>


What should I do to correct this?

Answer

Your create table line;

db.execSQL("CREATE TABLE IF  NOT EXISTS " + TABLE + 
  " (ID INTEGER KEY, "+COLUMN_1+ "TEXT, "+COLUMN_2 +"TEXT);");

...generates the SQL...

CREATE TABLE IF  NOT EXISTS MY_TABLE (ID INTEGER KEY, TITLETEXT, STORYTEXT);

...which, due to missing spaces is not valid SQL and won't create the table.

Add the missing spaces to your create table, and it should work better.

db.execSQL("CREATE TABLE IF  NOT EXISTS " + TABLE + 
  " (ID INTEGER KEY, "+COLUMN_1+ " TEXT, "+COLUMN_2 +" TEXT);");

CREATE TABLE IF  NOT EXISTS MY_TABLE (ID INTEGER KEY, TITLE TEXT, STORY TEXT);
Comments