Deleting an entry in database

Databases
1

Hello,

I have made a realsql database and filled out the data, but when i use the following to delete an entry;

[code] sql=“DELETE FROM lines WHERE ID = ‘111’”

msgbox(sql)

db1.sqlexecute(sql)



if db1.error then
  msgbox("DB Error: "+db1.errormessage)
else
  db1.commit
  msgbox("Deleted")
end[/code]

I get the 2 message boxes up but when i do a new search query to return everything and reload my listbox the row with column ID value of 111 is still there. I have also checked this with DB Browser for SQLite

any ideas?

edit;

i have tried a few other combinations such as removing the ’ from around 111, adding ’ around the ID and around the lines

im not sure if its an error with the command or what,

Also i am running RB 2011 R4.2

2

is ID an integer?
Than you may not need to put it in quotes.

3 
sql="CREATE TABLE lines (ID INTEGER, Left_X DOUBLE, Left_Y DOUBLE, Right_X DOUBLE, Right_Y DOUBLE, Tab INTEGER, Action VARCHAR, Start_Colour VARCHAR, Successor INTEGER, Predessor INTEGER, Enabled INTEGER, Turn_Red VARCHAR, Turn_Green VARCHAR);"

that is the code used to make the table. I have tried with an without quotes. I have tried the following combinations;

sql="DELETE FROM lines WHERE ID = '111'" sql="DELETE FROM lines WHERE ID = 111" sql="DELETE FROM lines WHERE 'ID' = 111" sql="DELETE FROM 'lines' WHERE ID = 111" sql="DELETE FROM 'lines' WHERE 'ID' = 111" sql="DELETE FROM 'lines' WHERE 'ID' = '111'"

nothing has deleted the entry nor given any errors when i check db1.error

Edit;

I have also tried adding a ; at the end of the line, no difference

4

Assuming that it’s some idiosyncrasy from RealSQL compared with SQLite (you should try SQLite, really), try this:

Remove the line: db1.commit

Check if this make some magic to happen: sql=“BEGIN; DELETE FROM lines WHERE ID = 111; COMMIT;”

5

I will give that a try either tonight or in the morning depending on when i can next get on my pc
I will let you know the results

Thank you for the help :slight_smile:

6

Also, make your primary key explicit.

sql=“CREATE TABLE lines (ID INTEGER, Left_X DOUBLE, Left_Y DOUBLE, Right_X DOUBLE, Right_Y DOUBLE, Tab INTEGER, Action VARCHAR, Start_Colour VARCHAR, Successor INTEGER, Predessor INTEGER, Enabled INTEGER, Turn_Red VARCHAR, Turn_Green VARCHAR, PRIMARY KEY (ID ASC));”

7

thank you for all the advice

I tried the above but was still getting the same results

I have since found out that some code above the actual deletion was the source of the problem ( the code before was adjusting a few values in the database) not really sure why it was causing an issue, but I have been able to get round the adjustments and got rid of the troublesome code :slight_smile:

Thanks again for all the help :smiley: