Forum > Database

Deleting rows in SQLite tables corrupts rowid

<< < (7/9) > >>

heebiejeebies:

--- Quote from: 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  [+][-]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";}};} ---UserFileSave.Params.ParamByName('Newissuename').AsString:= NewIssueVar;  //Notice the "AsString" instead of "Value" 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)


--- End quote ---

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.

Zvoni:
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

heebiejeebies:
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!

Zvoni:

--- Quote from: heebiejeebies on April 20, 2022, 10:01:32 am ---1. Is there a way to get the DBGrid to scroll to the bottom and select the new entry?

--- End quote ---
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

--- Quote from: heebiejeebies on April 20, 2022, 10:01:32 am ---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? 

--- End quote ---
Do you actually have enough entries to exceed the visible portion of the Grid?

--- Quote from: heebiejeebies on April 20, 2022, 10:01:32 am ---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!

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

Zvoni:
btw: Your "If ProtocolHomeTabs.ActivePage..."-Mess can be cut down

--- 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";}};} ---Const  TableArray:Array[0..3] Of String=('NegPatterns','Custom1','Custom2','Custom3');  SQL:String='SELECT * FROM ';        ...ProtocolQuery.SQL.Text:=(SQL+TableArray[ProtocolHomeTabs.PageIndex]); 
No reason to pollute the code with those If/Then

Navigation

[0] Message Index

[#] Next page

[*] Previous page

Go to full version