Real Software Forums

The forum for Real Studio and other Real Software products.
[ REAL Software Website | Board Index ]
It is currently Fri Nov 16, 2018 4:15 am
xojo

All times are UTC - 5 hours




Post new topic Reply to topic  [ 5 posts ] 
Author Message
 Post subject: listbox and realsql.
PostPosted: Sat Jan 12, 2013 5:45 am 
Offline

Joined: Tue Jan 08, 2013 8:27 pm
Posts: 38
can someone please help me?

i have currently got a listbox and a delete button and my listbox shows the realsql results, the delete button works but only deletes the top entry on the listbox how can i get it to delete the current highlighted box, thanks

dim db as database = komplete
Dim rs as RecordSet = komplete.SQLSelect("select * from Tenants")


rs.DeleteRecord
db.Commit

ListBox1.ColumnCount=4

ListBox1.HasHeading=True
ListBox1.Heading(0)="Address"
ListBox1.Heading(1)="Title"
ListBox1.Heading(2)="First Name"
ListBox1.Heading(3)="Surname"

listbox1.DeleteAllRows

dim sql as string
sql = "SELECT * FROM Tenants"

dim data as RecordSet
data = komplete.SQLSelect(sql)


if data <> Nil then
while not data.EOF
listbox1.AddRow(data.field("currentaddress").StringValue, data.field("title").StringValue, _
data.field("firstname").StringValue, data.field("surname").StringValue)

data.MoveNext
wend
data.Close
end if


this is my current delete button action.


Top
 Profile  
Reply with quote  
 Post subject: Re: listbox and realsql.
PostPosted: Sat Jan 12, 2013 8:42 am 
Offline
User avatar

Joined: Fri Jun 05, 2009 11:50 am
Posts: 348
Location: Hamburg, Germany
What is your goal?

Do you want to
- delete all selected lines from the list box, but not from the DB?
- delete all selected listbox lines from the database and repopulate the listbox?


if A)
iterate through all listbox rows (from last to first), check selected property and if selected use listbox.removerow(line no.)

if B)
a good way to start is to have the unique primary key of a record stored in the celltag.
iterate through listbox, check selected property of each row. if selected, add primary key to a string as
Quote:
'first primary key', 'second primary key', 'third primary key'


once all primary keys of the DB records that should be deleted are collected, execute following sql

"delete from table where <primary key> in ("+string holding the keys+")"

db.commit

repopulate listbox

_________________
Best regards

Stefan

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


Last edited by elChupete on Tue Jan 15, 2013 3:02 pm, edited 1 time in total.

Top
 Profile  
Reply with quote  
 Post subject: Re: listbox and realsql.
PostPosted: Sat Jan 12, 2013 11:14 am 
Offline

Joined: Tue Jan 08, 2013 8:27 pm
Posts: 38
sorry i forgot to mention i was a beginner that has just confused me, lol

sorry


Top
 Profile  
Reply with quote  
 Post subject: Re: listbox and realsql.
PostPosted: Tue Jan 15, 2013 2:45 pm 
Offline

Joined: Mon Aug 14, 2006 9:33 pm
Posts: 1774
Please do as Stefan suggested and clearly define your goal. That will help both you and those who will try to help.

_________________
Roger Clary
Class One Software
Educational Software for Lifelong Learning
http://www.classonesoftware.com


Top
 Profile  
Reply with quote  
 Post subject: Re: listbox and realsql.
PostPosted: Tue Jan 15, 2013 3:30 pm 
Offline
User avatar

Joined: Fri Jun 05, 2009 11:50 am
Posts: 348
Location: Hamburg, Germany
Do give you an Idea what you are currently doing in your Delete Buttons Action Event.

dim db as database = komplete
Dim rs as RecordSet = komplete.SQLSelect("select * from Tenants")

This selects ALL records stored in Table Tenants (not only those selected in the Listbox) and return them in rs (as recordset)


rs is somewhat of an array.
e.g. if your table has currently 5 records, rs holds ALL five records.
The cursor is set to the first record.

All methods available for recordset (such as rs. DeleteRecord, rs.edit etc.) will be executed against the current record referenced by the cursorposition.

rs.movenext for example will move cursor to second record of your select result.

rs.DeleteRecord
db.Commit

Deletes the record set of the current cursor position. In your case the first one


ListBox1.ColumnCount=4

ListBox1.HasHeading=True
ListBox1.Heading(0)="Address"
ListBox1.Heading(1)="Title"
ListBox1.Heading(2)="First Name"
ListBox1.Heading(3)="Surname"

Redefines Listbox. (not needed IMHO) as this should be done once before the Database content is shown for the first time)


listbox1.DeleteAllRows

deletes all rows in the listbox (Should not be part of the Delete Action event, but put into a separate Populate Listbox method)


dim sql as string
sql = "SELECT * FROM Tenants"

dim data as RecordSet
data = komplete.SQLSelect(sql)


if data <> Nil then
while not data.EOF
listbox1.AddRow(data.field("currentaddress").StringValue, data.field("title").StringValue, _
data.field("firstname").StringValue, data.field("surname").StringValue)

data.MoveNext
wend
data.Close
end if

Repopulate the Listbox with the current content of your Database table. Put that in a seperate method called populatelistbox and just call it after record is deleted.



Explaining what I have written above under b). (assuming you want B)

Despite the columns needed in your table you should always have a so called Unique Primary Key for each record added to the database. This makes it a lot more easier to refer to a specific database record later.
RealSQL (sqllite) does that automatically for you, but this ID might change. So to make sure your have a unique identifier you should add a new column IDNO and make it the unique primary key and put it to autoincrement.


When populating the Listbox, read that IDNO as well and either put it in a separate column of the listbox (visible to the User) or e.g. to the celltag of the first listbox cell.
Doing this let allow you to read that IDNO later and use it for deleting the record.

e.g. Five record with IDNO 001, 002, 003, 004, 005

If the User selects 1st 3rd and 4th record for deletion you simply
- use a for next loop to iterate through all listboxrows
- in the loop check selected property of the row and if selected add the IDNO to a string
- after looping through the listbox your string should have the following content
'001', '003', '004'
- connect to the database
- assuming the string is called IDNOFORDEL
- call db.sqlexecute ("delete from Tenants where IDNO in ("+IDNOFORDEL+")")
- call db.commit
- call the populatelistbox method

PLEASE NOTE
The above explains just the way. A correct implementation will check for db.error after each db.call and will handle errors such as db.rollback and the like.

A good way to start learning what is the correct way to handle DB is this thread

_________________
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  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 5 posts ] 

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