Updating data from a KDBGrid to SQLite

[heebiejeebies]

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. 

[heebiejeebies]

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

[Chris78]

Hi.

What happens if you don't use any transaction ?

[heebiejeebies]

Hi, thanks for the reply.

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

[Zvoni]

Does the same happen with a "regular" DBGrid?

[GetMem]

@heebiejeebies

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.

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]

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.

[heebiejeebies]

@heebiejeebies

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.

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 

[Zvoni]

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

[heebiejeebies]

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.