Another Android Blog

Insights into those hard to solve Android Development problems

Skip to: Content | Sidebar | Footer

Android Database Tutorial

4 August, 2010 (04:05) | Database | By: Randall Mitchell

Getting Started

This tutorial will cover the basics of using the SQLite database tools in Android. I am going to try to organize this tutorial so that it is easy to navigate, for myself and others, to use as a reference. At the same time, I will try to make it long enough so that people needing the extra help will be able to read through the entire tutorial to gain understanding on how to use databases in Android. Finally, I did some pretty heavy commenting in the code. You can jump straight to that if you prefer to read through just the code and comments. Let’s get started.

Android Database Tools

The Android SDK provides two basic tools that assist you in writing basic programs that access a database in the Android environment. The first tool is SQLiteOpenHelper – which is responsible for creating, opening, and upgrading a program’s database. The other tool is SQLiteDatabase – which is responsible for communicating changes to the data within the database. Once you know how to use these two classes, you should be able to write programs that create and communicate with an Android database.

In this tuturial, I will first go about creating a database manager class that can be modified and plugged into most apps. This program will make use of both the SQLiteOpenHelper and the SQLiteDatabase classes. Afterwords, we will create a simple application that will instantiate and use our new database manager class.


Table of Contents

Pages: 1 2 3 4 5 6 7 8 9

Comments

Comment from giumazzi
Time August 22, 2010 at 3:45 pm

I’m new to android world. Can You attach the entire eclipse project to understanda better?
Thank You

Comment from Randall Mitchell
Time August 23, 2010 at 6:53 pm

No Problem. I’ve added it to the last page: here.

Pingback from links for 2010-08-23 | Chris F. Waigl
Time August 24, 2010 at 1:02 am

[...] Another Android Blog » Android Database Tutorial Maybe useful. (tags: android database sql sqlite programming) [...]

Comment from muhammad babar naveed
Time August 26, 2010 at 5:21 pm

Dear Sir,
I start android development week ago . i have a lot of problem in database
your above example solve my all problem and broad my vision in database. I am very thankful to you. I hope GOD give you long life.

Comment from SATYA.N
Time August 31, 2010 at 7:46 am

hi,
Its a great tutorial.

I got a problem in retrieving data from the table.
Actually I have created a table and the table will be updated dynamically for every 13.6 seconds, and I need to get the last row of the table when it was updated.

when I am using the Max(ROWID) its saying misuse of Max() function.

Can you help me in this??
Thank You,
SATYA.N

Comment from Randall Mitchell
Time September 4, 2010 at 7:29 am

Sorry I am just getting back to this. I had notification going to the wrong email address. I’m guessing you’ve already moved on. If not… From what I can tell, you are using a good enough method for getting the last row. There are actually many ways you can accomplish the same thing. Row ID works fine. Using a timestamp would be another effective method.

I can’t say for sure what the problem is without first seeing the code. Your SQLite query should probably look something like this:

SELECT * FROM tableName WHERE rowID = (SELECT MAX(rowID) FROM tableName);

hope that helps!

Comment from Seven
Time September 7, 2010 at 2:37 pm

Great!
What about database with more than one table with foreign keys, etc ?

Comment from Randall Mitchell
Time September 8, 2010 at 4:08 pm

Hey Seven.

Creating multiple tables isn’t much harder, just more work. You should have a set of constants for each new table in your database manager class. In the onCreate() method of the SQLiteOpenHelper, you would create a query string and run a db.execSQL() command for each table. Of course, there will be a need to modify the existing methods or create new ones. On more complex apps, you could design your class like I’ve done my database manager class; but, then you may need to consider refactoring to keep things organized (i.e., make yourself a database manager interface and have db manager classes that implement it – but that’s an entirely different tutorial).

As far as foreign keys. I think that’s a bit much to discuss in a comment to a blog post. You might look at the following link for more information…

http://www.sqlite.org/foreignkeys.html

If you have questions from there. let me know. I don’t have plans at the moment to do a more advanced DB tutorial (or a part two)…maybe in the future sometime.

Comment from Siddharth
Time September 19, 2010 at 12:25 pm

Hello. I have made some changes to your example to accommodate 8 columns in the table. Also, instead of displaying them in the layout you have used for the example, I want to show just one column entries in a ListActivity. Could you help me with the modifications to the code, or at least give me some hints which will get me in the right direction?

Thanks in advance. Love the tutorial. ;)

Comment from Randall Mitchell
Time September 21, 2010 at 2:05 pm

Hey Siddharth, are you having any luck with your project? I’ve got a tutorial on ListView. The code is here. You might take a look at it. Sorry for the slowness (again). I’m taking four classes this semester and working full time so my times been pretty short lately. Have a look at that code and let me know if you need some additional assistance. Thanks for reading!

Comment from Siddharth
Time September 22, 2010 at 10:53 am

Thanks for the link Randall. Do take your own time with the classes, etc. The more time flies by, the more information I search and find newer things while I am at it. :)

I gave a look to the listview tutorial link you provided and it the last part which I think is more relevant to my situation. And unfortunately, that’s the one which is not coded. :(

My test application is based on this Database tutorial and if you could provide the code for the listview with this tut in context, it would be mighty helpful. But please don’t go out of way doing it. As and when time permits you to look into this works for me.

Many thanks for your help and this brilliant tutorial. I was fishing in muddy waters before chancing on your website. :)

Regards.

Comment from Randall Mitchell
Time September 22, 2010 at 5:50 pm

Hey Siddharth,

I’ve got a couple tips for you to hopefully get you pointed in the right direction. ListActivity can be used with a Cursor object. You can create a method inside the database class that returns all of the data from a column inside of a Cursor object. Here is the code I came up with:

public Cursor getColumn(String columnName)
{
	Cursor cursor = null;
	String[] columns = new String[1];
	columns[0] = columnName;
 
	try
	{
		cursor = db.query(tableName, columns, null, null, null, null, null);
	}
	catch (SQLiteException e)
	{
		Log.e("Database.addRow", "Database Error: " + e.toString());
		e.printStackTrace();
	}
 
	return cursor;
}

Here’s some code you can use inside of the ListActivity:

...
public class MyList extends ListActivity
{
	// bring in the database object
	AABDatabaseMananger db;
 
	...
 
	@Override
	onCreate(Bundle savedInstanceState)
	{
		...
		db = new AABDatabaseManager();
		String columnName = "my_db_column";  // better to use constants here
 
		// these arrays are used by the list adapter while connecting the data to the ListView
		String[] fields = new String[] { columnName };
		String[] views = new int[] { android.R.id.text1 }
 
		// create our cursor by calling the database method.
		Cursor cursor = db.getColumn(columnName);
 
		// call setListAdapter(), which is a method from the parent class.
		setListAdapter(new SimpleListAdapter(
			this,
			android.R.layout.simple_list_item_1,
			cursor,
			fields,
			views));
 
		...
	}
	...
}

Hopefully this will get you pointed in the right direction. If you need further explanation, let me know. Also, I did not test this out; so, if there are any errors, get back to me.

Good luck.

Comment from Siddharth
Time September 22, 2010 at 7:32 pm

Hey Randall.

Thanks a bunch for the quick reply. I will try it out first thing tomorrow morning. But judging by the tutorial, I think it will work like a charm anyway. Will post results as soon as its done.

Thanks a lot yet again for your patience. :)

Cheers….

Comment from Siddharth
Time September 23, 2010 at 7:27 pm

Whoops…. That’s, sadly, still a no go. Would it help if I was to give you a code of what I have done so far? Only if you have the time to look into it of course.

Thanks.

Comment from Siddharth
Time September 24, 2010 at 12:09 pm

Hey there. Just in case you get the time, please tell me where I am going wrong with this.

Code for the Adapter:

package com.dzinesunlimited.quotetogo;
 
import java.util.ArrayList;
 
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
 
public class DBAdapter
{
	// the Activity or Application that is creating an object from this class.
	Context context;
 
	// a reference to the database used by this application/object
	private SQLiteDatabase db;
 
	// These constants are specific to the database.  They should be 
	// changed to suit your needs.
	private final String DB_NAME = "quoteonthego";
	private final int DB_VERSION = 1;
 
	// These constants are specific to the database table.  They should be
	// changed to suit your needs.
	private final String TABLE_NAME = "projects";
	private final String TABLE_ROW_ID = "id";
	final static String TABLE_ROW_NAME = "name";
	private final String TABLE_ROW_TYPE = "type";
	private final String TABLE_ROW_CONTACTS = "contacts";
	private final String TABLE_ROW_HOURS = "hours";
	private final String TABLE_ROW_CHARGES = "charges";
	private final String TABLE_ROW_STARTDATE = "startdate";
	private final String TABLE_ROW_ENDDATE = "enddate";
	private final String TABLE_ROW_ESTIMATE = "estimate";
 
	public DBAdapter(Context context)
	{
		this.context = context;
 
		// create or open the database
		CustomSQLiteOpenHelper helper = new CustomSQLiteOpenHelper(context);
		this.db = helper.getWritableDatabase();
	}
 
 
 
 
	/**********************************************************************
	 * ADDING A ROW TO THE DATABASE TABLE
	 * 
	 * 
	 * the key is automatically assigned by the database
	 * @param rowStringOne the value for the row's first column
	 * @param rowStringTwo the value for the row's second column 
	 */
	public void addRow(	String rowName, String rowType, String rowContacts,
						String rowHours, String rowCharges, String rowStartDate, 
						String rowEndDate, String rowEstimate)
	{
		// this is a key value pair holder used by android's SQLite functions
		ContentValues values = new ContentValues();
		values.put(TABLE_ROW_NAME, rowName);
		values.put(TABLE_ROW_TYPE, rowType);
		values.put(TABLE_ROW_CONTACTS, rowContacts);
		values.put(TABLE_ROW_HOURS, rowHours);
		values.put(TABLE_ROW_CHARGES, rowCharges);
		values.put(TABLE_ROW_STARTDATE, rowStartDate);
		values.put(TABLE_ROW_ENDDATE, rowEndDate);
		values.put(TABLE_ROW_ESTIMATE, rowEstimate);
 
		// ask the database object to insert the new data 
		try{db.insert(TABLE_NAME, null, values);}
		catch(Exception e)
		{
			Log.e("DB ERROR", e.toString());
			e.printStackTrace();
		}
	}
 
 
 
	/**********************************************************************
	 * DELETING A ROW FROM THE DATABASE TABLE
	 * 
	 * This is an example of how to delete a row from a database table
	 * using this class. In most cases, this method probably does
	 * not need to be rewritten.
	 * 
	 * @param rowID the SQLite database identifier for the row to delete.
	 */
	public void deleteRow(long rowID)
	{
		// ask the database manager to delete the row of given id
		try {db.delete(TABLE_NAME, TABLE_ROW_ID + "=" + rowID, null);}
		catch (Exception e)
		{
			Log.e("DB ERROR", e.toString());
			e.printStackTrace();
		}
	}
 
	/**********************************************************************
	 * UPDATING A ROW IN THE DATABASE TABLE
	 * 
	 * @param rowID the SQLite database identifier for the row to update.
	 * @param rowStringOne the new value for the row's first column
	 * @param rowStringTwo the new value for the row's second column 
	 */ 
	public void updateRow(long rowID, 
			String rowName, String rowType, String rowContacts,
			String rowHours, String rowCharges, String rowStartDate, 
			String rowEndDate, String rowEstimate)
	{
		// this is a key value pair holder used by android's SQLite functions
		ContentValues values = new ContentValues();
		values.put(TABLE_ROW_NAME, rowName);
		values.put(TABLE_ROW_TYPE, rowType);
		values.put(TABLE_ROW_CONTACTS, rowContacts);
		values.put(TABLE_ROW_HOURS, rowHours);
		values.put(TABLE_ROW_CHARGES, rowCharges);
		values.put(TABLE_ROW_STARTDATE, rowStartDate);
		values.put(TABLE_ROW_ENDDATE, rowEndDate);
		values.put(TABLE_ROW_ESTIMATE, rowEstimate);
 
		// ask the database object to update the database row of given rowID
		try {db.update(TABLE_NAME, values, TABLE_ROW_ID + "=" + rowID, null);}
		catch (Exception e)
		{
			Log.e("DB Error", e.toString());
			e.printStackTrace();
		}
	}
 
	/**********************************************************************
	 * RETRIEVING A ROW FROM THE DATABASE TABLE
	 * 
	 * This is an example of how to retrieve a row from a database table
	 * using this class.  You should edit this method to suit your needs.
	 * 
	 * @param rowID the id of the row to retrieve
	 * @return an array containing the data from the row
	 */
	public ArrayList getRowAsArray(long rowID)
	{
		// create an array list to store data from the database row.
		// I would recommend creating a JavaBean compliant object 
		// to store this data instead.  That way you can ensure
		// data types are correct.
		ArrayList rowArray = new ArrayList();
		Cursor cursor;
 
		try
		{
			// this is a database call that creates a "cursor" object.
			// the cursor object store the information collected from the
			// database and is used to iterate through the data.
			cursor = db.query
			(
					TABLE_NAME,
					new String[] { 	TABLE_ROW_ID, TABLE_ROW_NAME, TABLE_ROW_TYPE,
									TABLE_ROW_CONTACTS, TABLE_ROW_HOURS, TABLE_ROW_CHARGES,
									TABLE_ROW_STARTDATE, TABLE_ROW_ENDDATE, TABLE_ROW_ESTIMATE},
					TABLE_ROW_ID + "=" + rowID,
					null, null, null, null, null
			);
 
			// move the pointer to position zero in the cursor.
			cursor.moveToFirst();
 
			// if there is data available after the cursor's pointer, add
			// it to the ArrayList that will be returned by the method.
			if (!cursor.isAfterLast())
			{
				do
				{
					rowArray.add(cursor.getLong(0));
					rowArray.add(cursor.getString(1));
					rowArray.add(cursor.getString(2));
					rowArray.add(cursor.getString(3));
					rowArray.add(cursor.getString(4));
					rowArray.add(cursor.getString(5));
					rowArray.add(cursor.getString(6));
					rowArray.add(cursor.getString(7));
					rowArray.add(cursor.getString(8));
				}
				while (cursor.moveToNext());
			}
 
			// let java know that you are through with the cursor.
			cursor.close();
		}
		catch (SQLException e) 
		{
			Log.e("DB ERROR", e.toString());
			e.printStackTrace();
		}
 
		// return the ArrayList containing the given row from the database.
		return rowArray;
	}
 
 
 
 
	/**********************************************************************
	 * RETRIEVING ALL ROWS FROM THE DATABASE TABLE
	 * 
	 * This is an example of how to retrieve all data from a database
	 * table using this class.  You should edit this method to suit your
	 * needs.
	 * 
	 * the key is automatically assigned by the database
	 */
 
	public ArrayList<ArrayList> getAllRowsAsArrays()
	{
		// create an ArrayList that will hold all of the data collected from
		// the database.
		ArrayList<ArrayList> dataArrays = new ArrayList<ArrayList>();
 
		// this is a database call that creates a "cursor" object.
		// the cursor object store the information collected from the
		// database and is used to iterate through the data.
		Cursor cursor;
 
		try
		{
			// ask the database object to create the cursor.
			cursor = db.query(
					TABLE_NAME,
					new String[]{TABLE_ROW_ID, TABLE_ROW_NAME, TABLE_ROW_TYPE,
							TABLE_ROW_CONTACTS, TABLE_ROW_HOURS, TABLE_ROW_CHARGES,
							TABLE_ROW_STARTDATE, TABLE_ROW_ENDDATE, TABLE_ROW_ESTIMATE},
					null, null, null, null, null
			);
 
			// move the cursor's pointer to position zero.
			cursor.moveToFirst();
 
			// if there is data after the current cursor position, add it
			// to the ArrayList.
			if (!cursor.isAfterLast())
			{
				do
				{
					ArrayList dataList = new ArrayList();
 
					dataList.add(cursor.getLong(0));
					dataList.add(cursor.getString(1));
					dataList.add(cursor.getString(2));
					dataList.add(cursor.getString(3));
					dataList.add(cursor.getString(4));
					dataList.add(cursor.getString(5));
					dataList.add(cursor.getString(6));
					dataList.add(cursor.getString(7));
					dataList.add(cursor.getString(8));
 
					dataArrays.add(dataList);
				}
				// move the cursor's pointer up one position.
				while (cursor.moveToNext());
			}
		}
		catch (SQLException e)
		{
			Log.e("DB Error", e.toString());
			e.printStackTrace();
		}
 
		// return the ArrayList that holds the data collected from
		// the database.
		return dataArrays;
	}
 
 
	public Cursor getColumn(String columnName) {
		Cursor cursor = null;
		String[] columns = new String[1];
		columns[0] = columnName;
 
		try
		{
			cursor = db.query(TABLE_NAME, columns, TABLE_ROW_NAME, null, null, null, null, null);
		}
		catch (SQLiteException e)
		{
			Log.e("Database.addRow", "Database Error: " + e.toString());
			e.printStackTrace();
		}
 
		return cursor;
	}
 
 
	/**********************************************************************
	 * THIS IS THE BEGINNING OF THE INTERNAL SQLiteOpenHelper SUBCLASS.
	 * 
	 * I MADE THIS CLASS INTERNAL SO I CAN COPY A SINGLE FILE TO NEW APPS 
	 * AND MODIFYING IT - ACHIEVING DATABASE FUNCTIONALITY.  ALSO, THIS WAY 
	 * I DO NOT HAVE TO SHARE CONSTANTS BETWEEN TWO FILES AND CAN
	 * INSTEAD MAKE THEM PRIVATE AND/OR NON-STATIC.  HOWEVER, I THINK THE
	 * INDUSTRY STANDARD IS TO KEEP THIS CLASS IN A SEPARATE FILE.
	 *********************************************************************/
 
	/**
	 * This class is designed to check if there is a database that currently
	 * exists for the given program.  If the database does not exist, it creates
	 * one.  After the class ensures that the database exists, this class
	 * will open the database for use.  Most of this functionality will be
	 * handled by the SQLiteOpenHelper parent class.  The purpose of extending
	 * this class is to tell the class how to create (or update) the database.
	 * 
	 * @author Randall Mitchell
	 *
	 */
	private class CustomSQLiteOpenHelper extends SQLiteOpenHelper
	{
		public CustomSQLiteOpenHelper(Context context)
		{
			super(context, DB_NAME, null, DB_VERSION);
		}
 
		@Override
		public void onCreate(SQLiteDatabase db)
		{
			// This string is used to create the database.  It should
			// be changed to suit your needs.
			String newTableQueryString = "create table " +
			TABLE_NAME +
			" (" +
			TABLE_ROW_ID + " integer primary key autoincrement not null," +
			TABLE_ROW_NAME + " text," +
			TABLE_ROW_TYPE + " text," +
			TABLE_ROW_CONTACTS + " text," +
			TABLE_ROW_HOURS + " text," +
			TABLE_ROW_CHARGES + " text," +
			TABLE_ROW_STARTDATE + " text," +
			TABLE_ROW_ENDDATE + " text," +
			TABLE_ROW_ESTIMATE + " text" +
			");";
			// execute the query string to the database.
			db.execSQL(newTableQueryString);
		}
 
 
		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
		{
			// NOTHING TO DO HERE. THIS IS THE ORIGINAL DATABASE VERSION.
			// OTHERWISE, YOU WOULD SPECIFIY HOW TO UPGRADE THE DATABASE.
		}
	}
}

————————————————————————————————

Code for the JAVA file:

package com.dzinesunlimited.quotetogo;
 
import android.app.ListActivity;
import android.database.Cursor;
import android.os.Bundle;
import android.widget.SimpleCursorAdapter;
 
public class EditProject extends ListActivity {	
	@Override
	protected void onCreate(Bundle savedInstanceState) {
 
		DBAdapter db;
 
		// TODO Auto-generated method stub
		super.onCreate(savedInstanceState);
 
		db = new DBAdapter(this);
 
		String columnName = "TABLE_ROW_NAME";  // better to use constants here
 
		// these arrays are used by the list adapter while connecting the data to the ListView
		String[] fields = new String[] { columnName };
		int[] views = new int[] { android.R.id.text1 };
 
		// create our cursor by calling the database method.
		Cursor cursor = db.getColumn(columnName);
 
		// call setListAdapter(), which is a method from the parent class.
		setListAdapter(new SimpleCursorAdapter(
			this,
			android.R.layout.simple_list_item_1,
			cursor,
			fields,
			views));
	}
}

Hope I don’t end up taking too much of your time. Thanks in advance.

Comment from vicky
Time September 28, 2010 at 7:15 am

Can any one help me for copy or use the assets folder database to data/data/package/databases folder…

Please help me..

Comment from Randall Mitchell
Time September 28, 2010 at 5:01 pm

@Sid. A couple things. Could you patch in the xml file as well. Also, the line in your activity class that looks like this:

String columnName = "TABLE_ROW_NAME";  // better to use constants here

should look like this based on your adapter class:

String columnName = db.TABLE_ROW_NAME;

You’ll need to change all of the static final variables in the adapter from private to public. That’s what I meant by “better to use constants”. Right now, you are pulling values from a row that doesn’t exist.

@vicky I didn’t really cover using an existing database (I think that’s what your after). There is a tutorial here that may have what you are looking for.

Comment from Siddharth
Time October 1, 2010 at 5:38 am

Hey there.

Sorry for the late reply. Was traveling on business and could not keep up with the blog. I did a lot of trial and error and finally got the solution. And your suggestion was absolutely on target. That was what I was missing. Along with that, I had absolutely no luck with the DB Adapter. Was messing up the code doing various things. Finally I realized that I could use debugging to see what I am doing wrong. And bingo. Got the solution. :)

Let me know if you would like to see the code. Slightly different from your suggestion, but pretty much takes after it.

Many thanks yet again.

Comment from Azeem
Time October 7, 2010 at 1:21 pm

I am very thankful to the author who wrote this wonderful article which helped me to understand the android database concept.
Thank You so much!!!

Comment from Siddharth
Time October 11, 2010 at 12:22 pm

Hey. I hope I wont bother you with another problem of mine. Instead of fetching records based on their ID, I need to fetch records based on their name. What do I need to change to get that functionality?

Thanks in advance.

Comment from Randall Mitchell
Time October 13, 2010 at 5:36 am

Hey Siddharth,

You could try something like this instead of the getAllIDs() method in the database object. It probably would have been better for me to do it this way in the first place. In this method, I’m just returning the cursor (actually copied this from a project I’m working on). You could of course pull the data from the cursor first if you’d prefer – just it similar to how I did in getAllIDs(). Let me know how that goes.

/**
 * Get one or more columns from a database table.
 * @param tableName the name of the table to get a column from.
 * @param columnNames an Array of column names to be retrieved.
 * @return a Cursor with all the given columns from a given table.
 */
public Cursor getColumns(String tableName, String[] columnNames)
{
	Cursor cursor = null;
 
	try
	{
		cursor = db.query(tableName, columnNames, null, null, null, null, null);
	}
	catch (SQLiteException e)
	{
		Log.e("Database.getColumns()", e.toString());
		e.printStackTrace();
	}
 
	return cursor;
}

Comment from Fiaz
Time October 19, 2010 at 12:01 pm

hey guys, all your help is excellent. I have tried to follow this tutorial (newbie) and cant get it to updatethe database how i needed. if i keep it at two row like in the example it works. the moment i add more of my own id doesnt.. any explantion on or has any had this same problem

Comment from fiaz
Time October 19, 2010 at 12:09 pm

hey Randall , all your work is really appreciated. I am new to android and have tried to follow your database tutorial and have managed to get it working fine. However when i add more rows to the database it doesnt seem to update the database. You currently have 2 rows in your example and i require 7 rows but no luck. I have follows just as in the tutorial no luck. Could i email you my eclipse project and see where it is i am goin wrong . Thanks randall

Comment from fiaz
Time October 20, 2010 at 9:22 am

Hey Randall, basically i created a student planner as a project of mine. I have read up on the android basics and im ok with that now. I need to create a profile section though.

the profile section will allow the student to add the following details on ONE screen:
1.student name
2. student Id
3.Lecturer Name
4. Lecturer Number
5.Lecurer Email
6. Unversity/college name
7. university/college number

the above seven need to be inputted by the student then put into database.

A SEPERATE screen is needed to show all of the above information.

a third screen needed to update the profile.

i have tried for several days to alter and try my methods along with this tutorial but have failed. soon as i start using more than two edittext boxeses it doenst seem to work. Sorry for this , really need your help .

Thanks Randall

Comment from Randall Mitchell
Time October 21, 2010 at 1:03 am

Hey Fiaz. Sorry to keep you hanging. I’ve been buried in a project and haven’t been coming out much. Have you been checking out the error log and console? Go to the debugging perspective and check the “LogCat”, “Error Log”, and Console windows for error messages (in red). Let me know if you find any leads there. Otherwise we can go over the code and try to find the problem.

Comment from kihore
Time October 21, 2010 at 11:24 am

Hai Randall Mitchell , Can u help me for copying or using the database in assets folder… i tried with the code below by placing database, which is created in sql manager in assets folder,
public class DataBaseHelper extends SQLiteOpenHelper {

//The Android’s default system path of your application database.
private static String DB_PATH = “/data/data/com.dharani.LazyApple/databases/”;

private static String DB_NAME = “ParkingHeroDB.sqlite”;

private SQLiteDatabase myDataBase;

private final Context myContext;

/**
* Constructor
* Takes and keeps a reference of the passed context in order to access to the application assets and resources.
* @param context
*/

public DataBaseHelper(Context context) {

super(context, DB_NAME, null, 1);

this.myContext = context;
}

/**
* Creates a empty database on the system and rewrites it with your own database.
* */
public void createDataBase() throws IOException{

boolean dbExist = checkDataBase();

if(dbExist){
//do nothing – database already exist
}else{

//By calling this method and empty database will be created into the default system path
//of your application so we are gonna be able to overwrite that database with our database.
this.getReadableDatabase();

try {

copyDataBase();

} catch (IOException e) {

throw new Error(“Error copying database”);

}
}

}

/**
* Check if the database already exist to avoid re-copying the file each time you open the application.
* @return true if it exists, false if it doesn’t
*/
private boolean checkDataBase(){

SQLiteDatabase checkDB = null;

try{
String myPath = DB_PATH + DB_NAME;
checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);

}catch(SQLiteException e){

//database does’t exist yet.

}

if(checkDB != null){

checkDB.close();

}

return checkDB != null ? true : false;
}

/**
* Copies your database from your local assets-folder to the just created empty database in the
* system folder, from where it can be accessed and handled.
* This is done by transfering bytestream.
* */
private void copyDataBase() throws IOException{

//Open your local db as the input stream
InputStream myInput = myContext.getAssets().open(DB_NAME);

// Path to the just created empty db
String outFileName = DB_PATH + DB_NAME;

//Open the empty db as the output stream
OutputStream myOutput = new FileOutputStream(outFileName);

//transfer bytes from the inputfile to the outputfile

int length;
byte[] buffer = new byte[12024];
while ((length = myInput.read(buffer))>0){
myOutput.write(buffer, 0, length);
}

//Close the streams
myOutput.flush();
myOutput.close();
myInput.close();

}

public void openDataBase() throws SQLException{

//Open the database
String myPath = DB_PATH + DB_NAME;
myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);

}

@Override
public synchronized void close() {

if(myDataBase != null)
myDataBase.close();

super.close();

}

public void onCreate(SQLiteDatabase db) {

}

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

}
}

and code in main class

public void onCreate(Bundle icicle)
{
super.onCreate(icicle);
setContentView(R.layout.main);

DataBaseHelper myDbHelper = new DataBaseHelper(this);

myDbHelper = new DataBaseHelper(this);

try {

myDbHelper.createDataBase();

} catch (IOException ioe) {

throw new Error(“Unable to create database”);

}

try {

myDbHelper.openDataBase();

}catch(SQLException sqle){

throw sqle;
}
}
}

really need your help .

Comment from Fizz
Time October 21, 2010 at 11:59 am

Thanks for getting back Randall. ok i have tried it again and have doubled checked all the code and everything seems ok. BUT when im entering the data for the 7 text fields and click ADD. it emptys the texts like it should but not displaying the data at the bottom. I checked the error log and have posted it here below. it says it cant find coloumn 3 and 7 but i have set them up.

could i send you my code in a project file so you can run it and see whats what. i have tried to follow just like you said. Really appreciate all your work.

10-21 11:54:15.791: WARN/System.err(315): at android.app.ActivityThread.main(ActivityThread.java:4627)
10-21 11:54:15.791: WARN/System.err(315): at java.lang.reflect.Method.invokeNative(Native Method)
10-21 11:54:15.802: WARN/System.err(315): at java.lang.reflect.Method.invoke(Method.java:521)
10-21 11:54:15.802: WARN/System.err(315): at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:868)
10-21 11:54:15.802: WARN/System.err(315): at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:626)
10-21 11:54:15.811: WARN/System.err(315): at dalvik.system.NativeStart.main(Native Method)
10-21 11:54:30.782: DEBUG/dalvikvm(109): GC_EXTERNAL_ALLOC freed 442 objects / 23288 bytes in 139ms
10-21 11:54:32.732: DEBUG/dalvikvm(109): GC_EXTERNAL_ALLOC freed 404 objects / 22128 bytes in 70ms
10-21 11:54:34.452: DEBUG/dalvikvm(109): GC_EXTERNAL_ALLOC freed 402 objects / 21952 bytes in 70ms
10-21 11:54:37.512: INFO/Database(315): sqlite returned: error code = 1, msg = table profileTable has no column named table_row_seven
10-21 11:54:37.612: DEBUG/dalvikvm(315): GC_FOR_MALLOC freed 6370 objects / 378984 bytes in 87ms
10-21 11:54:37.622: INFO/Database(315): sqlite returned: error code = 1, msg = no such column: table_row_three
10-21 11:54:37.632: WARN/System.err(315): android.database.sqlite.SQLiteException: no such column: table_row_three: , while compiling: SELECT id, table_row_one, table_row_two, table_row_three, table_row_four, table_row_five, table_row_six, table_row_seven FROM profileTable
10-21 11:54:37.632: WARN/System.err(315): at android.database.sqlite.SQLiteCompiledSql.native_compile(Native Method)
10-21 11:54:37.632: WARN/System.err(315): at android.database.sqlite.SQLiteCompiledSql.compile(SQLiteCompiledSql.java:91)
10-21 11:54:37.641: WARN/System.err(315): at android.database.sqlite.SQLiteCompiledSql.(SQLiteCompiledSql.java:64)
10-21 11:54:37.641: WARN/System.err(315): at android.database.sqlite.SQLiteProgram.(SQLiteProgram.java:80)
10-21 11:54:37.641: WARN/System.err(315): at android.database.sqlite.SQLiteQuery.(SQLiteQuery.java:46)
10-21 11:54:37.641: WARN/System.err(315): at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:42)
10-21 11:54:37.641: WARN/System.err(315): at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1345)
10-21 11:54:37.641: WARN/System.err(315): at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1229)
10-21 11:54:37.641: WARN/System.err(315): at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1184)
10-21 11:54:37.641: WARN/System.err(315): at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1264)
10-21 11:54:37.641: WARN/System.err(315): at com.ProfileManager.ProfileDbHelper.getAllRowsAsArrays(ProfileDbHelper.java:224)
10-21 11:54:37.641: WARN/System.err(315): at com.ProfileManager.ProfileManager.updateTable(ProfileManager.java:258)
10-21 11:54:37.651: WARN/System.err(315): at com.ProfileManager.ProfileManager.addRow(ProfileManager.java:149)
10-21 11:54:37.651: WARN/System.err(315): at com.ProfileManager.ProfileManager.access$0(ProfileManager.java:132)
10-21 11:54:37.651: WARN/System.err(315): at com.ProfileManager.ProfileManager$1.onClick(ProfileManager.java:100)
10-21 11:54:37.651: WARN/System.err(315): at android.view.View.performClick(View.java:2408)
10-21 11:54:37.651: WARN/System.err(315): at android.view.View$PerformClick.run(View.java:8816)
10-21 11:54:37.651: WARN/System.err(315): at android.os.Handler.handleCallback(Handler.java:587)
10-21 11:54:37.651: WARN/System.err(315): at android.os.Handler.dispatchMessage(Handler.java:92)
10-21 11:54:37.651: WARN/System.err(315): at android.os.Looper.loop(Looper.java:123)
10-21 11:54:37.661: WARN/System.err(315): at android.app.ActivityThread.main(ActivityThread.java:4627)
10-21 11:54:37.661: WARN/System.err(315): at java.lang.reflect.Method.invokeNative(Native Method)
10-21 11:54:37.661: WARN/System.err(315): at java.lang.reflect.Method.invoke(Method.java:521)
10-21 11:54:37.661: WARN/System.err(315): at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:868)
10-21 11:54:37.661: WARN/System.err(315): at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:626)
10-21 11:54:37.661: WARN/System.err(315): at dalvik.system.NativeStart.main(Native Method)
10-21 11:54:37.831: DEBUG/dalvikvm(109): GC_EXTERNAL_ALLOC freed 392 objects / 21568 bytes in 60ms

theres the log above,
whats your email Randall plzzz

Comment from Fizz
Time October 21, 2010 at 12:03 pm

