Android Question ExecuteListView Error

[Devan]

Hi There im getting below error. Any Idea why?
I created the List View with designer. But than every time I get Object must be initialized.
so I initialize ListView1 in Activity Create

    If ListView1.IsInitialized = False Then
                ListView1.Initialize("ListView1")
        End If

** Activity (main) Create, isFirst = true **
true
true
** Activity (main) Resume **
100
** Activity (main) Pause, UserClosed = false **
** Activity (update_delivery) Create, isFirst = true **
** Activity (update_delivery) Resume **
(SQLiteCursor) android.database.sqlite.SQLiteCursor@21a20918
Error occurred on line: 187 (dbutils)
android.database.sqlite.SQLiteException: near "(": syntax error (code 1): , while compiling: (SQLiteCursor) android.database.sqlite.SQLiteCursor@21a20918
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:601)
at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1317)
at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1256)
at anywheresoftware.b4a.sql.SQL.ExecQuery2(SQL.java:167)
at anywheresoftware.b4a.sql.SQL.ExecQuery(SQL.java:155)
at whse.deliverystatus.dbutils._executememorytable(dbutils.java:513)
at whse.deliverystatus.dbutils._executelistview(dbutils.java:172)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:525)
at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:636)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:302)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:238)
at java.lang.reflect.Method.invokeNative(Native Method)

Sub Pnl5Bttn5start_search_PO_Click
    'Search PO/DO base on PO/DO Number input.
    Dim Input As String
    Input = Pnl5EdtTxt1PoDo_no.Text
   
    'Search PO/DO base on date input.
    Dim Input2 As String
    Input2 = Pnl5EdtTxt2PoDo_date.Text
   
   
    ListView1.Visible = True
    'Already declare in Sub Process Global.
    'Dim DBFileName As String                                        : DBFileName = "delivery.db"
    'Dim DBFilePath As String                                        : DBFilePath = File.DirInternal
    'Dim DBTableName1 As String                                        : DBTableName1 = "DeliveryRegister"
   
    'If Input.Length > 0 Then
   
            'select query with where clause
            'Input is the search parameter_value for PO/DO Number 
        Dim Cursor1 As Cursor
               'Cursor1 = SQL1.ExecQuery("SELECT DONumber FROM " & DBTableName1& "")
            Cursor1 = SQL1.ExecQuery2("SELECT ID, DONumber, DODate, DOReceiveDate, DeliveryAddress, DeliveryDate, OutletName, LineItems, Region, SpecialInstructions, Remarks FROM " & DBTableName1 & " WHERE DONumber= ?", Array As String(Input))
               Log(Cursor1)
             
           For i = 0 To Cursor1.RowCount - 1
              Cursor1.Position = i
           
           
            DBUtils.ExecuteListView(SQL1, Cursor1, Null, 0 , ListView1, True)
            Log(DBUtils.ExecuteListView(SQL1, Cursor1, Null, Null, ListView1, True))
              'username(i)=readqry.GetString("name")'
              'userid(i)=readqry.GetInt("userid")
              'Log("Select query with where clause: name = " & username(i) & " userid = " & userid(i))
           Next

 

[DonManfred]

Error occurred on line: 187 (dbutils)
android.database.sqlite.SQLiteException: near "(": syntax error (code 1): , while compiling: (SQLiteCursor)

this is the error you need to fix
check the values of input and input2

btw: Check if input isnt a reserved word too

 

[Devan]

Hi DonManfred,
Thank you for your help. Im sure im doing something wrong.
But I still cant find the solution. below is the error
DBUtils
Line 187 cur = SQL.ExecQuery(Query)

 

[DonManfred]

ine 187 cur = SQL.ExecQuery(Query)

put a log(Query) in front of that command to get the query logged

 

[Devan]

Yes Thats a Good Idea... let me try & revert back to you. Have a nice day.

 

[Devan]

Hi DonManfred,
I put the Log. This is the below statement received.
what to do next?
(Cursor) Not initialized

 

