Lazarus

Free Pascal => Database => Topic started by: heebiejeebies on April 15, 2022, 10:24:42 pm

Title: Deleting rows in SQLite tables corrupts rowid
Post by: heebiejeebies on April 15, 2022, 10:24:42 pm
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  [Select][+][-]
  1. procedure TForm1.DeleteIssueButtonClick(Sender: TObject);
  2. begin
  3.  
  4.    if MessageDlg('', 'Do you want to delete the selected issue?', mtConfirmation,
  5.    [mbYes, mbNo],0) = mrNo
  6.   then
  7.     exit
  8.     else
  9.   UserFileConnection.Open;
  10.   UserFileTransaction.Active:= True;
  11.  
  12. UserFileSave.SQL.Text:=('DELETE FROM `Custom1` WHERE _rowid_=:RowParam;');
  13.  
  14.   UserFileSave.Params.ParamByName('RowParam').Value := IssueListDisplay.ItemIndex+1;
  15.  
  16. UserFileSave.ExecSQL;
  17. UserFileTransaction.Commit;
  18.  
  19.   IssueListDisplay.Items.Delete(IssueListDisplay.ItemIndex);
  20. end;                                                        
  21.  

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  [Select][+][-]
  1. procedure TForm1.GoStdProtocolBtnClick(Sender: TObject);
  2. begin
  3.  
  4.  
  5.   UserFileConnection.Open;
  6.   UserFileTransaction.Active:= True;
  7.  
  8. UserFileLoad.SQL.Text:=('SELECT * FROM Custom1 WHERE _rowid_ = :RowParam');
  9.  
  10. UserFileLoad.Params.ParamByName('RowParam').Value := IssueListDisplay.ItemIndex+1;
  11.  
  12. UserFileTransaction.Commit;
  13.    UserFileLoad.Open;
  14. IssueNameFld.Text := UserFileLoad.Fieldbyname('Issue').AsString;
  15. IssueCommentsFld.Text := UserFileLoad.Fieldbyname('IssueComments').AsString;
  16.  
  17. // Load the other fields corresponding to this entry etc.
  18.  
  19.  end;                                                                            

Any thoughts?  Thanks!  :D
Title: Re: Deleting rows in MySQL tables corrupts rowid
Post by: dseligo on April 15, 2022, 11:18:48 pm
In your database you have this situation:
Row IDValue
1Item one
2Item two
3Item three
4Item 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?
Title: Re: Deleting rows in MySQL tables corrupts rowid
Post by: dseligo on April 15, 2022, 11:23:20 pm
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.
Title: Re: Deleting rows in MySQL tables corrupts rowid
Post by: heebiejeebies on April 16, 2022, 12:27:04 am

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?

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?
Title: Re: Deleting rows in MySQL tables corrupts rowid
Post by: af0815 on April 16, 2022, 09:05:57 am
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.
Title: Re: Deleting rows in MySQL tables corrupts rowid
Post by: dseligo on April 16, 2022, 10:17:03 am
Can't use DBGrid because I cannot get any database-aware components to work.

From your signature I can see that you use Lazarus 2.0.10, and in your first post you mentioned mySQL. If you can't make database-aware controls to work with this combination you are doing something wrong.
Obviously, you can connect to the database and use SQL commands.
Put TDataSource and TDBGrid component on your form.
Click on DataSource1 and in Object Inspector change DataSet property to your SQL query component (e.g. UserFileLoad).
Click on DBGrid1 and in Object Inspector change DataSource property to DataSource1.

Now in form's OnCreate event or in some Button's OnClick event put something like this:
Code: Pascal  [Select][+][-]
  1. ...
  2. UserFileLoad.Close;
  3. UserFileLoad.SQL.Text := 'select * from Custom1';
  4. UserFileLoad.Open;
  5. ...

And run your program.

Quote
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?

Show your table structure. In mySQL you can use describe Custom1;
Title: Re: Deleting rows in MySQL tables corrupts rowid
Post by: heebiejeebies on April 16, 2022, 09:28:56 pm
Thanks guys. That's crazy, can't believe this issue has never created a problem for anyone before.

Sounds like my only option is to copy the table row by row to a temporary one then drop & re-create tho original table.  Any other easier options before I start coding, bearing in mind that database-aware components do not work and I had a whole previous thread where I could not solve the issue?
Title: Re: Deleting rows in MySQL tables corrupts rowid
Post by: af0815 on April 16, 2022, 10:04:21 pm
BTW: if you use two queries for the same table, the queries are not automatily in sync. So if you change anything with query one, the information of query two is not automatic refreshe and iMHO not the same like in the DB. So you have to refresh the information by your logic.

And a query can hold a SQL-Statement for select and Staments for update insert and delete. And this staments are working together.

If you insert only a select statement - the internal SQL Parser try to find out to fill out the missing staments. This can go wrong :-) Typical if you use a not editable view in your Select statement.
Title: Re: Deleting rows in MySQL tables corrupts rowid
Post by: heebiejeebies on April 16, 2022, 10:11:42 pm
Thanks af0815.  I think I'm going to have to put this one in the 'too hard' pile for the moment.

One last ditch attempt. I tried dseligo's suggestion above and it loaded all the field names from the Custom 1 table in the DBGrid as a heading, but where all the data should be it just says (memo).  It appears to have the right number of columns to match the data source, but has not loaded any of the data.  For some weird reason the first column entries all appear as (MEMO) in caps and the rest all appear as (memo) in lower case.
Title: Re: Deleting rows in SQLite tables corrupts rowid
Post by: heebiejeebies on April 16, 2022, 11:45:54 pm
Derrr.... Of course, I meant SQLite, not MySQL. Sorry.  I use the two words interchangeably and sometimes forget there's actually a difference.

So I did some more googling.  Lots of people with this problem, but seemingly no solution.  Closest I found is this - I create a field with a primary key/autoincrement then reset the SQLITE_SEQUENCE table every time there's an entry deleted.

This is what I'm using; I've tried about 20 other things which all errored, mostly complaining that the Table or the Column doesn't exist (which they do).  This at least runs without error, but it does nothing:

Code: Pascal  [Select][+][-]
  1. UserFileConnection.ExecuteDirect('DELETE FROM `Custom1`;'+
  2. 'DELETE FROM SQLITE_SEQUENCE WHERE NAME = `id`');  

Using the table name in the second line also does nothing.  The Primary Key continues its sequence without resetting.  Nice solution would be just to drop the entire Primary Key column and create a new one, but SQLite does not allow you to drop columns!! Can only drop the entire table and re-create it with the column missing!! No thanks!
Title: Re: Deleting rows in SQLite tables corrupts rowid
Post by: heebiejeebies on April 17, 2022, 03:54:23 am
Some progress at last - HOORAY.

I got the DBGrid to work, but I had to change a flag in the inspector options - it's essentially a 'broken/working' flag called dgDisplayMemoText.  It's set to broken by default for some reason.  Cannot get the DBListbox to work, but that's okay because I've set up the DBGrid to look exactly like a listbox anyway.  It loads just the title column of the table.

NOW... the bit I can't figure out ...  :D

How do I get the rowid selected by the user in the DBGrid?  If I can put this into a global variable then the rest of my code should work with minimal changes.  Thanks everyone!
Title: Re: Deleting rows in SQLite tables corrupts rowid
Post by: af0815 on April 17, 2022, 08:07:01 am
If a user selcts an entry, the dataset is adjusted direct. The actual record in the dateset is the correct to ask.

And you have no rowid to remember, you must use the the correct primary key !

If your definition of a coloumn in th DB is memo or somthing similar, the field is not a string, it result in a memo field. This is not shown in components, only in DBMemo and stringgrid with the correct flag set.
Title: Re: Deleting rows in SQLite tables corrupts rowid
Post by: heebiejeebies on April 17, 2022, 08:50:26 am
Thank you.  So supposing I create a primary key field for the table.  Is there a way to see the key for the row the user has selected in the DBGrid?  The grid only shows the title field, so the user will be selecting from that one field, then I want to look up the key for that row.  Is it possible?
Title: Re: Deleting rows in SQLite tables corrupts rowid
Post by: af0815 on April 17, 2022, 09:22:52 am
The actual record is the selected.
Title: Re: Deleting rows in SQLite tables corrupts rowid
Post by: heebiejeebies on April 17, 2022, 09:44:58 am
I mean selected by the user's mouse click in the DBGrid, not the Select command in SQL.  :D
Title: Re: Deleting rows in SQLite tables corrupts rowid
Post by: dseligo on April 17, 2022, 11:59:09 am
I mean selected by the user's mouse click in the DBGrid, not the Select command in SQL.  :D

Your SQL query component's cursor is changed when you select different row in DBGrid.

You can get current values with something like this:
Code: Pascal  [Select][+][-]
  1. UserFileLoad.FieldByName('your primary key field name').AsString;

You can use AfterScroll event of the SQL query to detect when user changes row in DBGrid.
Title: Re: Deleting rows in SQLite tables corrupts rowid
Post by: dseligo on April 17, 2022, 12:06:56 pm
If you don't make it to work, I can make you small example project.
Do you use standard Lazarus DB components to work with SQlite or maybe ZeosDBO?
Title: Re: Deleting rows in SQLite tables corrupts rowid
Post by: dseligo on April 17, 2022, 02:46:18 pm
OK, I made you an example project.

I created 3 events: OnCellClick and OnDblClick of DBGrid, and AfterScroll of SQLquery.
I printed output from those 3 events to Memo so you can see what's going on.
Deleting row is implemented in OnDblClick event.

I tested it and all works as expected.
Title: Re: Deleting rows in SQLite tables corrupts rowid
Post by: heebiejeebies on April 17, 2022, 10:29:49 pm
Thanks very much for your effort, I appreciate it a lot.

Unfortunately I think this is beyond my ability.  When I copy everything into my own project, the DBGrid stops populating and qView gives an error that there's no such field as ID (which there is).

I really don't know what I'm doing here so I think I should move on for the moment.
Title: Re: Deleting rows in SQLite tables corrupts rowid
Post by: dseligo on April 18, 2022, 02:30:06 am
Don't put everything in your project.
Run project as I sent you, and observe how it works (make SQlite library available, and you have to press 'Create DB' first).

Maybe in your table you don't have ID column.
Title: Re: Deleting rows in SQLite tables corrupts rowid
Post by: kapibara on April 18, 2022, 06:11:06 am
I think you should study dseligo's example and ask more questions when needed. To learn database programming takes a certain investment in time and effort. The database tutorials are very useful: https://wiki.lazarus.freepascal.org/SQLdb_Tutorial0

Another (dbaware) aproach is to use a DBLookupListBox. To get that going you fill out Keyfield, Listfield and ListSource. Datasource and Datafield is left empty and the ListSource points to a TSQLQuery with someting like "select * from mytable".

Play around, make a few small test projects and I'm sure you'll soon figure it out. Its worth it.
Title: Re: Deleting rows in SQLite tables corrupts rowid
Post by: Zvoni on April 19, 2022, 09:21:36 am
Not having read through the whole Thread: What exactly are you trying to achieve?
every time i see the word "sequence" in conjunction with "rowid" and SQLite i get stomach cramps....
There are a lot of pitfalls in SQLite if you try to influence THE "sequence" (which is still possible, though)
Title: Re: Deleting rows in MySQL tables corrupts rowid
Post by: Zvoni on April 19, 2022, 02:07:44 pm
A ID in the DB is never changed. If you delete one row it is deleted and the other remains unchanged.
Wrong.
This only applies to surrogate Primary Keys (so called anonymous "ID"'s). And even then you can still change them
A primary Key can be anything as long as it is unique to the table resp. the data-model

Think why you can use an email-address ONLY ONCE when you register yourself somewhere. (because an email-address is unique in the whole world).
The same for phonenumbers, Bank-Account-Number and similar

and a primary key can even be a composite key, consisting of 2 or more columns.
A "classic" for something like that scenario would be an "m:m"-relation between two tables, where the two foreign keys in the connecting table can be declared together as Primary Key
Something like a Table "Employees" and a Table "Tasks" --> connecting Table "emp_tasks"
With both Foreign Keys as a composite Primary key, it's not possible to assign a task twice to one and the same employee (which wouldn't make sense)

Bottom Line: If you don't need a surrogate Primary Key, then don't use it!
The only scenario i (usually) use a surrogate key is if that Key is a Foreign Key somewhere else, and my first candidate would be a composite Primary Key.

And if you use Foreign Keys, never ever forget ON UPDATE CASCADE (most people only use ON DELETE CASCADE)

Oh, and FWIW: Don't use the blasted AUTOINCREMENT for Integer Primary Key ("ID") in SQLite. You don't need it.
Title: Re: Deleting rows in SQLite tables corrupts rowid
Post by: af0815 on April 19, 2022, 02:24:48 pm
My remarks are based on this line
Quote from: heebiejeebies
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.

A ID in the DB is never changed. If you delete one row it is deleted and the other remains unchanged.
Wrong.
This only applies to surrogate Primary Keys (so called anonymous "ID"'s). And even then you can still change them
A primary Key can be anything as long as it is unique to the table resp. the data-model
Not more and not less - what should i discuss wit a DB-newbee about the theory of databaseprograming and the specialties of sqlite.

I know the problems of multi user Databasystems and replication for years (and i have learned to hate autoinc fields, they are so different between the platforms and a horror for replication).
Title: Re: Deleting rows in SQLite tables corrupts rowid
Post by: Zvoni on April 19, 2022, 02:50:03 pm
My remarks are based on this line
Quote from: heebiejeebies
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.

A ID in the DB is never changed. If you delete one row it is deleted and the other remains unchanged.
Wrong.
This only applies to surrogate Primary Keys (so called anonymous "ID"'s). And even then you can still change them
A primary Key can be anything as long as it is unique to the table resp. the data-model
Not more and not less - what should i discuss wit a DB-newbee about the theory of databaseprograming and the specialties of sqlite.

I know the problems of multi user Databasystems and replication for years (and i have learned to hate autoinc fields, they are so different between the platforms and a horror for replication).
Ah, OK.
My Apologies.
Your answer just bugged me, because "An ID never changes" is just plain wrong.

@Replication: I take it you're talking about Master-Master-Replication, because i can't see the Problem with Master-Slave
Title: Re: Deleting rows in SQLite tables corrupts rowid
Post by: heebiejeebies on April 19, 2022, 09:40:59 pm
Thanks for the replies everyone.  Still having trouble getting the DBGrid to populate, sometimes.  The frustrating thing is that code that works perfectly in some situations fails in others.  For example, I have a TPageControl at the top of the page which doesn't actually change any pages, I just purely use the tab part to select which table from the database should be displayed in the DBGrid.  The code in the OnChange event is the following:

Code: Pascal  [Select][+][-]
  1.  ProtocolQuery.Close;
  2.  
  3.   if ProtocolHomeTabs.ActivePage=PNegPatTab then ProtocolQuery.SQL.Text:=('SELECT * FROM NegPatterns');
  4.   if ProtocolHomeTabs.ActivePage=PCustom1Tab then ProtocolQuery.SQL.Text:=('SELECT * FROM Custom1');
  5.   if ProtocolHomeTabs.ActivePage=PCustom2Tab then ProtocolQuery.SQL.Text:=('SELECT * FROM Custom2');
  6.   if ProtocolHomeTabs.ActivePage=PCustom3Tab then ProtocolQuery.SQL.Text:=('SELECT * FROM Custom3');
  7.  
  8.  
  9.     ProtocolQuery.Open;
  10.                                                        
  11.  

This works fine, and whichever tab you choose, that table loads into the DBGrid every time.  However, the following is my 'add new issue' button code, and after running this, the DBGrid goes blank.  If I copy and paste the exact code from above at the end of the following routine, it doesn't work.  Yet the tabs still work every time, even after saving a new issue.  How can the exact same code fail in one place and work in another?!

Code: Pascal  [Select][+][-]
  1. procedure TForm1.AddNewIssueBtnClick(Sender: TObject);
  2. var
  3. NewIssueVar: string;
  4. begin
  5. NewIssueVar :='New issue name';
  6.    UserFileConnection.Open;
  7.   UserFileTransaction.Active:= True;
  8.  
  9.   if ProtocolHomeTabs.ActivePage=PNegPatTab then UserFileSave.SQL.Text:=('INSERT INTO `NegPatterns`(`Issue`) VALUES (:Newissuename);');
  10.   if ProtocolHomeTabs.ActivePage=PCustom1Tab then UserFileSave.SQL.Text:=('INSERT INTO `Custom1`(`Issue`) VALUES (:Newissuename);');
  11.   if ProtocolHomeTabs.ActivePage=PCustom2Tab then UserFileSave.SQL.Text:=('INSERT INTO `Custom2`(`Issue`) VALUES (:Newissuename);');
  12.   if ProtocolHomeTabs.ActivePage=PCustom3Tab then UserFileSave.SQL.Text:=('INSERT INTO `Custom3`(`Issue`) VALUES (:Newissuename);');
  13.  
  14.  
  15.    if not InputQuery('Enter Issue Name:', '', NewIssueVar) then
  16.     exit;
  17.  
  18.          UserFileSave.Params.ParamByName('Newissuename').Value := NewIssueVar;
  19.  
  20.  UserFileTransaction.Commit;
  21.    UserFileSave.ExecSQL;
  22.  
  23. end;                                                              
  24.  
Title: Re: Deleting rows in SQLite tables corrupts rowid
Post by: af0815 on April 19, 2022, 09:56:45 pm
I can see only a small part but:

I thinks it is better to use 4 queries. One for every tab. In the queries you can enter a statemnt for select, insert, update and delete. If you fill in this correct, you can work with the query without loading new statements.

you can open the query and work on the base of the dataset itself. With the switching of the sql-Statemnt and changing between execsql, and open the dataset sensitive components like DBGrid are confused.

One experiment:
Take one query, fill the nedded sql statement and use a Dataset, DBGrid a DBNavigator and some DbEdit. Use only the navigator to browse, delete or insert data in the table. The dataset will change his state according the commands of the navigator and the DBGrid. You can browse, insert, delete and update the data. All without losing the control and changing the sql statements. This is the idea behind the components.

If you want to work with reloading and changing the statement, dont use datasensitive components and do ALL by code. Dont mix it, you get only confused.

The old words: Take it or leave ist (don´t mix it)

BTW: I think Zvoni can it better explain.
Title: Re: Deleting rows in SQLite tables corrupts rowid
Post by: dseligo on April 20, 2022, 12:36:46 am
How can the exact same code fail in one place and work in another?!

You made an error somewhere.
If someone doesn't figure it out from your code, you'll have to make small project which shows your problem and post it here.
Title: Re: Deleting rows in SQLite tables corrupts rowid
Post by: Zvoni on April 20, 2022, 08:17:59 am
The only thing i can see is his Line 18 and Line 20/21 in the second code-block.
Line 18 would have expected:
Code: Pascal  [Select][+][-]
  1. UserFileSave.Params.ParamByName('Newissuename').AsString:= NewIssueVar;  //Notice the "AsString" instead of "Value"
  2.  
Line 20/21:
You commit before you execute.
Turn it around: Execute first, then Commit

EDIT: This is just at first look.
There is other stuff, which is not making a lot of sense.
1) Why are you opening the connection (again)? Open it once at startup, and keep it open. Period.
2) Don't activate the Transaction if you don't need it
3) As a rule of thumb:
a) Let the User make all inputs
b) validate the input
c) (optional: Open the Connection) - Activate the Transaction
d) Set Parameters
e) Execute Insert/Update/Delete-Query
f) If not error, commit Transaction --> Hint: Lookup "Try... finally/except"
g) (Optional: Deactivate the Transaction)
Title: Re: Deleting rows in SQLite tables corrupts rowid
Post by: Zvoni on April 20, 2022, 08:25:51 am
BTW: I think Zvoni can it better explain.
Thank you for that vote of confidence  :D
I don't think i can help him with db-aware controls, since the last time i used one is some 20 years ago in Visual Basic
Title: Re: Deleting rows in SQLite tables corrupts rowid
Post by: heebiejeebies on April 20, 2022, 08:51:30 am
The only thing i can see is his Line 18 and Line 20/21 in the second code-block.
Line 18 would have expected:
Code: Pascal  [Select][+][-]
  1. UserFileSave.Params.ParamByName('Newissuename').AsString:= NewIssueVar;  //Notice the "AsString" instead of "Value"
  2.  
Line 20/21:
You commit before you execute.
Turn it around: Execute first, then Commit

EDIT: This is just at first look.
There is other stuff, which is not making a lot of sense.
1) Why are you opening the connection (again)? Open it once at startup, and keep it open. Period.
2) Don't activate the Transaction if you don't need it
3) As a rule of thumb:
a) Let the User make all inputs
b) validate the input
c) (optional: Open the Connection) - Activate the Transaction
d) Set Parameters
e) Execute Insert/Update/Delete-Query
f) If not error, commit Transaction --> Hint: Lookup "Try... finally/except"
g) (Optional: Deactivate the Transaction)


Thanks for the input guys.  Zvoni, if ever you're tempted to ask why I did something, the answer is "because I'm a bad programmer."  :)

The save code works fine, by the way, I only posted it because I knew someone would ask.  The problem is purely that the DBGrid goes blank after the new row is added, and doesn't reload until I switch tabs.
Title: Re: Deleting rows in SQLite tables corrupts rowid
Post by: Zvoni on April 20, 2022, 09:43:41 am
Hmm....have you tried to Refresh the underlying Dataset of the DBGrid?
I'd probably do it from the AfterPost-Event.

IIRC, DBGrid.DataSource.DataSet.Refresh i think

EDIT: Just saw it.
FWIW, don't do SELECT * FROM SomeTable.
Don't! Period!
Explicitly Select the Columns you need
Title: Re: Deleting rows in SQLite tables corrupts rowid
Post by: heebiejeebies on April 20, 2022, 10:01:32 am
So I fixed it - my solution was literally just to paste the entire "if ProtocolHomeTabs.ActivePage" ..... routine into the code a second time.  As in, that part of the code is repeated twice.  Seemed to fix the problem entirely, but just out of curiosity I commented out the second occurrence and tried Zvoni's suggestion of 'DBGrid.DataSource.DataSet.Refresh' - and it still worked.  So I commented out the refresh statement and guess what .... STILL WORKED.  WHAT THE?  Not the first time I've had weird things like that happen and there is at least one other section of code in my program where I need to repeat the code or it doesn't work. Anyway, it's working - nobody breathe!

So I think I'm very nearly near the end of this saga, and then I'll go away and stop annoying you all for a few months until I hit my next violence-inducing catastrophe.  I just have two more issues.

1. Is there a way to get the DBGrid to scroll to the bottom and select the new entry?
2. Speaking of scrolling, I have the ScrollBars property set to SSVertical.  Doesn't work.  Is there a way to get Scrollbars to actually appear on the DBGrid? 

If not, my other option is  ....

3. Is there a way to fix the previous (MEMO) issue I had when using a DBLookupListBox ? I can get it to connect to the database no problem, so it seems to be a viable option, but all the data appears as (MEMO).  There's no dgDisplaymemotext flag in the inspector like there is with DBGrid.

Thanks everyone!
Title: Re: Deleting rows in SQLite tables corrupts rowid
Post by: Zvoni on April 20, 2022, 10:07:47 am
1. Is there a way to get the DBGrid to scroll to the bottom and select the new entry?
At a guess: DBGrid.DataSource.DataSet.Last
EDIT:
NotaBene: Pretty sure that's not going to work if you have an ORDER BY-Clause.
As a Workaround: Get the last Inserted ID. Locate the ID in the Dataset
2. Speaking of scrolling, I have the ScrollBars property set to SSVertical.  Doesn't work.  Is there a way to get Scrollbars to actually appear on the DBGrid? 
Do you actually have enough entries to exceed the visible portion of the Grid?
3. Is there a way to fix the previous (MEMO) issue I had when using a DBLookupListBox ? I can get it to connect to the database no problem, so it seems to be a viable option, but all the data appears as (MEMO).  There's no dgDisplaymemotext flag in the inspector like there is with DBGrid.

Thanks everyone!
And we're back at your SELECT * FROM .....
Try:
SELECT CAST(Field AS CHAR) AS Field, SomeOtherFields FROM MyTable
Title: Re: Deleting rows in SQLite tables corrupts rowid
Post by: Zvoni on April 20, 2022, 10:35:33 am
btw: Your "If ProtocolHomeTabs.ActivePage..."-Mess can be cut down
Code: Pascal  [Select][+][-]
  1. Const
  2.   TableArray:Array[0..3] Of String=('NegPatterns','Custom1','Custom2','Custom3');
  3.   SQL:String='SELECT * FROM ';        
  4. .
  5. .
  6. .
  7. ProtocolQuery.SQL.Text:=(SQL+TableArray[ProtocolHomeTabs.PageIndex]);
  8.  

No reason to pollute the code with those If/Then
Title: Re: Deleting rows in SQLite tables corrupts rowid
Post by: heebiejeebies on April 20, 2022, 11:22:57 am
At a guess: DBGrid.DataSource.DataSet.Last

Brilliant guess!  Perfect!  :D


Quote
Do you actually have enough entries to exceed the visible portion of the Grid?

Yes, although I've tried both ssVertical and ssAutoVertical and neither works in any case.  I presume ssVertical should show them whether there's enough entries or not.


Quote
And we're back at your SELECT * FROM .....
Try:
SELECT CAST(Field AS CHAR) AS Field, SomeOtherFields FROM MyTable

Another brilliant guess, thank you!  :D  I can probably just use this in place of the DBGrid as it has scrollbars.
Title: Re: Deleting rows in SQLite tables corrupts rowid
Post by: heebiejeebies on April 20, 2022, 11:23:54 am
btw: Your "If ProtocolHomeTabs.ActivePage..."-Mess can be cut down
Code: Pascal  [Select][+][-]
  1. Const
  2.   TableArray:Array[0..3] Of String=('NegPatterns','Custom1','Custom2','Custom3');
  3.   SQL:String='SELECT * FROM ';        
  4. .
  5. .
  6. .
  7. ProtocolQuery.SQL.Text:=(SQL+TableArray[ProtocolHomeTabs.PageIndex]);
  8.  

No reason to pollute the code with those If/Then

Haha, thank you! :) Maybe if I post enough of my horrible code I can get you to optimise my entire program for free, just through sheer irritation!
Title: Re: Deleting rows in SQLite tables corrupts rowid
Post by: Zvoni on April 20, 2022, 11:45:29 am
Haha, thank you! :) Maybe if I post enough of my horrible code I can get you to optimise my entire program for free, just through sheer irritation!
2 € / line of Code... *gggg*
 :P :P :P :P :P

Quote
Yes, although I've tried both ssVertical and ssAutoVertical and neither works in any case.  I presume ssVertical should show them whether there's enough entries or not.
Eh, No!
ssVertical just prepares the Scrollbar (ssAutoVertical doesn't!).
And the Scrollbar itself only shows up if the entries exceed the visible Part
Title: Re: Deleting rows in SQLite tables corrupts rowid
Post by: heebiejeebies on April 20, 2022, 09:22:12 pm
Spoke too soon.  DBLookupListBox doesn't have an "OnCellClick" event, and when I put my code in the regular OnClick event, it doesn't recognise which row the user has selected - it just does everything as though the user had selected the first row.

Anyone know how to make it recognise the row?  Or anyone have any other ideas about fixing the DBGRid scrollbars?  Thanks!
Title: Re: Deleting rows in SQLite tables corrupts rowid
Post by: dseligo on April 21, 2022, 12:56:42 am
Spoke too soon.  DBLookupListBox doesn't have an "OnCellClick" event, and when I put my code in the regular OnClick event, it doesn't recognise which row the user has selected - it just does everything as though the user had selected the first row.

Anyone know how to make it recognise the row?  Or anyone have any other ideas about fixing the DBGRid scrollbars?  Thanks!

It has OnClick event. If you enable 'ScrollListDataset' in Object inspector then this works in OnClick event:
Code: Pascal  [Select][+][-]
  1. DBLookupListBox1.ListSource.DataSet.FieldByName('your_field_name').Your_Field_Type
Title: Re: Deleting rows in SQLite tables corrupts rowid
Post by: heebiejeebies on April 22, 2022, 04:49:24 am
That works. Thanks again for all your help!  :D
TinyPortal © 2005-2018