CODEWITHSUNDEEP
javaandroidsqlitecursor
Hoo
Hoo

Reputation: 1840

cannot delete data using given Query

I have three tables and I want to delete them.

Table Info:id(PK),name,status,date,weather

Table WorkForce: id1(PK), subContractors,noOfPeople,noOfHours,TInfo_id(FK to table Info)

Table WorkDetails:id2(PK),project,workDescription, Twf_id(FK to table workForce),TableInfo_id(FK to table Info) //contains multiple row

Table Info

 ID          NAME        Weather        Date     Status
 ----------  ----------  ----------  ----------  ----------
    1           Paul        Sunny         15/10      MC
    2           Allen       Rainy         15/10      Working

Table WorkForce

ID1          SubContractors   NoOfPeople      NoOfHours        TInfo_id
----------  --------------   ----------       ----------     -----------
1           AAA                2                 2                 1
2           BBB                3                 1                 2

Table WorkDetails

ID2         Project       WorkDescription        TableInfo_id      Twf_id
----------  ----------     --------------          ----------    ----------
1              A               B                       1             1
2                                                      1             1
3                                                      1             1
4                                                      1             1
5               C               D                      2             2
6                                                      2             2
7                                                      2             2
8                                                      2             2

Assume I want to delete ID 17 in table Info by using code below

ListDisplay.java

  listView.setOnItemLongClickListener(new AdapterView.OnItemLongClickListener() {
            public boolean onItemLongClick(final AdapterView<?> p, View v, final int po, long id) {
                final long id1=id;
                AlertDialog.Builder builder = new AlertDialog.Builder(ListDisplay.this);
                builder.setTitle("Delete");
                builder.setMessage("Are you sure you want to delete?");
                builder.setIcon(android.R.drawable.ic_dialog_alert);
                builder.setPositiveButton("Yes", new DialogInterface.OnClickListener() {
                    public void onClick(DialogInterface dialog, int ii) {

                        database = dbHelper.getWritableDatabase();
                        Cursor cursor = (Cursor) p.getItemAtPosition(po);

                        // Get the state's capital from this row in the database.
                        long ID =
                                cursor.getLong(cursor.getColumnIndexOrThrow("_id"));
                      sqlcon.delete(ID);

                    }
                });
                builder.setNegativeButton("Cancel", new DialogInterface.OnClickListener()

                        {
                            public void onClick(DialogInterface dialog, int ii) {
                                dialog.dismiss();
                            }
                        }

                );
                builder.show();
                return true;
            }
        });

InfoAPI.java

  public void delete(long a)
    {
         database.delete(MyDatabaseHelper.TABLE_INFO + MyDatabaseHelper.TABLE_WORKFORCE, MyDatabaseHelper.TABLE_WORKDETAILS + "WHERE" + MyDatabaseHelper.ID + "=" + a+ MyDatabaseHelper.TInfo_id+ "=" + a +MyDatabaseHelper.Twf_id+ "=" + a, null);
    }

I get error as below 

10-19 01:45:54.816    6002-6002/com.example.project.project E/AndroidRuntime﹕ FATAL EXCEPTION: main
    Process: com.example.project.project, PID: 6002
    android.database.sqlite.SQLiteException: unrecognized token: "17TInfo_id" (code 1): , while compiling: DELETE FROM InformationWorkForce WHERE WorkDetailsWHERE_id=17TInfo_id=17TWf_id=17
            at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
            at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:887)

Upvotes: 1

Views: 72

Answers (1)

jyanks
jyanks

Reputation: 2396

You need to add spaces and an AND. Also, you don't need to add the Where since it's already added by the SqliteDatabase.

It also looks like you're trying to potentially delete from two tables in one call. It needs to be one delete call per table.

  public void delete(long a)
{
     database.delete(MyDatabaseHelper.TABLE_INFO + MyDatabaseHelper.TABLE_WORKFORCE, MyDatabaseHelper.ID + " =? " AND " + MyDatabaseHelper.TInfo_id + " =?" + MyDatabaseHelper.Twf_id + "=? ", new String[] { a + "", a + "", a + "" });
}

So that the sql statement reads as:

DELETE FROM InformationWorkForce WHERE _id=17 AND TInfo_id=17 AND TWf_id=17

Most likely though, you need to just do something like this:

database.delete(MyDatabaseHelper.TABLE_INFO, MyDatabaseHelper.TInfo_id + " =?", new String[] { tinfo_id + "" });
database.delete(MyDatabaseHelper.TABLE_WORKFORCE, MyDatabaseHelper.Twf_id + " =?", new String[] { twf_id + "" });

Note that the new String[] array is what replaces the ? marks. You will need to first query for the ids and then call the respective deletes. Fix your where clause code and it'll be easy.

Upvotes: 2

Related Questions