5 Replies - 2275 Views - Last Post: 03 May 2013 - 09:06 AM

#1 dekon1   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 03-May 13

can't insert data into MYSQLite database

Posted 03 May 2013 - 08:14 AM

hi i am new to android development and need help with inserting my data into a MYSQLite database could someone help me understand the problem thanks using log cat and this is the error that it gives me

05-02 18:53:05.087: I/Database(331): sqlite returned: error code = 1, msg = table suspect has no column named other 05-02 18:53:05.097: E/Database(331): Error inserting other=8 14 nationality= height=8 14 age= name= suspect= gender=Male [email protected]

even tried getting rid other string and it then sujects that column suspect can't be created

package com.example.sherlock;

import java.util.List;

import android.app.Activity;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.view.View.onclickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Spinner;

public class Database extends Activity implements onclickListener {

private EditText nametxt;
private Spinner gender;
private Spinner age;
private EditText hairtxt;
private EditText heighttxt;
private EditText nationalitytxt;
private EditText othertxt;
private Spinner suspecttype;
private Button submitbtn;
private Button clearbtn;


//called when activity is created
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_database);

nametxt =(EditText) findViewById(R.id.nametxt);
gender =(Spinner) findViewById(R.id.gender);
age =(Spinner) findViewById(R.id.age);
hairtxt =(EditText) findViewById(R.id.hairtxt);
heighttxt =(EditText) findViewById(R.id.heighttxt);
nationalitytxt =(EditText) findViewById(R.id.nationalitytxt);
othertxt =(EditText) findViewById(R.id.othertxt);
suspecttype =(Spinner) findViewById(R.id.age);

submitbtn =(Button)findViewById(R.id.submitbtn);
submitbtn.setonclickListener(this);
clearbtn =(Button)findViewById(R.id.clearbtn);
clearbtn.setonclickListener(this);
}

public void onclick(View v){

   if(v.getId ()==R.id.submitbtn){
  String providenametxt = nametxt.getText().toString();
  String providegender = gender.getSelectedItem().toString();
  String provideage = age.getSelectedItem().toString();
  String providehairtxt = hairtxt.getText().toString();
  String provideheighttxt = heighttxt.getText().toString();
  String providenationalitytxt = nationalitytxt.getText().toString();
  String provideothertxt = othertxt.getText().toString();
  String providesuspecttype = suspecttype.getSelectedItem().toString();


  DBAdapter db = new DBAdapter(this);
  db.addSuspect(new Suspects( providenametxt, providegender, 
  provideage, providehairtxt,                   
  provideheighttxt, providenationalitytxt, 
  provideothertxt, providesuspecttype));
  //reading all contacts
  Log.d("Reading: ", "Reading all Contacts..");
  List<Suspects> suspects = db.getAllSuspects();

  for (Suspects cn : suspects) {
   String log = "Id: "+ cn.getID() +" ,Name: " + cn.getName() 
   +" ,Gender: " +    cn.getGender()  +" ,Age: " + cn.getAge()
   +" ,Hair: " + cn.getHair() +" ,Height: " + cn.getHeight()
   +" ,Nationality: " + cn.getNationality() +" ,Other: " + cn.getOther() 
   +" ,Suspecttype: " + cn.getSuspect();
  //Writing contacts to log
  Log.d("Name: ", log);
   }
   //release from existing UI
   db.close();
  finish();
  }
  }
  }


DBhandler

ackage com.example.sherlock;
import java.util.ArrayList;
import java.util.List;

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

public class DBhandler extends SQLiteOpenHelper {

	// All Static variables
	// Database Version
	private static final int DATABASE_VERSION = 1;

	// Database Name
	private static final String DATABASE_NAME = "SuspectDB";

	// Contacts table name
	private static final String TABLE_SUSPECTS = "suspect";

	// Contacts Table Columns names
	private static final String KEY_ROWID = "id";
	private static final String KEY_NAME = "name";
	private static final String KEY_GENDER = "gender";
	private static final String KEY_HEIGHT = "height";
	private static final String KEY_AGE = "age";
	private static final String KEY_HAIR = "hair";
	private static final String KEY_NATIONALITY = "nationality";
	private static final String KEY_SUSPECT = "suspect";
	private static final String KEY_OTHER = "other";

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

	// Creating Tables
	@Override
	public void onCreate(SQLiteDatabase db) {
		String CREATE_Suspects_TABLE = "CREATE TABLE " + TABLE_SUSPECTS + "("
				 + KEY_ROWID + " INTEGER PRIMARY KEY,"
				 + KEY_NAME + " TEXT,"
				 + KEY_GENDER + " TEXT,"
				 + KEY_HEIGHT + " TEXT,"
				 + KEY_AGE + " TEXT,"
				 + KEY_HAIR + "TEXT,"
				 + KEY_NATIONALITY + "TEXT,"
				 + KEY_SUSPECT + "TEXT ,"
				 + KEY_OTHER +"TEXT," +")";
		db.execSQL(CREATE_Suspects_TABLE);
	}

	// Upgrading database
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// Drop older table if existed
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_SUSPECTS);

		// Create tables again
		onCreate(db);
	}

	/**
	 * All CRUD(Create, Read, Update, Delete) Operations
	 */

	// Adding new contact
	void addSuspects(Suspects suspect) {
	SQLiteDatabase db = this.getWritableDatabase();
	ContentValues values = new ContentValues();
	values.put(KEY_NAME, suspect.getName()); // Contact Name
	values.put(KEY_GENDER, suspect.getGender()); // Contact Sex
	values.put(KEY_HEIGHT, suspect.getHeight()); // Contact Height
	values.put(KEY_AGE, suspect.getAge()); // Contact Age
	values.put(KEY_HAIR, suspect.getHair()); // Contact Hair Colour
	values.put(KEY_NATIONALITY, suspect.getNationality()); // Contact Additional Information
	values.put(KEY_SUSPECT, suspect.getSuspect()); // Contact Nationality
	values.put(KEY_OTHER, suspect.getOther()); // Contact Type
	// Inserting Row
	db.insert(TABLE_SUSPECTS, null, values);
	db.close(); // Closing database connection
	}

	// Getting single contact
	Suspects getSuspects(int id) {
		SQLiteDatabase db = this.getReadableDatabase();

		Cursor cursor = db.query(TABLE_SUSPECTS, new String[] { KEY_ROWID,
				 KEY_NAME, KEY_GENDER, KEY_HEIGHT, KEY_AGE, KEY_HAIR,KEY_NATIONALITY,KEY_SUSPECT}, KEY_ROWID + "=?",
				new String[] { String.valueOf(id) }, null, null, null, null);
		if (cursor != null)
			cursor.moveToFirst();

		Suspects suspect = new Suspects(Integer.parseInt(cursor.getString(0)),
				cursor.getString(1),
                cursor.getString(2),
                cursor.getString(3),
                cursor.getString(4),
                cursor.getString(5),
                cursor.getString(6),
                cursor.getString(7),
                cursor.getString(8));
		// return contact
		return suspect;
	}
	
	// Getting All Contacts
	public List<Suspects> getAllSuspects() {
		List<Suspects> suspectList = new ArrayList<Suspects>();
		// Select All Query
		String selectQuery = "SELECT  * FROM " + TABLE_SUSPECTS;

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

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				Suspects suspect = new Suspects();
				 suspect.setID(Integer.parseInt(cursor.getString(0)));
	                suspect.setName(cursor.getString(1));
	                suspect.setGender(cursor.getString(2));
	                suspect.setHeight(cursor.getString(3));
	                suspect.setAge(cursor.getString(4));
	                suspect.setHair(cursor.getString(5));
	                suspect.setNationality(cursor.getString(6));
	                suspect.setSuspect(cursor.getString(7));
	                suspect.setOther(cursor.getString(8));
				// Adding contact to list
				suspectList.add(suspect);
			} while (cursor.moveToNext());
		}

		// return contact list
		return suspectList;
	}

	// Updating single contact
	public int updateContact(Suspects suspect) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(KEY_ROWID, suspect.getName());
        values.put(KEY_NAME, suspect.getName());
        values.put(KEY_GENDER, suspect.getGender());
        values.put(KEY_HEIGHT, suspect.getHeight());
        values.put(KEY_AGE, suspect.getAge());
        values.put(KEY_HAIR, suspect.getHair());
        values.put(KEY_NATIONALITY, suspect.getNationality());
        values.put(KEY_SUSPECT, suspect.getSuspect());
        values.put(KEY_OTHER, suspect.getOther());

		// updating row
		return db.update(TABLE_SUSPECTS, values, KEY_ROWID + " = ?",
				new String[] { String.valueOf(suspect.getID()) });
	}

	// Deleting single contact
	public void deleteContact(Suspects suspect) {
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(TABLE_SUSPECTS, KEY_ROWID + " = ?",
				new String[] { String.valueOf(suspect.getID()) });
		db.close();
	}


	// Getting contacts Count
	public int getContactsCount() {
		String countQuery = "SELECT  * FROM " + TABLE_SUSPECTS;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(countQuery, null);
		cursor.close();

		// return count
		return cursor.getCount();
	}

}


