Alpha Video Training
Results 1 to 11 of 11

Thread: SQL and ARGs help please

  1. #1
    Member
    Real Name
    John Berry
    Join Date
    Nov 2007
    Location
    Maryland, USA
    Posts
    643

    Default SQL and ARGs help please

    I'm embarrassed to say how many hours I've worked on this and variations of it and I'm no further in understanding why it's not working. The first code example works fine if I hardcode the CapSat_Main_Tbl.UID)=97, but it fails if I put in CapSat_Main_Tbl.UID)=:LastID


    Another thing, when I hardcode the number, it does add the records to the table but when stepping through the code it hits the line below highlighted. I'm not sure why it adds them anyway if supposedly there is an error.

    flag = cn.Execute(sqlInsertStatement)
    if flag = .f. then
    ui_msg_box("Error","Record was not inserted. Error reported was: " + crlf(2) + cn.CallResult.text,UI_STOP_SYMBOL)
    else
    if cn.AffectedRows() = 1 then
    ui_msg_box("Notice","Record was created.",UI_INFORMATION_SYMBOL)
    else
    ui_msg_box("Error","Record was not inserted." ,UI_STOP_SYMBOL)
    end if
    end if



    Can somebody see what's wrong with the code below?

    Thanks

    -John



    dim args as sql::arguments
    dim sqlInsertStatement as c

    args.add("LastID","UID")

    sqlInsertStatement = <<%txt%
    INSERT INTO CapSat_Scores_Horiz ( YR, CapSat_Main_ID_fk, Component, Element, Element_ID_fk, PC_ID_fk, Program_ID_fk, Partner_ID_fk )
    SELECT CapSat_Main_Tbl.YR, CapSat_Main_Tbl.UID, CapSat_Components_Tbl.Component_Name, CapSat_Elements_Tbl.Element, CapSat_Elements_Tbl.UID_Element, CapSat_Main_Tbl.PC_ID_fk, CapSat_Main_Tbl.PGM_ID_fk, CapSat_Main_Tbl.Partner_ID_fk
    FROM CapSat_Main_Tbl, CapSat_Elements_Tbl INNER JOIN CapSat_Components_Tbl ON CapSat_Elements_Tbl.Component_ID_fk = CapSat_Components_Tbl.UID_Component
    WHERE (((CapSat_Components_Tbl.ShowThis)=True) AND ((CapSat_Elements_Tbl.ShowThis)=True) AND ((CapSat_Main_Tbl.UID)=97))
    %txt%

  2. #2
    "Certified" Alphaholic mikeallenbrown's Avatar
    Real Name
    Mike Brown
    Join Date
    Nov 2009
    Location
    United States
    Posts
    1,803

    Default Re: SQL and ARGs help please

    You get used to the embarrassment after a while...

    This is the source of your trouble. You're making the argument, LastID, equal to the character string "UID".

    Code:
    args.add("LastID","UID")
    Code:
    CapSat_Main_Tbl.UID = 'UID'
    You can see this if you debug the code and run replace_arguments_in_string(sqlInsertStatement,args,3).

    If you do this I'm betting your code would work...

    Code:
    args.add("LastID",97)
    The question is what should be in it's place?
    Mike Brown - Contact Me
    Programmatic Technologies, LLC
    Programmatic-Technologies.com
    Independent Developer & Consultant

  3. #3
    "Certified" Alphaholic glenschild's Avatar
    Real Name
    Glen Schild
    Join Date
    Apr 2000
    Location
    Frome, Somerset, UK
    Posts
    1,507

    Default Re: SQL and ARGs help please

    Where you are adding the argument you are entering a character field whereas it looks like it should be an integer.

    Also in the execute statement you need to pass the arguments so this should change to cn.Execute(sqlInsertStatement,args)

    Finally personally I would use args.set instead of args.add
    Glen Schild



    My Blog


  4. #4
    "Certified" Alphaholic mikeallenbrown's Avatar
    Real Name
    Mike Brown
    Join Date
    Nov 2009
    Location
    United States
    Posts
    1,803

    Default Re: SQL and ARGs help please

    Quote Originally Posted by glenschild View Post
    Also in the execute statement you need to pass the arguments so this should change to cn.Execute(sqlInsertStatement,args)
    Nice catch! Didn't see that myself.
    Mike Brown - Contact Me
    Programmatic Technologies, LLC
    Programmatic-Technologies.com
    Independent Developer & Consultant

  5. #5
    Member
    Real Name
    John Berry
    Join Date
    Nov 2007
    Location
    Maryland, USA
    Posts
    643

    Default Re: SQL and ARGs help please

    Hi Mike,

    Oh jeez, so obvious when you look at it with a fresh set of eyes.

    I forgot to put a few more lines of code in there

    UID = convert_type(e.lastIdentityValue,"n")

    so the 97 is really supposed to be the value of the e.lastIdentityValue

    I'll go give it a shot.


    Hi Glenn,

    Yes, as I've been flip flopping around that has bitten me in the butt a few times.

    Thank you both.

    -John

  6. #6
    Member
    Real Name
    John Berry
    Join Date
    Nov 2007
    Location
    Maryland, USA
    Posts
    643

    Default Re: SQL and ARGs help please

    Mike, that worked! thanks. I also debugged and put the replace_arguments_in_string(sqlInsertStatement,args,3) and saw it was now using the correct number.

    Thanks again both of you.

    -John

  7. #7
    "Certified" Alphaholic
    Real Name
    Gregg Schmidt
    Join Date
    Mar 2001
    Location
    Milwaukee
    Posts
    1,332

    Default Re: SQL and ARGs help please

    I could be wrong, but [code]args.add("LastID","UID")] is actually setting the value for LastID to a character value of "UID".
    My guess is that UID is a variable meant to be set at "97" or perhaps "98", or even an integer value of 97 or 98.

    Based on this , I would use
    Code:
    args.set("LastID",UID)
    . This will set the value of the LastID to the value of the variable UID.

    I didn't see which version of SQL you're using, so I'm including samples using MS-SQL and MySQL.
    As long as LastID is truly an integer, the query should work with or without the single quotes as shown in the attached images.
    mssqlExample.PNG
    mysqlExample.png
    Hope this helps.

    Gregg

  8. #8
    Member
    Real Name
    John Berry
    Join Date
    Nov 2007
    Location
    Maryland, USA
    Posts
    643

    Default Re: SQL and ARGs help please

    Hi Gregg,

    Thanks. That was the problem. I had "UID" instead of UID.

    Glenn also mentioned using args.set instead of args.add, what's the difference between the two?

    Thanks

    -John

  9. #9
    "Certified" Alphaholic glenschild's Avatar
    Real Name
    Glen Schild
    Join Date
    Apr 2000
    Location
    Frome, Somerset, UK
    Posts
    1,507

    Default Re: SQL and ARGs help please

    From memory, args.add will add a new argument whereas args.set will update an existing argument if exists otherwise creates a new one.
    Glen Schild



    My Blog


  10. #10
    "Certified" Alphaholic
    Real Name
    Gregg Schmidt
    Join Date
    Mar 2001
    Location
    Milwaukee
    Posts
    1,332

    Default Re: SQL and ARGs help please

    Hi John,

    Glad to hear the problem is solved.

    I'm not sure about the difference between .add and .set, but I think .add is a legacy option.
    I've been using .set for years.

  11. #11
    Member
    Real Name
    John Berry
    Join Date
    Nov 2007
    Location
    Maryland, USA
    Posts
    643

    Default Re: SQL and ARGs help please

    Ok, got it. Thanks all for the help.

    -J

Similar Threads

  1. Args.set from Textbox Control Value
    By swest in forum Mobile & Browser Applications
    Replies: 4
    Last Post: 12-14-2017, 06:54 PM
  2. Get date into Args?
    By Keith Hubert in forum Alpha Five Version 11 - Desktop Applications
    Replies: 14
    Last Post: 08-03-2013, 12:41 PM
  3. Complicated args and sql
    By Sparticuz in forum Application Server Version 11 - Web/Browser Applications
    Replies: 5
    Last Post: 05-01-2012, 11:47 AM
  4. Args.DataSubmitted.Rn.varablename fails
    By oldtony in forum Application Server Version 8
    Replies: 1
    Last Post: 09-15-2008, 06:29 PM
  5. Executing an external program with args.
    By Larry Treachler in forum Alpha Five Version 5
    Replies: 8
    Last Post: 10-14-2003, 11:46 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
  •