My app crashes and creates an SQLite compiling error when clicking an item in ListView. What is wrong?

huangapple go评论65阅读模式
英文:

My app crashes and creates an SQLite compiling error when clicking an item in ListView. What is wrong?

问题

以下是logcat中的崩溃报告:

android.database.sqlite.SQLiteException: near "Press": 语法错误 (代码 1 SQLITE_ERROR):在编译时:SELECT ID FROM BUTTONMANAGER WHERE FULL_NAME = James Smith AND DATE_ADDED = 7/30/2020

我正在尝试获取数据库中与ListView中的项目名称匹配FULL_NAME和日期添加匹配DATE_ADDED的行的项目ID。

我不确定我在这里做错了什么。我检查了我的queryString的语法,没有找到任何错误。当我仅检查FULL_NAME而不是DATE_ADDED时,应用程序不会崩溃。但是,我希望检查DATE_ADDED以确保我在数据库中删除了正确的行。

这是我的DatabaseHelper代码:

public Cursor getItemID(String name, String dateAdded) {
    SQLiteDatabase db = this.getWritableDatabase();
    String queryString = "SELECT " + COLUMN_ID + " FROM " + BUTTON_MANAGER + " WHERE " + COLUMN_FULL_NAME + " = " + name + " AND " + COLUMN_DATE_ADDED + " = " + dateAdded;

    Cursor data = db.rawQuery(queryString, null);
    return data;
}

这是我的mainActivity:

mainListView = (ListView) findViewById(R.id.mainListView);
mainListView.setOnItemClickListener(new AdapterView.OnItemClickListener() {
    @Override
    public void onItemClick(AdapterView<?> parent, View view, int position, long id) {
        String name = parent.getItemAtPosition(position).toString();
        String dateAdded = dateButton.getText().toString();

        Cursor data = databaseHelper.getItemID(name, dateAdded); //获取与ListView中项目名称关联的ID
        int itemID = -1;
        while(data.moveToNext()) {
            itemID = data.getInt(0);
        }

        if (itemID > -1) {
            Intent editExerciseIntent = new Intent(MainActivity.this, edit_existing_exercise.class);
            editUserIntent.putExtra("id", itemID);
            editUserIntent.putExtra("name", name);
            editUserIntent.putExtra("dateAdded", dateAdded);
            startActivity(editUserIntent);
            overridePendingTransition(R.anim.slide_in_left, R.anim.slide_out_right);
        }
    }
});

当我点击ListView中的项目时发生崩溃。

更多的崩溃报告:

android.database.sqlite.SQLiteException: near "Press": 语法错误 (代码 1 SQLITE_ERROR):在编译时:SELECT ID FROM BUTTONMANAGER WHERE FULL_NAME = James Smith AND DATE_ADDED = '7/30/2020'
    at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
    at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1016)
    at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:623)
    at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:590)
    at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:61)
    at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
    at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:46)
    at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1545)
    at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1484)
    at com.mycompany.mainapp.DatabaseHelper.getItemID(DatabaseHelper.java:118)
    at com.mycompany.mainapp.MainActivity$15.onItemClick(MainActivity.java:404)
    at android.widget.AdapterView.performItemClick(AdapterView.java:330)
    at android.widget.AbsListView.performItemClick(AbsListView.java:1187)
    at android.widget.AbsListView$PerformClick.run(AbsListView.java:3179)
    at android.widget.AbsListView$3.run(AbsListView.java:4097)
    at android.os.Handler.handleCallback(Handler.java:907)
    at android.os.Handler.dispatchMessage(Handler.java:99)
    at android.os.Looper.loop(Looper.java:223)
    at android.app.ActivityThread.main(ActivityThread.java:7478)
    at java.lang.reflect.Method.invoke(Native Method)
    at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:549)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:941)
英文:

Here is the crash report in logcat:

 android.database.sqlite.SQLiteException: near &quot;Press&quot;: syntax error (code 1 SQLITE_ERROR): , while compiling: SELECT ID FROM BUTTONMANAGER WHERE FULL_NAME = James Smith AND DATE_ADDED = 7/30/2020

I am trying to get the Item ID of a row in the database where the name of the item in the listview matches FULL_NAME and the date added matches DATE_ADDED.

I'm not sure what I'm doing wrong here. I looked over the syntax of my queryString and didn't find any errors. The app does not crash when I only check for FULL_NAME and not DATE_ADDED. However, I want to check for DATE_ADDED to ensure that I am deleting the correct row in the database.

This is my DatabaseHelper code:

    public Cursor getItemID(String name, String dateAdded) {
    SQLiteDatabase db = this.getWritableDatabase();
    String queryString = &quot;SELECT &quot; + COLUMN_ID + &quot; FROM &quot; + BUTTON_MANAGER + &quot; WHERE &quot; + COLUMN_FULL_NAME + &quot; = &quot; + name + &quot; AND &quot; + COLUMN_DATE_ADDED + &quot; = &quot; + dateAdded;

    Cursor data = db.rawQuery(queryString, null);
    return data;
}

