Android Question [SOLVED] Problem with MDB ACCESS Writing/Updating...

[Magma]

Hi there...

I am using Jackess Library... seems is the best for using mdb files databases...

so..

With northwind.mdb database tried to read it - write it all works super...
but with my database seems something goes wrong... not know what....

the database made with access 2003, tried with 2007 too - the same..

i am getting the following error:

** Activity (main) Create, isFirst = true **
** Activity (main) Resume **

java.lang.IllegalArgumentException: Given rowId is invalid: -1:-1

at com.healthmarketscience.jackcess.Table.requireValidRowId(Table.java:2138)
at com.healthmarketscience.jackcess.Table.updateRow(Table.java:1556)
at com.healthmarketscience.jackcess.Cursor.updateCurrentRow(Cursor.java:664)
at com.healthmarketscience.jackcess.Table.updateCurrentRow(Table.java:1541)
at anywheresoftware.b4a.objects.JackcessDatabase$JackcessTable.UpdateCurrentRow(JackcessDatabase.java:422)
at magma.apografh.main._timer1_tick(main.java:499)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:507)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:175)
at anywheresoftware.b4a.objects.Timer$TickTack.run(Timer.java:105)
at android.os.Handler.handleCallback(Handler.java:587)
at android.os.Handler.dispatchMessage(Handler.java:92)
at android.os.Looper.loop(Looper.java:123)
at android.app.ActivityThread.main(ActivityThread.java:3683)


at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:507)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:839)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:597)
at dalvik.system.NativeStart.main(Native Method)
java.lang.IllegalArgumentException: Given rowId is invalid: -1:-1

My code is above:

  File.Copy(File.DirAssets, "apografh.mdb", File.DirRootExternal, "apografh.mdb") 
   Access.Open(File.DirRootExternal & "/apografh.mdb")
 
   Table.Initialize(Access.GetTable("apothikieidwn"))
 
   Table.Reset

   IndexNames = Table.GetIndexNames
   For i = 0 To IndexNames.Length -1
     Index.Initialize(Table.GetIndex(IndexNames(i)))
   Next 
   Index.Initialize(Table.GetIndex("barcode"))


BARCODE = EditText1.text
    EditText1.text = ""

    Cursor.Initialize(Table.GetTable, Index.GetIndex)

    Before=Found
'seems ok here!
    Found = Cursor.FindFirstRowByEntry(Array As Object(BARCODE))
 
    If Found Then

'finds the record ... !!! is ok
        Label3.text = "found it !"
        'Label5.text = Cursor.GetColumnValue("....a field 2")
        Dim p As Double
        p=EditText2.text
        If Before=Found Then
            p=Cursor.GetColumnValue("APOGRAFH")+1
            EditText2.Text = p
            p = EditText2.text
            Cursor.SetCurrentRowValue("APOGRAFH",p)
            Else
            If Cursor.GetColumnValue("APOGRAFH")=0 Then
            EditText2.Text = "1"
            Else
            EditText2.Text = Cursor.GetColumnValue("APOGRAFH")
            End If
            p = EditText2.text
            Cursor.SetCurrentRowValue("APOGRAFH",p)
        End If
'the problem seems here... but not know what is it ?
        Table.UpdateCurrentRow(Array As Object(BARCODE))
    Else
        EditText2.Text = "0"
        Label3.Text = "not found BARCODE!"
        Label5.Text = "N/A"
    End If

i am uploading the mdb file too... into zip file will see it.. (scroll down to download whole project)

 

[Myr0n]

Hi, please, next time, you should post a Project with all references to variables that you are using or the involved variables that you are using in the code that is having problems, because, it is very hard to guess, what type are, specially if some of us are having sometime free and we want to debug the code in real time.

1.-If you are testing your app in an emulator and using File.DirRootExternal, you should specify the SD Card size more than zero in the settings.
2.-Modify the type of the next variables or just add the next lines after Access.Open(File.DirRootExternal & "/apografh.mdb")

    Dim Table As JackcessTable
    Dim IndexNames() As String
    Dim Index As JackcessIndex
    Dim Cursor As JackcessIndexCursor
    Dim Found As Boolean

 

[Magma]

@Myron Thank you for your answer... the variables was already set at globals... tried into sub but the same...

i am uploading all the project/app - is a Store/Storage-Products-Counter (in Greek language)...

You can try it.... and took the error...

when running set at edittext field the value: 1 or 2 or 3 (barcode)

must return you the description of product and then add +1 (count one more product) at APOGRAFH...(field name is ΑΠΟΓΡΑΦΗ in Greek)

