datagrid form save / update sqlite

Moderators: heatherlaine, kevinmiller, robinmiller, Zryip TheSlug

Post Reply
paulalsmith1000
Posts: 58
Joined: Sat Jun 15, 2019 10:09 am

datagrid form save / update sqlite

Post by paulalsmith1000 » Thu Aug 01, 2019 10:28 pm

Dear Anyone

Thanks very much for all your help on my last post about blob images, after many hours its now working fine (I am however starting to think that datagrids may not be the best thing to try and use in your first app!).

Anyway, I thought I may as well plough on seeing as I've almost finished.

So, the last page I need to do is basically a datagrid form with a tick box and option menu (or similar) - I have uploaded a screenshot that probably explains it better:

-the image at the left hand side of each row is just a placeholder for now, but when the app is used it will show pictures taken during a wildlife survey (these pictures are taken earlier in the app and saved in the sqlite db, then used to populate the form). This bit I can do

-the field "species recorded" is the name of the species the child thinks the picture shows (this again is saved earlier in the app and then used to populate the form). This bit I can do.

-after the survey, I would like go through the form shown in the screenshot and if the "species recorded" is right tick the checkbox, or if not, select the species from the option menu (or ideally using an input field with predictive text to guess the name as i start typing, although this doesn't seem to be possible)

Then when I press a button or as the app/card closes save changes to the db to the right row/cell etc...

The problem is I can't get my head round how to start getting my head round how to do this.

I have tried reading a few of the the forums / livecode lessons, etc... but don't really understand.

Any help whatsoever, would be marvelous.

Kind regards

Paul
Attachments
Screenshot from 2019-08-01 22-27-50.png

SparkOut
Posts: 2834
Joined: Sun Sep 23, 2007 4:58 pm

Re: datagrid form save / update sqlite

Post by SparkOut » Fri Aug 02, 2019 8:15 am

It's very hard to be specific without knowing your code, and data structure. In general terms though:
When you query the table to populate the form, include the record id (unique indexed value) and although it might not be displayed, that will then be the hook you can use to get the confirmation details back into the database.
I suspect that for this app there is not going to be anything in particular that causes performance issues, or terrible disaster if an update breaks/fails. So rather than do one big transaction of all the updates at the end, I would send the changes back to the table as each form record is updated.
When the record on the form is updated you collect the changed values on that form record and the record id. Then you can make an update query to set the columns with the updated values WHERE recordID = 'theIndexValueForThisRowYouStoredEarlier'

paulalsmith1000
Posts: 58
Joined: Sat Jun 15, 2019 10:09 am

Re: datagrid form save / update sqlite

Post by paulalsmith1000 » Sat Aug 03, 2019 11:09 am

Hi Spark Out

Thanks for your reply, I think I may be getting there, but now I'm stuck on something that seems like it should be simple

I took your advise and am going to add another value to the array which tells me what row it came from in the DB and then use that to save it back the right one in the DB. However, I was just playing around to make sure I knew how it works, but I don't.

I already had something set up to save a chosen image from the datagrid back to the DB into a specific column, this saves the record in the column, but on a new row.

I presumed this was because I used the INSERT statement and so it treats it as a new record, so I tried UDATE instead and tried to specify the rowid, but I can't get this to work?

The attached screenshot shows the table - so the question is what is the correct syntax to get the data in the far right column to go in the first row - rowid 1 / column favourite_picture

Any help would be much appreciated

Kind regards

Paul
Attachments
Screenshot from 2019-08-03 11-05-46.png

SparkOut
Posts: 2834
Joined: Sun Sep 23, 2007 4:58 pm

Re: datagrid form save / update sqlite

Post by SparkOut » Sat Aug 03, 2019 1:17 pm

The syntax for your update query is

UPDATE survey_results SET species_image = '<theBase64EncodedData>' WHERE rowid = <theIdoftheRowtobeUpdated>

https://www.w3schools.com/sql/sql_ref_update.asp will give you more help, as will the whole site.

I assume I have got your table and data structure and types correct, please check I have understood correctly. You will need to correctly concatenate the query string to mix the literal strings with the variable content. Seeing your actual code snippets would help us to verify this for you.

paulalsmith1000
Posts: 58
Joined: Sat Jun 15, 2019 10:09 am

Re: datagrid form save / update sqlite

Post by paulalsmith1000 » Sun Aug 04, 2019 10:27 pm

Hi Spark Out

Thanks v much for the reply, I'm not quite sure how to edit the code you suggested to fit what I have, so as you suggest I have pasted a code snippet below:-

command takeImageFromDatagrid



put getDatabaseID() into tDatabaseID

put the dgHilitedLines of group "mydatagrid" into theLine
put the dgDataOfLine[theLine] of group "mydatagrid" into theDataA
put (theDataA["species_image"]) into timage2
put "INSERT into survey_results (favourite_picture) VALUES (:1)" after tSQL
revExecuteSQL tDatabaseID, tSQL, "timage2"


end takeImageFromDatagrid

This command runs when a button in the row template of the datagrid is clicked. Currently, as shown in previous screenshot it saves the image (already base64encoded) into the right column but as a new entry.

Could you possibly tell me how to use the UPDATE and rowid terms in above command?

Many thanks

Paul

SparkOut
Posts: 2834
Joined: Sun Sep 23, 2007 4:58 pm

Re: datagrid form save / update sqlite

Post by SparkOut » Sun Aug 04, 2019 10:56 pm

I don't see where you have identified the row ID in the datagrid, but let's say it's in the dgData as a column called "rowid", and matches the index column name in the SQLite table. Try:

Code: Select all

command takeImageFromDatagrid
   put getDatabaseID() into tDatabaseID

   put the dgHilitedLines of group "mydatagrid" into theLine
   put the dgDataOfLine[theLine] of group "mydatagrid" into theDataA
   put theDataA["species_image"] into timage2 --this I assume is the Base64Encoded data, not the binary
   put theDataA["rowid"] into tRowID
   put "UPDATE survey_results SET favourite_picture = :1 WHERE rowid =" & tRowID into tSQL
   --the WHERE clause is important or else this will affect ALL the rows in the table.
   
   --alternative version without placeholder:
   --put "UPDATE survey_results SET favourite_picture = '" & tImage2 & "' WHERE rowid =" & tRowID into tSQL
   -- note the single quotes included in the literal string contained in the double quotes above, which will be needed to surround the tImage2 data
   
   revExecuteSQL tDatabaseID, tSQL, "timage2"
   
   --alternative version without placeholder variable
   --revExecuteSQL tDatabaseID, tSQL
   
end takeImageFromDatagrid
I'm not quite clear why you would be putting the same picture data back into the same record under a different column name though? Here you are duplicating the image data in the species_image column and saving it in the same row in the favourite_picture column? That isn't what you actually want is it?

paulalsmith1000
Posts: 58
Joined: Sat Jun 15, 2019 10:09 am

Re: datagrid form save / update sqlite

Post by paulalsmith1000 » Mon Aug 05, 2019 6:30 am

Hi Spark Out

Thanks a million it worked perfectly.

I know it seems a bit odd to re save the image again, but there will be hundreds of images in the species column and it was the only way I could think of to make it easy to find the favourite image (as selected by the user after the survey) which would then be used to populate a certificate/report that will be emailed to them.

As a matter of etiquette, could you tell me, can I ask as many questions as I like on the forum? I've never really used them before, but it seems like a goldmine of information and otherwise I would plugging away for days trying work out the answer by myself.

Thanks again

Paul

BTW - slightly accidentally I realised that I could get the rowid directly from my sqlite DB without having a specific column for it, because the table autoi-ncrements and numbers each record.

paulalsmith1000
Posts: 58
Joined: Sat Jun 15, 2019 10:09 am

Re: datagrid form save / update sqlite

Post by paulalsmith1000 » Mon Aug 05, 2019 7:23 am

Hi again Spark Out

Erm, here we go again (apologies if this is to many questions)

From what you told me the saving back should now be fine, but the last bit (screenshot below) I could really do with a little direction:



The last DG on the this page is roughly the same format, but it has

the image on the left hand side
the rowid
the name of the species(just below the rowid)
then a scrolling list
then a tick box


The idea is that someone could go through each row and check if the species is correct. If the species is correct they just tick the checkbox, if not they select from the scrolling list and then tick the checkbox.

Each time the checkbox is ticked it then updates the row in the DB or puts it in another column called "species_confirmed"

The bit I'm a little stuck on is how to populate the scrolling list and how to use the checkbox

At the moment I have just edited the row template and put the scrolling list and checkbox in.

There is a bit of code in the scrolling list script which tells the list what to show (it is populated from a variable from a previous page)

I don't think this is the right way to do it though.

Again any help would be wonderful.

Kind regards

Paul
Attachments
Screenshot from 2019-08-05 07-22-56.png

SparkOut
Posts: 2834
Joined: Sun Sep 23, 2007 4:58 pm

Re: datagrid form save / update sqlite

Post by SparkOut » Mon Aug 05, 2019 7:43 am

Hi Paul
Firstly, you can ask as many questions as you like. Related to the same original enquiry, as here, continue to post on the same thread. But for a new subject, open a new thread. Expect a lot of answers, and a lot of help, but also do your own research and learn. Especially from the Scripting Conferences for LiveCode basics. In the case of learning about database queries, the w3schools link above is your best guide.

For the follow up question, I will get back to you later, I am on phone here and just headed out to site for work si can't get very in depth. I don't know exactly how you are working with this so it's hard to tell if it's for the best but maybe:

Have a boolean table column for "correct" and another for "confirmed".

Have two buttons on the form, "Correct" and "Incorrect"

When the reviewer clicks the "correct" button it also sets the "confirmed" box and writes both back to the table.

If the reviewer clicks the "Incorrect" button, it will do some list population (can't help without knowing more about your sources and working) to be selected. Then the confirmed flag can be set, and will also write "false" to the "correct" column.

You can then check who got what right and wrong, as well as recording the confirmed answer.

I may be miles off your intentions though.

paulalsmith1000
Posts: 58
Joined: Sat Jun 15, 2019 10:09 am

Re: datagrid form save / update sqlite

Post by paulalsmith1000 » Mon Aug 26, 2019 7:28 pm

Hi SparkOut

Thanks v much for the above (I did slightly avoid this for a while because I couldn't get anywhere) but now I've almost finished the app, so no choice :)

Could u possibly tell me if there would be any reason why the following happens:-

if i edit the row template group (datagrid form) and add a checkbox

then write in the script of the checkbox-

on mouseup
disable me
disable field "x"
end mouseup

it does this not only for whichever row i click on, but also for another one.

for example if I click the checkbox in row 1 it greys out the checkbox and field "x" in row 1 and 4???

Any light you could shed on this would be fab

(I can upload whatever you might need to see)

kind regards

Paul

SparkOut
Posts: 2834
Joined: Sun Sep 23, 2007 4:58 pm

Re: datagrid form save / update sqlite

Post by SparkOut » Fri Aug 30, 2019 12:05 am

This answer was rescued from where I posted before it gotceaten by the server move:

Firstly, no I am not sure what is going on. It must be that the first and fourth rows are being referenced the same somehow. I don't know if that is because of a script error, or some kind of mismatch or corruption of the index, dgIndex or other reference. 
What looks pretty much wrong is

Code: Select all

on mouseup
   disable me 
   disable field "x"
end mouseup
"Disable me" doesn't appear to reference the correct object, and if you disable it, then you can't mouseUp on it any more to run the script, but in any event there's no script to turn the disabled state off again. Without knowing exactly what you have got it should be more like

Code: Select all

on mouseup 
   set the disabled of field "myField" to the hilite of button "myCheckbox" of me 
end mouseup
but that's very much a guess, and I am not sure what exactly you need.

Post Reply

Return to “Data Grid Helper”