Recent

Author Topic: Updating data from a KDBGrid to SQLite  (Read 1138 times)

heebiejeebies

  • Full Member
  • ***
  • Posts: 120
Updating data from a KDBGrid to SQLite
« 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][+][-]
  1. Userfiletransaction.Active := true;
  2.   SQLQuery1.ApplyUpdates;
  3.   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
Fedora 37/Lazarus 2.2.4- FPC 3.3.1

heebiejeebies

  • Full Member
  • ***
  • Posts: 120
Re: Updating data from a KDBGrid to SQLite
« Reply #1 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
Fedora 37/Lazarus 2.2.4- FPC 3.3.1

Chris78

  • Newbie
  • Posts: 2
Re: Updating data from a KDBGrid to SQLite
« Reply #2 on: February 05, 2023, 06:00:34 pm »
Hi.

What happens if you don't use any transaction ?

heebiejeebies

  • Full Member
  • ***
  • Posts: 120
Re: Updating data from a KDBGrid to SQLite
« Reply #3 on: February 06, 2023, 10:51:45 am »
Hi, thanks for the reply.

What happens is - absolutely nothing. No error, but no result either.
Fedora 37/Lazarus 2.2.4- FPC 3.3.1

Zvoni

  • Hero Member
  • *****
  • Posts: 1681
Re: Updating data from a KDBGrid to SQLite
« Reply #4 on: February 06, 2023, 11:12:27 am »
Does the same happen with a "regular" DBGrid?
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

GetMem

  • Hero Member
  • *****
  • Posts: 4026
Re: Updating data from a KDBGrid to SQLite
« Reply #5 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.

heebiejeebies

  • Full Member
  • ***
  • Posts: 120
Re: Updating data from a KDBGrid to SQLite
« Reply #6 on: February 07, 2023, 07:17:10 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.
Fedora 37/Lazarus 2.2.4- FPC 3.3.1

heebiejeebies

  • Full Member
  • ***
  • Posts: 120
Re: Updating data from a KDBGrid to SQLite
« Reply #7 on: February 07, 2023, 07:19:05 am »
@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
Fedora 37/Lazarus 2.2.4- FPC 3.3.1

Zvoni

  • Hero Member
  • *****
  • Posts: 1681
Re: Updating data from a KDBGrid to SQLite
« Reply #8 on: February 07, 2023, 08:58:57 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][+][-]
  1. SELECT CHAR(SomeTextColumn) AS Field1 FROM SomeTable
« Last Edit: February 07, 2023, 09:24: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: 120
Re: Updating data from a KDBGrid to SQLite
« Reply #9 on: February 11, 2023, 01:19:38 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][+][-]
  1. 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.
« Last Edit: February 11, 2023, 10:18:42 pm by heebiejeebies »
Fedora 37/Lazarus 2.2.4- FPC 3.3.1

 

TinyPortal © 2005-2018