Error in Sqlite database

Question

While running my application using Sqlite i am getting the below error.

SQLiteLog(779): (1) near "SQXC31DLT": syntax error
System.out(779): DATABASE ERROR android.database.sqlite.SQLiteException: near "SQXC31DLT": syntax error (code 1): , while compiling: SELECT  count(*) FROM MessageTable  WHERE userID =? SQXC31DLT

Code:

Here is my query

   String query = "INSERT INTO MessageTable (user_message_id,message,title,message_time,mp3,userID) values ('" + 

              umsermessgid + "','" + Messages +"','" + Title +"','" + timedate+ "','" + musicmp3 + "','" + userid + "')";


                     sqlHandler.executeQuery(query);
                        showList();

Here is the remaining showlist() method:

 private void showList() {
            // TODO Auto-generated method stub
            contactList = new ArrayList<ContactListItems>();
             contactList.clear();
             userid=    mDataStorage.getToken(FirstActivity.this);

String query = "SELECT  count(*) FROM MessageTable  WHERE userID = '" + userid +"'";              Cursor c1 = sqlHandler.selectQuery(query);
              if (c1 != null && c1.getCount() != 0) {
               if (c1.moveToFirst()) {
              //  do {
                 ContactListItems contactListItems = new ContactListItems();

                 contactListItems.setSlno(c1.getString(c1.getColumnIndex("ID")));
                 contactListItems.setmessage(c1.getString(c1.getColumnIndex("message")));
                 contactListItems.settitle(c1.getString(c1.getColumnIndex("title")));
                 contactListItems.setmp3(c1.getString(c1.getColumnIndex("mp3")));
                 contactListItems.setusermessageid(c1.getString(c1.getColumnIndex("user_message_id")));
                 contactList.add(contactListItems);

               // } while (c1.moveToNext());
               }
              }
              c1.close();


        }

I have checked everything but still i am receiving error

CursorWindow(1211): Failed to read row 0, column -1 from a CursorWindow which has 1 rows, 1 columns.



   05-18 10:37:11.022: E/AndroidRuntime(1211): FATAL EXCEPTION: main
05-18 10:37:11.022: E/AndroidRuntime(1211): java.lang.IllegalStateException: Couldn't read row 0, col -1 from CursorWindow.  Make sure the Cursor is initialized correctly before accessing data from it.
05-18 10:37:11.022: E/AndroidRuntime(1211):     at android.database.CursorWindow.nativeGetString(Native Method)
05-18 10:37:11.022: E/AndroidRuntime(1211):     at android.database.CursorWindow.getString(CursorWindow.java:434)
05-18 10:37:11.022: E/AndroidRuntime(1211):     at android.database.AbstractWindowedCursor.getString(AbstractWindowedCursor.java:51)
05-18 10:37:11.022: E/AndroidRuntime(1211):     at com.example.universal.FirstActivity.showList(FirstActivity.java:330)
05-18 10:37:11.022: E/AndroidRuntime(1211):     at com.example.universal.FirstActivity.access$1(FirstActivity.java:317)
05-18 10:37:11.022: E/AndroidRuntime(1211):     at com.example.universal.FirstActivity$GetTask.onPostExecute(FirstActivity.java:137)
05-18 10:37:11.022: E/AndroidRuntime(1211):     at com.example.universal.FirstActivity$GetTask.onPostExecute(FirstActivity.java:1)
05-18 10:37:11.022: E/AndroidRuntime(1211):     at android.os.AsyncTask.finish(AsyncTask.java:631)
05-18 10:37:11.022: E/AndroidRuntime(1211):     at android.os.AsyncTask.access$600(AsyncTask.java:177)
05-18 10:37:11.022: E/AndroidRuntime(1211):     at android.os.AsyncTask$InternalHandler.handleMessage(AsyncTask.java:644)
05-18 10:37:11.022: E/AndroidRuntime(1211):     at android.os.Handler.dispatchMessage(Handler.java:99)
05-18 10:37:11.022: E/AndroidRuntime(1211):     at android.os.Looper.loop(Looper.java:137)
05-18 10:37:11.022: E/AndroidRuntime(1211):     at android.app.ActivityThread.main(ActivityThread.java:5041)
05-18 10:37:11.022: E/AndroidRuntime(1211):     at java.lang.reflect.Method.invokeNative(Native Method)
05-18 10:37:11.022: E/AndroidRuntime(1211):     at java.lang.reflect.Method.invoke(Method.java:511)
05-18 10:37:11.022: E/AndroidRuntime(1211):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:793)
05-18 10:37:11.022: E/AndroidRuntime(1211):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:560)
05-18 10:37:11.022: E/AndroidRuntime(1211):     at dalvik.system.NativeStart.main(Native Method)

Can anybody help me to solve this issue!! @Thanks

Answer 1

What are you trying to do with your code?

This sql query should be the right query for your first problem:

String query = "SELECT count(*) FROM MessageTable WHERE userID = '" + userid +"'"; (by Gunaseelan)

This error:

Failed to read row 0, column -1 from a CursorWindow which has 1 rows, 1 columns.

is because your using a false query. The query you are using returns only a number - the count. ( one column and one row)

The right query for this code:

Cursor c1 = sqlHandler.selectQuery(query); if (c1 != null && c1.getCount() != 0) { if (c1.moveToFirst()) { // do { ContactListItems contactListItems = new ContactListItems();

             contactListItems.setSlno(c1.getString(c1.getColumnIndex("ID")));
             contactListItems.setmessage(c1.getString(c1.getColumnIndex("message")));
             contactListItems.settitle(c1.getString(c1.getColumnIndex("title")));
             contactListItems.setmp3(c1.getString(c1.getColumnIndex("mp3")));
             contactListItems.setusermessageid(c1.getString(c1.getColumnIndex("user_message_id")));
             contactList.add(contactListItems);

           // } while (c1.moveToNext());
           }
          }
          c1.close();

should be:

String query = "SELECT * FROM MessageTable WHERE userID = '" + userid +"'";

and not:

String query = "SELECT count(*) FROM MessageTable WHERE userID = '" + userid +"'";

Answer 2

Change your query like the following

String query = "SELECT  count(*) FROM MessageTable  WHERE userID = '" + userid +"'";

Or try like the following.

Cursor c = db.rawQuery("SELECT count(*) FROM MessageTable where userID = '"+ userid + "'", null);

Answer 3

your query is wrong.

use:

String query = "SELECT  count(*) FROM MessageTable  WHERE userID =" + userid ;

in your showList() function.