Trying to Update A Record in SQL

[mvaughn]

Hi all,
I'm trying to update a MySQL record and I keep getting an error at my sqlInsert line saying invalid entry handle. I'm sure its a syntax thing. Could you point me in the right direction please?

As always,
Thanks in Advance!


sqlInsert="UPDATE appdata1 SET" +
"majormed = " + :f1 + "," +
"accidentinsur = " + :f2 + "," +
"dental = " + :f3 +
"WHERE add_email = " + :vUser

flagResult = cn.open("::Name::ABLEDB")
if flagResult = .f. then
e.javascript = "alert('Error : could not connect to the database - contact support');"
end
end if

flag = cn.Execute(sqlInsert)
if flag = .f. then
cn.Close()
msg = "Could not delete record. Error reported was: " + cn.CallResult.text
msg = js_escape(msg)
jscmd = "alert('" + msg + "');"
exit function
end if

[Al Buchholz]

Re: Trying to Update A Record in SQL

Is this thread related?

http://msgboard.alphasoftware.com/al...te-Help-Please

I don't see how the arguments are incorporated into the sql statement...

There should be an example of using arguments..

[mvaughn]

Re: Trying to Update A Record in SQL

Thank you Al
My variables are logical 0/1 for yes/no

dim f1 as c = e.datasubmitted.majormed
dim f2 as c = e.datasubmitted.accidentinsur
dim f3 as c = e.datasubmitted.dental
dim vUser as c = e.datasubmitted.email

[Al Buchholz]

Re: Trying to Update A Record in SQL

I suggest that you use the debug(1) function to see what is in the sqlinsert variable.

or use the interactive window.

a couple of potential issues are

1. you need a space between SET and majormed
2. quotes around a character value

sqlInsert="UPDATE appdata1 SET " +
"majormed = " + :f1 + "," +
"accidentinsur = " + :f2 + "," +
"dental = " + :f3 +
"WHERE add_email = ' " + :vUser +" ' "

[christappan]

Re: Trying to Update A Record in SQL

Two options here:
1) you use variables, in which case you don't need the ":":

dim f1 as c = e.datasubmitted.majormed
dim f2 as c = e.datasubmitted.accidentinsur
dim f3 as c = e.datasubmitted.dental
dim vUser as c = e.datasubmitted.email 
sqlInsert="UPDATE appdata1 SET " +
"majormed = " + f1 + "," +
"accidentinsur = " + f2 + "," +
"dental = " + f3 +
" WHERE add_email = '" + vUser +"'"

or more simply,

dim f1 as c = e.datasubmitted.majormed
dim f2 as c = e.datasubmitted.accidentinsur
dim f3 as c = e.datasubmitted.dental
dim vUser as c = e.datasubmitted.email 
sqlInsert="UPDATE appdata1 SET majormed = " + f1 + ", accidentinsur = " + f2 + ", dental = " + f3 + " WHERE add_email = '" + vUser + "'"

or 2) use arguments:

dim args as sql::arguments
args.add("f1",e.datasubmitted.majormed)
args.add("f2",e.datasubmitted.accidentinsur)
args.add("f3",e.datasubmitted.dental)
args.add("vUser",e.datasubmitted.email)
sqlInsert="UPDATE appdata1 SET majormed = :f1, accidentinsur = :f2, dental = :f3 WHERE add_email = :vUser"

cn.Execute(sqlInsert,args)

[mvaughn]

Re: Trying to Update A Record in SQL

OMG! Chris you are a saint!
I have literally spent 12+ hours working on this today and I am so grateful that you took the time to help!
I plugged in the code from box 1B above and it worked without modification. It was sooo nice to finally see some 1's in my record instead of default 0's. Once I get my code cleaned up I will post the whole thing for others to learn from.

Again,
Thank You!

[mvaughn]

Re: Trying to Update A Record in SQL

As promised, here is my working code for an MySQL Update Record using a Dialog2 Control:

function afterDialogValidate as v (e as p)
'debug(1)