[klaus]

What do you want to put into ListView1 ?

You use this:
DBUtils.ExecuteListView(SQL1, Cursor1, Null, 0 , ListView1, True)
ExecuteListView expects a Query text and not a Cursor !
DBUtils.ExecuteListView(SQL1, Query, Null, 0 , ListView1, True)

 

[DonManfred]

I put the Log. This is the below statement received.

i dont believe that THIS is the output of your command

log(Query)

!

So. Instead of talking about another problem you should first fix the first problem!
The not initialized Cursor is the result of a wrong Query.

 

[Devan]

Hi Klaus,
Thank you for your support.
After the changes now line 199 (dbUtils) showing error.
Can you pls help me identify the problem. I have included Log details & Code

Log details.
Installing file.
** Activity (main) Pause, UserClosed = false **
PackageAdded: package:whse.deliverystatus
** Activity (main) Create, isFirst = true **
true
true
** Activity (main) Resume **
100
** Activity (main) Pause, UserClosed = false **
** Activity (update_delivery) Create, isFirst = true **
** Activity (update_delivery) Resume **
** Activity (update_delivery) Pause, UserClosed = true **
** Activity (main) Resume **
true
ExecuteHtml: SELECT ID As [ID], DONumber As [DO Number], DODate As [DO Date], DOReceiveDate As [DO Receive Date], DeliveryAddress As [Delivery Address], DeliveryDate As [Delivery Date], OutletName As [Outlet Name], LineItems As [Line Items], Region As [Regions], SpecialInstructions As [Special Instructions], Remarks As [Remarks] FROM DeliveryRegister
** Activity (main) Pause, UserClosed = false **
** Activity (update_delivery) Create, isFirst = false **
** Activity (update_delivery) Resume **
(SQLiteCursor) android.database.sqlite.SQLiteCursor@21c568b8
13
5
(Cursor) Not initialized
ExecuteMemoryTable:
(SQLiteCursor) android.database.sqlite.SQLiteCursor@21c57698
Error occurred on line: 199 (dbutils)
android.database.sqlite.SQLiteException: not an error (code 0)
at android.database.sqlite.SQLiteConnection.nativeExecuteForCursorWindow(Native Method)
at android.database.sqlite.SQLiteConnection.executeForCursorWindow(SQLiteConnection.java:845)
at android.database.sqlite.SQLiteSession.executeForCursorWindow(SQLiteSession.java:868)
at android.database.sqlite.SQLiteQuery.fillWindow(SQLiteQuery.java:62)
at android.database.sqlite.SQLiteCursor.fillWindow(SQLiteCursor.java:144)
at android.database.sqlite.SQLiteCursor.getCount(SQLiteCursor.java:133)
at anywheresoftware.b4a.sql.SQL$CursorWrapper.getRowCount(SQL.java:313)
at whse.deliverystatus.dbutils._executememorytable(dbutils.java:541)
at whse.deliverystatus.dbutils._executelistview(dbutils.java:172)
at whse.deliverystatus.update_delivery._pnl5bttn5start_search_po_click(update_delivery.java:509)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:525)
at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:636)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:302)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:238)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:525)
at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:121)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:175)
at anywheresoftware.b4a.BA.raiseEvent(BA.java:171)
at anywheresoftware.b4a.objects.ViewWrapper$1.onClick(ViewWrapper.java:77)
at android.view.View.performClick(View.java:4392)
at android.view.View$PerformClick.run(View.java:18156)
at android.os.Handler.handleCallback(Handler.java:730)
at android.os.Handler.dispatchMessage(Handler.java:92)
at android.os.Looper.loop(Looper.java:150)
at android.app.ActivityThread.main(ActivityThread.java:5406)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:525)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:737)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:553)
at dalvik.system.NativeStart.main(Native Method)
** Activity (main) Resume **

Sub Pnl5Bttn5start_search_PO_Click
    'Search PO/DO base on PO/DO Number input.
    Dim Input As String
    Input = Pnl5EdtTxt1PoDo_no.Text
   
    'Search PO/DO base on date input.
    Dim Input2 As String
    Input2 = Pnl5EdtTxt2PoDo_date.Text
   
   
    ListView1.Visible = True
    'Already declare in Sub Process Global.
    'Dim DBFileName As String                                        : DBFileName = "delivery.db"
    'Dim DBFilePath As String                                        : DBFilePath = File.DirInternal
    'Dim DBTableName1 As String                                        : DBTableName1 = "DeliveryRegister"
   
    'If Input.Length > 0 Then
   
            'select query with where clause
            'Input is the search parameter_value for PO/DO Number 
            
            'Dim Cursor1 As Cursor   'Already Declare in Sub Globals
            'Cursor1.IsInitialized(True)  
               'Cursor1 = SQL1.ExecQuery("SELECT DONumber FROM " & DBTableName1& "")
            Cursor1 = SQL1.ExecQuery2("SELECT ID, DONumber FROM DeliveryRegister WHERE DONumber = ?", Array As String (Input))
               'Cursor1 = SQL1.ExecQuery2("SELECT ID, DONumber, DODate, DOReceiveDate, DeliveryAddress, DeliveryDate, OutletName, LineItems, Region, SpecialInstructions, Remarks FROM " & DBTableName1 & " WHERE DONumber = ?", Array As String(Pnl5EdtTxt1PoDo_no.Text))
             Cursor1.Position = 0
           
            Log(Cursor1)
       
        Dim Query As String
        Dim i As Int
        Log (Query)
       
           For i = 0 To Cursor1.RowCount - 1
              Cursor1.Position = i
           
            Log(Cursor1.GetString("ID"))
            Log(Cursor1.GetString("DONumber"))
           
            Log(Query)
           
            DBUtils.ExecuteListView(SQL1, Query, Null, 0 , ListView1, True)
            Log(DBUtils.ExecuteListView(SQL1, Query, Null, Null, ListView1, True))
            Log(Query)
            Log(Cursor1)
              'username(i)=readqry.GetString("name")'
              'userid(i)=readqry.GetInt("userid")
              'Log("Select query with where clause: name = " & username(i) & " userid = " & userid(i))
          Next
         Cursor1.Close

 

[klaus]

What do you want to put into ListView1 ?

I asked you a question, why don't you answer it ?

