I have a Xojo database app that uses SQL. If I want to delete a record (row) in a table, I run the delete code, but the record will still be visible on the screen until I reload the table. It won’t immediately disappear. I have navigation buttons that can moveFirst, movePrevious, moveNext, and moveLast. So what I want to do is have the record delete then be able to click the Next or Previous button and see the record that came before or after the deleted record, and have the deleted record disappear.
I spent the whole afternoon trying to research this and have found nothing. Is there a moveTo command or something that lets you reload the table and then move directly to the record just before the deleted record?
The way I am doing it now is to create a variable called WA that has a Integer that acts as a bookmark. I then have to reload the table and loop through every record until it finds the record that matches my bookmark.
Here is the code I am using:
[code]//This code reloads the table
Dim sqlCalls as string
sqlCalls = “select * from MyTable”
rsC=Roster.SQLSelect(sqlCalls)
// This code will loop through the records until it finds the bookmarked record, then will display it.
while not rsC.EOF
if rsC.field(“Call”).StringValue = WA then Exit
rsC.MoveNext
wend
UpdateDisplay ///Redisplays the record on the screen[/code]
The problem is that if the table has thousands of records this could really slow things down. I was wondering if there was a better way to do this where you could go directly to the bookmarked record and still be able to moveNext and MovePrevious?
Yes, each record has a Call_ID column called “Call” which is an integer. Each row has a different Call_ID which is unique and sequential.
Here is the entire code for deleting the record:
[code]dim result as integer
Dim sql as string
result=MsgBox("Do you really want to delete this Call ID for " + txtCall.Text + " ? ", 36)
If result=6 then
Dim X as Integer
Dim S as Integer
X = Val(txtCall.Text)
S= Val(txtSet.Text)
If S = X Then
S=S-1
Else
S=S + 1
End If
Wa= format(S, "0")
rsC.DeleteRecord
Roster.Commit
sql = "select * from Calls"
rsC=Roster.SQLSelect(sql)
Dim sqlCalls as string
sqlCalls = "select * from Calls"
rsC=Roster.SQLSelect(sqlCalls)
// This code will loop through the records until it finds the bookmarked record, then will display it.
while not rsC.EOF
if rsC.field("Call").StringValue = WA then Exit
rsC.MoveNext
wend
UpdateDisplay //This code redisplays the current record on the screen.
End If[/code]
The Table is being displayed on the screen in a series of TextBoxes. The method UpdateDisplay displays the data in the text boxes. An example would be below
The record is deleted, but since the recordset is already loaded the deleted record still appears, unless I reload the table.
What I like to do is to be able to delete the record and then immediately display the record before the deleted record, and then when the “NEXT” button is pressed go to the next record in the table after the deleted record.
The navigation buttons used to navigate between records are shown below.
Unfortunately, RecordSet doesn’t make a very good data store. It is tantalizingly close and may appear to work (but only for a very limited number of database engines), but it really doesn’t. You have found one of the problems with using a recordset for data storage in your app. There isn’t any other way around it than what you are currently doing - reload the recordset and find your position in it.
The other issue that you should test to make sure it’s working for you is whether the recordset retains any new values when you update a record. It’s not guaranteed. You may have to do the same trick and reload the recordset on update as well as delete.
Your right, you have to do the same thing when you Update as well. The recordset does retain the changes. It is just slow, especially in Windows. Mac is not really bad.
So what is the alternative? How can I display records in textboxes and be able to delete and update an SQLite database? I thought I was doing it the way it is suppose to be done.
In LIVE Code (which I am not wild about) you can MoveToRecord.
The code is below:
[code] //put “SELECT * from Questions” into tSQLQuery
put fld “txtK” into txtK
Put field “txtBookMark2” into field “txtBooK”
put “SELECT * FROM Mark” into tSQLQuery
put revQueryDatabase(sDatabaseID,tSQLQuery) into sRecordSetID2
displayRecord
There are a couple of different ways to do it. One might be to create a Class that holds the data for a record and maintain an array of objects, or rows.
But it might be easiest to adjust your code to just get the records one at a time. That leaves you with a recordset you can use just as you currently do, but it will only have the one record in it.
Next Record:
Dim X as String
X = txtCall.Text
sql = "select * from Calls where Call > " + X + " order by Call limit 1"
...
Previous Record:
Dim X as String
X = txtCall.Text
sql = "select * from Calls where Call < " + X + " order by Call desc limit 1"
...
One benefit to this approach is that it doesn’t break when you have a gap in the Call number sequence.
For some reason that code is not working. When I run it instead of going to the next record, it goes to the fist record. The second block of code goes to a blank record instead of the previous record.
[code] Dim X as String
X = txtCall.Text
Dim sql as String
sql = “select * from Calls where Call > " + X + " order by Call limit 1”
rsC=Roster.SQLSelect(sql)
UpdateDisplay
[/code]
Can you see what may be wrong?
Another approach would be to not delete the records, but mark them as deleted and move on skipping records market as deleted. This would also allow for an undo feature.
You would also of course need a housekeeping routine to delete market records from time to time.
Hi James. I really don’t have a problem deleting records. They delete just fine. Its reloading the database and going to a specific record.
The DataControl has a MoveTo command, but the DataControl has been depreciated with Xojo. I just don’t understand why the command cannot be used in the regular data access in Xojo. As I said earlier, it works in Live Code, so why not Xojo?
The typical application for database is that you can search for a record, make changes and then save it. I would think there would be some sort of way to navigate back to the record easily, without having to create a loop.
Yes but you do not need to reload the RecordSet if you only update it to indicate it is deleted and move to the next non deleted row and display it thus reducing the time in the loop.
Ah, you’re treating Call as an integer, but you’re storing it in a string column. How is it that your original sql gets the data in order? Is there an integer primary key? If so, use that instead of Call.
I changed the Call field from Text to Number in the database and the code to move to the next record works perfectly. That code is below:
[code] Dim X as String
X = txtCall.Text
Dim sql as String
//sql="SELECT * FROM Calls WHERE UPPER(Call) > '" + uppercase(txtCall.Text) + "'order by Call limit 1"
//sql="SELECT * FROM Calls WHERE (Call) > '" + (txtCall.Text) + "'order by Call limit 1"
sql = "select * from Calls where (Call) > " + X + " order by Call limit 1"
rsC=Roster.SQLSelect(sql)
UpdateDisplay[/code]
BUT… The code to move to the previous record still does not work. It actually takes you to the first record in the database instead of the previous record. Here is that code:
[code] Dim X as String
X = txtCall.Text
Dim sql as String
sql = “select * from Calls where (Call) < " + X + " order by Call limit 1”
rsC=Roster.SQLSelect(sql)
UpdateDisplay[/code]
I don’t understand why this would not work when the other works fine?
Because SQL returns the first record that satisfied the criteria… which is record #1.
Perhaps you might think about putting the record keys into an array, or dictionary, look them up there and use the returned value to get the database record.
