Updating MySQL database from DataGrid

[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

[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=" &quote& tFirstName &quote & comma & \
"last_name=" &quote& tLastName &quote& comma &&\
"mobile_number="  &quote& tMob &quote& comma &&\
"office_address="  &quote& tAddress &quote& comma &&\
"thm_img="  &quote& tImageData &quote &&\
"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

[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"

[bangkok]

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.

[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.

[shawnblc]

By chance is there an example stack of a datagrid being used that updates a mysql database when the datagrid is edited inline?