SQLite Command Question (DELETE ROW)...

[Intrigued]

Objective:

Delete one or all rows of data from a SQLite database upon the 'On Click' event 'firing' from a Button object in AMS 5 (Pro for my example)


Steps I took to try and solve such:

I...

1. read the SQLite plugin help file (from I.R.)
2. searched the AMS 5 forum (here) for a workable solution
3. searched the documentation and F.A.Q. from the web site of the creator of the SQLite application

(Note: I did try the solution user Worm put forth and this did not solve the problem. SEE: Worms (delete row of data from SQLite data)

Conclusion:

I did not find a workable solution and am now soliciting for such here in the forum.

The project is just about at a close and an expedited workable solution offered up here would be greatly appreciated.

Thank you in advance to those that offer assistance!

Sincerely,

[Worm]

This would delete the record that's ID is 100
SQLite.Query(db, "Delete from YourTable where ID = 100")

This would delete the record that's ID is 101 - 199
SQLite.Query(db, "Delete from YourTable where (ID > 100) AND (ID < 200)")

You basically need to pick a field or fields to match on, then perform the DELETE SQL.

Objective:

Delete one or all rows of data from a SQLite database upon the 'On Click' event 'firing' from a Button object in AMS 5 (Pro for my example)


Steps I took to try and solve such:

I...

1. read the SQLite plugin help file (from I.R.)
2. searched the AMS 5 forum (here) for a workable solution
3. searched the documentation and F.A.Q. from the web site of the creator of the SQLite application

(Note: I did try the solution user Worm put forth and this did not solve the problem. SEE: Worms (delete row of data from SQLite data)

Conclusion:

I did not find a workable solution and am now soliciting for such here in the forum.

The project is just about at a close and an expedited workable solution offered up here would be greatly appreciated.

Thank you in advance to those that offer assistance!

Sincerely,

[Intrigued]

So in essence there is no way to use a wildcard character (eg. '*') to delete all rows of date?

I will try again to use the first one you posted. There is some spacing differences from the last time you posted such (in that thread I shared that you posted in Worm) though as in AMS it seems spacing half the time is not an issue (contactenation example: "Hi" .. " There!" is the same as "Hi".." There!"?)

[Intrigued]

Worm, by the way thank you for helping nontheless.

Here is a an error code that my application (reset database button) encounters upon firing the 'On Click' event.

Error Detail: [unfinished string near `"delete * from
'] in [SQLite.Query(db, "delete * from ]

Now here is the actually code that is drawing that error:

SQLite.Query(db, "Delete from BLOG where BlogID = 1"); -- testing out

Note: I have SQLite.Query 'insert' code in the On Start event of the project. Along with several actions that access the database. Worm, this is the only database structure changing code I have (as the SQLite creators site says that only one change can be done at a time, where several accesses to the database (say to query the data) is ok.)

Please advise.

Thank you again Worm for all your help and insight.

Sincerely,

[Worm]

Sure you could delete all of them in much the same way
SQLite.Query(db, "Delete from YourTable where ID > -1")

Your IDs should be greater than 0. I'm sure there is a more elegant way, but I really haven't dealt too much with the Delete Queries in SQLite. In Access or SQL server, you would use an wildcard (Delete * from YourTable)

So in essence there is no way to use a wildcard character (eg. '*') to delete all rows of date?

I will try again to use the first one you posted. There is some spacing differences from the last time you posted such (in that thread I shared that you posted in Worm) though as in AMS it seems spacing half the time is not an issue (contactenation example: "Hi" .. " There!" is the same as "Hi".." There!"?)

[Intrigued]

Ok, thanks again Worm.

Here is a screen shot proving that I have at least one record in the database for this test.

SEE: Attached

[Intrigued]

Victory!

Worm, I decided to commented out ('--') all but the line of code in question and used the (-1) for all rows in the table and it works now!

Thank you for your patience and help. If I ever get back to the ex-furniture capital of the word... I will treat you to a 1913 room (still around?) style dinner! (grin)

Very sincerely,

Intrigued

[Worm]

Cool Biz!

I do believe the 1913 Room is still there. I've never been, it's a little out of my price range for grub

Please do look me up if you're ever here. It'd be cool to be able to put a face to a name.

Victory!

Worm, I decided to commented out ('--') all but the line of code in question and used the (-1) for all rows in the table and it works now!

Thank you for your patience and help. If I ever get back to the ex-furniture capital of the word... I will treat you to a 1913 room (still around?) style dinner! (grin)

Very sincerely,

Intrigued

[Intrigued]

Ya, I miss those excellent July 4th fireworks too.

Please do look me up if you're ever here. It'd be cool to be able to put a face to a name.

You got it.

Thanks again...

[jfxwave]

I was doing a search on deleting all row that contain certain words and ran across this one.

What i'm trying to do is delete all rows under a column that contains "Pepsi"

i have a db named Unit and inside i have 3 columns, ID, UnitMake, Model.
if i have it set like:

1 Pepsi, Pepsi
2 Coke, Coke
3 Coke, Sprite
4 Pepsi, Diet Pepsi

how would i go about deleting all the rows under "UnitMake" that contain Pepsi with one click.

I have a ComboBox that lists all the UnitMake only once so i don't think deleting the ID would work.

Thanks for any help

Jean

[jfxwave]

Hi, well i'm on another project and come across the same thing that i asked before but i'm a little smarter and still can't do this. I updated the post above below.

I was doing a search on deleting all row that contain certain words and ran across this one.

What i'm trying to do is delete all rows under a column that contains "Pepsi"

i have a db named "whatever" and table "Unit" and inside i have 3 columns, ID, UnitMake, Model.

if i have it set like:

1 Pepsi, Pepsi
2 Coke, Coke
3 Coke, Sprite
4 Pepsi, Diet Pepsi

how would i go about deleting all the rows under "UnitMake" that contain Pepsi with one click. (delete 1 and 4)

I have a ComboBox that lists all the UnitMake only once so i don't think deleting the ID would work.

Thanks for any help

Jean

thanks

[Worm]

--% is your wildcard
sSQL = "Delete * from Unit WHERE UnitMake = \"%Pepsi%\""
SQLite.Query(db, sSQL)

[jfxwave]

Now that was fast


Thanks alot i will give it a shot.


Thanks again

Jean

[jfxwave]

I can't get sql to find and delete any words, now i can delete anything that has a number.

this will not work
pid = "pepsi";
sQuery = "Delete from DropBox WHERE (make = " .. pid .. ")";

this will
pid = "19";
sQuery = "Delete from DropBox WHERE (make = " .. pid .. ")";

And just to see i did what Worm said and it did not delete
sQuery = "Delete * from DropBox WHERE make = \"%pepsi%\""

if i'm trying to find a word in a column it will not work but if i'm looking for a number it works everytime. and i'm sure the word is under the colunm when i tell sql to find it and delete.

i'm lost please help

thanks

[Worm]

a word needs to be enclosed in quotes

pid = "pepsi";
sQuery = "Delete from DropBox WHERE (make = \"" .. pid .. "\")";