Page 1 of 1
Updating MySQL database from DataGrid
Posted: Mon Mar 17, 2014 4:52 am
by shawnblc
I can query my MySQL database and show that data in the DataGrid, but having trouble updating the database from the DataGrid.
I'm trying to do something like this:
Code: Select all
## Query the database for data
put revQueryDatabase( theConnectionID, "UPDATE mammals SET the hilitedline WHERE id=id") into theCursor
Re: Updating MySQL database from DataGrid
Posted: Mon Mar 17, 2014 5:14 am
by Simon
Hi shawnblc,
Check out "revExecuteSQL" in the dictionary and use;
http://www.w3schools.com/sql/default.asp
You are looking for "Update"
Ends up looking like;
Code: Select all
put "UPDATE " & tTableName && "SET first_name=" "e& tFirstName "e & comma & \
"last_name=" "e& tLastName "e& comma &&\
"mobile_number=" "e& tMob "e& comma &&\
"office_address=" "e& tAddress "e& comma &&\
"thm_img=" "e& tImageData "e &&\
"WHERE id=" & tID into tSQL
revExecuteSQL gConnectionID, tSQL, "tFirstName", "tLastName", "tMob", "tAddress","tImageData"
There is another way where you use VALUES 1;2;3; or something like that. I haven't been able to find the docs on why to use VALUES but it does have something to do with security. It's no simpler to code.
Simon
Re: Updating MySQL database from DataGrid
Posted: Mon Mar 17, 2014 1:59 pm
by shawnblc
Not getting this to work, LC gives me the error in the image.
Code: Select all
put "UPDATE " & mammals & "SET fldwhatis = fld 'fldwhatis' WHERE fldwordis = '" &fld "fldwordis" & "' AND fldpic = '" &fld "fldpic" & "'" into tSQL
revExecuteSQL gConnectionID, tSQL, "fldwhatis","fldwordis","fldpic"
Re: Updating MySQL database from DataGrid
Posted: Mon Mar 17, 2014 3:05 pm
by bangkok
shawnblc wrote:Not getting this to work, LC gives me the error in the image.
Code: Select all
put "UPDATE " & mammals & "SET fldwhatis = fld 'fldwhatis' WHERE fldwordis = '" &fld "fldwordis" & "' AND fldpic = '" &fld "fldpic" & "'" into tSQL
revExecuteSQL gConnectionID, tSQL, "fldwhatis","fldwordis","fldpic"
-You are mixing the regular way and the way with variable list (":1", ":2") [look documentation]. It can't work.
-if mammals contains the name of your table then you forgot a space before SET (sql will read "update lionSET" for instance, which will throw an error)
-plus probably, you made a mistake with the name of one your fields (hence the error message "no such object")
Try the regular way first :
Code: Select all
put "UPDATE lion SET fldwhatis ='"&fld "fldwhatis" &"' WHERE fldwordis = '" &fld "fldwordis" & "' AND fldpic = '" &fld "fldpic" & "'" into tSQL
answer tSQL
revExecuteSQL gConnectionID, tSQL
Provided :
-if fldwhatis, fldwordis, fldpic are proper column name
-and if those fields exist in your stack
In any case, do a "answer tSQL" so you'll be able to read what is actually sent to your MySQL server, making debug operation easier.
Re: Updating MySQL database from DataGrid
Posted: Mon Mar 17, 2014 6:42 pm
by shawnblc
I'm learning. Haven't got it working yet, but if I have to spend another day trying to figure it out ... I will. My SQL queries look fine, I'm connecting to the database, just the datagrid isn't staying updated when I refresh the datagrid.
Re: Updating MySQL database from DataGrid
Posted: Wed Mar 19, 2014 11:15 am
by shawnblc
By chance is there an example stack of a datagrid being used that updates a mysql database when the datagrid is edited inline?