This is my mainActivity:

        mainListView = (ListView) findViewById(R.id.mainListView);
    mainListView.setOnItemClickListener(new AdapterView.OnItemClickListener() {
        @Override
        public void onItemClick(AdapterView&lt;?&gt; parent, View view, int position, long id) {
            String name = parent.getItemAtPosition(position).toString();
            String dateAdded = dateButton.getText().toString();

            Cursor data = databaseHelper.getItemID(name, dateAdded); //Get ID associated with name of item in ListView
            int itemID = -1;
            while(data.moveToNext()) {
                itemID = data.getInt(0);
            }

            if (itemID &gt; -1) {
                Intent editExerciseIntent = new Intent(MainActivity.this, edit_existing_exercise.class);
                editUserIntent.putExtra(&quot;id&quot;, itemID);
                editUserIntent.putExtra(&quot;name&quot;, name);
                editUserIntent.putExtra(&quot;dateAdded&quot;, dateAdded);
                startActivity(editUserIntent);
                overridePendingTransition(R.anim.slide_in_left, R.anim.slide_out_right);
            }
        }
    });
}

The crash happens when I click the item in the ListView.

More of the crash report:

    android.database.sqlite.SQLiteException: near &quot;Press&quot;: syntax error (code 1 SQLITE_ERROR): , while compiling: SELECT ID FROM BUTTONMANAGER WHERE FULL_NAME = James Smith AND DATE_ADDED = &#39;7/30/2020&#39;
    at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
    at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1016)
    at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:623)
    at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:590)
    at android.database.sqlite.SQLiteProgram.&lt;init&gt;(SQLiteProgram.java:61)
    at android.database.sqlite.SQLiteQuery.&lt;init&gt;(SQLiteQuery.java:37)
    at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:46)
    at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1545)
    at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1484)
    at com.mycompany.mainapp.DatabaseHelper.getItemID(DatabaseHelper.java:118)
    at com.mycompany.mainapp.MainActivity$15.onItemClick(MainActivity.java:404)
    at android.widget.AdapterView.performItemClick(AdapterView.java:330)
    at android.widget.AbsListView.performItemClick(AbsListView.java:1187)
    at android.widget.AbsListView$PerformClick.run(AbsListView.java:3179)
    at android.widget.AbsListView$3.run(AbsListView.java:4097)
    at android.os.Handler.handleCallback(Handler.java:907)
    at android.os.Handler.dispatchMessage(Handler.java:99)
    at android.os.Looper.loop(Looper.java:223)
    at android.app.ActivityThread.main(ActivityThread.java:7478)
    at java.lang.reflect.Method.invoke(Native Method)
    at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:549)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:941)

答案1

得分: 1

似乎语法错误是因为您在日期添加部分使用了反斜杠。

尝试这样做:

String queryString = "SELECT " + COLUMN_ID + " FROM " + BUTTON_MANAGER + " WHERE " + COLUMN_FULL_NAME + " = " + name + " AND " + COLUMN_DATE_ADDED + " = '" + dateAdded + "'";
英文:

It looks like the syntax error is due to your slashes in date added.

Try this

String queryString = &quot;SELECT &quot; + COLUMN_ID + &quot; FROM &quot; + BUTTON_MANAGER + &quot; WHERE &quot; + COLUMN_FULL_NAME + &quot; = &quot; + name + &quot; AND &quot; + COLUMN_DATE_ADDED + &quot; = &#39;&quot; + dateAdded + &quot;&#39;&quot;;

答案2

得分: 0

最后,这个问题有了解决方案。我不得不在查询字符串的变量周围添加额外的引号。查询字符串的语法如下:

String queryString = "SELECT " + COLUMN_ID + " FROM " + BUTTON_MANAGER + " WHERE " + COLUMN_FULL_NAME + " = '" + name + "'" + " AND " + COLUMN_DATE_ADDED + " = '" + dateAdded + "'";

@Dave Newton,您的回答是正确的,我在最初的回答中没有正确使用语法。

感谢所有回答并帮助我的人。我感激不尽。

英文:

Finally, this question has a solution. I had to add extra quotations around the variables in the queryString. The syntax for the queryString looks like this:

String queryString = &quot;SELECT &quot; + COLUMN_ID + &quot; FROM &quot; + BUTTON_MANAGER + &quot; WHERE &quot; + COLUMN_FULL_NAME + &quot; = &#39;&quot; + name + &quot;&#39;&quot; + &quot; AND &quot; + COLUMN_DATE_ADDED + &quot; = &#39;&quot; + dateAdded + &quot;&#39;&quot;;

@Dave Newton your response was correct, I didn't use the syntax correctly when I originally responded.

Thank you to everyone who responded and helped me out. I appreciate it.

huangapple
  • 本文由 发表于 2020年7月31日 06:39:51
  • 转载请务必保留本文链接:https://go.coder-hub.com/63182389.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定