I wanted to get it so i could have like a tabular display. the first tab will have all the edit texts fileds where the student input the data. the second tab is where they can view all there details in a list and the third tab in which they can update the data. Sorry to pester but im stuck. i have looked up on tabular display and im learning that now. but dont know how it will integrate with your coz i cant even get the basic version working…i got your version with the 2 inputs working fine.. just not 7 like i need. :(

Comment from Fizz
Time October 24, 2010 at 8:11 pm

Hey randall i got your email and replied to it with the attachment ..have u got it…

Comment from Randall Mitchell
Time October 25, 2010 at 3:15 pm

typed on my samsung epic…i have. sorrry i have not gotten back on it yet. i had friends in from out of town and i am serving jury duty today…currently waiting for jury selection to begin. i will do my best to get to it tonight. i did briefly look at the code but did not notice anything right away.

Comment from Randall Mitchell
Time October 26, 2010 at 1:21 am

HI Fizz. I’ve sent you an email.

Comment from kavya
Time October 26, 2010 at 5:51 am

hi im new to database i tried ur program given in the tutorial plz give me a solution to this error
Cannot instantiate the type AABDatabaseManager.CustomSQLiteOpenHelper

Comment from mackern
Time October 26, 2010 at 11:38 am

Hi,
I get this error message when trying to add the clickhandler for the button. The method onClick(View) of type new View.OnClickListener(){} must override a superclass method
Im a both Android and Java newbie, could it be some settings of my Eclipse that is wrong?

Comment from Randall Mitchell
Time October 26, 2010 at 5:34 pm

@mackern
Briefly,

An object of a class that is a subclass of an abstract class must Override the abstract methods of that class. Meaning, if you make a non-abstract child of an abstract class, you must write its abstract methods. You do this by using @Override directive followed immediately by the non-abstract method. In Eclipse, you should be able to point at the code underlined in red and Eclipse will prompt you to let it add the missing method.
You want something that looks similar to:

addButton.setOnClickListener
(
	new View.OnClickListener()
	{
		@Override
		public void onClick(View v) {addRow();}
	}
);

Comment from Randall Mitchell
Time October 26, 2010 at 5:36 pm

@kavya I would not be able to figure that one out without more code/information. Feel free to zip and email me your code: mitch@anotherandroidblog.com.

Comment from Randall Mitchell
Time October 26, 2010 at 5:41 pm

@ FizzI haven’t really messed with tabs Fizz. I would imagine that they are not much different than any other container embedded in an Activity. You would create the tabs widget just like any other object and then do the work with it in java via reference findViewByID(). I’d also guess that creating custom widgets by extending regular widgets would be the best method for each tab page.

Comment from mackern
Time October 28, 2010 at 11:26 am

Randall, that was exactly what I had done when I got the error.
I read on some post on another forum, that I should change the Java Compiler Compliance level from 1.5 to 1.6 and that seemed to do the trick.
Another question though. How can I access the logfile which Log class writes to, I assume it is a file somewhere on the emulator?

Comment from Randall Mitchell
Time October 28, 2010 at 5:53 pm

Hey mackern,
On the top right of Eclipse (below the minimize, close, etc. buttons) is where you change perspectives. Switch to (or add) the Debug perspective there. It should be set to “Java” right now. Once in the Debug perspective, you can go to Window->Show View->LogCat. Honestly, I’ve never retrieved the log from file before so I couldn’t tell you where it is off hand.

Comment from Fon
Time November 8, 2010 at 1:49 pm

Hi Randall,

I’m trying to read data from a database and store the data into an array “int[] db_date”, but I have trouble accessing this “db_date” outside the try-catch section.
(I got an error “db_date cannot be solved to a variable” in the last line of the code below.)

Since I’m new to both Java and Android, it might be that the way I’m trying to store the data isn’t that good. Do you have any suggestions about how to store the data that can be read outside? Your answer will be very much appreciated.

public class CalendarAct extends Activity {
 
       AABDatabaseManager db; // the class that opens the database
 
       /** Called when the activity is first created. */
       @Override
       public void onCreate(Bundle savedInstanceState) {
    	   super.onCreate(savedInstanceState);
    	   setContentView(new DrawView(this));
       }
 
       private class DrawView extends View {
              private Paint paint;
              public DrawView(Context context) {
                     super(context);
              }
 
              protected void onDraw(Canvas canvas) {
                     super.onDraw(canvas);
 
         			try
        			{
        				// create the database manager object
        		        db = new DatabaseManager(CalendarAct.this);
 
        			    // get row information
        		    	ArrayList<ArrayList> data = db.getAllRowsAsArrays();
 
						// get size of database and create integer array of that size
        		    	int sizeOfDb = data.size();
        		    	int[] db_date = new int[sizeOfDb];
 
        		       	// iterate the ArrayList
        		       	for (int position=0; position &lt; sizeOfDb; position++)
        		       	{
        		       		ArrayList row = data.get(position);
 
							//Get data from database and write to db_date array
        		       		db_date[position] = Integer.parseInt(row.get(1).toString());
        		       	}
        			}
        			catch (Exception e)
        			{
        				Log.e("GetError", e.toString());
        			}
 
                int quotient = (int)(db_date[idx]/7);

Comment from Randall Mitchell
Time November 9, 2010 at 1:57 pm

Hey Fon. Where you declare a variable, as in ArrayList<ArrayList> data;, determines where you can use it. Because you declared the ArrayList inside of the try/catch block, it is a local variable and is destroyed as soon as the block has finished executing. The same is true if you declare a variable inside of a method. As soon as the method has finished executing, the variable would not longer be available after.

You need to declare the array outside of the try/catch block. If you want the array available anywhere inside of the class, at the top of the class you should have.

public class... {
       AABDatabaseManager db; // the class that opens the database
       ArrayList<ArrayList> data; // I would change data to something more meaningful that describes what the data is.
       ...

Then, in the onCreate() method, you instantiate you variables.

@Override
public void onCreate(Bundle savedInstanceState) {
       ...
       db = new DatabaseManager(CalendarAct.this);
       data = new ArrayList<ArrayList>();
}

And finally, you give the array it’s data in the try/catch block.

data = db.getAllRowsAsArrays();

I hope that clears it up for you.

Comment from Fon
Time November 10, 2010 at 1:37 pm

Hi Randall,
Thank you very much for your answer. It was very clear with example which helped me solve the problem.

Comment from Harshal Kulkarni
Time December 3, 2010 at 12:50 pm

Really Nice Work….
It helped me a lot… !!!!!
:) :) :)

Comment from Ford
Time December 18, 2010 at 2:08 pm

Hi , must say great tutorial – google should use this!

Comment from satish
Time December 31, 2010 at 7:12 am

your tutorial is very useful for me,because before that i have seen many tutorials about database ,but i did not get proper idea ,but when i see your tutorial i got a clear idea ,
one request from me ,
That is i want to display my data table data into another screen not bottom of the main screen ,what should i do i tried a lot but i did not get the data into another screen,plz help me out.

thanks.

Comment from Randall Mitchell
Time January 5, 2011 at 9:38 pm

hi satish. sorry for the delay. I am working on a tutorial that will show you how to use a single database object throughout an application. It involves creating a subclass of Android’s Application class, using your manifest file to tell you application to use the new class, creating an instance of the database object inside the application subclass, then creating a pointer to your application subclass within each of your activities. If you need to store data permanently, this may be the best way to go. Any application settings should be handled different. Also, data you only need temporarily should be passed through an intent.

Some of these tutorials take me longer to write than you’d expect. This one may not be done until late in the month; however, there are many like it online already.

Comment from adrian
Time January 7, 2011 at 6:39 am

Hi Randall,
Thanks for the great, succinct, tutorial. Could you tell me how I would add a spinner to your project for a user then select an table1 name.

I can then get the row id and parse the output to my program which currently only uses a static XML based array.

Many thanks,
Adrian

Comment from Randall Mitchell
Time January 10, 2011 at 6:01 pm

Hi Adrian. I haven’t really played around with the spinner before. This looks like a pretty good spinner tutorial here

Comment from adrian
Time January 11, 2011 at 2:34 am

Thanks Randall for the reply.
I had a look at the link and personally require a SQLite and Spinner example.
There are a few out there but ive looked extensively and found none with complete source examples.
I’m a learn by example kinda guy unfortunately.
Thanks,
Adrian

Comment from Mehmet Gonullu
Time January 16, 2011 at 5:32 am

Awesome Tutorial, the only one that explained it clear and easily, I would make a donation for you soon ;)

Comment from E
Time January 19, 2011 at 10:14 pm

Let me begin by saying this is a great writeup that is easy to follow. Nice work, Randall.

Fizz,
In the openDataBase method, do you have it set to OPEN_READONLY? I had to change mine to OPEN_READWRITE in order to have my database update to any row changes.

Adrian,
Check out stackoverflow.com as they have numerous questions and answers regarding spinners. I spent a lot of time getting dynamic spinners setup for my app.

Comment from Terry Bieritz
Time January 26, 2011 at 2:15 am

Hi Randall
I just found your site while roaming around looking for Android examples. I love what you’ve put together. And it sounds like your a busy fellow! I hope college is going well for you. I’m new to Android development and have a database challenge. I’d like to see if you’ve “been there” but wanted to make sure you are in a position to respond. Thanks for getting back when you have a moment.
Terry

Comment from Micheal
Time February 2, 2011 at 2:50 am

Great Tutorial. I have been looking for a long time for something like this, And you have done a great job putting it all together, in order for newbie’s like myself can understand………… I have been able to split up the main.xml into four others “add.xml” , “edit.xml” , “view.xml” , ”delete.xml” along with their corresponding java classes…The Main.xml now contains only the data table (TableLayout),Is it possible to add a OnClickListener to this tablelayout that brings up a menu showing “Edit” , ”View” and “Delete” and then once clicked pass the information over and take you to the corresponding xml ………….after many hours searching, I have come to the conclusion that it may be best to use a Listview for this if this is correct or not could you please advise and even better show me the way…Thanks

Comment from shakuri
Time February 7, 2011 at 4:27 am

Can someone please send me a tutorial/source or something about how to make dynamic spinners….i am a newbie ….THANKS IN ADVANCE FOR ANYONE THAT CAN HELP

Pingback from Bloggin' with Charlotte Backus
Time February 10, 2011 at 4:06 pm

[...] tutorial here. Categories: Project Tags: Android, project, [...]

Comment from KISHORE
Time February 17, 2011 at 7:23 am

Hi Randall Mitchell, my name is K. Kishore and i’m from india. I am trying to create a fuel calculator app. This tutorial is very much helpful to me. I followed the tutorial, every thing worked out well but i am unable to view the table in the UI and I feel that OnClickListener’s are not working good.

Please find time to go through the code and help me out.

thanks….

this is my cod for Main Activity FuelInfor.java

package com.kk.FuelInfo;

import java.util.ArrayList;

import android.app.Activity;
import android.app.ListActivity;
import android.content.Context;
import android.database.Cursor;
import android.os.Bundle;
import android.speech.tts.TextToSpeech;
import android.speech.tts.TextToSpeech.OnInitListener;
import android.util.Log;
import android.view.View;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.SimpleCursorAdapter;
import android.widget.TableLayout;
import android.widget.TableRow;
import android.widget.TextView;
import android.widget.Toast;

public class FuelInfo extends ListActivity implements OnInitListener {

EditText textFieldOne, textFieldTwo,textFieldThree,textFieldFour,textFieldFive,textFieldSix, idField, updateIDField, updateTextFieldOne,updateTextFieldTwo,
updateTextFieldThree,updateTextFieldFour,updateTextFieldFive,updateTextFieldSix;
TextToSpeech tx;
Button addButton, deleteButton, retrieveButton, updateButton;
private ArrayAdapter listAdapter;

TableLayout dataTable;
DataHandler db;

/** Called when the activity is first created. */
@Override
public void onCreate(Bundle savedInstanceState)
{
// this try catch block returns better error reporting to the log
try
{
// Android OS specific calls
super.onCreate(savedInstanceState);
setContentView(R.layout.main);

db = new DataHandler(this);
setupViews();

// make the buttons clicks perform actions
addButtonListeners();

// load the data table
updateTable();

}
catch (Exception e)
{
Log.e(“ERROR”, e.toString());
e.printStackTrace();
}

}
private void setupViews()
{
// THE DATA TABLE
dataTable= (TableLayout)findViewById(R.id.data_table);

// THE DATA FORM FIELDS
textFieldOne= (EditText)findViewById(R.id.text_field_one);
textFieldTwo= (EditText)findViewById(R.id.text_field_two);
idField= (EditText)findViewById(R.id.id_field);
updateIDField= (EditText)findViewById(R.id.update_id_field);
updateTextFieldOne= (EditText)findViewById(R.id.update_text_field_one);
updateTextFieldTwo= (EditText)findViewById(R.id.update_text_field_two);
updateTextFieldThree=(EditText)findViewById(R.id.update_text_field_three);
updateTextFieldFour=(EditText)findViewById(R.id.update_text_field_four);
updateTextFieldFive=(EditText)findViewById(R.id.update_text_field_five);
updateTextFieldSix=(EditText)findViewById(R.id.update_text_field_six);
// THE BUTTONS
addButton = (Button)findViewById(R.id.add_button);
deleteButton = (Button)findViewById(R.id.delete_button);
retrieveButton = (Button)findViewById(R.id.retrieve_button);
updateButton = (Button)findViewById(R.id.update_button);

}
private void addButtonListeners()
{

addButton.setOnClickListener
(
new View.OnClickListener()
{
@Override public void onClick(View v) {addRow(); tx.speak(“Updated”,TextToSpeech.QUEUE_FLUSH, null);}
}
);

deleteButton.setOnClickListener
(
new View.OnClickListener()
{
@Override public void onClick(View v) {deleteRow(); tx.speak(“Updated”,TextToSpeech.QUEUE_FLUSH, null);}
}
);

updateButton.setOnClickListener
(
new View.OnClickListener()
{
@Override public void onClick(View v) {updateRow(); tx.speak(“Updated”,TextToSpeech.QUEUE_FLUSH, null);}
}
);

retrieveButton.setOnClickListener
(
new View.OnClickListener()
{
@Override public void onClick(View v) {retrieveRow(); tx.speak(“Updated”,TextToSpeech.QUEUE_FLUSH, null);}
}
);

}
private void addRow()
{
try
{
// ask the database manager to add a row given the two strings
// this is addRow() in the activity calling addRow() in the database object
db.addRow
(
textFieldOne.getText().toString(),
textFieldTwo.getText().toString(),textFieldThree.getText().toString(),textFieldFour.getText().toString(),textFieldFive.getText().toString(),textFieldSix.getText().toString()
);

// request the table be updated
updateTable();

// remove all user input from the Activity
emptyFormFields();
}
catch (Exception e)
{
Log.e(“Add Error”, e.toString());
e.printStackTrace();
}
}
private void deleteRow()
{
try
{
// ask the database manager to delete the row with the give rowID.
db.deleteRow(Long.parseLong(idField.getText().toString()));

// request the table be updated
updateTable();

// remove all user input from the Activity
emptyFormFields();
}
catch (Exception e)
{
Log.e(“Delete Error”, e.toString());
e.printStackTrace();
}
}

private void retrieveRow()
{
try
{
// The ArrayList that holds the row data
ArrayList row;
// ask the database manager to retrieve the row with the given rowID
row = db.getRowAsArray(Long.parseLong(updateIDField.getText().toString()));

// update the form fields to hold the retrieved data
updateTextFieldOne.setText((String)row.get(1));
updateTextFieldTwo.setText((String)row.get(2));
updateTextFieldThree.setText((String)row.get(3));
updateTextFieldFour.setText((String)row.get(4));
updateTextFieldFive.setText((String)row.get(5));
updateTextFieldSix.setText((String)row.get(6));
}
catch (Exception e)
{
Log.e(“Retrieve Error”, e.toString());
e.printStackTrace();
}
}
private void updateRow()
{
try
{
// ask the database manager to update the row based on the information
// found in the corresponding user entry fields
db.updateRow
(
Long.parseLong(updateIDField.getText().toString()),
updateTextFieldOne.getText().toString(),
updateTextFieldTwo.getText().toString(),updateTextFieldThree.getText().toString(),updateTextFieldFour.getText().toString(),updateTextFieldFive.getText().toString(),updateTextFieldSix.getText().toString()
);

// request the table be updated
updateTable();

// remove all user input from the Activity
emptyFormFields();
}
catch (Exception e)
{
Log.e(“Update Error”, e.toString());
e.printStackTrace();
}
}
private void updateTable()
{
// delete all but the first row. remember that the count
// starts at one and the index starts at zero
while (dataTable.getChildCount() > 1)
{
// while there are at least two rows in the table widget, delete
// the second row.
dataTable.removeViewAt(1);
}

// collect all row data from the database and
// store it in a two dimensional ArrayList
ArrayList<ArrayList> data = db.getAllRowsAsArrays();

// iterate the ArrayList, create new rows each time and add them
// to the table widget.
for (int position=0; position < data.size(); position++)
{
TableRow tableRow= new TableRow(this);

ArrayList row = data.get(position);

TextView idText = new TextView(this);
idText.setText(row.get(0).toString());
tableRow.addView(idText);

TextView textOne = new TextView(this);
textOne.setText(row.get(1).toString());
tableRow.addView(textOne);

TextView textTwo = new TextView(this);
textTwo.setText(row.get(2).toString());
tableRow.addView(textTwo);
TextView textThree = new TextView(this);
textTwo.setText(row.get(3).toString());
tableRow.addView(textTwo);
TextView textFour= new TextView(this);
textTwo.setText(row.get(4).toString());
tableRow.addView(textTwo);
TextView textFive = new TextView(this);
textTwo.setText(row.get(5).toString());
tableRow.addView(textTwo);
TextView textSix = new TextView(this);
textTwo.setText(row.get(6).toString());
tableRow.addView(textTwo);

dataTable.addView(tableRow);
}
}
private void emptyFormFields()
{
textFieldOne.setText(“”);
textFieldTwo.setText(“”);
idField.setText(“”);
updateIDField.setText(“”);
updateTextFieldOne.setText(“”);
updateTextFieldTwo.setText(“”);
updateTextFieldThree.setText(“”);
updateTextFieldFour.setText(“”);
updateTextFieldFive.setText(“”);
updateTextFieldSix.setText(“”);
}
@Override
public void onInit(int status) {
// TODO Auto-generated method stub

}

}

This is my data handler code DataHandler.java
package com.kk.FuelInfo;

import java.io.Flushable;
import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.speech.tts.TextToSpeech;
import android.speech.tts.TextToSpeech.OnInitListener;
import android.util.Log;

public class DataHandler implements OnInitListener {

private SQLiteDatabase db; // a reference to the database manager class.
private final String DB_NAME = “Master”; // the name of our database
private final int DB_VERSION = 1; // the version of the database
Context context;
TextToSpeech tx;
// the names for our database columns
private final String TABLE_NAME = “Fuel_Info”;
private final String TABLE_ROW_No = “SNo”;
private final String TABLE_ROW_ONE = “FillDate”;
private final String TABLE_ROW_TWO = “FuelQuantity”;
private final String TABLE_ROW_THREE = “FCPLtr”;
private final String TABLE_ROW_FOUR = “FuelAvl”;
private final String TABLE_ROW_FIVE = “TotFAvl”;
private final String TABLE_ROW_SIX = “TotalFuelCost”;

public DataHandler(Context context) {

this.context=context;
MySQLiteOpenHelper helper = new MySQLiteOpenHelper(context);
this.db = helper.getWritableDatabase();

}

public void addRow(String FillDate, String FuelQuant,String FCPL,String FuelAvl,String TotFuel,String TFC)
{
// this is a key value pair holder used by android’s SQLite functions
ContentValues values = new ContentValues();

// this is how you add a value to a ContentValues object
// we are passing in a key string and a value string each time
values.put(TABLE_ROW_ONE, FillDate);
values.put(TABLE_ROW_TWO, FuelQuant);
values.put(TABLE_ROW_THREE, FCPL);
values.put(TABLE_ROW_FOUR, FuelAvl);
values.put(TABLE_ROW_FIVE, TotFuel);
values.put(TABLE_ROW_SIX, TFC);

// ask the database object to insert the new data
try
{
db.insert(TABLE_NAME, null, values);
}
catch(Exception e)
{
Log.e(“DB ERROR”, e.toString()); // prints the error message to the log
e.printStackTrace(); // prints the stack trace to the log
}
}

public void deleteRow(long rowID)
{
// ask the database manager to delete the row of given id
try
{
db.delete(TABLE_NAME, TABLE_ROW_No + “=” + rowID, null);
}
catch (Exception e)
{
Log.e(“DB ERROR”, e.toString());
e.printStackTrace();
}
}
private class MySQLiteOpenHelper extends SQLiteOpenHelper
{

public MySQLiteOpenHelper(Context context) {
super(context, DB_NAME, null, DB_VERSION);
// TODO Auto-generated constructor stub
}
// TODO: override the constructor and other methods for the parent class

@Override
public void onCreate(SQLiteDatabase db) {
String Query =
“create table ” +
TABLE_NAME +
” (” +
TABLE_ROW_No + ” integer primary key autoincrement not null,” +
TABLE_ROW_ONE + ” text,” +
TABLE_ROW_TWO + ” text” +
TABLE_ROW_THREE + ” text” +
TABLE_ROW_FOUR + ” text” +
TABLE_ROW_FIVE + ” text” +
TABLE_ROW_SIX + ” text” +
“);”;
tx.speak(“Database Created”,TextToSpeech.QUEUE_FLUSH, null);
// execute the query string to the database.
db.execSQL(Query);

}

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

}
}

public void updateRow(long rowID,String FillDate, String FuelQuant,String FCPL,String FuelAvl,String TotFuel,String TFC)
{
// this is a key value pair holder used by android’s SQLite functions
ContentValues values = new ContentValues();
values.put(TABLE_ROW_ONE, FillDate);
values.put(TABLE_ROW_TWO, FuelQuant);
values.put(TABLE_ROW_THREE, FCPL);
values.put(TABLE_ROW_FOUR, FuelAvl);
values.put(TABLE_ROW_FIVE, TotFuel);
values.put(TABLE_ROW_SIX, TFC);
// ask the database object to update the database row of given rowID
try {db.update(TABLE_NAME, values, TABLE_ROW_No + “=” + rowID, null);
tx.speak(“Updated”,TextToSpeech.QUEUE_FLUSH, null);
}
catch (Exception e)
{
Log.e(“DB Error”, e.toString());
e.printStackTrace();
}
}
public ArrayList getRowAsArray(long rowID)
{
// create an array list to store data from the database row.

ArrayList rowArray = new ArrayList();
Cursor cursor;

try
{
// this is a database call that creates a “cursor” object.
// the cursor object store the information collected from the
// database and is used to iterate through the data.
cursor = db.query
(
TABLE_NAME,
new String[] { TABLE_ROW_No, TABLE_ROW_ONE, TABLE_ROW_TWO ,TABLE_ROW_THREE,TABLE_ROW_FOUR,TABLE_ROW_FIVE,TABLE_ROW_SIX},
TABLE_ROW_No + “=” + rowID,
null, null, null, null, null
);

// move’s the pointer to position zero in the cursor.
cursor.moveToFirst();

// if there is data available after the cursor’s pointer, we are adding
// it to the ArrayList that will be returned by the method.
if (!cursor.isAfterLast())
{
do
{
rowArray.add(cursor.getLong(0));
rowArray.add(cursor.getString(1));
rowArray.add(cursor.getString(2));
rowArray.add(cursor.getString(3));
rowArray.add(cursor.getString(4));
rowArray.add(cursor.getString(5));
rowArray.add(cursor.getString(6));
rowArray.add(cursor.getString(7));

}
while (cursor.moveToNext());
}

tx.speak(“Updated”,TextToSpeech.QUEUE_FLUSH, null);
cursor.close();
}
catch (SQLException e)
{
Log.e(“DB ERROR”, e.toString());
e.printStackTrace();
}

// return the ArrayList containing the given row from the database.
return rowArray;
}
public ArrayList<ArrayList> getAllRowsAsArrays()
{
// create an ArrayList that will hold all of the data collected from
// the database.
ArrayList<ArrayList> dataArrays = new ArrayList<ArrayList>();

// this is a database call that creates a “cursor” object.
// the cursor object store the information collected from the
// database and is used to iterate through the data.
Cursor cursor;

try
{
// database object to create the cursor.
cursor = db.query(
TABLE_NAME,
new String[]{TABLE_ROW_No, TABLE_ROW_ONE, TABLE_ROW_TWO,TABLE_ROW_THREE,TABLE_ROW_FOUR,TABLE_ROW_FIVE,TABLE_ROW_SIX},
null, null, null, null, null
);

// move the cursor’s pointer to position zero.
cursor.moveToFirst();

// if there is data after the current cursor position,we are adding it
// to the ArrayList.
if (!cursor.isAfterLast())
{
do
{
ArrayList dataList = new ArrayList();

dataList.add(cursor.getLong(0));
dataList.add(cursor.getString(1));
dataList.add(cursor.getString(2));
dataList.add(cursor.getString(3));
dataList.add(cursor.getString(4));
dataList.add(cursor.getString(5));
dataList.add(cursor.getString(6));
dataList.add(cursor.getString(7));

dataArrays.add(dataList);
}
// move the cursor’s pointer up one position.
while (cursor.moveToNext());
tx.speak(“Retrived”,TextToSpeech.QUEUE_FLUSH, null);
}
}
catch (SQLException e)
{
Log.e(“DB Error”, e.toString());
e.printStackTrace();
}

// return the ArrayList that holds the data collected from
// the database.
return dataArrays;
}

@Override
public void onInit(int status) {
// TODO Auto-generated method stub
db.execSQL(“create table ” +
TABLE_NAME +
” (” +
TABLE_ROW_No + ” integer primary key autoincrement not null,” +
TABLE_ROW_ONE + ” text,” +
TABLE_ROW_TWO + ” text” +
TABLE_ROW_THREE + ” text” +
TABLE_ROW_FOUR + ” text” +
TABLE_ROW_FIVE + ” text” +
TABLE_ROW_SIX + ” text” +
“);”);
tx.speak(“DB CREATED”,TextToSpeech.QUEUE_FLUSH, null);
}

}

