Forum > Database
Deleting rows in SQLite tables corrupts rowid
heebiejeebies:
Hi all,
I have TListBox that loads a string field of an SQLite database, then when you double click on each item, it goes to a separate page where other database fields corresponding to that item are loaded and displayed in other fields. Note it is a regular TListBox as I simply can't get any database-aware components to work - see my previous thread on that, which is the only topic I haven't been able to solve.
Anyway, it mostly works fine except when I delete rows - then everything gets out of whack. If I have a table like this:
Item one
Item two
Item three
Item four
And then I delete item two, double clicking on item three's entry in the TListbox will now load a blank set of data. Double clicking on item four's entry in the TListbox loads item three's data. When I open the database externally in an SQL browser, it all looks absolutely fine. There's no blank row, and the browser renders the rowids sequentially. Here is the delete code:
--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---procedure TForm1.DeleteIssueButtonClick(Sender: TObject);begin if MessageDlg('', 'Do you want to delete the selected issue?', mtConfirmation, [mbYes, mbNo],0) = mrNo then exit else UserFileConnection.Open; UserFileTransaction.Active:= True; UserFileSave.SQL.Text:=('DELETE FROM `Custom1` WHERE _rowid_=:RowParam;'); UserFileSave.Params.ParamByName('RowParam').Value := IssueListDisplay.ItemIndex+1; UserFileSave.ExecSQL;UserFileTransaction.Commit; IssueListDisplay.Items.Delete(IssueListDisplay.ItemIndex);end;
I won't paste the entire loading code as it's long, but this should be enough to demonstrate how I'm doing it:
--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---procedure TForm1.GoStdProtocolBtnClick(Sender: TObject);begin UserFileConnection.Open; UserFileTransaction.Active:= True; UserFileLoad.SQL.Text:=('SELECT * FROM Custom1 WHERE _rowid_ = :RowParam'); UserFileLoad.Params.ParamByName('RowParam').Value := IssueListDisplay.ItemIndex+1; UserFileTransaction.Commit; UserFileLoad.Open;IssueNameFld.Text := UserFileLoad.Fieldbyname('Issue').AsString;IssueCommentsFld.Text := UserFileLoad.Fieldbyname('IssueComments').AsString; // Load the other fields corresponding to this entry etc. end;
Any thoughts? Thanks! :D
dseligo:
In your database you have this situation:
Row IDValue1Item one2Item two3Item three4Item four
When you add this strings in ListBox, indexes are:
0 - Item one
1 - Item two
2 - Item three
3 - Item four
But, when you delete 'Item two', you have following situation in ListBox:
0 - Item one
1 - Item three
2 - Item four
Indexes in Listbox are changed, and row id in your database stays the same.
You can't use Listbox index for this purpose, row id must be stored somewhere else.
Why don't you use DBGrid to list your items?
dseligo:
One more thing: I didn't analyze your code thoroughly, but it seems to me that you either need 'begin' between lines 8 and 9, or you don't need 'else' in line 8.
heebiejeebies:
--- Quote from: dseligo on April 15, 2022, 11:18:48 pm ---
Indexes in Listbox are changed, and row id in your database stays the same.
You can't use Listbox index for this purpose, row id must be stored somewhere else.
Why don't you use DBGrid to list your items?
--- End quote ---
Can't use DBGrid because I cannot get any database-aware components to work.
Does deleting a row not reorder the row IDs in the database? Because when I open the database in the DB browser, it appears that it does. Or if not automatically, is there an SQL command that will refresh the row IDs?
af0815:
We know only * from your DB. This means nothing.
A ID in the DB is never changed. If you delete one row it is deleted and the other remains unchanged. In some DB Browser you see a rownumber, but this is only a indication from the browser for orientation, so you can see where you in the fetched dataset. This is new build if you fetch a new dataset. But this Information is not useable for you, its only a visual indication.
And why you get no database aware component to work, this must be a problem, because i can always use the dbgrid with the dbnavigator. Alwaya, or i have a corrupt installation.
Navigation
[0] Message Index
[#] Next page