stuck again why isnt this update working

[kcwvc52]

i took this code from a working example. i do not understand why it works one place but doesn't work in another any suggestion on how to make this work would be much appreciated.

Code: Select all

 
global dataid
on mouseUp
   put card field "name" into updatename
    put card field "blah" into updateblah
    put "UPDATE user SET blah="& updateblah &    "WHERE name=" & updatename   into tSQL
 revExecuteSQL dataid, tSQL
   
   
end mouseUp

[dglass]

First guess is that the strings aren't escaped, and are being considered column names by the DB engine.

You should put a breakpoint in there and check both tSQL before the DB call, and the result after the DB call to see what the error is.

EDIT: and there aren't enough spaces in your SQL. Given your variable values and SQL statement, what you are sending to the DB is this:

UPDATE user SET blah=blahWHERE name=name

What you want is probably something like:

UPDATE user SET blah = 'blah' WHERE name = 'name'

[kcwvc52]

got it thanks the code should have been

Code: Select all

global dataid
on mouseUp
   put card field "name" into updatename
    put card field "blah" into updateblah
    put "UPDATE user SET blah = ' " & updateblah & " '  WHERE name = ' " & updatename &" ' " into tSQL
   
revExecuteSQL dataid, tSQL 
   
   
end mouseUp

this works properly. makes much more sense now

[dglass]

Code: Select all

global dataid
    put "UPDATE user SET blah = ' " & updateblah & " '  WHERE name = ' " & updatename &" ' " into tSQL
   

this works properly. makes much more sense now

If it's working now, it's probably coincidental.

You are now sending this to the DB engine:

UPDATE user SET blah = ' blah ' WHERE name = ' name '

Note the leading and trailing spaces around your variable values.

So, a) your 'blah' value is going to get inserted with leading and trailing spaces, and your 'name' value is only going to match if it has leading and trailing spaces.

[kcwvc52]

that is interesting because when i told into answer tSQL

UPDATE user SET blah = '3.7' WHERE name = 'george'

but when i told it to go into another field it did this

UPDATE user SET blah = ' 3.7 ' WHERE name = ' george '

you are saying this could cause problems right? this isn't an actual program i am making i am just trying to learn how i can use sqlite with the programs i am making. please explain how this could mess things up. and did livecode just do the fixing for me in that situation?

[dglass]

Yes, I believe those extra spaces will cause problems.

I don't know why it would display one way in an answer dialog, and a different way in a field.

[kcwvc52]

that is confusing but i got it working thanks to your help and i have now corrected it to make sure it does not cause problems. do you know if livecode is capable of connecting to multiple databases at once as long as they have different connection variables?

[Klaus]

Hi kcwvc52,

do you know if livecode is capable of connecting to multiple databases at once as long as they have different connection variables?

Yes, I know








Just kidding, yes, you can connect to multiple database at once.


Best

Klaus

[kcwvc52]

great thanks guys this is tons of help!!!