When trying to update the record ! BAM! Error :-(

 

[Magma]

seems something wrong with jackess library...

if you put after the line command "Table.Reset" - these lines of code:

    For i = 0 To Table.GetRowCount - 1
        Table.GetNextRow
    Next

seems that needs to read the whole database first !

seem that go one step more but... getting different error now..

java.io.IOException: New row [1, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null] violates uniqueness constraint for index


Data number: 0
Page number: 20
Is Backing Primary Key: true
Is Unique: true
Ignore Nulls: false
Columns: [ColumnDescriptor Name: (APOTHIKIeidwn) ΚΩΔΙΚΟΣ
Type: 0x4 (LONG)
Number: 0
Length: 4
Variable length: falflags: 1]
Initialized: true
EntryCount: 1786
Cache:
NodeDataPage[20] 0, 0, (47), [Node RowId = 81:2, SubPage = 160, Bytes = 7F 80 00 02 1C
, Node RowId = 190:8, SubPage = 161, Bytes = 7F 80 00 04 7A
, Node RowId = 141:7, SubPage = 191, Bytes = 7F 80 00 06 D4
]
LeafDataPage[160] 0, 161, (0), [RowId = 45:0, Bytes = 7F 80 00 00 01
, RowId = 81:2, Bytes = 7F 80 00 02 1C
]
LeafDataPage[161] 160, 191, (0), [RowId = 81:3, Bytes = 7F 80 00 02 1D


, RowId = 190:8, Bytes = 7F 80 00 04 7A
]
LeafDataPage[191] 161, 47, (0), [RowId = 190:9, Bytes = 7F 80 00 04 7B
, RowId = 141:7, Bytes = 7F 80 00 06 D4
]
LeafDataPage[47] 191, 0, (0), [RowId = 141:8, Bytes = 7F 80 00 06 D5
, RowId = 226:4, Bytes = 7F 80 00 07 EF
]
at com.healthmarketscience.jackcess.IndexData.addEntry(IndexData.java:570)
at com.healthmarketscience.jackcess.IndexData.addRow(IndexData.java:537)
at com.healthmarketscience.jackcess.Table.updateRow(Table.java:1674)
at com.healthmarketscience.jackcess.Cursor.updateCurrentRow(Cursor.java:664)
at com.healthmarketscience.jackcess.Table.updateCurrentRow(Table.java:1541)
at anywheresoftware.b4a.objects.JackcessDatabase$JackcessTable.UpdateCurrentRow(JackcessDatabase.java:422)
at magma.apografh.main._timer1_tick(main.java:487)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:507)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:175)
at anywheresoftware.b4a.objects.Timer$TickTack.run(Timer.java:105)
at android.os.Handler.handleCallback(Handler.java:587)
at android.os.Handler.dispatchMessage(Handler.java:92)
at android.os.Looper.loop(Looper.java:123)
at android.app.ActivityThread.main(ActivityThread.java:3683)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:507)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:839)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:597)
at dalvik.system.NativeStart.main(Native Method)
java.io.IOException: New row [1, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null] violates uniqueness constraint for index
Data number: 0
Page number: 20
Is Backing Primary Key: true
Is Unique: true
Ignore Nulls: false
Columns: [ColumnDescriptor Name: (APOTHIKIeidwn) ΚΩΔΙΚΟΣ
Type: 0x4 (LONG)
Number: 0
Length: 4
Variable length: falflags: 1]
Initialized: true
EntryCount: 1786
Cache:
NodeDataPage[20] 0, 0, (47), [Node RowId = 81:2, SubPage = 160, Bytes = 7F 80 00 02 1C
, Node RowId = 190:8, SubPage = 161, Bytes = 7F 80 00 04 7A
, Node RowId = 141:7, SubPage = 191, Bytes = 7F 80 00 06 D4
]
LeafDataPage[160] 0, 161, (0), [RowId = 45:0, Bytes = 7F 80 00 00 01
, RowId = 81:2, Bytes = 7F 80 00 02 1C
]
LeafDataPage[161] 160, 191, (0), [RowId = 81:3, Bytes = 7F 80 00 02 1D
, RowId = 190:8, Bytes = 7F 80 00 04 7A
]
LeafDataPage[191] 161, 47, (0), [RowId = 190:9, Bytes = 7F 80 00 04 7B
, RowId = 141:7, Bytes = 7F 80 00 06 D4
]
LeafDataPage[47] 191, 0, (0), [RowId = 141:8, Bytes = 7F 80 00 06 D5
, RowId = 226:4, Bytes = 7F 80 00 07 EF
]

now what... ?
seems that need to pass all the values at the fields... but why saying for new record... just updating an old one...

 

[Myr0n]

1.-I checked your project in debug (legacy) step by step and it seems that your timer is in the wrong place, I saw that in your If found else you are finding again the same "Found = Cursor.FindFirstRowByEntry(Array As Object(BARCODE))" meaning you have twice the same code but doing the same thing and in the last findfirstrowbyentry you don't have any If found, what gonna happen if there is no barcode?