Is This A Good Question/Topic? 0
  • +

Replies To: can't insert data into MYSQLite database

#2 Atli   User is offline

  • Enhance Your Calm
  • member icon

Reputation: 4241
  • View blog
  • Posts: 7,216
  • Joined: 08-June 10

Re: can't insert data into MYSQLite database

Posted 03 May 2013 - 08:35 AM

Hey.

First of all, there is no such thing as "MYSQLite". There is "MySQL" and there is "SQLite". The two don't mix :)

As to the problem. Look a bit closer at your CREATE statement. The last four columns you define there are defined like this:
 + KEY_HAIR + "TEXT,"


Notice the lack of a space between the column name and the data type. The end result of this will read like: hairTEXT, which will become the column name with what I assume to be the default type of TEXT. (SQLite technically stores everything as text.)

So when you try to insert data into the "hair" column, it won't work because the column is actually named "hairTEXT". Same goes for the nationality, suspect and other columns.
Was This Post Helpful? 0
  • +
  • -

#3 dekon1   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 03-May 13

Re: can't insert data into MYSQLite database

Posted 03 May 2013 - 08:50 AM

public void onCreate(SQLiteDatabase db) {
		String CREATE_SUSPECTS_TABLE = "CREATE TABLE " +
		TABLE_SUSPECTS +
		"(" + KEY_ROWID + " INTEGER PRIMARY KEY," +
		KEY_NAME + " TEXT," +
		KEY_GENDER + " TEXT," +
		KEY_HEIGHT + " TEXT," +
		KEY_AGE + " TEXT," +
		KEY_HAIR + " TEXT," +
		KEY_NATIONALITY + " TEXT," +
		KEY_SUSPECT + " TEXT," +
		KEY_OTHER + " TEXT"+ ")";
		db.execSQL(CREATE_SUSPECTS_TABLE);
		}


changed the code to this and still producing the same error
Was This Post Helpful? 0
  • +
  • -

#4 Atli   User is offline

  • Enhance Your Calm
  • member icon

Reputation: 4241
  • View blog
  • Posts: 7,216
  • Joined: 08-June 10

Re: can't insert data into MYSQLite database

Posted 03 May 2013 - 08:53 AM

Did you delete the old SQLite data file? This change you made won't take effect while an existing version of the database exists with the table already created.
Was This Post Helpful? 0
  • +
  • -

#5 dekon1   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 03-May 13

Re: can't insert data into MYSQLite database

Posted 03 May 2013 - 09:00 AM

how would i go about doing this?
Was This Post Helpful? 0
  • +
  • -

#6 Atli   User is offline

  • Enhance Your Calm
  • member icon

Reputation: 4241
  • View blog
  • Posts: 7,216
  • Joined: 08-June 10

Re: can't insert data into MYSQLite database

Posted 03 May 2013 - 09:06 AM

I'm not sure. I've never developed anything on Android. The SQLite database is stored in a file, though, so it would be somewhere on the file-system. If you locate it and delete it, that would do it.

Going over some of the Android docs relating to this, I came across this method: Context::deleteDatabase. I'd guess that would do the trick as well. If you did something like this in the DBHandler constructor, that might work:
public class DBhandler extends SQLiteOpenHelper {
	public DBhandler(Context context) {
		context.deleteDatabase(DATABASE_NAME);
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}
}


If it does, just remember to remove it once you're done fixing this, or you'll be deleting the database each time the class is created.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1