Real Software Forums

The forum for Real Studio and other Real Software products.
[ REAL Software Website | Board Index ]
It is currently Wed Nov 14, 2018 10:41 am
xojo

All times are UTC - 5 hours




Post new topic Reply to topic  [ 17 posts ]  Go to page 1, 2  Next
Author Message
 Post subject: Database record delete button
PostPosted: Thu Jan 24, 2013 8:32 pm 
Offline

Joined: Thu Jan 24, 2013 8:08 pm
Posts: 9
Hi, first post. I checked many posts and tried examples but having no luck. I use RB to create small projects for myself. The only database I created years ago was to hold my passwords. It has worked great for many years. Acting a little weird on Mt. Lion so I want to update. Yes, I used the evil database query and data control, please forgive me. But now I want to "man up" and code right! Now I can create a database with a table, add data to it, and display it in a listbox. Whoopdy do! But this is what I want. I want my delete button to do exactly the same thing it used to do back when I used the bindings. You know, select a row in the listbox click the delete button and bingo, done. Seems so simple, but I can't do it! So much for "manning up" in code. What did that magical binding together with those evil controls do so easily? As you can see I'm not a database-er. Please help if you get a moment. Thanks, Richard


Top
 Profile  
Reply with quote  
 Post subject: Re: Database record delete button
PostPosted: Thu Jan 24, 2013 10:24 pm 
Offline
User avatar

Joined: Sun Aug 05, 2007 10:46 am
Posts: 4931
Location: San Diego, CA
What makes a row in your database unique? A RowID? or???

SQLite for example has a built in CID (column id) value

in any event... execute this SQL statement

DELETE FROM mytable WHERE unique_field="unique_value"

DELETE FROM mytable where CID=42


myTable is name of your table
unique_field is a Field in your Table
and "unique_value" is a value IN that field that identifies the row (or rows) to be deleted

Once deleted.. you will also have to delete the Listbox Row (listbox1.remove x)

_________________
Dave Sisemore
iMac I7[2012], OSX Mountain Lion 10.8.3 RB2012r2.1
Note : I am not interested in any solutions that involve custom Plug-ins of any kind


Top
 Profile  
Reply with quote  
 Post subject: Re: Database record delete button
PostPosted: Thu Jan 24, 2013 11:07 pm 
Offline
User avatar

Joined: Sun Jan 25, 2009 5:11 pm
Posts: 576
Location: Guatemala, Central America
Does your listbox has a column where you "show" the unique identifier for that record? if not store it using rowid and when the user selects the row in the listbox read that value, if the value is being displayed then get the value from the cel. Once you got the unique identifier value use the code that Dave posted or a variation of it.

_________________
Future RS guru.
Ride the world!


Top
 Profile  
Reply with quote  
 Post subject: Re: Database record delete button
PostPosted: Fri Jan 25, 2013 3:38 pm 
Offline

Joined: Thu Jan 24, 2013 8:08 pm
Posts: 9
Thanks for your time Dave and Hmarroquinc

I never added a separate id column to the original project (because the binding did that). But the "name" field contains a unique product/website name which appears in the first column of the listbox. Can this be used?

Isn't the RB database based on SQLite? So would there be a built in CID value already there? How would I access the CID value of the selected listbox row in that case?

Not sure about RowID, is that belonging to listbox or to database?

Finally, here is the code I attempted before asking for help. Seems I might have been on the right track. I'll try some of your tips. Just not sure how to get and use those values and put it together.

i=val(Listbox1.Cell(Listbox1.ListIndex,0))

db.SQLExecute "DELETE FROM PassStore WHERE name" + (str(i))


Thanks again.


Top
 Profile  
Reply with quote  
 Post subject: Re: Database record delete button
PostPosted: Fri Jan 25, 2013 3:56 pm 
Offline

Joined: Fri Jan 06, 2006 3:21 pm
Posts: 12388
Location: Portland, OR USA
You said the first column was a name, so why use Val() and Str()? Seems like it would mangle the name, unless all your names are really numbers. Try

dim s as string
s = ListBox1.Cell(ListBox1.ListIndex, 0)
db.SQLExecute "DELETE FROM PassStore WHERE name = '" + s + "'"

You need an equals sign and single quotes around the string value.

RowID belongs to the database and is provided by sqlite. You can request it as part of your select statement. Note that select * does not return the RowID field, you must ask for it explicitly.


Top
 Profile  
Reply with quote  
 Post subject: Re: Database record delete button
PostPosted: Fri Jan 25, 2013 3:57 pm 
Offline
User avatar

Joined: Sun Jan 25, 2009 5:11 pm
Posts: 576
Location: Guatemala, Central America
SQLite databases have a ROWID which is created for every row, you can read that and add it to the listbox .rowtag

Just a little something to get you started, say you populate the listbox like this:

dim i as integer

for i = 1 to 100
listbox1.AddRow("Row " + str(i))
listbox1.RowTag(listbox1.LastIndex ) = i // i should be the ROWID from your table
next i


Then in the listbox Cellclick or the event that best suit you read the ROWID like this

dim i as integer = listbox1.RowTag(row)
msgbox "Selected ID : " + str(i)

_________________
Future RS guru.
Ride the world!


Top
 Profile  
Reply with quote  
 Post subject: Re: Database record delete button
PostPosted: Fri Jan 25, 2013 4:02 pm 
Offline
User avatar

Joined: Sun Jan 25, 2009 5:11 pm
Posts: 576
Location: Guatemala, Central America
Personally I would rather use the ROWID than risk a not unique name in your table. RowID is guaranteed to be unique. But then again TimHare is the grand master and I'm just a little grasshopper.

_________________
Future RS guru.
Ride the world!


Top
 Profile  
Reply with quote  
 Post subject: Re: Database record delete button
PostPosted: Fri Jan 25, 2013 6:19 pm 
Offline

Joined: Thu Jan 24, 2013 8:08 pm
Posts: 9
You're all great, thanks again for taking time to help. Delete button up and running. I see your point about using rowid or something similar rather than the name field. I tested it and sure enough, 2 items named bosco gone in a flash. For my purposes though it's ok.
I'm learning a lot about databases from this little db project of mine. Not easy stuff!

Which brings up one more question if I may. I create my db using this code:
Dim db as REALSQLdatabase
Dim f as FolderItem
Dim result as Boolean
f=New FolderItem("Passwords")
db=New REALSQLdatabase
db.databaseFile=f
result=db.CreateDatabaseFile
If db.Connect() then
db.SQLExecute("create table PassStore(name varchar, userName varchar, password varchar, email varchar, license varchar, notes varchar)")
db.Commit
else
MsgBox "Database not created"
end if


Probably not great but it works. Ideally, where should I put it?
Now, every time i want to access the db how do i make sure I access that file and I'm connected? Certainly not by using this code again?
Great resource this forum. Thanks again!
Richard


Top
 Profile  
Reply with quote  
 Post subject: Re: Database record delete button
PostPosted: Fri Jan 25, 2013 6:55 pm 
Offline

Joined: Fri Jan 06, 2006 3:21 pm
Posts: 12388
Location: Portland, OR USA
I personally would use the RowID, or better, create an autoincrement primary key for the table and use that value. But I didn't want to muddy the waters until he got past the sql syntax at least.

Don't call CreateDatabaseFile unless f.Exists is false. Then it's either/or
db.databaseFile = f
if f.Exists then
if not db.Connect then
MsgBox "Cannot connect database"
end if
else
if db.CreateDatabaseFile then
db.SQLExecute("create table PassStore(name varchar, userName varchar, password varchar, email varchar, license varchar, notes varchar)")
db.Commit
else
MsgBox "Database not created"
end if
end if
// use the database


If you make db a global variable, then you can call this code once at app open and use db everywhere in your code without connecting again.


Top
 Profile  
Reply with quote  
 Post subject: Re: Database record delete button
PostPosted: Sat Jan 26, 2013 2:07 pm 
Offline
User avatar

Joined: Fri Jun 05, 2009 11:50 am
Posts: 348
Location: Hamburg, Germany
Please be aware, that SQLites RowID might change from time to time based on certain DB actions

So a DB record previously using ID 1 might become ID 2 and the like.

So using an own autoincrement primary key, seems to be the saver way

_________________
Best regards

Stefan

