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
Daniel
Hint:
12/07/2011: I edited the important parts!
Klaus