Layout main.xml

Comment from x1um1n
Time February 17, 2011 at 10:05 pm

Hi,
I’m new to android dev (and haven’t done any serious java in a few years) so I suspect I’m making a fairly stupid mistake. My app FC’s with a null pointer exception when I try and create a dbmanager object. I think I’m not passing the context correctly, but can’t figure out what I’m doing wrong:

[code]public class newListDialog extends Dialog implements OnClickListener {
Button btnOK;
Button btnCancel;
EditText txtListName;
Spinner cmbNewListStore;

DBManager db;

public newListDialog(Context context) {
super(context);
setContentView(R.layout.new_list_dlg);

//fixme:seriously broken WTF!!!!!!
db = new DBManager(this.getContext());

btnOK = (Button) findViewById(R.id.btnOK);
btnOK.setOnClickListener(this);

btnCancel = (Button) findViewById(R.id.btnCancel);
btnCancel.setOnClickListener(this);

txtListName = (EditText) findViewById(R.id.txtListName);

//Store Selector Combobox
String[] storez = context.getResources().getStringArray(R.array.storez);
//fixme: connect to the DB and fill storez with store names
//db.getAllStores().toArray(storez);

ArrayAdapter storezAdapter = new ArrayAdapter (context, android.R.layout.simple_spinner_item, storez);
cmbNewListStore = (Spinner) findViewById(R.id.cmbNewListStore);
cmbNewListStore.setAdapter(storezAdapter);

}

@Override
public void onClick(View v) {
if (v == btnOK) {
//db.addList(txtListName.getText().toString(), db.getStoreID(cmbNewListStore.getSelectedItem().toString()));

//clear the form
txtListName.setText("");
cmbNewListStore.setSelection(0);

dismiss();
} else {
if (v == btnCancel) {
//clear the form
txtListName.setText("");
cmbNewListStore.setSelection(0);

cancel();
}
}
}

}[/code]

any help would be much appreciated. If you need to see more code, it’s all available here: https://code.google.com/p/yasl/source/browse/#svn%2Ftrunk%2Fsrc%2Fuk%2Fco%2Fpenguinpower%2Fyasl

Thanks
x1um1n

Comment from JohnyXD
Time February 25, 2011 at 8:42 am

Great tutorial, easy enough to understand.
BTW I don’t know if anyone noticed but you sometimes use 8 arguments for db.query()

Comment from sazid
Time March 3, 2011 at 10:03 am

Hey…Randall…great tutorial…..the best tutorial on android i have seen so far…

Comment from Shaista Naaz
Time March 4, 2011 at 5:52 am

Awesome tutorial. thanks a tons. :)

Comment from Irena
Time March 4, 2011 at 1:19 pm

Great Tutorial. Thanks a lot! Please keep posting tutorial for Android :)

Comment from Imran Zahid
Time April 3, 2011 at 4:16 pm

i have completely copied your tutorial but my application can’t retrieve data by click get button.database file has created in data folder but i am not able to fetch data in table layout.please help me………….

Comment from Sayantini
Time April 25, 2011 at 8:50 am

Hi Randy, I have tried your code but it didnt work. I am surprised why. The build was a success. Even the emulator came up but it was blank even after launching the application. Do you have any idea why ?

Comment from Santosh
Time May 5, 2011 at 10:11 am

I am just reading your blog and by the way I just looked at your appeal section on donation and it is a genuine appeal. I am also sorry for not being able to help you on that part.
But I want to thank you from heart to your effort and the way you reply all queries and give your 100% to make readers understand fully.I found your blog very helpful and guys like you are always an inspiration for me to carry on and work hard.
Thanks again ;)

Comment from Randall Mitchell
Time May 5, 2011 at 2:36 pm

Hi Imran and Sayantini. Sorry I missed these. I’m not sure if I missed the notification emails, they didn’t get sent, or I just failed to follow up on it. You’ve probably got some sort of type in your code. If you give me a little more information, I may be able to help. Probably, you should investigate debugging techniques regarding the log but I will be happy to help.

Randall

Comment from laurence
Time May 7, 2011 at 12:24 am

Nice tutorial, just a few comments:

I was using the code from your tutorial in my application, however, since I was using the database in multiple activities, I noticed that logcat was giving me error messages likes: “close() was never explicitly called on database”

You should close the database and helper when you’re done using them:
http://stackoverflow.com/questions/4464892/android-error-close-was-never-explicitly-called-on-database

I would change the code sample above to something like this:


private CustomSQLiteOpenHelper helper;
private SQLiteDatabase db;

public DBProfileManager(Context context)
{
this.context = context;

// create or open the database
helper = new CustomSQLiteOpenHelper(context);
this.db = helper.getWritableDatabase();
}

public void close()
{
if (helper != null)
helper.close();

if (db != null)
db.close();
}

and then use it like:


DBProfileManager db = new DBProfileManager(ProfileActivity.this);
db.addRow(entryOne, entryTwo);
db.close();

Comment from m0rb
Time May 7, 2011 at 10:53 am

Hey Guys,

thx first to Randall who wrote this very helpful tutorial.

@laurence
I got the same problem as u have and i found the same close method used in other tutorials. It seems pretty simple to write the close Method for the helper Object but i still have an “basic understanding” problem of the usage of that particular helper class.

e.g.: I have several activities (or views), where i need to call DB operations. Do i have to instantiate the DB class just once in each new activity or do i have to do this in every single method !?

Would be very thankful for an answer !

greetings from Germany
m0rb

Comment from Randall Mitchell
Time May 7, 2011 at 5:55 pm

@laurence. Thanks for that. I have since found this to be a problem in this software. Basically, I refactored this class in subsequent software and need to update this tutorial. Passing out arrays would mean you could open and close the database inside of each method in the db object. Better would be to pass out custom objects that handle the data you need to collect and pass those out.

@m0rb there are different ways to do what you are looking for. One method is to create the db object inside of the activity class. Do that if the database will need to be accessed several times inside of the activity. You need to think about the trade off between size and speed. Storing in the activity uses a little more space the entire time the activity is open. Creating a new db object each time you call a method takes time and can slow your application down. If you call that method from inside of a loop, your application could very well slow to a crawl. Alternatively, create a custom android.app.Application object and store it there. It takes a little more setup but if you need the database in most of your activities this could be very useful. This would take a little more memory but help with performance because you only load the db class once each time your user uses your application. Generally speaking, these are nominal – maybe unnoticed – quantities unless you do something like call a database method inside of a loop and end up creating the database object over and over.

Check out the code in this link for an alternative structure to using a database class: http://www.anotherandroidblog.com/2011/03/07/a-very-basic-application/.

Comment from Sayantini
Time May 25, 2011 at 6:12 pm

Hi Randall, I always keep on coming back to your site. Randall, I would like some help from you for an app. If you could give me a some lead, it would be really great. I need to store a .csv file onto my app and fetch the data with date.

Comment from Randall Mitchell
Time May 25, 2011 at 6:29 pm

Hi Sayantini, it sounds like you will need a script to iterate through the cvs file. You will also need to be able to load the file into your program in some manner. Are you retrieving the file from a web server or locally? Storing and retrieving the data would be hard to explain in this format. Have you already started trying to get the code written?

Comment from n.a.
Time June 11, 2011 at 5:23 pm

This is the best article I’ve ever read about SQLite. Seriously. And I’ve read a lot, but the others are not comparable. It could be bigger, and I wouldn’t care, because it didn’t bored me.
Now I’m feelin like I had discovered the world. haha

Comment from John Scott
Time June 16, 2011 at 4:34 pm

What a fantastic blog post. This has just saved my life up against a very tight deadline. Just wanted to say thank you :)

Comment from Avi Kumar Manku
Time June 17, 2011 at 1:38 pm

Thanx, Randall Mitchell it is really a helpful tutorial
and nice

Pingback from android | niujins blog
Time June 22, 2011 at 2:29 am

Pingback from Remove deletion anomaly ? | Android JB
Time June 23, 2011 at 12:01 pm

[...] am developing an application where i build a database , i build my database with help of tutorial http://www.anotherandroidblog.com/2010/08/04/android-database-tutorial/ ,the code is working fine, but the problem is that when i delete an i item from a particular id, it [...]

Comment from Noman khan
Time July 14, 2011 at 8:42 am

Simply da best tutorial for DB

Comment from Shardul
Time July 14, 2011 at 6:06 pm

Nice Work there! Made the learning curve for Android DB super smooth for me!

Comment from Theo Kramer
Time July 30, 2011 at 7:35 pm

Great tutorial, including all details about typecasting and declarations to get data in and out of sqlite, and a working project, thanks a lot

Comment from Panneerselvam.G
Time August 4, 2011 at 8:20 am

Hey its really good ..
I was trouble in Database concept but now i cleared my all doubts.
Its superb:)

Pingback from SQLite issues. Please help!! – Android Forums
Time August 9, 2011 at 4:23 pm

[...] these database tutorials: Using your own SQLite database in Android applications | ReignDesign Blog Another Android Blog Android Database Tutorial I learned a lot from them. The first link deals a lot with creating and opening a database. The [...]

Comment from Niyi
Time August 21, 2011 at 2:53 pm

Hello Randall, I discovered your blog last week and I think it is absolutely fantastic. Thank you for posting up such helpful and carefully explained details.

I have carefully worked through your tutorials and have found them excellent. I do apologise but I have been stuck for 2 days now on the cursor to ListView example that you and Siddarth worked through a few months back. I just can’t see where I am going wrong and wondered if you can see anything obviously wrong please. Initially the application would crash when I opened the SimpleCursorAdapter file, now it gives a screen but nothing from the database. My AADBDatabaseManager and SimpleCursorAdapter code are below. Any advice would be very much appreciated. Regards Niyi

/*==================================================================================== */
public Cursor getColumn(String columnName)
{
Cursor cursor = null;
String[] columns = new String[1];
columns[0] = columnName;

try
{
cursor = db.query(TABLE_NAME, columns, null, null, null, null,null);
}
catch (SQLiteException e)
{
Log.e(“Database.addRow”, “Database Error: ” + e.toString());
e.printStackTrace();
}

return cursor;
}}

//=========================================================

package com.android.revision;

import android.app.ListActivity;
import android.database.Cursor;
import android.os.Bundle;
import android.widget.SimpleCursorAdapter;

public class ListPop extends ListActivity {
@Override
protected void onCreate(Bundle savedInstanceState) {

AABDatabaseManager db;

// TODO Auto-generated method stub
super.onCreate(savedInstanceState);

db = new AABDatabaseManager(this);

//String columnName = db.TABLE_ROW_ONE; // better to use constants here
String columnName = “db.TABLE_ROW_ONE”; // better to use constants here
// these arrays are used by the list adapter while connecting the data to the ListView
String[] fields = new String[]{columnName};
//String[] fields = new String[]{“id”,”table_row_one”,”table_row_two”};
int[] views = new int[]{android.R.id.text1};

// create our cursor by calling the database method.
Cursor cursor = db.getColumn(columnName);

// call setListAdapter(), which is a method from the parent class.
setListAdapter(new SimpleCursorAdapter(
this,
android.R.layout.simple_list_item_1,
cursor,
fields,
views));
}
}

Comment from Niyi
Time August 22, 2011 at 6:12 am

Hello Randall,
Please ignore my last post. I have picked through my code tidied it up and got it working. It took a few days but very useful learning. Many thanks again. Niyi

Comment from Randall Mitchell
Time August 22, 2011 at 1:06 pm

Hi Niyi, Glad to hear you found the solution. Sorry I was of no help. I’ve got family in town and am gearing up for school to start Thursday. I didn’t get your post until just now. Best regards, Randall.

Comment from Niyi
Time August 24, 2011 at 8:53 am

Hello Randall,
Absolutely nothing to apologise for. You are helping to educate people for free. Rather than just learning Android by rote I need to spend a lot more time reading what parameters the functions accept and that kind of thing. Best wishes with your school work, if this blog is anything to go by I am sure you will excel.
Best regards
Niyi

Comment from Fredrick
Time September 29, 2011 at 11:18 am

This is the greatest post about SQLite I have seen, Thanks Alot.

In your XML( )
android:id=”@+id/data_table”

Where and what is this (data_table) ?

I cant see it as a field, button or text. It is suppose to be a vurtiral palce where you temporiry store data?

Can you please advise?

Comment from Randall Mitchell
Time September 29, 2011 at 1:06 pm

HI and thanks. “data_table” is actually the table that the data is fed to in the user interface. When a user enters data and submits, the data is entered into the database and then a new row is added to “data_table” for user feedback. The table also feeds back updates and deletions. On page 7 you can see data_table being created in setupViews(), and it is recreated at each db modification (waste of processing but increase in simplicity for the tutorial) in updateTable() on the same page.

Cheers,
Randall

Comment from Fredrick
Time September 29, 2011 at 1:37 pm

Thanks for the quick reply. Can I please get in touch via email: adamblogger@yahoo.co.uk

I am strugling with my project and can need a hand. Moneys will be sorted.

Thanks.

Comment from Nina
Time November 10, 2011 at 7:10 am

Hye Randall,

Thanks for the tutorial. I added another edit text as input named TABLE_ROW_THREE into the system, but I got error saying

11-10 02:39:51.405: ERROR/DB Error(308): android.database.sqlite.SQLiteException: no such column: table_row_three: , while compiling: SELECT id, table_row_one, table_row_two, table_row_three FROM database_table

Where does the possible line located? The error clearly said at SELECT statement, but there is no SELECT statement in the coding. Or is it located somewhere else?

Comment from Randall Mitchell
Time November 10, 2011 at 12:42 pm

Based on the code you sent me and this error message I was able quickly to find at least one error in this section of code in AABDatabaseManager.java:

@Override
public void onCreate(SQLiteDatabase db)
{
// This string is used to create the database. It should
// be changed to suit your needs.
String newTableQueryString = “create table ” +
TABLE_NAME +
” (” +
TABLE_ROW_ID + ” integer primary key autoincrement not null,” +
TABLE_ROW_ONE + ” text,” +
TABLE_ROW_TWO + ” text” +
TABLE_ROW_THREE + ” text” +
“);”;
// execute the query string to the database.
db.execSQL(newTableQueryString);
}

You are missing a comma after the word text in the line:

TABLE_ROW_TWO + ” text” +
That line should look like:
TABLE_ROW_TWO + ” text,” +

Note that the line after that does not need a comma after text. I’ve made that mistake before. It can be hard to notice.

Good luck,
Randall

Comment from Nina
Time November 15, 2011 at 1:21 am

Hi Randall,

How to display the data_table in a new screen? Is it possible to do this without adding another activity?

Comment from Rajesh
Time December 23, 2011 at 7:09 am

Hi, Excellent in-depth tutorial.

One question:
In AndroidManifest.xml, for the TableLayout, you have declared a TableRow with 2 TextViews, but not used it anywhere. What is the purpose of declaring it? Can it be removed?

Comment from Randall Mitchell
Time December 24, 2011 at 2:52 am

Hi Rajesh. Honestly, it’s been a great while since I reviewed this code and don’t remember exactly. From a brief overview of the code in the activity class, I think you are right about these not being used. Possibly I added these for clarity for the readers, or just didn’t notice they were not used.

Comment from Randall Mitchell
Time December 24, 2011 at 2:58 am

@Nina. Sorry I missed answering your post – just noticed it. Here is my opinion on the matter for anyone else who reads this. If you follow the standards of android, a new “screen” is, in a round about way, a new activity. I would suggest creating a new activity if you want a new screen. I generally add the database object to a child of “Application” and access it within each activity through the application. There are other ways to do this (for example, adding a database object to each activity). My post of a very basic application has example code doing this:

http://www.anotherandroidblog.com/2011/03/07/a-very-basic-application

- Randall

Comment from mayur
Time January 23, 2012 at 5:35 am

Fantastic work!!!!!!
thanks a lot!!

Comment from Arpit Trivedi
Time February 5, 2012 at 8:50 am

thanks for your blog. can you give me simple example of android-database for more than one table….

Thanks in advance…

Comment from sheetal
Time March 5, 2012 at 7:36 pm

hi thanks for this brilliant tutorial. i am new to android so can you please tell me where i need to include AABDatabaseManager.java class file.

Comment from Randall Mitchell
Time March 5, 2012 at 8:11 pm

@sheetal Hi. Mostly, it depends on how you set up your package structure. Typically, you would put it somewhere like “src/com/your_web_site/your_project/db” or something along those lines rather than dumping it directly into your root package. If you take a look at the zip file on the last page of the tutorial, it will show you how I set everything up inside of an actual project (without organizing any packages). Let me know if you cannot find it from there.

Comment from sheetal
Time March 6, 2012 at 5:35 pm

hi i got it. thanks a lot

Comment from paolo
Time March 13, 2012 at 3:13 pm

Very good tutorial. I am new to android and the first thing I looked for was a working crud sample. Many thanks!

