Code review needed

Databases
1

Hi,
My code below is used to delete a record from my database, and then update the listbox.

Can someone please tell me why when this code is run - the listbox does not change and I get the MsgBox error BUT when I quit and restart the app - the database entry has indeed been deleted as expected?

When I step through the code in the debugger - I get a NilObjectException.
I can not understand this because if the entry got deleted - how can rs=Nil??

I am having trouble jiggling my code around so that I still have error checking, without getting the error MsgBox?

[code]// DELETE THE SELECTED SNIPPET
dim sql, selected as string
selected=MainWindow.Listbox1.cell(MainWindow.Listbox1.listindex,0)
sql=“delete from Snippets where Title=’”+selected+"’"
rs=db.SQLSelect(sql)
db.Commit

if rs=nil then
msgbox(“Could not delete snippet.”)
self.close
else
// SUCCESS, SO UPDATE THE LISTBOX
MainWindow.Timer2.mode = MainWindow.Timer2.ModeSingle
end if[/code]

2

Instead of SQLSelect use SQLExecute, see if that helps. RecordSet is nil because DELETE FROM doesn’t return anything.

3

Tim,
I changed the code to SQLExecute but I got an error - this Method Does Not Return a Value

4

of course it does not, because it should not.

Simply don’T expect a recordset.
Also check for db.error, not for rs=nil.

5

So should I simply remove the following segment of code?:

if rs=nil then msgbox("Could not delete snippet.") self.close

6

Christian - my code now looks as follows - but still has exactly the same problem - I am now lost:

[code] // DELETE THE SELECTED SNIPPET
dim sql, selected as string
selected=MainWindow.Listbox1.cell(MainWindow.Listbox1.listindex,0)
sql=“delete from Snippets where Title=’”+selected+"’"
rs=db.SQLSelect(sql)
db.Commit

if db.error then
msgbox(“database error!”)
self.close
else
// SUCCESS, SO UPDATE THE LISTBOX
MainWindow.Timer2.mode = MainWindow.Timer2.ModeSingle
end if[/code]

7

I think you should slow down and stop to think about the answers you’re given or your efforts to learn will be lost in your reliance on the forum.

8

Okay, really now?
We both told you DELETE FROM returns nothing.
Why are you still trying to make a RecordSet from nothing?

SLOW DOWN. Think. Read. Ponder. Try.
THEN ask.

9

Richard
Is your database SQLIte? If so is the “Title” the primary key and is it an integer?

That could be the problem.

10

Tim,
I have got the code working, and thank you for your patience.
I apologise for my apparent ignorance, but I am still trying to grasp certain concepts, and at the moment my head is still all over the place as I recently found my mum dead!

I am concentrating on Xojo in order to distract myself.
Anyway - my code below now works - sorry for any annoyance I may have caused.

// DELETE THE SELECTED SNIPPET
dim sql, selected as string
selected=MainWindow.Listbox1.cell(MainWindow.Listbox1.listindex,0)
sql=“delete from Snippets where Title=’”+selected+"’"
db.SQLExecute(sql)
db.Commit
DeleteConfirmationWindow.close

// SUCCESS, SO UPDATE THE LISTBOX
MainWindow.Timer2.mode = MainWindow.Timer2.ModeSingle

11

The thing is I know you are trying to learn, which is very different from some of the others. But if you’re speeding so quickly and you don’t stop to think about the answers, you won’t retain the information for your future endeavors. I’m just concerned that you won’t learn what you wanted to.

12

A cup of tea goes a long way to digest code :wink:

13

Off to make a cup of tea :slight_smile:

Although the above code now works - if I insert the following code:

if db.error then MsgBox("Error") end if

I still see the error MsgBox??
Never mind - I will try and work out why.

Thank you for all your help - I really appreciate it!

14

db.ErrorMessage will give you some more insight for that error.

15

I got the error: Error1 - cannot commit - no transaction is active

I then removed the line of code which commits - and it now seems to work fine.
Thank you for the pointer :slight_smile:

16

Richard, I am very sorry for your loss. That must’ve been traumatic.

17

but please check error flag after each sqlExecute or SQLSelect call.

18

Thanks Tim,
yes it was - I went to visit mum on mothers day and found her dead on the floor, and to make it worse - she had already been dead a few days!

At least I don’t have to worry about her being alone anymore.
Anyway - on a more cheerful note - thank you everyone for the help.

19

So sorry to hear about this, Richard. Our mothers are a great blessing.

20

That’s a great loss Richard.
I wish you strength in the process of this loss.