How could you expect us helping you if you don't tell us what you want to do !?
And I'm afraid that you don't really understand what you are doing as you are confusing cursors and queries.
You add a variable Query, but you don't give it any value ?!
In DBUtils.ExecuteListView(SQL1, Query, Null, 0 , ListView1, True), Query must be a SQL query !
Something like "SELECT ID, DONumber, DODate, DOReceive ... "
like in Cursor1 = SQL1.ExecQuery2("SELECT ID, DONumber, ...

Before we could give you concrete advices you must explain in detail what exactly you want to do !

 

[Devan]

Hi Klaus,
I'm very sorry for not answering your question earlier.
Yes, im quite confuse with Queries & cursor, since im just learning programming.
Maybe you can help me with that.

What do you want to put into ListView1 ?

Actually I want, ID, DONumber, DODate, DOReceiveDate, DeliveryAddress, DeliveryDate, OutletName, LineItems, Region, SpecialInstructions, Remarks details to put into the ListView1 from the SQL table DeliveryRegister.
With your Help & Guidance I manage to put the ID, DONumber for testing purpose and now it works. See below code: Am I doing it in correct way?

Cursor1 = SQL1.ExecQuery2("SELECT ID, DONumber FROM DeliveryRegister WHERE DONumber = ?", Array As String (Input))
               'Cursor1 = SQL1.ExecQuery2("SELECT ID, DONumber, DODate, DOReceiveDate, DeliveryAddress, DeliveryDate, OutletName, LineItems, Region, SpecialInstructions, Remarks FROM " & DBTableName1 & " WHERE DONumber = ?", Array As String(Pnl5EdtTxt1PoDo_no.Text))
             Cursor1.Position = 0
         
            'Log(Cursor1)
     
        Dim Query As String
        Dim i As Int
        Log (Query)
     
           For i = 0 To Cursor1.RowCount - 1
              Cursor1.Position = i
         
            Log(Cursor1.GetString("ID"))
            Log(Cursor1.GetString("DONumber"))
         
            Log(Query)
            DBUtils.ExecuteListView(SQL1, "SELECT ID, DONumber FROM DeliveryRegister WHERE DONumber = " & Input& "", Null, 0 , Pnl5ListView1, True)
         
            Log(Cursor1.GetString("ID"))
            Log(Cursor1.GetString("DONumber"))

But now I have 2 problem:
1. The program only can process numbers in the input. But anything with letters front or back of the numbers cannot retrieve details and in the log tab "No Such column". How to do it. Example: Q12223, H111, 111H. If Just numbers than it works fine.
2. Can I put description on the ListView for my results. something like this "ID: ", "DO Number: " and follow up by the results. like this ID: 5, DO Number: 4456789

Thank you for your support.

 

[klaus]

1. I don't understand exactly the problem.
The program only can process numbers in the input.
What input and from where and what for?
2. Yes.

I still don't understand what you want to do! Your explanations are not precise enought.
You fill the ListView in the For / Next loop which means that you fill the ListView for each row but you will see only the data for the last row.
Do you want to show the data from all rows ? Then ListView is not a solution a Table Class would be more efficient.
Do you want to show the data from one given row ?

You should begin reading the Beginner's Guide, look at the documentation of the different views and later read the User's Guide, there you have a complete chapter about SQLite.

 

[Devan]

Hi Klaus,
Good Morning.

1. I don't understand exactly the problem.
The program only can process numbers in the input.
What input and from where and what for?

The input is by user through EditText to Search DONumber in the DeliveryRegister table:
Dim Input As String
Input = Pnl5EdtTxt1PoDo_no.Text (EditText). The EditText input will be DONumber in the DeliveryRegister table. I want to search only 1 particular row in the DeliveryRegister table to show on the ListView (Pnl5ListView1). Later I want to initialize a new panel when the user click on the ListView.

2. Yes.

How to add the description on the ListView.

Do you want to show the data from one given row ?

Yes

Do you want to show the data from all rows ? Then ListView is not a solution a Table Class would be more efficient.

Table Class. Does it have ItemClick (Position As Int, Value As Object).

You fill the ListView in the For / Next loop which means that you fill the ListView for each row but you will see only the data for the last row.

Can you pls explain this For/Next loop function.

I hope the above explanation gives you clear picture. If you need further explanation pls let me know.
Thank you for your help.

 

[klaus]

Are the DONumbers unique in the database ?
If yes, you will have only 1 row. Therefore no need to have a loop to go through the rows.

How to add the description on the ListView.

I suppose that you want to learn the language, so I suggest you to find this by yourself.

Can you pls explain this For/Next loop function.

Do you want an explanation of what a For/Next loop is doing or what happens in the For/Next loop in your code ?

 

[Devan]

Hi Klaus,
Good Day to you.

Are the DONumbers unique in the database ?
If yes, you will have only 1 row. Therefore no need to have a loop to go through the rows.

Yes, the DONumbers are unique. I will follow your advice.

For/Next Loop is my code what happens.

Thank you.

 

[klaus]

For/Next loop.
You execute DBUtils.ExecuteListView(SQL1... in the loop, which means that you initialize the ListView for each row and therefore only the last row is displayed. If there is only one row the result is OK but the loop is of no use.

 

[Devan]

Hi Klaus,
Thank you for the below explanation.
Have Great Week End.

For/Next loop.
You execute DBUtils.ExecuteListView(SQL1... in the loop, which means that you initialize the ListView for each row and therefore only the last row is displayed. If there is only one row the result is OK but the loop is of no use.