Possible issue with my synax? livecode or mySQL?

[sms5138]

Hi everyone,

I have tried writing this several different ways, but seem to get a different flavor of the same error each time. I'm looking to make it easier for HR to update IT with information they have regarding Employee computers, email changes, logins, etc.

I've checked the mySQL command, and believe I have it correct (and see no errors in my livecode script) but do not seem to be getting anywhere. Any advice would be great! I've put the code below:

Code: Select all

on mouseUp
   global gConnectionID
    if gConnectionID is not a number then
        answer error "Please connect to the database first."
        exit to top
    end if

    put "status" into tTableName
    put "email, login, ext, vm, compnum, os, hardware, sftinstalled, update, test" into tFields
    /*put field "em" into tEmail
    put field "lg" into tLogin
    put field "ext" into tExte
    put field "vmail" into tVm
    put field "compn" into tCompnum
    put field "osys" into tOs
    put field "hwar" into tHardware
    put field "sftin" into tSftinstalled
    put field "upd" into tUpdate
    put field "tst" into tTest*/

    put "UPDATE status SET email='"& field"em"&"', SET login='"& field"lg"&"', SET ext='"& field"exte"&"', SET vm='"& field"vmail"&"', SET compnum='"& field"compn"&"', SET os='"& field"osys"&"', SET hardware='"& field"hwar"&"', SET sftinstalled='"& field"sftin"&"', SET upda='"& field"upda"&"', SET test='"& field"tst"&"'  WHERE name='"& selectedtext of field"who9"&"'" into tSQL

    revExecuteSQL gConnectionID, tSQL, "tEmail", "tLogin", "tExte", "tVm", "tCompnum", "tOs", "tHardware", "tSftinstalled", "tUpdate", "tTest"

    if the result is a number then
       answer info "update complete."
    else
        answer error "There was a problem adding the record to the database:" & cr & the result
    end if
end mouseUp

Thanks in advance!

-Sean

[sms5138]

Well i'm a bit embarrassed. I've figured out where my error was. It was a silly mistake in mySQL.

Code: Select all

   put "UPDATE status SET email='"& field"em"&"', login='"& field"lg"&"', ext='"& field"exte"&"', vm='"& field"vmail"&"', compnum='"& field"compn"&"', os='"& field"osys"&"', hardware='"& field"hwar"&"', sftinstalled='"& field"sftin"&"', upda='"& field"upda"&"', test='"& field"tst"&"'  WHERE name='"& selectedtext of field"who9"&"'" into tSQL

I put SET in after each field name, but it was only needed one.

[dave.kilroy]

Sean I've found that 99% of my coding errors originate somewhere between my chair and my computer - you would NOT BELIEVE the number of asinine things that are birthed there...

[sms5138]

haha i'm quickly coming to that realization myself.

[phaworth]

I know you found the problem but I think you could make your SQL statement much easier to read. You're including the values of the fields in the SQL but also naming the variables that hold those same values in your call to revExecuteSQL. I think youyr SQL could be as follows with no change to the revExecuteSQL call

Code: Select all

put "UPDATE status SET email=:1', login=:2, ext=:3, vm=:4', compnum=:5, os=:6, hardware=:7, sftinstalled=:8, upda=:9, test=:10  WHERE name='"& selectedtext of field"who9"&"'" into tSQL

As long as the variables you name in revExecute are in the same order as the column names in the UPDATE statement, that should all work. Using the :n notation also protects you from SQL injection attacks and removes any need to escape quote characters in the data.

HTH,

Pete

[sms5138]

I really appreciate it Pete. It does make it easier to read that way.

I've been wanting to clean up my code, and find shorter ways to do the same thing. this was really helpful.