[Beginner] - Need some assistance on this code plz

[shawnblc]

Needs some assistance on this SQL query. On my card I have fldA and fldB. In my database I have dbfld, dbfldA and dbfldB.

Code: Select all

      put "UPDATE"  &&TABLENAME && "SET dbfld = '1' WHERE dbfldA = 'fldA' AND dbfldB = 'fldB'" into tSQL

[bangkok]

Needs some assistance on this SQL query. On my card I have fldA and fldB. In my database I have dbfld, dbfldA and dbfldB.

Code: Select all

      put "UPDATE"  &&TABLENAME && "SET dbfld = '1' WHERE dbfldA = 'fldA' AND dbfldB = 'fldB'" into tSQL

-first rule : use meaningful name (dbflda... prone to errors), it's easier to read, to debug, and to remember

-second rule : when a SQL query doesn't work... ask LiveCode to "print" it, like :

Code: Select all

answer tSQL

So you can see the final result, sent to the database server, and see clearly what's wrong.

-third rule : you have to use "&" to concatenate a text and the content of a field, like :
put "HELLO " & fld "flda" & " HOW ARE YOU" ?

-fourth rule : watch out with single and double quotes. LiveCode need double quotes, and SQL needs single.
So to summarize :

Code: Select all

 put "UPDATE"  &&TABLENAME && "SET dbfld = '1' WHERE dbfldA = '" & fld "fldA" & "' AND dbfldB = '" &fld "fldB" & "'" into tSQL
answer tSQL

[shawnblc]

Thank you bangkok. I appreciate the rules you outlined, that'll help me in the future as I continue learning this beast. And your solution works too.

I also have this code, but I'm always getting record updated. I tried to use is not a number, but then I always get an error.

Code: Select all

   if the result is a number then
      answer info "Record Updated."

tried, which isn't working the way I anticipate either. which i guess ideally if there's an error i want an error to show and if there's no error to proceed.

Code: Select all

if tSQL contains "revdberr" then
answer warning "error"

[shawnblc]

Got it working the way I need it. Whether or not it's the way you more experienced users would do it. I don't know. But it's working. I'd be interested in how someone with experience would do this though bangkok thanks again for the response above. it helps a great deal. Lots of nice and knowledgeable guys/gals on this board. Makes me glad I was part of the KS campaign.


if the result = 0 then

code
else
code
end if

Note to other beginners like me: 0 is returned when no rows are effected.