CODEWITHSUNDEEP
datagridlivecode
Sheils
Sheils

Reputation: 343

How to update database from change made in livecode datagrid

I have a datagrid in livecode. I want to update the data in the underlying database after the user have made a change in a row of the datagrid

Hi Malte,

That's looking very promising. The only problem is that the name field in the database table is not the same as the column name. I wanted to use an sql update statement to update the database. I tried the following code

on CloseFieldEditor pFieldEditor

--Connect to database
  databaseConnect
--Update Record

put  GetDataOfLine( the dgHilitedlines of me,"Pathogen") into strPathogen
put  GetDataOfLine( the dgHilitedlines of me,"Offset") into strIncubation
put  GetDataOfLine( the dgHilitedlines of me,"Duration") into     strDurationofIllness
put  GetDataOfLine( the dgHilitedlines of me,"ID") into IntID

put "UPDATE  tblPathogen SET fldPathogenName='" & strPathogen & "',     fldIncubation='" & strIncubation &  "', fldDurationofIllness='" &     strDurationofIllness & "'" into strSQL
put " WHERE fldPathogenID=" & IntID after strsql

put strsql into field "test"

--SaveDataToDatabase theTable, theRowID, theColumnBeingEdited, theNewText

end CloseFieldEditor

The problem is that the value in the grid change back to the original value before being assigned to the variables. How do I update the grid to hold the text of pFieldEditor

Upvotes: 0

Views: 571

Answers (1)

Malte Pfaff-Brill
Malte Pfaff-Brill

Reputation: 28

If you do not override the stndard datagrid behaviour by using your own template, a CloseFieldEditor message is sent to the grid control. You can use that message to react to the change of data and trigger a handler which stores the data in a database.

The problem here is though that you trap the handler that actually updates the datagrid. In a regular case the data of the datagrid is updated AFTER the message is executed. So in your case, you will want to update the data yourself...

Modified script:

on CloseFieldEditor pFieldEditor
   -- declared locals to make it compile in strict mode
   local strPathogen,strIncubation,strDurationOfIllness,intID,strSQL
   local theColumnBeingEdited,tLineDataArray
   local tNewText


   put the text of pFieldEditor into tNewText
   -- this holds the value the user entered

   put the dgColumn of the target into theColumnBeingEdited
   -- find the column that has been changed

   put the dgDataOfLine[the dgHilitedLine of me] of me into tLineDataArray
   -- get the whole line of data and put it into an array

   put tNewText into tLinedataArray[theColumnBeingEdited]
   -- update the col in the array accordingly

   set the dgDataOfLine[the dgHilitedLine of me] of me to tLineDataArray
   -- update data in grid

   --Connect to database
   databaseConnect
   --Update Record

   put  GetDataOfLine( the dgHilitedlines of me,"Pathogen") into strPathogen
   put  GetDataOfLine( the dgHilitedlines of me,"Offset") into strIncubation
   put  GetDataOfLine( the dgHilitedlines of me,"Duration") into     strDurationofIllness
   put  GetDataOfLine( the dgHilitedlines of me,"ID") into IntID

   put "UPDATE  tblPathogen SET fldPathogenName='" & strPathogen & "',     fldIncubation='" & strIncubation &  "', fldDurationofIllness='" &     strDurationofIllness & "'" into strSQL
   put " WHERE fldPathogenID=" & IntID after strsql

   put strsql into field "test"

   --SaveDataToDatabase theTable, theRowID, theColumnBeingEdited, theNewText

end CloseFieldEditor

This is explained quite nicely over here: http://lessons.runrev.com/m/datagrid/l/7337-how-do-i-save-changes-the-user-makes-in-an-editor-field-to-an-external-data-source

Keep in mind that the scripts are not optimized for strict compilation mode though (which I always recommend using, as it saves you from shooting your own foot). If you want the script to compile in strict mode, you would also need to declare the variables.

Upvotes: 1

Related Questions