Comment from srikanth
Time March 30, 2012 at 11:06 am

Hi, thank you for giving us such a nice tutorial.. now am able to do all these things, but now i want to get the id of the row when is data inserted in a row. in my application my data base is not visible to enter the id manually. so can you please help me in doing this.

Thank you.

Comment from Randall Mitchell
Time March 30, 2012 at 12:57 pm

Hi Srikanth. Great question. I’m not sure why it hasn’t come up before. The solution is simple and important in many problem spaces. The method that inserts the row into the database actually returns the id of the new row. addRow should be modified similar to as follows:

public long addRow(String rowStringOne, String rowStringTwo)
{
	long rowId = -1; 
	// this is a key value pair holder used by android's SQLite functions
	ContentValues values = new ContentValues();
 
	// this is how you add a value to a ContentValues object
	// we are passing in a key string and a value string each time
	values.put(TABLE_ROW_ONE, rowStringOne);
	values.put(TABLE_ROW_TWO, rowStringTwo);
 
	// ask the database object to insert the new data 
	try
	{
		rowId = db.insert(TABLE_NAME, null, values);
	}
	catch(Exception e)
	{
		Log.e("DB ERROR", e.toString()); // prints the error message to the log
		e.printStackTrace(); // prints the stack trace to the log
	}
	return rowId;
}

Note most importantly the addition of rowId and the fact that it is returned. Now when you call addRow, you can catch the return, which will contain the row ID. Check page 3 of the tutorial for the context of this method:

http://www.anotherandroidblog.com/2010/08/04/android-database-tutorial/3/#addrow

Hope this helps!
Randall

Comment from Hardik Tailor
Time July 11, 2012 at 1:23 pm

Fyi,
i have two android app
1) App A
2) App B

In App A i have sqlite database.
i want to reuse that sqlite app A sqlite database in App B.
i dont want use App B database.

In App A my Database definition is look like
ID——-NAME ———- NUMBER

and i want to create App B like
Insert, update , delete on App A sqlite DB.

Thanks

Comment from Randall Mitchell
Time July 11, 2012 at 1:53 pm

You should set up your database inside of a content provider in App A and then allow App B access to your content provider through the AndroidManifest. It is really best practice to wrap your database inside content providers. Hopefully, the transition from the code in this tutorial should not be that difficult.

http://developer.android.com/guide/topics/providers/content-providers.html

Best of luck and thanks for reading,
Randall

Comment from nirmal kumar
Time July 13, 2012 at 7:58 am

i have read lots of tutorial on android database ,but trust me truly this has been really a great tutorial for beginners.

Comment from Hardik Tailor
Time July 16, 2012 at 1:30 pm

Hey Randall Mitchell,
How to use content provider for App A and App B in Android Application can you give me in detail.

Thanks

Comment from Randall Mitchell
Time July 16, 2012 at 3:01 pm

This is a somewhat lengthy process that I do not currently have the time to dive into with you. I haven’t written a tutorial on the matter either. I suggest you should look over the official documentation. Hopefully, my database tutorial will help you through many of the steps that are glossed over or simply iterated in the official documentation. I think if you made it though my material, the following link should get you where you need to be.

http://developer.android.com/guide/topics/providers/content-provider-creating.html

Comment from sahana
Time August 11, 2012 at 3:16 pm

Hi
I am new to andriod.Really very good Tutorial.just I inserted datas in database.when i run my apps ,i didnt get anything.i don’t know whether i have done any mistake.Could you please check my code? i want to know what mistake i have done .I am trying past one week to find solution for this problem.

please tell me how to check our datas in sqlite database?is there any code for that? plz reply

Thanking you

////********Wallpaperact.java********/////

package com.example.wallpaper;

import android.os.Bundle;
import android.app.Activity;
import android.util.Log;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.support.v4.app.NavUtils;

public class Wallpaperact extends Activity {

EditText add_ed1, add_ed2,del_ed1;
Button btn_add,btn_del;
AABDatabaseManager db;//to cal sql to this class

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

RefXmlMethod();
ButtonClickListener();

}catch(Exception e){
Log.e(“WHEN CALL FIRST ACTIVITY ERROR”, e.toString());
e.printStackTrace();

}
}

private void ButtonClickListener() {
// TODO Auto-generated method stub
btn_del.setOnClickListener(new OnClickListener(){

public void onClick(View v) {
// TODO Auto-generated method stub
DelRow();

}

});

btn_add.setOnClickListener(new OnClickListener(){

public void onClick(View v) {
// TODO Auto-generated method stub
AddSingleValue();

}

});

}

protected void DelRow() {
// TODO Auto-generated method stub
try{
//to delete datas
db.DelRow(Long.parseLong(del_ed1.getText().toString()));

//asking requset to update in db
//AskRequest();

// to clear all text from edittext
clearDatas();

}catch(Exception e){
Log.e(“ERRORS OCCURE WHEN DEL FROM DB”, e.toString());
e.printStackTrace();
}

}

private void AskRequest() {
// TODO Auto-generated method stub

}

protected void AddSingleValue() {
// TODO Auto-generated method stub
try{

// add datas in db
db.AddSingleValue(add_ed1.getText().toString(),add_ed2.getText().toString());

//asking request to update
//AskRequest();

//clear text in edittext
clearDatas();

}catch(Exception e){
Log.e(“Add error”, e.toString());
e.printStackTrace();
}

}

private void clearDatas() {
// TODO Auto-generated method stub
add_ed1.setText(“”);
add_ed2.setText(“”);
del_ed1.setText(“”);
}

private void RefXmlMethod() {
// TODO Auto-generated method stub
btn_add = (Button) findViewById(R.id.btn_first_save);
btn_del = (Button) findViewById(R.id.btn_DEL);

add_ed1 = (EditText) findViewById(R.id.first_edit_add);
add_ed2 = (EditText) findViewById(R.id.sec_edit_add);
del_ed1 = (EditText) findViewById(R.id.ed1_DEL);

}

@Override
public boolean onCreateOptionsMenu(Menu menu) {
getMenuInflater().inflate(R.menu.activity_wallpaperact, menu);
return true;
}

}

////************AABDatabaseManager.java**********************//////
package com.example.wallpaper;

import android.content.ContentValues;
import android.content.Context;
import android.content.Intent;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import android.widget.Toast;

public class AABDatabaseManager {

Context c;
private SQLiteDatabase db ;

private final String table_name = “Student”;

private final String db_name = “Stu”;
private final int version = 1;

private final String first_field = “stu_id”;
private final String sec_field = “stu_name”;
private final String third_field = “phno”;

//create or open DATABASE

public AABDatabaseManager(Context c){

this.c = c;
customsqlitopenlistener cSQl = new customsqlitopenlistener(c);
this.db = cSQl.getWritableDatabase();

}

// this our own method to add/del datas from DB

public void AddSingleValue(String sf, String tf){

ContentValues cv = new ContentValues();
cv.put(sec_field, sf);
cv.put(third_field, tf);
try{
db.insert(table_name, null, cv);
Toast.makeText(null, “Data has added in sqlite db”, Toast.LENGTH_LONG).show();
}catch(Exception e){
Log.e(“This is when inserting datas in DB”, e.toString());

}//try

}//catch

public void DelRow(long first_id){

try{
db.delete(table_name, first_field + “>2″+ first_id, null);

}catch(Exception e){
Log.e(“DB Error IN DELETING”, e.toString());
}
}//delRow

/*public void UpdateRow(long first_id, String sec , String third){
ContentValues ucv = new ContentValues();
ucv.put(sec , sec);
ucv.put(third, third);
try{
db.update(table_name, ucv, first_field+ “=” + first_id, null);
}catch(Exception e){
Log.e(“DB ERROR IN UPDATING”, e.toString());
}

}*/

/// THIS CLASS IS EXTEND SQLITEOPENHELPER CLASS AND TO CREATE DB AND UPDATE IT

private class customsqlitopenlistener extends SQLiteOpenHelper{

public customsqlitopenlistener(Context c) {
super(c, db_name, null, version);
// TODO Auto-generated constructor stub
}

@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
String query_create = “create table” + table_name + “(” + first_field +
“integer primary key autoincreament,” + sec_field + “text ,” + third_field + “text” + “);”;
db.execSQL(query_create);
}

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

}

}
}

Comment from sahana
Time August 11, 2012 at 5:36 pm

Hi,

When i insert datas in SQLite DB, I am getting error and warning like this. Please help me how to fix this error .

08-11 23:01:33.335: E/Add error(824): java.lang.NullPointerException
08-11 23:01:33.344: W/System.err(824): java.lang.NullPointerException
08-11 23:01:33.355: W/System.err(824): at com.example.wallpaper.Wallpaperact.AddSingleValue(Wallpaperact.java:98)
08-11 23:01:33.355: W/System.err(824): at com.example.wallpaper.Wallpaperact$2.onClick(Wallpaperact.java:59)
08-11 23:01:33.365: W/System.err(824): at android.view.View.performClick(View.java:3511)
08-11 23:01:33.365: W/System.err(824): at android.view.View$PerformClick.run(View.java:14105)
08-11 23:01:33.365: W/System.err(824): at android.os.Handler.handleCallback(Handler.java:605)
08-11 23:01:33.375: W/System.err(824): at android.os.Handler.dispatchMessage(Handler.java:92)
08-11 23:01:33.386: W/System.err(824): at android.os.Looper.loop(Looper.java:137)
08-11 23:01:33.386: W/System.err(824): at android.app.ActivityThread.main(ActivityThread.java:4424)
08-11 23:01:33.395: W/System.err(824): at java.lang.reflect.Method.invokeNative(Native Method)
08-11 23:01:33.405: W/System.err(824): at java.lang.reflect.Method.invoke(Method.java:511)
08-11 23:01:33.405: W/System.err(824): at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:784)
08-11 23:01:33.425: W/System.err(824): at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:551)
08-11 23:01:33.425: W/System.err(824): at dalvik.system.NativeStart.main(Native Method)

Comment from Randall Mitchell
Time August 26, 2012 at 5:27 pm

You probably need to work on your debugging skills (no offense). Your first goal here should be to figure out what is null when the line of code numbered 98 in the file Wallpaperact.java is called. There are several ways to do this, best is to use debug perspective, insert a breakpoint on the line in question, and use the Variables window to see what is null on that line when it is supposed to execute.

Comment from sunny
Time October 20, 2012 at 11:59 am

Great tutorial , the best I have read on DB yet .

Comment from Jack
Time November 21, 2012 at 7:23 am

Hey, Randall.
when I create my sql database, eclipse reported error: aused by: android.database.sqlite.SQLiteException: no such column: note: , while compiling: SELECT rowid, note, created FROM notes.
This is my table:
public static final String KEY_ROWID = “rowid”;
public static final String KEY_NOTE = “note”;
public static final String KEY_CREATED = “created”;

private static final String DATABASE_CREATE =
“create table”+” “+DATABASE_TABLE+” “+”(”
+KEY_ROWID+” “+”INTEGER PRIMARY KEY,”
+KEY_NOTE+” “+”TEXT,”
+KEY_CREATED+” “+”TEXT,”
+”modified”+” “+”INTEGER,”
+”);”;
//测试添加时间

private static class DatabaseHelper extends SQLiteOpenHelper {

public DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
// TODO Auto-generated constructor stub
}

@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
db.execSQL(DATABASE_CREATE);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
db.execSQL(“DROP TABLE IF EXISTS “+DATABASE_TABLE);
onCreate(db);
}

}

I just could not figure out why.

Comment from Randall Mitchell
Time November 21, 2012 at 11:20 am

There is a bug in your create statement. You need to remove the last comma after the word INTEGER. Try that first and let me know how it goes.

Comment from Jack
Time November 22, 2012 at 7:59 am

Randall, I just remove the last comma.
But another error occured. When I try to insert an item and display it. Eclipse reported: java.lang.RuntimeException: Failure delivering result ResultInfo{who=null, request=4098, result=-1, data=Intent { cmp=com.demo.android.notes/.Notes (has extras) }} to activity {com.demo.android.notes/com.demo.android.notes.Notes}: java.lang.IllegalArgumentException: column ‘_id’ does not exist

Actually, my table doesn’t contain a column called ‘_id’ now. (The value of KEY_ROWID was once ‘_id’, but I changed it to ‘rowid’ later.)

So I just change the value of KEY_ROWID back to “_id”. I just met a new error: I/Database(990): sqlite returned: error code = 1, msg = no such column: _id

these methods are related to the insert and display operation. And eclipse also reports there may some errors in them.
// get all entries
public Cursor getall() {
return db.query(DATABASE_TABLE,
new String[] {KEY_ROWID,KEY_NOTE, KEY_CREATED},
null, null, null, null, null);
}

