Recent

Author Topic: Deleting rows in SQLite tables corrupts rowid  (Read 6491 times)

heebiejeebies

  • Full Member
  • ***
  • Posts: 127
Re: Deleting rows in SQLite tables corrupts rowid
« Reply #30 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.
Fedora 38/Lazarus 2.2.4- FPC 3.3.1

Zvoni

  • Hero Member
  • *****
  • Posts: 2319
Re: Deleting rows in SQLite tables corrupts rowid
« Reply #31 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
« Last Edit: April 20, 2022, 09:47:33 am by Zvoni »
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

heebiejeebies

  • Full Member
  • ***
  • Posts: 127
Re: Deleting rows in SQLite tables corrupts rowid
« Reply #32 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!
Fedora 38/Lazarus 2.2.4- FPC 3.3.1

Zvoni

  • Hero Member
  • *****
  • Posts: 2319
Re: Deleting rows in SQLite tables corrupts rowid
« Reply #33 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
« Last Edit: April 20, 2022, 10:18:07 am by Zvoni »
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

Zvoni

  • Hero Member
  • *****
  • Posts: 2319
Re: Deleting rows in SQLite tables corrupts rowid
« Reply #34 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
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

heebiejeebies

  • Full Member
  • ***
  • Posts: 127
Re: Deleting rows in SQLite tables corrupts rowid
« Reply #35 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.
« Last Edit: April 20, 2022, 11:27:29 am by heebiejeebies »
Fedora 38/Lazarus 2.2.4- FPC 3.3.1

heebiejeebies

  • Full Member
  • ***
  • Posts: 127
Re: Deleting rows in SQLite tables corrupts rowid
« Reply #36 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!
Fedora 38/Lazarus 2.2.4- FPC 3.3.1

Zvoni

  • Hero Member
  • *****
  • Posts: 2319
Re: Deleting rows in SQLite tables corrupts rowid
« Reply #37 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
« Last Edit: April 20, 2022, 11:47:27 am by Zvoni »
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

heebiejeebies

  • Full Member
  • ***
  • Posts: 127
Re: Deleting rows in SQLite tables corrupts rowid
« Reply #38 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!
Fedora 38/Lazarus 2.2.4- FPC 3.3.1

dseligo

  • Hero Member
  • *****
  • Posts: 1196
Re: Deleting rows in SQLite tables corrupts rowid
« Reply #39 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

heebiejeebies

  • Full Member
  • ***
  • Posts: 127
Re: Deleting rows in SQLite tables corrupts rowid
« Reply #40 on: April 22, 2022, 04:49:24 am »
That works. Thanks again for all your help!  :D
Fedora 38/Lazarus 2.2.4- FPC 3.3.1

 

TinyPortal © 2005-2018