Unrecognized command for null object: xxx

[zioabete]

Hi to all,
I'm writing a program for fleet maintenance, using a SQLite database.
With this program you can keep track of car maintenance, and singles components too (such as tyres)
You can connect a single maintenance order with one or more "move-in/move-out component" orders (let's call it "MIMO order")

Now, I'm crushing my head against a problem that I cannot understand:
Prior to delete (mark for deletion) a maintenance order, I want to check if the deletion is possibile.
For example, i moved-in summer tyres with order 918 and "MIMO order" 919, then I moved-out summer tyres with order 950 and MIMO order 951 and moved-in winter tyres with MIMO order 952 connected to 950.
The deletion of order 918 shouldn't be allowed.

To do this check I wrote this code:

' Search for "MIMO orders" connected to the deleting order
mSQL$ = "SELECT IDComponent, Km FROM TD_Orders "
mSQL$ = mSQL$ + "WHERE OrdRef = '"+lsDeletingOrder$+"' " '
mSQL$ = mSQL$ + "AND DeletionFlag = 'N' "
sqliteconnect #DB, db$
#DB execute(mSQL$)
If #DB hasAnswer() Then
' Found it!
 For x = 1 To #DB rowcount()
   #row = #DB #nextrow()
   lsIdComponent$ = #row idcomponent$()
   lsKm$ = #row km$()
' Search for the most recent "MIMO order" for this component
   mSQL$ = "SELECT Km FROM TD_Orders "
   mSQL$ = mSQL$ + "WHERE IDComponent = '"+lsIdComponent$+"' "
   mSQL$ = mSQL$ + "AND DeletionFlag = 'N' "
   mSQL$ = mSQL$ + "ORDER BY Km DESC "
   mSQL$ = mSQL$ + "LIMIT 1 "
   #DB execute(mSQL$)
   If #DB hasAnswer() Then
     #rowM = #DB #nextrow()
     If lsKm$ <> #rowM km$() Then
       message$ = "Deletion of order n° "+lsDeletingOrder$+" is not allowed!"
     End If
   End If
 Next x
End If
#DB disconnect()



When I test the deletion of the order 918 I get this runtime error:

Runtime Error in program 'CAR_Maint': lsIdComponent$ = #row idcomponent$()
Unrecognized command for null object: idcomponent$()

This piece of code has been copied/pasted from another section of the same .BAS file, where I read data from an order for editing it and allow/deny changing on MIMO orders connected. In that section is still running!!!!

I try changing #handle names, Capital Letters for fields names ... nothing to do!

Now I solve the problem with this workaround:


mSQL$ = "SELECT IDComponent, Km, DeletionFlag FROM TD_Orders "

...

lsRow$ = #DB nextrow$("|")
x1 = InStr(lsRow$,"|")
lsIdComponent$ = Left$(lsRow$,x1-1)
x2 = InStr(lsRow$,"|", x1+1)
lsKm$ = Mid$(lsRow$,x1+1,x2-x1-1)


But I would like to understand what's wrong with that piece of code !

Thanks in advance

Andrea
(happy RunBasic user from Italy)

[meerkat]

Not sure exactly what your tables look like.
It could be that you have a null column. Try to set NullColumn$ = ""

Why not do your test with one SQL. Something like this:
mSQL$ = "SELECT IDComponent, Km
FROM TD_Orders
JOIN TD_Orders as t1
ON t1.IDComponent = TD_Orders.IDComponent
AND t1.DeleteFlag = 'N'
AND t1.Km = TD_Orders.Km
WHERE OrdRef = '";lsDeletingOrder$;"'
AND DeletionFlag = 'N' "
#DB1 execute(mSQL1$)
if #DB1 hasAnswer() then message$ = "Deletion of order n° "+lsDeletingOrder$+" is not allowed!"


Usually you like to preserve your cursors through the DB. But in your case it may not matter. Maybe something like this will help:
NullColumn$ = ""
db$ = "c:/????? " set to wherever your database is located
sqliteconnect    #DB,   db$  ' Connect to the DB
sqliteconnect    #DB1,  db$  ' Connect to the DB

' Search for "MIMO orders" connected to the deleting order
sql$ = "
SELECT IDComponent, Km
FROM TD_Orders
WHERE OrdRef = '";lsDeletingOrder$;"'
AND DeletionFlag = 'N' "
#DB     execute(sql$)
WHILE #DB hasAnswer()
   #row = #DB #nextrow()
   lsIdComponent$ = #row idcomponent$()
   lsKm$ = #row km$()

' Search for the most recent "MIMO order" for this component
   sql1$ = "
   SELECT Km
   FROM TD_Orders
   WHERE IDComponent = '"+lsIdComponent$+"'
   AND DeletionFlag = 'N'
   ORDER BY Km DESC LIMIT 1"
   #DB1 execute(sql1$)
   WHILE #DB1 hasAnswer()  
     #rowM = #DB1 #nextrow()
     If lsKm$ <> #rowM km$() Then message$ = "Deletion of order n° "+lsDeletingOrder$+" is not allowed!"
   WEND
WEND
#DB  disconnect()
#DB1 disconnect()

Hope this helps

[zioabete]

Thank you very much Dan, you give me other two interesting point of view
I'll try then as soon as possibile.

I still don't understand why the same piece of code, run in a section and dump in another of the same file ... but I found a workaround solution and I'll try your suggestion

Thanks again

Have a nice day.

Andrea

[meerkat]

Runtime Error in program 'CAR_Maint': lsIdComponent$ = #row idcomponent$()
Unrecognized command for null object: idcomponent$()

The reason for the error is because the "SELECT IDComponent" and the lsIdComponent$ = #row idcomponent$() does not match. Should be lsIdComponent$ = #row IDComponent$() because RB needs to match case..

Hope this helps..
Dan