2.-After the first search, your timer is in a loop, searching nothing.

I'll check later your code.

 

[Myr0n]

You should move File.Copy(File.DirAssets, "apografh.mdb", File.DirRootExternal, "apografh.mdb") to Activity_Create between the If Firsttime

    If FirstTime Then
        File.Copy(File.DirAssets, "apografh.mdb", File.DirRootExternal, "apografh.mdb")
    End If

 

[Magma]

You should move File.Copy(File.DirAssets, "apografh.mdb", File.DirRootExternal, "apografh.mdb") to Activity_Create between the If Firsttime

    If FirstTime Then
        File.Copy(File.DirAssets, "apografh.mdb", File.DirRootExternal, "apografh.mdb")
    End If

That was only for test purposes...

Timer working good not in loop

My problem is not the search - search is ok....if you run the code.. you ll see it..

did you try to run the code... ?
did you try to enter at barcode value 1... (normally the code must add +1 at ΠΟΣΟΤΗΤΑ (quantity of products) - that is storage counter - but when trying to update the record returns error)

if you add these lines after table.reset (wondering WHY MUST ADD THIS PIECE OF CODE):

For i = 0 To Table.GetRowCount - 1
        Table.GetNextRow
    Next

having other error -seems to goes on but something other is wrong - error got: java.io.IOException: New row [1, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null] violates uniqueness constraint for index

 

[Myr0n]

Just to help you a little more, can you tell me, please, what is the purpose / σκοπός of your app?
Is this to correct a products inventory and correct the product stock with the physical stock?
if I'm right, this app will not work, because after you scan 3 barcodes and scan the first barcode that you scanned at first, the app go to update the stock with 1 and your physical stock is 2.

 

[Myr0n]

Found it, at the line if Cursor.GetColumnValue("ΑΠΟΓΡΑΦΗ")=0
that operation is invalid, because your column "ΑΠΟΓΡΑΦΗ" is NULL and you are comparing with 0, you should use a Try catch or verify the column if is null before to compare with a value.

 

[Magma]

is not the purpose... the title of thread tells everything..

Just to help you a little more, can you tell me, please, what is the purpose / σκοπός of your app?
Is this to correct a products inventory and correct the product stock with the physical stock?
if I'm right, this app will not work, because after you scan 3 barcodes and scan the first barcode that you scanned at first, the app go to update the stock with 1 and your physical stock is 2.

---> NO just leaves the same value

Found it, at the line if Cursor.GetColumnValue("ΑΠΟΓΡΑΦΗ")=0
that operation is invalid, because your column "ΑΠΟΓΡΑΦΗ" is NULL and you are comparing with 0, you should use a Try catch or verify the column if is null before to compare with a value.

---> NO IS NOT NULL is 0.000

@Myron ..my problem is not the correct values (by the way i want to have it in this way - notice: there is a second editbox that waits for value) at this time... thank you for your interesting... i am not here to tell you make-for-me the app - i am here for the piece of code: Can't UPDATE the MDB access file (write to it)

Sorry but the problems... continue.. please try the app - there are no problems - at points you are telling...

 

[Myr0n]

My recommendation, put a try catch to debug your app.

Sorry if I asked you too much, I am working and I was trying to debug your app, step by step and, I was trying to help you and you are right, is not null that row. The main problem is your table, that is in greque, if you want to update a field, you should recreate your table because if you rename just the fields, does not work, I tried before to post this final answer to this tread.

I am attaching an example using the Cursor.SetCurrentRowValue, with your own database, I created a table in the ms access db called Test, just check the log and my example works fine and your table give the same error that in your app.

I am sorry again.

 

[Magma]

My recommendation, put a try catch to debug your app.

Sorry if I asked you too much, I am working and I was trying to debug your app, step by step and, I was trying to help you and you are right, is not null that row (THANK U). The main problem is your table, that is in greque (NO IS NOT that... computers understand GREEKS!), if you want to update a field, you should recreate your table because if you rename just the fields, does not work, I tried before to post this final answer to this tread.

I am attaching an example using the Cursor.SetCurrentRowValue, with your own database, I created a table in the ms access db called Test, just check the log and my example works fine and your table give the same error that in your app.

I am sorry again.

@Myron My Friend Myron ... i am not "scold" you... i am happy helping me... and i hope to help you in future..

@Myron No the PROBLEM is not the greque... Greek - Hellenic Letters..

@Myron the problem is that... Table.UpdateCurrentRow(Array As Object(BARCODE)) - and your example help me to see that: Access.flush

Only thing that i must change (add) was that... Access.flush

THANK YOU SO MUCH FOR YOUR HELP !