dim cn as SQL::Connection
dim flagResult as c
dim msg as c
dim jscmd as c
dim f1 as c = e.datasubmitted.majormed
dim f2 as c = e.datasubmitted.accidentinsur
dim f3 as c = e.datasubmitted.dental
dim f4 as c = e.datasubmitted.hospitalconfine
dim f5 as c = e.datasubmitted.life
dim f6 as c = e.datasubmitted.cancer
dim f7 as c = e.datasubmitted.vision
dim f8 as c = e.datasubmitted.shortmed
dim f9 as c = e.datasubmitted.other
dim f10 as c = e.datasubmitted.besttimecallinsurance
dim f11 as c = e.datasubmitted.gen_applyb4
dim f12 as c = e.datasubmitted.gen_dateapplied
dim f13 as c = e.datasubmitted.gen_empb4
dim f14 as c = e.datasubmitted.gen_weed
dim f15 as c = e.datasubmitted.gen_quit
dim f16 as c = e.datasubmitted.gen_car
dim f17 as c = e.datasubmitted.gen_childcare
dim f18 as c = e.datasubmitted.gen_workus
dim f19 as c = e.datasubmitted.gen_ovr18
dim f20 as c = e.datasubmitted.gen_criminal
dim f21 as c = e.datasubmitted.gen_crimexplain
dim f22 as c = e.datasubmitted.gen_felony
dim f23 as c = e.datasubmitted.gen_felonexplain
dim f24 as c = e.datasubmitted.gen_accident
dim f25 as c = e.datasubmitted.genaccidentexplain
dim f26 as c = e.datasubmitted.gen_30d
dim f27 as c = e.datasubmitted.gen_meds
dim f28 as c = e.datasubmitted.gen_dob
dim f30 as c = e.datasubmitted.gen_sex
dim f31 as c = e.datasubmitted.gen_dateavail
dim sInsert as c ="UPDATE appdata1 SET majormed = " + f1 + ", accidentinsur = " + f2 + ", dental = " + f3 + ", hospitalconfine = " + f4 + ", life = " + f5 + ", cancer = " + f6 + ", vision = " + f7 + ", shortmed = " + f8 + ", other = " + f9 + ", besttimecallinsurance = " + f10 + ", gen_applyb4 = " + f11 + ", gen_dateapplied = " + f12 + ", gen_empb4 = " + f13 + ", gen_weed = " + f14 + ", gen_quit = " + f15 + ", gen_car = " + f16 + ", gen_childcare = " + f17 + ", gen_workus = " + f18 + ", gen_ovr18 = " + f19 + ", gen_criminal = " + f20 + ", gen_crimexplain = " + f21 + ", gen_felony = " + f22 + ", gen_felonexplain = " + 23 + ", gen_accident = " + f24 + ", genaccidentexplain = " + f25 + ", gen_30d = " + f26 + ", gen_meds = " + f27 + ", gen_dob = " + f28 + ", gen_sex = " + f29 + ", gen_dateavail = " + f30 + " WHERE add_socialsecurity = '" + vUser + "'"

flagResult = cn.open("::Name::ABLEDB")
if flagResult = .f. then
e.javascript = "alert('Error : could not connect to the database - contact support');"
end
end if

flag = cn.Execute(sInsert)
if flag = .f. then
cn.Close()
msg = "Could not delete record. Error reported was: " + cn.CallResult.text
msg = js_escape(msg)
jscmd = "alert('" + msg + "');"
exit function
end if

cn.close()

end function

[Steve Wood]

Re: Trying to Update A Record in SQL

You really should use arguments in your script because your entries come from a data-entry form and is subject to "sql injection" errors. Here is my shot, minus some error checking.

dim cn as sql::Connection
dim args as sql::Arguments
dim vSql as c = <<%txt%
UPDATE appdata1 SET
majormed =:majormed
accidentinsur =:accidentinsur
dental =:dental
%txt%
args.add("majormed", e.datasubmitted.majormed)
args.add("accidentinsur",e.datasubmitted.accidentinsur)
args.add("dental",e.datasubmitted.dental)

cn.open("::Name::ABLEDB") '== I don't always error check this because it is unlikely to fail, and the execute function below will catch the error

if cn.execute(vSql,args) = .f.
'==your error reporting routine here
cn.close()
end if
cn.close()

[-Jinx-]

Re: Trying to Update A Record in SQL

^ +1
Args are really easy to use once you get used to them (none of that "SELECT" +var+ " stuff that throws you off in long statements) and more secure.