DELETE WHERE ID not working

Question

I'm try to display the data from a database into a ListView. Each item within the ListView has one position and I'm using that int position as the ID for the DELETE query since it's the same. However, it doesn't do anything. I'm calling the method during a button click event. This is the function from the SQLite database class:

 public Cursor deleteFromRegSintomasWhereId(long id) {
    String sql = "DELETE FROM Reg_sintomas WHERE _ID= ? ; " ;
    String[] args = new String[]{id + ""};
    Cursor result = this.db.rawQuery(sql, args);
    return result;
}

This is my activity which contains the ListView:

   package com.example.bugdroid.menuexe.TabFragments;

import android.content.Intent;
import android.database.Cursor;
import android.os.Bundle;
import android.support.v4.app.Fragment;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.AdapterView;
import android.widget.ListView;
import android.widget.RelativeLayout;
import android.widget.TextView;
import android.widget.Toast;

import com.example.bugdroid.menuexe.Activities.DetalhesSintoma;
import com.example.bugdroid.menuexe.Activities.VerRegsSintomas;
import com.example.bugdroid.menuexe.CursorAdapter.RegistosCursorAdapter;
import com.example.bugdroid.menuexe.R;
import com.example.bugdroid.menuexe.database.DAL;
import com.example.bugdroid.menuexe.database.DBAccessMode;

public class TabSintomas extends Fragment {

    private DAL dal;

    @Override
    public View onCreateView(LayoutInflater inflater, ViewGroup container, Bundle savedInstanceState) {
        final RelativeLayout rootView = (RelativeLayout) inflater.inflate(R.layout.fragment_tab_sintomas, container, false);

        dal = new DAL(getActivity());
        dal.connect(DBAccessMode.READ);

        // Vai usar um cursor para percorrer a base de dados, usando o metodo "selectALLFromRegSintomas()" que vem da classe "DAL".
        Cursor cursor = dal.selectALLFromRegSintomas();
        // é precisar declarar o layout do fragment com que estamos a trabalhar
        final View v = inflater.inflate(R.layout.fragment_tab_sintomas, container, false);

        // Declarar a listview onde vao aparecer os dados
        ListView lv = (ListView) v.findViewById(R.id.listv);
        // Declarar o adapter que define como aparecem os dados na listview
        RegistosCursorAdapter todoAdapter = new RegistosCursorAdapter(getActivity(), cursor, 0);
        // Ligar o adapter
        lv.setAdapter(todoAdapter);

        //  Nos fragments é preciso sempre fazer return do layout que queremos mostrar, neste caso (fragment_tab_sintomas);

        lv.setOnItemClickListener(new AdapterView.OnItemClickListener() {
            public void onItemClick(AdapterView<?> parent, View view, int position, long id) {
                Intent intent = new Intent(getActivity(), DetalhesSintoma.class);
                intent.putExtra("myExtras", position+1);
                startActivity(intent);
                //Toast.makeText(getActivity(),"clique",
                //        Toast.LENGTH_SHORT).show();
            }
        });
        return v;
    }
}

This is the activity that loads when an item is clicked:

   package com.example.bugdroid.menuexe.Activities;

import android.app.Activity;
import android.app.AlertDialog;
import android.content.DialogInterface;
import android.content.Intent;
import android.database.Cursor;
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.support.v7.widget.Toolbar;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.Toast;

import com.example.bugdroid.menuexe.R;
import com.example.bugdroid.menuexe.database.DAL;
import com.example.bugdroid.menuexe.database.DBAccessMode;
import com.example.bugdroid.menuexe.database.DBContract;

import org.w3c.dom.Text;

public class DetalhesSintoma extends AppCompatActivity {

