1

I have a small database app. It requires a user to enter a code 1A1 for example to search the Db.

If I hard code the sql command it will find that row. 

    Cursor c=db.rawQuery("SELECT * from emdcode WHERE code = '1A1'",null);

On my layout I have a text box for user input

        <EditText
    android:id="@+id/codelookup"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:layout_alignBaseline="@+id/textView1"
    android:layout_alignBottom="@+id/textView1"
    android:layout_alignParentRight="true"
    android:layout_toRightOf="@+id/button3"
    android:ems="10" >
    <requestFocus />
    </EditText>

My question> How do I write the SQL to search for the user input?

I have done this in Java

     private void SrchCodeActionPerformed(java.awt.event.ActionEvent evt)

{

String sql =  "SELECT * FROM emdcodes WHERE Codes like ?";

        try 
{
ps = conn.prepareStatement(sql);    
    ps.setString(1, EMDCODELOOKUP.getText() + "%");        
    rs = ps.executeQuery();
    ResultsTable.setModel(DbUtils.resultSetToTableModel(rs));      
    } 

catch (Exception e) 
{
JOptionPane.showMessageDialog(null, e);
    }
      }

So I assumned SQLite is similar

I have tried

      Cursor c=db.rawQuery("SELECT * from emdcode WHERE code = codelookup",null);

      Cursor c=db.rawQuery("SELECT * from emdcode WHERE code = ?",null);

Any Suggestions or guidemnce would be great. Thank you

update

When using Cursor c=db.rawQuery("SELECT * from emdcode WHERE code = ?", new String[]{"codelookup"}); I get a null result set. This is the logcat file:

    07-31 00:10:34.735: E/Trace(1765): error opening trace file: No such file or directory (2)

07-31 00:10:38.645: E/AndroidRuntime(1765): FATAL EXCEPTION: main 07-31 00:10:38.645: E/AndroidRuntime(1765): java.lang.IllegalStateException: Could not execute method of the activity 07-31 00:10:38.645: E/AndroidRuntime(1765): at android.view.View$1.onClick(View.java:3599) 07-31 00:10:38.645: E/AndroidRuntime(1765): at android.view.View.performClick(View.java:4204) 07-31 00:10:38.645: E/AndroidRuntime(1765): at android.view.View$PerformClick.run(View.java:17355) 07-31 00:10:38.645: E/AndroidRuntime(1765): at android.os.Handler.handleCallback(Handler.java:725) 07-31 00:10:38.645: E/AndroidRuntime(1765): at android.os.Handler.dispatchMessage(Handler.java:92) 07-31 00:10:38.645: E/AndroidRuntime(1765): at android.os.Looper.loop(Looper.java:137) 07-31 00:10:38.645: E/AndroidRuntime(1765): at android.app.ActivityThread.main(ActivityThread.java:5041) 07-31 00:10:38.645: E/AndroidRuntime(1765): at java.lang.reflect.Method.invokeNative(Native Method) 07-31 00:10:38.645: E/AndroidRuntime(1765): at java.lang.reflect.Method.invoke(Method.java:511) 07-31 00:10:38.645: E/AndroidRuntime(1765): at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:793) 07-31 00:10:38.645: E/AndroidRuntime(1765): at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:560) 07-31 00:10:38.645: E/AndroidRuntime(1765): at dalvik.system.NativeStart.main(Native Method) 07-31 00:10:38.645: E/AndroidRuntime(1765): Caused by: java.lang.reflect.InvocationTargetException 07-31 00:10:38.645: E/AndroidRuntime(1765): at java.lang.reflect.Method.invokeNative(Native Method) 07-31 00:10:38.645: E/AndroidRuntime(1765): at java.lang.reflect.Method.invoke(Method.java:511) 07-31 00:10:38.645: E/AndroidRuntime(1765): at android.view.View$1.onClick(View.java:3594) 07-31 00:10:38.645: E/AndroidRuntime(1765): ... 11 more 07-31 00:10:38.645: E/AndroidRuntime(1765): Caused by: java.lang.IllegalArgumentException: the bind value at index 1 is null 07-31 00:10:38.645: E/AndroidRuntime(1765): at android.database.sqlite.SQLiteProgram.bindString(SQLiteProgram.java:164) 07-31 00:10:38.645: E/AndroidRuntime(1765): at android.database.sqlite.SQLiteProgram.bindAllArgsAsStrings(SQLiteProgram.java:200) 07-31 00:10:38.645: E/AndroidRuntime(1765): at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:47) 07-31 00:10:38.645: E/AndroidRuntime(1765): at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1314) 07-31 00:10:38.645: E/AndroidRuntime(1765): at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1253) 07-31 00:10:38.645: E/AndroidRuntime(1765): at com.example.studentdatabase.MainActivity.showdata(MainActivity.java:47) 07-31 00:10:38.645: E/AndroidRuntime(1765): ... 14 more 07-31 00:11:55.095: E/Trace(1839): error opening trace file: No such file or directory (2) 07-31 00:20:08.084: E/Trace(2020): error opening trace file: No such file or directory (2) 07-31 00:20:16.915: E/AndroidRuntime(2020): FATAL EXCEPTION: main 07-31 00:20:16.915: E/AndroidRuntime(2020): java.lang.IllegalStateException: Could not execute method of the activity 07-31 00:20:16.915: E/AndroidRuntime(2020): at android.view.View$1.onClick(View.java:3599) 07-31 00:20:16.915: E/AndroidRuntime(2020): at android.view.View.performClick(View.java:4204) 07-31 00:20:16.915: E/AndroidRuntime(2020): at android.view.View$PerformClick.run(View.java:17355) 07-31 00:20:16.915: E/AndroidRuntime(2020): at android.os.Handler.handleCallback(Handler.java:725) 07-31 00:20:16.915: E/AndroidRuntime(2020): at android.os.Handler.dispatchMessage(Handler.java:92) 07-31 00:20:16.915: E/AndroidRuntime(2020): at android.os.Looper.loop(Looper.java:137) 07-31 00:20:16.915: E/AndroidRuntime(2020): at android.app.ActivityThread.main(ActivityThread.java:5041) 07-31 00:20:16.915: E/AndroidRuntime(2020): at java.lang.reflect.Method.invokeNative(Native Method) 07-31 00:20:16.915: E/AndroidRuntime(2020): at java.lang.reflect.Method.invoke(Method.java:511) 07-31 00:20:16.915: E/AndroidRuntime(2020): at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:793) 07-31 00:20:16.915: E/AndroidRuntime(2020): at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:560) 07-31 00:20:16.915: E/AndroidRuntime(2020): at dalvik.system.NativeStart.main(Native Method) 07-31 00:20:16.915: E/AndroidRuntime(2020): Caused by: java.lang.reflect.InvocationTargetException 07-31 00:20:16.915: E/AndroidRuntime(2020): at java.lang.reflect.Method.invokeNative(Native Method) 07-31 00:20:16.915: E/AndroidRuntime(2020): at java.lang.reflect.Method.invoke(Method.java:511) 07-31 00:20:16.915: E/AndroidRuntime(2020): at android.view.View$1.onClick(View.java:3594) 07-31 00:20:16.915: E/AndroidRuntime(2020): ... 11 more 07-31 00:20:16.915: E/AndroidRuntime(2020): Caused by: java.lang.IllegalArgumentException: the bind value at index 1 is null 07-31 00:20:16.915: E/AndroidRuntime(2020): at android.database.sqlite.SQLiteProgram.bindString(SQLiteProgram.java:164) 07-31 00:20:16.915: E/AndroidRuntime(2020): at android.database.sqlite.SQLiteProgram.bindAllArgsAsStrings(SQLiteProgram.java:200) 07-31 00:20:16.915: E/AndroidRuntime(2020): at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:47) 07-31 00:20:16.915: E/AndroidRuntime(2020): at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1314) 07-31 00:20:16.915: E/AndroidRuntime(2020): at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1253) 07-31 00:20:16.915: E/AndroidRuntime(2020): at com.example.studentdatabase.MainActivity.showdata(MainActivity.java:47) 07-31 00:20:16.915: E/AndroidRuntime(2020): ... 14 more 07-31 00:32:07.675: E/Trace(2296): error opening trace file: No such file or directory (2) 07-31 00:33:22.405: E/Trace(2377): error opening trace file: No such file or directory (2)