// display items
private void fillData() {
mNotesCursor = mDbHelper.getall();
startManagingCursor(mNotesCursor);
String[] from = new String[]{NotesDbAdapter.KEY_NOTE};
int[] to = new int[]{android.R.id.text1};

// Now create a simple cursor adapter
SimpleCursorAdapter adapter =
new SimpleCursorAdapter(this, android.R.layout.simple_list_item_1, mNotesCursor, from, to);
setListAdapter(adapter);
}

I wonder why eclipse said there was a column called “_id” in my table when the real value of KEY_ROWID was actually “rowid”(i uninstall the app and intall it for several times, but i still met that error).
I still got puzzled by the fact that when i change KEY_ROWID backto “_id” eclipse said there was no column called “_id”.

Comment from Randall Mitchell
Time November 22, 2012 at 3:38 pm

I don’t think I have enough information to see the problem. If you post the database code and calling code to pastebin or elsewhere I would be happy to take a look at it.

Comment from Jack
Time November 23, 2012 at 12:56 am

Thank you very much, Randall.
This is my database code.
NotesDbAdapter.java:

package com.demo.android.notes;
 
import java.util.Calendar;
import java.util.Date;
 
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
 
public class NotesDbAdapter {
 
    private static final String DATABASE_NAME = "notes.db";
    private static final int DATABASE_VERSION = 1;
 
    private static final String DATABASE_TABLE = "notes";
 
    //start query
    //public static final String KEY_ROWID = "rowid";
    public static final String KEY_ROWID = "_id";
    public static final String KEY_NOTE = "note";
    public static final String KEY_CREATED = "created";
 
   private static final String DATABASE_CREATE =
    "create table"+" "+DATABASE_TABLE+" ("
        +KEY_ROWID+" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
        +KEY_NOTE+" TEXT NOT NULL,"
        +KEY_CREATED+" TEXT NOT NULL,"
        +"modified"+" INTEGER"
    +");";   
    private static class DatabaseHelper extends SQLiteOpenHelper {
 
        public DatabaseHelper(Context context) {
	super(context, DATABASE_NAME, null, DATABASE_VERSION);
			// TODO Auto-generated constructor stub
		}
 
    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub
         db.execSQL(DATABASE_CREATE);
	}
 
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	// TODO Auto-generated method stub
	db.execSQL("DROP TABLE IF EXISTS "+DATABASE_TABLE);
	onCreate(db);
    }
 
    }
 
    private Context mCtx = null;
    private DatabaseHelper dbHelper ;
    private SQLiteDatabase db;
 
    /** Constructor */
    public NotesDbAdapter(Context ctx) {
        this.mCtx = ctx;
    }
 
    public NotesDbAdapter open () throws SQLException {
        dbHelper = new DatabaseHelper(mCtx);
        db = dbHelper.getWritableDatabase();
        return this;
    }
 
    public void close() {
        dbHelper.close();
    }
 
 
    String[] strCols = new String[] {
        KEY_ROWID,
        KEY_NOTE,
        KEY_CREATED
    };
 
    // get all entries
    public Cursor getall() {
        return db.query(DATABASE_TABLE,
            new String[] {KEY_ROWID,KEY_NOTE, KEY_CREATED},
            null, null, null, null, null);
    }
 
    // add an entry
    public long create(String Note) {
        Date now = new Date();
        ContentValues args = new ContentValues();
        args.put(KEY_NOTE, Note);
        //
        Calendar calendar = Calendar.getInstance(); 
        String created = calendar.get(Calendar.YEAR)+ "年"+ calendar.get  (Calendar.MONTH)+ "月" 
        + calendar.get(Calendar.DAY_OF_MONTH)+ "日"+ calendar.get(Calendar.HOUR_OF_DAY)+"时"
        + calendar.get(Calendar.MINUTE)+ "分";
            args.put(KEY_CREATED, created);
        //
        return db.insert(DATABASE_TABLE, null, args);
    }
 
    //remove an entry
    public boolean delete(long rowId) {
        return db.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) &gt; 0;
    }
 
    //query single entry
    public Cursor get(long rowId) throws SQLException {
        Cursor mCursor = db.query(true,
                DATABASE_TABLE,
                new String[] {KEY_ROWID, KEY_NOTE, KEY_CREATED},
                KEY_ROWID + "=" + rowId,
                null, null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
    }
 
 
 
    //update
    public boolean update(long rowId, String note) {
        ContentValues args = new ContentValues();
        args.put(KEY_NOTE, note);
        //        
    Calendar calendar = Calendar.getInstance(); 
        String created = calendar.get(Calendar.YEAR)+ "年"+ calendar.get(Calendar.MONTH)+ "月" 
        + calendar.get(Calendar.DAY_OF_MONTH)+ "日"+ calendar.get(Calendar.HOUR_OF_DAY)+"时"
        + calendar.get(Calendar.MINUTE)+ "分";
           args.put(KEY_CREATED, created);
        //        
     return db.update(DATABASE_TABLE, args, KEY_ROWID + "=" + rowId, null)  &gt; 0;
    }
}

Comment from Jack
Time November 23, 2012 at 1:03 am

This is the main activity.
Notes.java:

package com.demo.android.notes;
 
import android.os.Bundle;
import android.app.ListActivity;
import android.content.Intent;
import android.database.Cursor;
import android.util.Log;
import android.view.ContextMenu;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.view.ContextMenu.ContextMenuInfo;
import android.widget.AdapterView;
import android.widget.ListView;
import android.widget.SimpleCursorAdapter;
 
public class Notes extends ListActivity {	
	@Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_notes);
        //Tell the list view which view to display when the list is empty
        getListView().setEmptyView(findViewById(R.id.empty));
        registerForContextMenu(getListView());
        setAdapter();
    }
       private NotesDbAdapter mDbHelper;
    private Cursor mNotesCursor;
 
    private void setAdapter() {
    	mDbHelper = new NotesDbAdapter(this);
        mDbHelper.open();
        //fillData();
           }
 
    private void fillData() {
        mNotesCursor = mDbHelper.getall();
        startManagingCursor(mNotesCursor);
              //
        String[] from = new String[]{NotesDbAdapter.KEY_NOTE};
        int[] to = new int[]{android.R.id.text1};
 
        // Now create a simple cursor adapter
        SimpleCursorAdapter adapter =
                    new SimpleCursorAdapter(this, android.R.layout.simple_list_item_1, mNotesCursor, from, to);
        setListAdapter(adapter);
    }
 
 
    protected static final int MENU_INSERT = Menu.FIRST;
    protected static final int MENU_DELETE = Menu.FIRST+1;
    protected static final int MENU_QUIT = Menu.FIRST+2;
    private static final int ACTIVITY_EDIT=0x1001;
    private static final int ACTIVITY_CREATE=0x1002;
 
	@Override
	public boolean onCreateOptionsMenu(Menu menu) {
		// TODO Auto-generated method stub
		menu.add(0, MENU_INSERT, 0, R.string.menu_insert).setIcon(android.R.drawable.ic_menu_add);
		menu.add(0, MENU_QUIT, 0, R.string.menu_quit).setIcon(android.R.drawable.ic_menu_close_clear_cancel);
		return super.onCreateOptionsMenu(menu);
	}
 
	@Override
	public boolean onOptionsItemSelected(MenuItem item) {
		// TODO Auto-generated method stub
		switch(item.getItemId()) {
        	case MENU_INSERT:
        		Intent intent = new Intent(this, NoteCreate.class);
        		startActivityForResult(intent,ACTIVITY_CREATE);
        	    break;
 
        	case MENU_QUIT:
                finish();
                break;
   	}
	return super.onOptionsItemSelected(item);
	}
 
	@Override
	protected void onListItemClick(ListView l, View v, int position, long id) {
	    super.onListItemClick(l, v, position, id);
	    Intent intent = new Intent(this, NoteEdit.class);
	    intent.putExtra(NotesDbAdapter.KEY_ROWID, id);
	    startActivityForResult(intent, ACTIVITY_EDIT);
	}
 
	@Override
	protected void onActivityResult(int requestCode, int resultCode, Intent data) {
	    super.onActivityResult(requestCode, resultCode, data);
	    switch(resultCode){
	    case RESULT_CANCELED:
	    	fillData();
	    	break;
 
	    case RESULT_OK:
	    	Bundle bunde=data.getExtras();
	   	String notetext=bunde.getString(NoteCreate.NOTE);
	    	mDbHelper.create(notetext);
	        	fillData();
	    	break;
	    }
	}
 
	@Override
	public boolean onContextItemSelected(MenuItem item) {
		// TODO Auto-generated method stub
		AdapterView.AdapterContextMenuInfo info;
		info = (AdapterView.AdapterContextMenuInfo) item.getMenuInfo();
		switch (item.getItemId()) { 
		case MENU_DELETE:
			Log.d("MENU", "item"+info.id);
			mDbHelper.delete(info.id);
            fillData();
            break;
		}
		return super.onContextItemSelected(item);
	}
 
	@Override
	public void onCreateContextMenu(ContextMenu menu, View v,
			ContextMenuInfo menuInfo) {
		// TODO Auto-generated method stub
		menu.add(0, MENU_DELETE, 0,  R.string.menu_delete);
        menu.setHeaderTitle(R.string.context_head);
		super.onCreateContextMenu(menu, v, menuInfo);
	}
}

Comment from Jack
Time November 23, 2012 at 1:07 am

The NoteCreate java:

package com.demo.android.notes;
 
import android.app.Activity;
import android.content.Intent;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;
 
 
public class NoteCreate extends Activity {
	/** Called when the activity is first created. */
	// intent
	public static final String NOTE = "NOTE";
 
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.note_create);
        findViews();
        setListeners();
    }
 
    private EditText field_text;
    private Button button_back;
 
    private void findViews() {
        field_text = (EditText) findViewById(R.id.text);
        button_back = (Button) findViewById(R.id.backmain);
    }
 
  //Listen for button clicks
    private void setListeners(){
    	button_back.setOnClickListener(backMain);	
    }
 
 
 
    private Button.OnClickListener backMain=new Button.OnClickListener(){
        public void onClick(View v){
           //Close this activity
 
        	//
            Intent intent1=new Intent();
            intent1.setClass(NoteCreate.this,Notes.class);
        	Bundle bundle=new Bundle();
        	String str = field_text.getText().toString();
        	bundle.putString(NOTE, str);
        	//        	
        	//        	
          intent1.putExtras(bundle);
        	setResult(RESULT_OK,intent1);
        	Toast.makeText(getApplicationContext(), str, Toast.LENGTH_SHORT).show();
        	 NoteCreate.this.finish();
        }
    };
}

Comment from Jack
Time November 23, 2012 at 4:07 am

Randall, I think i find the problem. If I want to use SimpleCursorAdapter to show the item list, the table must contain the column whose name is ‘_id’.

However even I create the column: ‘_id’ in my table, eclipse just say there is no such column: ‘_id’. I don’t konw why.

Comment from Randall Mitchell
Time November 23, 2012 at 6:30 pm

That is a bit of code for me to go through. Have you narrowed down which call to the database is failing? It sounds like you are able to create the database and add data to it, but are failing to fetch the data. Is that correct? If you can’t tell where the issue is based on the stack trace (someThrownError.printStackTrace()), you should be putting break points at the start of each of those actions and stepping through the code to see where the failure is occurring. The error message stating result=-1 concerns me. Calling DatabaseUtils.dumpCursor(Cursor) will print to log a human readable list of what is in the cursor. That should give you an idea of what is coming back from the database.

Comment from Jack
Time November 24, 2012 at 5:22 am

Thank you, Randall. Actually, I am failing to fetch data.
Yesterday , I spent many hours checking my tables to see if there was any error. Later I just tried to uninstall the app in my android virtual device and delete the apk file in the android application project. Then I restarted the virtual device and installed the app again. To my surprise, the app worked without errors.
In my opinion, by uninstalling the app and deleting the apk, the table is updated, so the problen is fixed.

Comment from Randall Mitchell
Time November 24, 2012 at 2:50 pm

I’m glad you were able to get things running. I know you said you were uninstalling and reinstalling the app. I don’t know how deleting the apk would have any effect unless you were inadvertently using the old apk. If you weren’t uninstalling the app, doing an install through eclipse will act as an update, leaving you with a potentially outdated db structure.

Comment from Hima
Time December 27, 2012 at 7:51 am

When I delete a row from the table, the IDs don’t change
if it was 1,2,3,4 and I deleted entry 3, the table would show 1,2,4
How would I make it update, would I have to delete and recreate the table?

Comment from Randall Mitchell
Time December 29, 2012 at 4:32 pm

This is standard functionality for auto-increment fields. Assuming you have a database with 500,000 entries, it would not be practical to change the id of numbers 100,000-500,000 each time one of the first hundred thousand entries are deleted. This would especially be problematic during batch deletions. If you are trying to use the id for something more than identifying an object, you should probably reconsider your usage of the id field. If your logic is dependent on consecutive id’s, I would definitely reconsider your logic.