CODEWITHSUNDEEP
androidsqlite
Bruce
Bruce

Reputation: 8849

data not found in sqlite database table

For my application i have created the sqlite database. i have added functions to add and retrieve data. i can see data added logcat message. but when receiving data it says no data found. my database code below

package com.burusoth1990.advertise;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseHandler extends SQLiteOpenHelper
{

    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME="ad_details";
    private static final String TABLE_NAME="advertisement";

    private static final String KEY_HEADING="heading";
    private static final String KEY_SRC="src";
    private static final String KEY_TYPE="type";
    private static final String KEY_DISTRICT="district";
    private static final String KEY_MORE="more";
    private static final String KEY_REF_ID="id";


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

    //create table for advertisements
    @Override
    public void onCreate(SQLiteDatabase db) 
    {
        String CREATE_AD_TABLE = "CREATE TABLE IF NOT EXIST " + TABLE_NAME + "(" +KEY_REF_ID+ "TEXT PRIMARY KEY,"+ KEY_HEADING + " TEXT," + KEY_DISTRICT + " TEXT,"+  KEY_TYPE + " TEXT" +KEY_SRC + " TEXT" +  KEY_MORE+ " TEXT" + ")";
        db.execSQL(CREATE_AD_TABLE);
    }

    //upgrade a existing table
    @Override
    public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) 
    {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        onCreate(db);
    }

    //add a advertisement into database
    public void add_ad(advertisements advertisement)
    {
        SQLiteDatabase db=this.getWritableDatabase();   
        ContentValues values=new ContentValues();
        values.put(KEY_REF_ID, advertisement._id);
        values.put(KEY_HEADING, advertisement._heading);
        values.put(KEY_DISTRICT, advertisement._district);
        values.put(KEY_TYPE, advertisement._type);
        values.put(KEY_SRC, advertisement._src);
        values.put(KEY_MORE, advertisement._more);

        db.insert(TABLE_NAME, null, values);
        Log.e("ADDED","data added to database");
        db.close();
    }

    //get a specific advertisement
    public advertisements get_ad(int ref_id)
    {
        return null;
    }

    //get a advertisement from database
    public List<advertisements> get_all()
    {
        List<advertisements> ad_list=new ArrayList<advertisements>();
        String select_query="SELECT * FROM "+TABLE_NAME;

        SQLiteDatabase db=this.getWritableDatabase();
        Cursor cursor=db.rawQuery(select_query, null);

        if(cursor.moveToFirst())
        {
            do{
                advertisements ad=new advertisements();
                ad.set_id(cursor.getString(0));
                ad.set_heading(cursor.getString(1));
                ad.set_district(cursor.getString(2));
                ad.set_type(cursor.getString(3));
                ad.set_src(cursor.getString(4));
                ad.set_more(cursor.getString(5));
                Log.e("DATABASE",cursor.getString(5));
                ad_list.add(ad);
            }
            while(cursor.moveToNext());
        }


        return ad_list;
    }

    //delete a advertisement from database
    public void delete_ad()
    {

    }

    //get number of advertisements in the database
    public int get_count()
    {
        String count_query="SELECT * FROM "+TABLE_NAME;
        SQLiteDatabase db=this.getReadableDatabase();
        Cursor cursor=db.rawQuery(count_query, null);
        cursor.close();
        db.close();

        return cursor.getColumnCount();
    }

}

my logcat says error while inserting

10-09 20:21:34.482: E/SQLiteDatabase(2778): Error inserting id=1000 more=s/s src=hi hi type=funny district=colombo heading=Testing
10-09 20:21:34.482: E/SQLiteDatabase(2778): android.database.sqlite.SQLiteException: table advertisement has no column named id (code 1): , while compiling: INSERT INTO advertisement(id,more,src,type,district,heading) VALUES (?,?,?,?,?,?)
10-09 20:21:34.482: E/SQLiteDatabase(2778):     at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
10-09 20:21:34.482: E/SQLiteDatabase(2778):     at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:882)
10-09 20:21:34.482: E/SQLiteDatabase(2778):     at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:493)
10-09 20:21:34.482: E/SQLiteDatabase(2778):     at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
10-09 20:21:34.482: E/SQLiteDatabase(2778):     at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
10-09 20:21:34.482: E/SQLiteDatabase(2778):     at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
10-09 20:21:34.482: E/SQLiteDatabase(2778):     at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1467)
10-09 20:21:34.482: E/SQLiteDatabase(2778):     at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1339)
10-09 20:21:34.482: E/SQLiteDatabase(2778):     at com.burusoth1990.advertise.DatabaseHandler.add_ad(DatabaseHandler.java:64)
10-09 20:21:34.482: E/SQLiteDatabase(2778):     at com.burusoth1990.advertise.Gadget_view.onCreate(Gadget_view.java:45)
10-09 20:21:34.482: E/SQLiteDatabase(2778):     at android.app.Activity.performCreate(Activity.java:5104)
10-09 20:21:34.482: E/SQLiteDatabase(2778):     at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1080)

Upvotes: 0

Views: 1700

Answers (4)

Tugrul
Tugrul

Reputation: 1808

I can not see any space between this texts.

"(" +KEY_REF_ID+ "TEXT PRIMARY KEY,"+

It concanated as idTEXT PRIMARY KEY

Secondly, you may try to write create string without these constans.

private static final String KEY_HEADING="heading";
    private static final String KEY_SRC="src";
    private static final String KEY_TYPE="type";
    private static final String KEY_DISTRICT="district";
    private static final String KEY_MORE="more";
    private static final String KEY_REF_ID="id";

Write like this; "CREATE TABLE IF NOT EXIST advertisement (id ... ,src ... , type ...) ";

Upvotes: 1

Johnny Z
Johnny Z

Reputation: 15457

You are missing a space:

Change:

"CREATE TABLE IF NOT EXIST " + TABLE_NAME + "(" +KEY_REF_ID+ "TEXT PRIMARY KEY,"

To:

"CREATE TABLE IF NOT EXIST " + TABLE_NAME + "(" +KEY_REF_ID+ " TEXT PRIMARY KEY,"

                                           Missing Space here ^

Upvotes: 1

Kuffs
Kuffs

Reputation: 35661

First you are making your SQL string much more complex by putting all the field names in static constants.

Your database oncreate will be called when you execute .getWritableDatabase. Move the call to your OpenHelper constructor as it is currently in your function that adds data but that function is unused.

Keep a reference to your database in your openhelper class rather than recreating in every data function.

Upvotes: 0

Danuofr
Danuofr

Reputation: 1710

First of all you have 

...+KEY_REF_ID + "TEXT PRIMARY KEY"...

which is a text primary key, you should replace with the following

...+KEY_REF_ID + "INTEGER PRIMARY KEY"...

Anyhow thats my suggestion it might help.

Upvotes: -1

Related Questions