Check duplicate values before inserting data into SQLite in android using eclipse?

Question

Hi can anyone help me in below code?

Once a record with 123 id is inserted and next time it should not accept for 123 id again to insert in database.... so please help me to complete this project...

package com.example.finder;

import android.app.Activity;

import android.database.Cursor;

import android.database.sqlite.SQLiteDatabase;

import android.graphics.Color;

import android.graphics.Typeface;

import android.os.Bundle;

import android.view.View;

import android.widget.EditText;

import android.widget.TableLayout;

import android.widget.TableRow;

import android.widget.TextView;

import android.widget.Toast;

public class AdminActivity extends Activity {

String id,name,email;
SQLiteDatabase db;
TableRow tableRow;
TextView textview,textview1,textview2,textview3,textview4,textview5;

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_admin);
    db=openOrCreateDatabase("EMP",MODE_PRIVATE,null);
    db.execSQL("CREATE TABLE IF NOT EXISTS EMP(id integer primary key,name varchar,email varchar);");
}

public void Submit(View view)
{       
    EditText edittext1=(EditText) findViewById(R.id.id);
    EditText edittext2=(EditText) findViewById(R.id.name);
    EditText edittext3=(EditText) findViewById(R.id.email);
    id=edittext1.getText().toString();
    name=edittext2.getText().toString();
    email=edittext3.getText().toString();
    db.execSQL("INSERT INTO EMP(id,name,email) values ('"+id+"','"+name+"','"+email+"');");

    if(id.equals("") && name.equals("") && email.equals(""))
    {
        Toast.makeText(getApplicationContext(), "Please enter details and submit", Toast.LENGTH_SHORT).show();
    }
    else 
        if(id.equals(""))
       {
            Toast toast=new Toast (getApplicationContext());
            toast.setDuration(Toast.LENGTH_LONG);
            Toast.makeText(getApplicationContext(), "Enter id....", Toast.LENGTH_SHORT).show();
       }
    else
        if(name.equals(""))
           {
            Toast toast=new Toast (getApplicationContext());
            toast.setDuration(Toast.LENGTH_LONG);
            Toast.makeText(getApplicationContext(), "Enter Name....", Toast.LENGTH_SHORT).show();
           }
        else
             if(email.equals(""))
            {
            Toast toast=new Toast (getApplicationContext());
            toast.setDuration(Toast.LENGTH_LONG);
             Toast.makeText(getApplicationContext(), "Enter email.....", Toast.LENGTH_SHORT).show();
            }
             else 
                { 
                    Toast toast=new Toast (getApplicationContext());
                    toast.setDuration(Toast.LENGTH_LONG);
                    edittext1.setText("");
                    edittext2.setText("");
                    edittext3.setText("");
                    Toast.makeText(getApplicationContext(), "Data submitted successfully", Toast.LENGTH_SHORT).show();
                    db.close();
                 }
            }

public void Showdata(View view)
{       
    Cursor c=db.rawQuery("SELECT * FROM EMP;",null);
    int count=c.getCount();
    c.moveToFirst();
    TableLayout tableLayout= new TableLayout(getApplicationContext());
    tableLayout.setVerticalScrollBarEnabled(true);
    TableRow tableRow;
    TextView textview,textview1,textview2,textview3,textview4,textview5;
    tableRow=new TableRow(getApplicationContext());
    textview=new TextView(getApplicationContext());
    textview.setText("Empid");
    textview.setTextColor(Color.RED);
    textview.setTypeface(null,Typeface.BOLD);
    textview.setPadding(0, 5, 3, 5);
    tableRow.addView(textview);
    textview4=new TextView(getApplicationContext());
    textview4.setText("Ename");
    textview4.setTextColor(Color.RED);
    textview4.setTypeface(null,Typeface.BOLD);
    textview4.setPadding(15, 10, 3, 5);
    tableRow.addView(textview4);
    textview5=new TextView(getApplicationContext());
    textview5.setText("Email");
    textview5.setTextColor(Color.RED);
    textview5.setTypeface(null,Typeface.BOLD);
    textview5.setPadding(25, 5, 3, 5);
    tableRow.addView(textview5);
    tableLayout.addView(tableRow);
    for (Integer j=0; j< count; j++)
    {
    tableRow= new TableRow(getApplicationContext());
    textview1=new TextView(getApplicationContext());
    textview1.setText(c.getString(c.getColumnIndex("id")));
    textview2=new TextView(getApplicationContext());
    textview2.setText(c.getString(c.getColumnIndex("name")));
    textview3=new TextView(getApplicationContext());
    textview3.setText(c.getString(c.getColumnIndex("email")));
    textview1.setPadding(0, 5, 3, 5);
    textview2.setPadding(15, 5, 3, 5);
    textview3.setPadding(25, 5, 3, 5);
    tableRow.addView(textview1);
    tableRow.addView(textview2);
    tableRow.addView(textview3);
    tableLayout.addView(tableRow);
    c.moveToNext();
    }
    setContentView(tableLayout);
    db.close();
}

public void Exit(View view){
    System.exit(0);
}

}

You can simply select from database if the id is present or not... And return error on present, else insert the data — Rehan
– Rehan, Commented Jan 15, 2014 at 11:53
Select first based upon id. If the cursor count is zero, you know you can insert. You need to move your INSERT as well - you currently appear to do that before validating the input. — NigelK
– NigelK, Commented Jan 15, 2014 at 11:57

Phantômaxx · Accepted Answer · 2014-01-15 12:41:27Z

1

I solved this issue by replacing all my inserts and my updates with the so called upserts (UPSERT = UPDATE OR INSERT).

String[] args = {"1", "newOrOldCategory"}; // where 1 is the category id
getWritableDatabase().execSQL("INSERT OR REPLACE INTO table_name (idColoumn, categoryColumn) VALUES (?, ?)", args);

or else (my current implementation):

String[] args = {"1", "newOrOldCategory"}; // where 1 is the category id
getWritableDatabase().execSQL("REPLACE INTO table_name (idColoumn, categoryColumn) VALUES (?, ?)", args);

Change the table and field names to match yours, and so the values.

Simple and effective: it inserts a record if it doesn't exist, else it updates it with the passed value.

No need to use the worst practices:

1 - update, check for error and (if so) insert

or (WORST EVER!)

2 - delete + insert

edited Jan 15, 2014 at 12:41

answered Jan 15, 2014 at 12:04

Phantômaxx

38.1k21 gold badges88 silver badges122 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SoulRayder · Accepted Answer · 2014-01-15 12:05:56Z

0

As suggested above, you can do this by checking the db whether that id exists in the table, with some valid entries. If it is not there, then only insert your new data.

Alternately, if you do not want duplicates altogether, you can also set the id in auto-increment mode, and hence there will be no duplicity.

answered Jan 15, 2014 at 12:05

SoulRayder

5,1666 gold badges52 silver badges99 bronze badges

Comments

vikas singh · Accepted Answer · 2014-01-15 12:16:51Z

use unique Key for that column and catch exception while inserting record.

like here name is unique so we cant insert duplicate name.


private static final String DATABASE_CREATE_TABLE_CLIENT = "create table " + DATABASE_TABLE_NAME_CLIENTS + "(_id integer primary key autoincrement,"+ NAME +" 
text not null ,"+ MOBILENO +" text not null," +COMPANYNAME+"  text not null,unique ("+NAME+"))";



try
        {
//          

            sqlite.execSQL("INSERT INTO "+DATABASE_TABLE_NAME_CLIENT +"("+CLIENTID+","+NAME+","+MOBILENO+","+COMPANYNAME+") 
values " +
                    "('"+clientid+"','"+name+"','"+mobileno+"','"+comname+"')");
        }

        catch(SQLiteConstraintException e)
        {
            Toast.makeText(context, "Please Enter different name....", Toast.LENGTH_LONG).show();
        }

may this answer help you

A.Muqtadir · Accepted Answer · 2022-06-28 06:31:32Z

0

Answer by @Phantômaxx found helpful to me. below is with small modification.This will it will not increase your read/write operation.

String[] args = {"1", "newOrOldCategory"}; // where 1 is the category id getWritableDatabase().execSQL("INSERT INTO table_name (idColoumn, categoryColumn) VALUES (?, ?) ON CONFLICT(id) DO NOTHING", args);

answered Jun 28, 2022 at 6:31

A.Muqtadir

1311 silver badge7 bronze badges

Collectives™ on Stack Overflow

Check duplicate values before inserting data into SQLite in android using eclipse?

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related