Alpha Video Training
Results 1 to 9 of 9

Thread: Trying to Update A Record in SQL

  1. #1
    Member mvaughn's Avatar
    Real Name
    Michael Vaughn
    Join Date
    Aug 2012
    Location
    Longview, Texas, USA
    Posts
    703

    Default Trying to Update A Record in SQL

    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

  2. #2
    Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,562

    Default 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..
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  3. #3
    Member mvaughn's Avatar
    Real Name
    Michael Vaughn
    Join Date
    Aug 2012
    Location
    Longview, Texas, USA
    Posts
    703

    Default 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

  4. #4
    Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,562

    Default 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 +" ' "
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  5. #5
    "Certified" Alphaholic
    Real Name
    Chris Tappan
    Join Date
    Jun 2009
    Location
    Marietta, GA
    Posts
    1,277

    Default Re: Trying to Update A Record in SQL

    Two options here:
    1) you use variables, in which case you don't need the ":":
    Code:
    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,
    Code:
    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:
    Code:
    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)
    Last edited by christappan; 12-08-2012 at 10:58 PM.

  6. #6
    Member mvaughn's Avatar
    Real Name
    Michael Vaughn
    Join Date
    Aug 2012
    Location
    Longview, Texas, USA
    Posts
    703

    Default 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!

  7. #7
    Member mvaughn's Avatar
    Real Name
    Michael Vaughn
    Join Date
    Aug 2012
    Location
    Longview, Texas, USA
    Posts
    703

    Default 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

  8. #8
    Moderator Steve Wood's Avatar
    Real Name
    Steve Wood
    Join Date
    Nov 2003
    Location
    Bay Area, California
    Posts
    8,827

    Default 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()
    Steve Wood
    Join the ALPHA DEVELOPERS NETWORK
    There is no Cloud. It's just someone else's computer.
    Web - Mobile - Hosting - Products - Frameworks - Developer Resources
    AlphaToGo | IADN (100% Alpha Anywhere Websites)

  9. #9
    Member -Jinx-'s Avatar
    Real Name
    Jinx
    Join Date
    Jul 2012
    Location
    Wisconsin
    Posts
    963

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

Similar Threads

  1. Record to update not found. It is possible that another user has deleted this record
    By Rich Hartnett in forum Application Server Version 11 - Web/Browser Applications
    Replies: 5
    Last Post: 08-20-2012, 05:42 PM
  2. Update Active Link Table and SQL reports SQL Statements from Xbasic?
    By compuaid in forum Alpha Five Version 11 - Desktop Applications
    Replies: 0
    Last Post: 05-01-2012, 07:27 AM
  3. SQL Update
    By cahalsall in forum Alpha Five Version 7
    Replies: 1
    Last Post: 06-03-2011, 10:32 AM
  4. SQL Update
    By jacklcs22 in forum Alpha Five Version 8
    Replies: 0
    Last Post: 10-13-2007, 02:03 AM
  5. SQL update
    By Roelof Elzinga in forum Alpha Five Version 6
    Replies: 4
    Last Post: 12-16-2004, 09:54 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •