Error - in such column

Question

Error - in such column

Asked 10 years, 9 months ago

Viewed 1,413 times

1

03-21 07:25:47.740: E/SQLiteLog(796): (1) no such column: name1    
03-21 07:25:47.750: E/data(796): Error while searching contacts: android.database.sqlite.SQLiteException: no such column: name1 (code 1): while compiling: SELECT _id, name1, name2, phone1 FROM contact

Next, the Log is indicating that it does not find the column 1, and error in the lines that I will point out soon after. In my view there is no hindrance that prevents the application from running as it should.

Contactsregister.java

package br.com.contactsmanager;

import java.util.List;

import android.app.ListActivity;
import android.content.Intent;
import android.os.Bundle;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.widget.ListView;
import br.com.contactsmanager.Contact.Contacts;

public class ContactRegister extends ListActivity {
    protected static final int INSERT_EDIT = 1;
    protected static final int SEARCH = 2;

    public static ContactRepositoryScript repository;


    private List<Contact> contacts;

    @Override
    public void onCreate(Bundle icicle) {
        super.onCreate(icicle);
        repository = new ContactRepositoryScript(this);
        updateList();    //ERRO AQUI!
    }

    protected void updateList() //ERRO AQUI!
    {
        contacts = repository.listContacts(); 

        setListAdapter(new ContactListAdapter(this, contacts));
    }

    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        super.onCreateOptionsMenu(menu);
        menu.add(0, INSERT_EDIT, 0, "Insert new").setIcon(R.drawable.newcontact);
        menu.add(0, SEARCH, 0, "Search").setIcon(R.drawable.search);
        return true;
    }

    @Override
    public boolean onMenuItemSelected(int featureId, MenuItem item) {

        switch (item.getItemId()) {
        case INSERT_EDIT:

            startActivityForResult(new Intent(this, EditContact.class), INSERT_EDIT);
            break;
        case SEARCH:

            startActivity(new Intent(this, SearchContact.class));
            break;
        }
        return true;
    }

    @Override
    protected void onListItemClick(ListView l, View v, int position, long id) {
        super.onListItemClick(l, v, position, id);
        editContact(position);
    }

    protected void editContact(int position) 
    {
        Contact contact = contacts.get(position);

        Intent it = new Intent(this, EditContact.class);

        it.putExtra(Contacts._ID, contact.id1);

        startActivityForResult(it, INSERT_EDIT);
    }

    @Override
    protected void onActivityResult(int code, int returnCode, Intent it) {
        super.onActivityResult(code, returnCode, it);

        if (returnCode == RESULT_OK) 
        {
            updateList();
        }
    }

    @Override
    protected void onDestroy()
    {
        super.onDestroy();

        repository.close();
    }

}

Contactrepository.java

package br.com.contactsmanager;

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

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.SQLiteQueryBuilder;
import android.util.Log;
import br.com.contactsmanager.Contact.Contacts;

public class ContactRepository {

    private static final String CATEGORY = "data";

    private static final String DB_NAME = "android_data";

    public static final String TABLE_NAME = "contact";

    protected SQLiteDatabase db;

    public ContactRepository(Context ctx) 
    {

        db = ctx.openOrCreateDatabase(DB_NAME, Context.MODE_PRIVATE, null);

    }

    protected ContactRepository() 
    {

    }

    public long save(Contact contact) 
    {
        long id = contact.id1;

        if (id != 0) {
            update(contact);
        }
        else
        {

            id = insert(contact);
        }

        return id;
    }

    // Insere uma nova pessoa   
    public long insert(Contact contact)
    {
        ContentValues values = new ContentValues();
        values.put(Contacts.NAME, contact.name1);
        values.put(Contacts.LASTNAME, contact.name2);
        values.put(Contacts.PHONE, contact.phone1);

        long id = insert(values);
        return id;
    }

    // Insere uma nova pessoa
    public long insert(ContentValues values)
    {
        long id = db.insert(TABLE_NAME, "", values);
        return id;
    }

    // Atualiza a pessoa no banco. O id da pessoa é utilizado.
    public int update(Contact contact) 
    {
        ContentValues values = new ContentValues();
        values.put(Contacts.NAME, contact.name1);
        values.put(Contacts.LASTNAME, contact.name2);
        values.put(Contacts.PHONE, contact.phone1);

        String _id = String.valueOf(contact.id1);

        String where = Contacts._ID + "=?";
        String[] whereArgs = new String[] { _id };

        int count = update(values, where, whereArgs);

        return count;
    }