    private DAL dal;
    private TextView DataInicio;
    private EditText HoraIncio;
    private TextView DataFim;
    private EditText HoraFim;
    private TextView Intensidade;
    private TextView Motivo;
    private TextView Medicacao;
    private Button Apagar;
    private Button Concluido;
    private int position;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_detalhes_sintoma);

        DataInicio = (TextView) findViewById(R.id.DiaInicioText);
        // HoraIncio = (EditText) findViewById(R.id.HoraInicioText);
        DataFim = (TextView) findViewById(R.id.DiaFimText);
        //  HoraFim = () findViewById(R.id.HoraFimText);
        Intensidade = (TextView) findViewById(R.id.IntensidadeText);
        Motivo = (TextView) findViewById(R.id.MotivoText);
        Medicacao = (TextView) findViewById(R.id.MedicacaoText);
        Apagar = (Button) findViewById(R.id.btnApagar);
        Concluido = (Button) findViewById(R.id.btnConcluido);

        Intent intent = getIntent();
        position = intent.getIntExtra("myExtras", 0);

        dal = new DAL(this);
        dal.connect(DBAccessMode.READ);

        Cursor res = dal.selectFromRegSintomasWhereId(position);
        res.moveToFirst();
        StringBuilder datahora = new StringBuilder();
        while (!res.isAfterLast()) {
            Intensidade.setText(res.getString(res.getColumnIndex(DBContract.Reg_sintomas.COL_Intensidade)));
            String Data = res.getString(res.getColumnIndex(DBContract.Reg_sintomas.COL_D_Inicio));
            String Hora = res.getString(res.getColumnIndex(DBContract.Reg_sintomas.COL_H_Inicio));
            res.moveToNext();
            DataInicio.setText(datahora.append(Data + " ás " + Hora));
        }

        Cursor res1 = dal.selectFromRegSintomasWhereId(position);
        res1.moveToFirst();
        StringBuilder datahoraF = new StringBuilder();
        while (!res1.isAfterLast()) {
            String Data = res1.getString(res1.getColumnIndex(DBContract.Reg_sintomas.COL_D_Fim));
            String Hora = res1.getString(res1.getColumnIndex(DBContract.Reg_sintomas.COL_H_Fim));
            res1.moveToNext();
            DataFim.setText(datahoraF.append(Data + " ás " + Hora));
        }

        Cursor res2 = dal.selectFromMotivoSintomassWhereId(position);
        res2.moveToFirst();
        StringBuilder sb = new StringBuilder();
        while (!res2.isAfterLast()) {
            sb.append(res2.getString(res2.getColumnIndex(DBContract.Motivo_sintomas.COL_Motivo)));
            res2.moveToNext();
        }
        Motivo.setText(sb.toString());

        Cursor res3 = dal.selectMedicacaoWhereId(position);
        res3.moveToFirst();
        StringBuilder sb2 = new StringBuilder();
        while (!res3.isAfterLast()) {
            sb2.append(res3.getString(res3.getColumnIndex(DBContract.Medicacao.COL_Medicacao)));
            res3.moveToNext();
        }
        Medicacao.setText(sb2.toString());


        Apagar.setOnClickListener(new View.OnClickListener() {
            public void onClick(View v) {
                dal.deleteFromRegSintomasWhereId(position);
                Intent intent = new Intent(DetalhesSintoma.this, VerRegsSintomas.class);
                startActivity(intent);
            }
        });
    }
}

This is where I call the function from the code above:

Apagar.setOnClickListener(new View.OnClickListener() {
        public void onClick(View v) {
            dal.deleteFromRegSintomasWhereId(position);
            Intent intent = new Intent(DetalhesSintoma.this, VerRegsSintomas.class);
            startActivity(intent);
        }
    });

How do I fix this error? Any suggestions?

Please mention the exact issue - whether the row is not deleted from the database or is there any other issue ? — Shadab Ansari, Jun 13 '16 at 21:39
Have you checked the sqlite database file to see if the row really is deleted but your UI just isn't updating? — Austin Hanson, Jun 13 '16 at 22:03
Use Firefox `SQLite Manager` plugin to check your database. Also, if possible, paste your `DAL` class. — Shadab Ansari, Jun 13 '16 at 22:26

score 3 · Answer 1 · answered Jun 14 '16 at 08:39

DELETE is a command, not a query.
You must execute it by using execSQL(), not rawQuery().

Note that execSQL() doesn't return anything.

This will work fine:

public void deleteFromRegSintomasWhereId(long id)
{
    String sql = "DELETE FROM Reg_sintomas WHERE _ID = ?";
    String[] args = new String[]{id};
    this.db.execSQL(sql, args);
}

DELETE WHERE ID not working

1 Answers1