Lazarus

Programming => Databases => Topic started by: heebiejeebies on February 04, 2023, 05:06:15 am

Title: Updating data from a KDBGrid to SQLite
Post by: heebiejeebies on February 04, 2023, 05:06:15 am

Hi all,

I feel like this is a really simple question that I should know the answer to, but unfortunately I am a little bit stupid and cannot find straightforward answers anywhere! :(

I have a DBGrid (actually a KDBGrid) that loads fields from a table CurrentSession using DataSource1 and SQLQuery1. My transaction is called UserFileTransaction.

OnChange appears to be the most appropriate event available in a KDBGrid. Is there a simple code I can add to this event that will save the user's edits to the database? Here is my best attempt so far, for your amusement:

Code: Pascal [Select][+]

Userfiletransaction.Active := true;
  SQLQuery1.ApplyUpdates;
  Userfiletransaction.Commit;  

Hope you all got a good chuckle out of that. Now anyone know what I actually need to do?

My eternal gratitude for any suggestions, and also for not throwing rotten fruit at me. :D

Title: Re: Updating data from a KDBGrid to SQLite
Post by: heebiejeebies on February 05, 2023, 03:00:22 am

By the way, I didn't bother including it because I figured my code is probably completely wrong from the ground up, but the error I'm getting from it is "cannot start a transaction within a transaction". I have no other transactions active. Halp please

Title: Re: Updating data from a KDBGrid to SQLite
Post by: Chris78 on February 05, 2023, 06:00:34 pm

Hi.

What happens if you don't use any transaction ?

Title: Re: Updating data from a KDBGrid to SQLite
Post by: heebiejeebies on February 06, 2023, 10:51:45 am

Hi, thanks for the reply.

What happens is - absolutely nothing. No error, but no result either.

Title: Re: Updating data from a KDBGrid to SQLite
Post by: Zvoni on February 06, 2023, 11:12:27 am

Does the same happen with a "regular" DBGrid?

Title: Re: Updating data from a KDBGrid to SQLite
Post by: balazsszekely on February 06, 2023, 01:10:17 pm

@heebiejeebies

Quote

By the way, I didn't bother including it because I figured my code is probably completely wrong from the ground up

Always attach a demo project, otherwise we just shot in the dark, it does not matter if it's wrong or not and don't worry we all make mistakes.

Quote

Halp please

Here you go:
- download then extract attached project
- copy sqlite3.dll to your project folder, make sure the bitness is correct depending on your Lazarus/FPC version
- compile/run project

PS: Please note that I never worked with KControls and KDBGrid in particular, maybe there are better ways to achieve the same thing.

Title: Re: Updating data from a KDBGrid to SQLite
Post by: heebiejeebies on February 07, 2023, 07:17:10 am

Quote from: Zvoni on February 06, 2023, 11:12:27 am

Does the same happen with a "regular" DBGrid?

That was another option that I tried, which again resulted in lots of lost hair. Basically I couldn't get a regular grid to load the data, it just showed (MEMO) in all the cells.

Title: Re: Updating data from a KDBGrid to SQLite
Post by: heebiejeebies on February 07, 2023, 07:19:05 am

Quote from: GetMem on February 06, 2023, 01:10:17 pm

@heebiejeebies
Quote
By the way, I didn't bother including it because I figured my code is probably completely wrong from the ground up
Always attach a demo project, otherwise we just shot in the dark, it does not matter if it's wrong or not and don't worry we all make mistakes.

Quote
Halp please
Here you go:
- download then extract attached project
- copy sqlite3.dll to your project folder, make sure the bitness is correct depending on your Lazarus/FPC version
- compile/run project

PS: Please note that I never worked with KControls and KDBGrid in particular, maybe there are better ways to achieve the same thing.

THANK YOU GETMEM! :) You are an absolute legend among legends.

Now, I just need to pull it apart and figure out what I was doing wrong. Will probably be back with questions :D

Title: Re: Updating data from a KDBGrid to SQLite
Post by: Zvoni on February 07, 2023, 08:58:57 am

Quote from: heebiejeebies on February 07, 2023, 07:17:10 am

Quote from: Zvoni on February 06, 2023, 11:12:27 am
Does the same happen with a "regular" DBGrid?

That was another option that I tried, which again resulted in lots of lost hair. Basically I couldn't get a regular grid to load the data, it just showed (MEMO) in all the cells.

And if you've just searched the Forum, there are enough answers for the "(MEMO)"-Thing for DBGrid in combination with SQLite....

The easiest being

Code: SQL [Select][+]

SELECT CHAR(SomeTextColumn) AS Field1 FROM SomeTable

Title: Re: Updating data from a KDBGrid to SQLite
Post by: heebiejeebies on February 11, 2023, 01:19:38 am

Quote from: Zvoni on February 07, 2023, 08:58:57 am

Quote from: heebiejeebies on February 07, 2023, 07:17:10 am
Quote from: Zvoni on February 06, 2023, 11:12:27 am
Does the same happen with a "regular" DBGrid?

That was another option that I tried, which again resulted in lots of lost hair. Basically I couldn't get a regular grid to load the data, it just showed (MEMO) in all the cells.

And if you've just searched the Forum, there are enough answers for the "(MEMO)"-Thing for DBGrid in combination with SQLite....

The easiest being
Code: SQL [Select][+][-]
SELECT CHAR(SomeTextColumn) AS Field1 FROM SomeTable

Thanks, I already use that code elsewhere to deal with the same problem (I think it was you that told me about it previously). But I also have other issues with DBGrid, such as DBGrid.DataSource.DataSet.Last and DBGrid.DataSource.DataSet.First not working, so I'll just stick with KDBGrid. GetMem's code works well!

A couple of questions:
In the example project, the code validates one of the fields to ensure it is a valid integer. Is there any way to validate input from the grid to ensure it is a valid date? and

One of the drawbacks of KDBGrid is that it doesn't allow you to set individual columns as read only. Any idea how to work around this? Maybe select a different column using FocusCell as soon as the user enters that column. I've tried this using the OnEditorSelect event, and while the event does fire instantly as expected, FocusCell doesn't actually do anything in this instance.

Thanks heaps everyone :)

Disregard. I'm going to keep one of my grids as editable and for the troublesome one, I'm just going to lock the text and pop up an editor instead.