Mac OSX Mountain Lion (latest Release) on a MacBook Pro 2.66 GHz Intel Core i7/ RB Professional 2011 Release 3


Top
 Profile  
Reply with quote  
 Post subject: Re: Database record delete button
PostPosted: Sat Jan 26, 2013 10:04 pm 
Offline

Joined: Thu Jan 24, 2013 8:08 pm
Posts: 9
Well, my little project is done thanks to all of you. Create db, add table, then add, edit, delete data without bindings and evil data controls. All code baby! It's not perfect but it does what it used to do and more - but better. Still need to work on the db global variable and the autoincrement primary key mentioned. Thanks again to all.
Richard


Top
 Profile  
Reply with quote  
 Post subject: Re: Database record delete button
PostPosted: Sat Jan 26, 2013 10:23 pm 
Offline
User avatar

Joined: Sun Jan 25, 2009 5:11 pm
Posts: 576
Location: Guatemala, Central America
Good deal

_________________
Future RS guru.
Ride the world!


Top
 Profile  
Reply with quote  
 Post subject: Re: Database record delete button
PostPosted: Sun Jan 27, 2013 8:59 am 
Offline

Joined: Thu Jan 24, 2013 8:08 pm
Posts: 9
OOPS, spoke too soon. Works fine before compiling the project. But in the built app the delete and edit buttons only work on the first record in the listbox not any others. Why? Maybe I need to try and loop through the listbox to see which row is selected? Or is it something else? If you think of something let me know. Richard

Here's that part of the code:
dim s as string
s = ListBox1.Cell(ListBox1.ListIndex, 0)
db.SQLExecute "DELETE FROM PassStore WHERE name = '" + s + "'"
db.Commit


UPDATE
Actually it works on any row except where the name contains an '. Such as Angie's List. Guess using rowid or autoincrement would cure that? Or can i alter this? Thanks.

UPDATE AGAIN
So how to begin with autoincrement. When I create my db and add table would this be correct?
If db.Connect() then
db.SQLExecute("CREATE TABLE PassStore(ID Integer PRIMARY KEY AUTOINCREMENT, name varchar, userName varchar, password varchar, email varchar, license varchar, notes varchar)")
db.Commit
else


Do I need to add an ID column to my listbox?

And in my sql statement where I am selecting the data from the selected listbox row what exactly will I be looking for - i guess the value in that first column now ID?

Missing steps I'm sure. Thanks.
Richard


Last edited by Roll on Sun Jan 27, 2013 10:26 am, edited 1 time in total.

Top
 Profile  
Reply with quote  
 Post subject: Re: Database record delete button
PostPosted: Sun Jan 27, 2013 10:23 am 
Offline
User avatar

Joined: Fri Jun 05, 2009 11:50 am
Posts: 348
Location: Hamburg, Germany
When using strings, you need to escape single quotes.

In your case the single quote in " Angie's " closes the sql statement.

I use a function called sqllit to to it

Function sqllit(ArgData as Variant) As Variant
ArgData = ReplaceAll(ArgData, "'", "''")
Return ConvertEncoding(ArgData, Encodings.UTF8)
End Function


Your code would then become

dim s as string
s = ListBox1.Cell(ListBox1.ListIndex, 0)
db.SQLExecute "DELETE FROM PassStore WHERE name = '" + sqllit(s) + "'"
db.Commit


The sql would look like

DELETE FROM PassStore WHERE name = 'Angie''s List'

Note: these are all single quotes

And Yes, using the unique autoincrement primary key is the better solution.

What happens when there are two records with the same Name?

Both wil be deleted....

_________________
Best regards

Stefan

Mac OSX Mountain Lion (latest Release) on a MacBook Pro 2.66 GHz Intel Core i7/ RB Professional 2011 Release 3


Top
 Profile  
Reply with quote  
 Post subject: Re: Database record delete button
PostPosted: Sun Jan 27, 2013 10:38 am 
Offline

Joined: Thu Jan 24, 2013 8:08 pm
Posts: 9
Well, that was easy - for you! Thanks, works like magic. Much appreciated.
Richard


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 17 posts ]  Go to page 1, 2  Next

All times are UTC - 5 hours


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  
cron
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group