    // Atualiza a pessoa com os valores abaixo
    // A cláusula where é utilizada para identificar a pessoa a ser atualizado
    public int update(ContentValues values, String where, String[] whereArgs) 
    {
        int count = db.update(TABLE_NAME, values, where, whereArgs);
        Log.i(CATEGORY, "Updated [" + count + "] registers");
        return count;
    }

    // Deleta a pessoa com o id fornecido
    public int delete(long id) {
        String where = Contacts._ID + "=?";

        String _id = String.valueOf(id);
        String[] whereArgs = new String[] { _id };

        int count = delete(where, whereArgs);

        return count;
    }

    // Deleta a pessoa com os argumentos fornecidos
    public int delete(String where, String[] whereArgs) {
        int count = db.delete(TABLE_NAME, where, whereArgs);
        Log.i(CATEGORY, "Has deleted [" + count + "] registers");
        return count;
    }

    // Busca a pessoa pelo id
    public Contact searchContact(long id) 
    {
        // select * from pessoa where _id=?
        Cursor c = db.query(true, TABLE_NAME, Contact.columns, Contacts._ID + "=" + id, null, null, null, null, null);

        if (c.getCount() > 0) {

            // Posiciona no primeiro elemento do cursor
            c.moveToFirst ();

            Contact contact = new Contact();

            // Lê os dados
            contact.id1 = c.getLong(0);
            contact.name1 = c.getString(1);
            contact.name2 = c.getString(2);
            contact.phone1 = c.getInt(3);

            return contact;
        }

        return null;
    }

    // Retorna um cursor com todas as pessoas
    public Cursor getCursor() {
        try {
            // select * from pessoas
            return db.query(TABLE_NAME, Contact.columns, null, null, null, null, null, null);
        } catch (SQLException e) {
            Log.e(CATEGORY, "Error while searching contacts: " + e.toString());
            return null;
        }
    }

    // Retorna uma lista com todas as pessoas
    public List<Contact> listContacts() 
        {
        Cursor c = getCursor();

        List<Contact> contacts = new ArrayList<Contact>();

        if ( c.moveToFirst() )  //ERRO AQUI!
        {

            // Recupera os índices das colunas
            int idxId = c.getColumnIndex(Contacts._ID);
            int idxFirstName = c.getColumnIndex(Contacts.NAME);
            int idxLastName = c.getColumnIndex(Contacts.LASTNAME);
            int idxPhone = c.getColumnIndex(Contacts.PHONE);

            // Loop até o final
            do 
            {
                Contact contact = new Contact();
                contacts.add(contact);

                // recupera os atributos da pessoa
                contact.id1 = c.getLong(idxId);
                contact.name1 = c.getString(idxFirstName);
                contact.name2 = c.getString(idxLastName);
                contact.phone1 = c.getInt(idxPhone);

            } 
            while (c.moveToNext());
        }

        return contacts;
    }

    // Busca a pessoa pelo nome "select * from pessoa where nome=?"
    public Contact searchContactByName(String firstname) {
        Contact contact = null;

        try {
            // Idem a: SELECT _id,nome,cpf,idade from pessoa where nome = ?
            Cursor c = db.query(TABLE_NAME, Contact.columns, Contacts.NAME + "='" + firstname + "'", null, null, null, null);

            // Se encontrou...
            if (c.moveToNext()) {

                contact = new Contact();

                // utiliza os métodos getLong(), getString(), getInt(), etc para recuperar os valores
                contact.id1 = c.getLong(0);
                contact.name1 = c.getString(1);
                contact.name2 = c.getString(2);
                contact.phone1 = c.getInt(3);
            }
        } catch (SQLException e) {
            Log.e(CATEGORY, "Error while searching person by name: " + e.toString());
            return null;
        }

        return contact;
    }

    // Busca uma pessoa utilizando as configurações definidas no
    // SQLiteQueryBuilder
    // Utilizado pelo Content Provider de pessoa
    public Cursor query(SQLiteQueryBuilder queryBuilder, String[] projection, String selection, String[] selectionArgs,
            String groupBy, String having, String orderBy) 
    {
        Cursor c = queryBuilder.query(this.db, projection, selection, selectionArgs, groupBy, having, orderBy);
        return c;
    }

    // Fecha o banco
    public void close() {
        // fecha o banco de dados
        if (db != null) {
            db.close();
        }
    }

}

Contacts (where table is created)

package br.com.contactsmanager;

import android.content.ContentUris;
import android.net.Uri;
import android.provider.BaseColumns;

public class Contact 
{

    public static String[] columns = new String[] { Contacts._ID, Contacts.NAME, Contacts.LASTNAME, Contacts.PHONE };

    public static final String AUTHORITY = "com.br.contactsmanager.contactsregister.provider.contact";

    public long id1;
    public String name1;
    public String name2;
    public int phone1;

    public Contact() 
    {

    }

    public Contact(String name1, String name2, int phone1) {
        super();
        this.name1 = name1;
        this.name2 = name2;
        this.phone1 = phone1;
    }

    public Contact(long id1, String name1, String name2, int phone1) {
        super();
        this.id1 = id1;
        this.name1 = name1;
        this.name2 = name2;
        this.phone1 = phone1;
    }

    public static final class Contacts implements BaseColumns 
    {

        // Não pode instanciar esta Classe
        private Contacts() 
        {

        }

        public static final Uri CONTENT_URI = Uri.parse("content://" + AUTHORITY + "/contacts");

        public static final String CONTENT_TYPE = "vnd.android.cursor.dir/vnd.google.contacts";

        public static final String CONTENT_ITEM_TYPE = "vnd.android.cursor.item/vnd.google.contacts";

        public static final String DEFAULT_SORT_ORDER = "_id ASC";

        public static final String NAME = "name1";
        public static final String LASTNAME = "name2";
        public static final String PHONE = "phone1";

        public static Uri getUriId(long id) 
        {
            // Adiciona o id na URI default do /pessoas
            Uri uriContact = ContentUris.withAppendedId(Contacts.CONTENT_URI, id);
            return uriContact;
        }
    }


}

EDIT.:

 03-21 13:56:04.661: E/SQLiteLog(1094): (1) no such column: name1
03-21 13:56:04.661: E/data(1094): Error while searching contacts: android.database.sqlite.SQLiteException: no such column: name1 (code 1): , while compiling: SELECT _id, name1, name2, phone1 FROM contact
03-21 13:56:04.671: D/AndroidRuntime(1094): Shutting down VM
03-21 13:56:04.671: W/dalvikvm(1094): threadid=1: thread exiting with uncaught exception (group=0xb4aa7b90)
03-21 13:56:04.691: E/AndroidRuntime(1094): FATAL EXCEPTION: main
03-21 13:56:04.691: E/AndroidRuntime(1094): Process: br.com.contactsmanager, PID: 1094
03-21 13:56:04.691: E/AndroidRuntime(1094): java.lang.RuntimeException: Unable to start activity ComponentInfo{br.com.contactsmanager/br.com.contactsmanager.ContactRegister}: java.lang.NullPointerException
03-21 13:56:04.691: E/AndroidRuntime(1094):     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2176)
03-21 13:56:04.691: E/AndroidRuntime(1094):     at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2226)
03-21 13:56:04.691: E/AndroidRuntime(1094):     at android.app.ActivityThread.access$700(ActivityThread.java:135)
03-21 13:56:04.691: E/AndroidRuntime(1094):     at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1397)
03-21 13:56:04.691: E/AndroidRuntime(1094):     at android.os.Handler.dispatchMessage(Handler.java:102)
03-21 13:56:04.691: E/AndroidRuntime(1094):     at android.os.Looper.loop(Looper.java:137)
03-21 13:56:04.691: E/AndroidRuntime(1094):     at android.app.ActivityThread.main(ActivityThread.java:4998)
03-21 13:56:04.691: E/AndroidRuntime(1094):     at java.lang.reflect.Method.invokeNative(Native Method)
03-21 13:56:04.691: E/AndroidRuntime(1094):     at java.lang.reflect.Method.invoke(Method.java:515)
03-21 13:56:04.691: E/AndroidRuntime(1094):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:777)
03-21 13:56:04.691: E/AndroidRuntime(1094):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:593)
03-21 13:56:04.691: E/AndroidRuntime(1094):     at dalvik.system.NativeStart.main(Native Method)
03-21 13:56:04.691: E/AndroidRuntime(1094): Caused by: java.lang.NullPointerException
03-21 13:56:04.691: E/AndroidRuntime(1094):     at br.com.contactsmanager.ContactRepository.listContacts(ContactRepository.java:168)
03-21 13:56:04.691: E/AndroidRuntime(1094):     at br.com.contactsmanager.ContactRegister.updateList(ContactRegister.java:39)
03-21 13:56:04.691: E/AndroidRuntime(1094):     at br.com.contactsmanager.ContactRegister.onCreate(ContactRegister.java:34)
03-21 13:56:04.691: E/AndroidRuntime(1094):     at android.app.Activity.performCreate(Activity.java:5243)
03-21 13:56:04.691: E/AndroidRuntime(1094):     at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1087)
03-21 13:56:04.691: E/AndroidRuntime(1094):     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2140)
03-21 13:56:04.691: E/AndroidRuntime(1094):     ... 11 more

where Voce declares _ID? Used in: Contacts._ID

– Math

2014/03/21 at 16:59
cue.. BaseColumns you didn’t do it

– Math

2014/03/21 at 17:00
what I meant by Editado com a resposta? As for edits Qtd, don’t worry about it. I could post your stack trace complete?

– Math

2014/03/21 at 17:46
I meant that my post was edited with the answer to your question. I will put, just a moment...

– Leonardo Saldanha

2014/03/21 at 17:55
Edited with the answer.

– Leonardo Saldanha

2014/03/21 at 17:55
edited? did not appear

– Math

2014/03/21 at 17:57
Look again.

– Leonardo Saldanha

2014/03/21 at 18:00
I believe you had a problem reading the name1 in the method getCursor that made the method return null which then generated the NullPointerException, then the NullPointerException is a consequence, not the cause. By the way, you did: e.toString(), I advise you to always do e.printStackTrace() because it shows the complete exception path instead of a simple error msg

– Math

2014/03/21 at 18:11
But in this section of getCursor I should only declare "Contact.Columns" then, without the nulls and the column fields?

– Leonardo Saldanha

2014/03/21 at 18:18
In vdd I did not try to give the solution in the comment above, just explained q NPE is not the cause of the problems, it is just another consequence.

– Math

2014/03/21 at 19:09

Show 5 more comments

1 answer

Browser other questions tagged java android sqlite

You are not signed in. Login or sign up in order to post.

by Math • **30,313** points · Answer 1 · 2014-03-21T17:24:55+00:00

2

You create a class variable:

public static final String NAME = "name1";

And use it in your code:

public Cursor getCursor() {
    try {
        // select * from pessoas
        return db.query(TABLE_NAME, Contact.columns, null, null, null, null, null, null);
    } catch (SQLException e) {
        Log.e(CATEGORY, "Error while searching contacts: " + e.toString());
        return null;
    }
}

As the generated exception says:

in such column: name1

Sure that in your database you gave another name to the column (maybe nome1?). Check your bd and change the column name on it or change the variable to the correct column name

I checked my database and all the code again, and the variables related to the column and the names are named as name 1, as well as the column.

– Leonardo Saldanha

2014/03/21 at 17:39
name1 no spaces, right?

– Math

2014/03/21 at 17:47
Yes sir! I forgot to comment that in the log, a Nullpointerexception is pointed out just before quoting the error in Contactrepository.

– Leonardo Saldanha

2014/03/21 at 17:52
@Leos Do you have more than one table? You are looking at the table contact comic book android_data ?

– Math

2014/03/21 at 18:17
by the way, if your intention is to select all (select *) you can use null in place of Contact.columns in the method db.query()

– Math

2014/03/21 at 18:18
@Leos I know I’ve asked this many times, but I need to ask one more time (I swear it’s the last one, rs..). I saw it in yours another question Voce created your table like this: "create table contact ( _id integer primary key autoincrement, firstname text not null, lastname text not null, phone text);". Sure the column name is name1 and not fisrtname that isn’t even in your other question?

– Math

2014/03/21 at 19:11
She was like this before I changed to name1. hahahaha Now she’s right. Trying to use the medium you said, selecting all and replacing Contact.Columns with null, the error is gone, but in return, this is: java.lang.Illegalstateexception: Couldn’t read Row 0, col -1 from Cursorwindow. Make sure the Cursor is initialized correctly before accessing data from it.

– Leonardo Saldanha

2014/03/21 at 19:31
I only have one table to save contacts, and yes, I’m looking.

– Leonardo Saldanha

2014/03/21 at 19:31

Show 3 more comments