Updating mySQL with Datagrid data

Anything beyond the basics in using the LiveCode language. Share your handlers, functions and magic here.

Moderators: Klaus, FourthWorld, heatherlaine, robinmiller, kevinmiller

Post Reply
bsouthuk
Posts: 261
Joined: Fri Dec 05, 2008 7:25 pm

Updating mySQL with Datagrid data

Post by bsouthuk » Tue Dec 06, 2011 8:03 pm

Hi All

I am trying to update the data that is stored in mySQL with the data that is in my datagrid. Where the data in column "ID" in mySQL matches the data in column "ID" of my datagrid, I want the data in column "Customer" of my datagrid updated in column "Customer" of mySQL table and so far I have the following code:

Code: Select all

on mouseUp
//CONNECT TO DATABASE

-- use a global variable to hold the connection ID so other scripts can use it
global gConnectionID

-- set up the connection parameters - edit these to suit your database
put "XXXXX" into tDatabaseAddress
put "XXXXXX" into tDatabaseName
put "XXXXX" into tDatabaseUser
put "XXXXX" into tDatabasePassword

-- connect to the database
put revOpenDatabase("MySQL", tDatabaseAddress, tDatabaseName, tDatabaseUser, tDatabasePassword) into tResult

-- check if it worked and display an error message if it didn't
-- & set the connection ID global
if tResult is a number then
put tResult into gConnectionID
//answer info "Proposal Generated." & cr & "Connection ID = " & gConnectionID
else
put empty into gConnectionID
end if

//ADD TO DATABASE


-- check the global connection ID to make sure we have a database connection
global gConnectionID
if gConnectionID is not a number then
answer error "Please connect to the database first."
exit to top
end if

 put the dgText of group "Upgrade" into tUpgrade
set the itemdelimiter to tab

repeat with y = 1 to the number of lines of tUpgrade

   put "UPDATE MobileInfo Login SET AccountManager=" & "'" & (item 2 of line y of tUpgrade) & "'" & "WHERE ID=" & "'" & (item 1 of line y of tUpgrade) & "'" into tSQL
   
   end repeat

-- send the SQL to the database, filling in the placeholders with data from variables
revExecuteSQL gConnectionID, tSQL

-- check the result and display the data or an error message
if the result is a number then
answer info "Handset Cost Updated."
else
answer error "There was a problem updating the record to the database:" & cr & the result
end if

//DISCONNECT FROM DATABASE

global gConnectionID

-- if we have a connection, close it and clear the global connection ID
if gConnectionID is a number then
revCloseDatabase gConnectionID
put empty into gConnectionID
end if

end mouseUp

Problem with the code is that only the last line of the datagrid updates my mySQL table. I have used the repeat loop for each line of the datagrid but still only the last line updates. Can anyone see what I have done wrong here?

Daniel

Hint:
12/07/2011: I edited the important parts!
Klaus

mwieder
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 2786
Joined: Mon Jan 22, 2007 7:36 am
Location: Berkeley, CA, US
Contact:

Re: Updating mySQL with Datagrid data

Post by mwieder » Tue Dec 06, 2011 9:03 pm

Daniel-

You're overwriting you tSQL variable each time through the repeat loop, then executing the SQL. Try putting the revExecuteSQL line inside the repeat loop.

Code: Select all

repeat with y = 1 to the number of lines of tUpgrade

      put "UPDATE MobileInfo Login SET AccountManager=" & "'" & (item 2 of line y of tUpgrade) & "'" & "WHERE ID=" & "'" & (item 1 of line y of tUpgrade) & "'" into tSQL

      -- send the SQL to the database, filling in the placeholders with data from variables
      revExecuteSQL gConnectionID, tSQL
   
   end repeat

bangkok
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 829
Joined: Fri Aug 15, 2008 7:15 am

Re: Updating mySQL with Datagrid data

Post by bangkok » Wed Dec 07, 2011 6:29 am

FOR CHRIST SAKE !

DON'T GIVE THE ADDRESS, THE LOGIN AND THE PASSWORD OF YOUR DATABASE !!!!!

You should change password immediately.

mwieder
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 2786
Joined: Mon Jan 22, 2007 7:36 am
Location: Berkeley, CA, US
Contact:

Re: Updating mySQL with Datagrid data

Post by mwieder » Wed Dec 07, 2011 7:16 am

...and edit your post... thanks, bangkok - I didn't even notice that part.

bsouthuk
Posts: 261
Joined: Fri Dec 05, 2008 7:25 pm

Re: Updating mySQL with Datagrid data

Post by bsouthuk » Thu Dec 08, 2011 7:16 pm

oops yes that was silly of me.

Thank you very much for your help!

montymay
Posts: 125
Joined: Thu Jul 18, 2013 5:23 am

Re: Updating mySQL with Datagrid data

Post by montymay » Sun Mar 24, 2019 1:41 am

Hello

My problem is similar to the original problem in this thread, so I will post my question here. Rather than a datagrid, I am having no success in updating the first field of each group of six groups within a containing group named "digest." My database is a MySQL database accessed via WAMP. I definitely made a connection to my MySQL database. The following code works when performing a single updating operation on my MySQL database, for example:

Code: Select all

   put fld "id" into tID
   put fld "judge" into tJudge
   put "UPDATE MyTable SET judge = '"&tJudge&"' WHERE id = '"&tID&"'" into tSQL
   revExecuteSQL gCourts_DatabaseID, tSQL
The following repeat loop worked when the database was sqlite, but not on the MySQL database. It has no effect. (Explanation: the name of the field is the same as the group that owns it):

Code: Select all

   repeat with x = 1 to 6
      put the name of grp x of grp "digest" into tField[x]
      put fld 1 of grp x of grp "digest" into tNewText[x]
      put "UPDATE MyTable SET '"&tField[x]&"' = '"&tNewText[x]&"' WHERE id='"&tID&"'" into tSQL
      revExecuteSQL gCourts_DatabaseID, tSQL
   end repeat
Could it be the single quote/double quote syntax? I noticed that the query is written differently in the above example:
AccountManager=" & "'" & (item 2 of line y of tUpgrade) & "'" & "
Changing the syntax to this pattern did not work for me either. Thanks for any replies.

Monty

Post Reply

Return to “Talking LiveCode”