Improve this question

2 Answers 2

Reset to default
1

You can try this:

public Cursor GetData(String Code) throws Exception 
    {   
        Cursor cursor;

        cursor = this.db.query("emdcode",null, "code = "+Code, null, null, null, null,null );

        return cursor;
    }

or

public Cursor GetData(String Code) throws Exception 
    {   
        Cursor cursor;

        cursor = this.db.query("emdcode",null, "code = ?", new String[]{code}, null, null, null,null );

        return cursor;
    }
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

Try.

Cursor c=db.rawQuery("SELECT * from emdcode WHERE code = ?", new String[]{theStringYouWantToSearchForHere});

You almost had it when you used the ?. The 2nd argument should be a string array for the where arguments.

If you look at the logcat, 

Caused by: java.lang.IllegalArgumentException: the bind value at index 1 is null

Means that the string you are parsing into the array is null, resulting in the crash

If you put 

String search = "1A1";
Cursor c=db.rawQuery("SELECT * from emdcode WHERE code = ?", new String[]{search});

That should work.

It means you are not initialising your search value before you call this method (most likely).

Edit: For the Edit text

You want to get the edit text value. Something like 

EditText codelookup = (EditText) ActivityName.this.findViewById(R.id.codelookup);
String search = codelookup.getText().toString();

Note that here I assume you are inside of the activity for findViewById. If you are going to use this alot I suggest you have the editTextFor codelookup as a class variable. Where you would only have to find it once (note that you would need to have the findViewById after the setContentView()).

Improve this answer

15 Comments

Maybe I am not quite understanding were the String is coming from.
Maybe I am not quite understanding were the String is coming from. I have tried ?", new String[]{'"+codelookup+"'}); I have tried ="@+id/codelookup. I keep getting a error - unfortanly, Code lookup app has stopped. any suggestions??
Are you able to post the logcat that you get when it crashes?
Can you please also add the whole enitre method for the call. I'm assumming the string is null.
I tried but it is too long by about 3000 charactors. How much of log do you need to see
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.