Announcement

Collapse
No announcement yet.

Updating form fields a different table

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Updating form fields a different table

    I am developing a project in which a form pulls data from a table named ResourceRequests. It includes fields for the user's telephone numbers, which are already stored in the sec_users table. When the form is loaded, I would like the initial value of the contact information fields in this form to be set from the values in the sec_users table.

    I added some lines of code in the Events > onValidate section of the sec_Login application to create global variables from the desired fields in the sec_users table. (These lines are marked below with the comment "# ADDED LINE".) However, after adding this code, when I ran the login application and entered my login credentials, the application generated the error message "Sorry, but user is not active!" I then deleted the added lines, and I was able to login successfully. I find this a complete mystery.

    How can I set these variables?

    Code:
    $slogin = sc_sql_injection({login});
    $spswd = sc_sql_injection(({pswd}));
    
    $sql = "SELECT
    priv_admin,
    active,
    name,
    email,
    uOfficePhone, # ADDED LINE
    uOfficeExt, # ADDED LINE
    uOfficeMobilePhone # ADDED LINE
    FROM sec_users
    WHERE login = $slogin
    AND pswd = ".$spswd."";
    
    sc_lookup(rs, $sql);
    
    if(count({rs}) == 0)
    {
    sc_log_add('login Fail', {lang_login_fail} . {login});
    sc_error_message({lang_error_login});
    sc_error_exit();
    }
    else if({rs[0][1]} == 'Y')
    {
    $usr_login = {login};
    $usr_priv_admin = ({rs[0][0]} == 'Y') ? TRUE : FALSE;
    $usr_name = {rs[0][2]};
    $usr_email = {rs[0][3]};
    $usr_off_phone = {rs[0][4]};  # ADDED LINE
    $usr_off_ext = {rs[0][5]};  # ADDED LINE
    $$user_mobile_phone = {rs[0][6]};  # ADDED LINE
    
    sc_set_global($usr_login);
    sc_set_global($usr_priv_admin);
    sc_set_global($usr_name);
    sc_set_global($usr_email);
    sc_set_global($usr_off_phone);  # ADDED LINE
    sc_set_global($usr_off_ext);  # ADDED LINE
    sc_set_global($usr_mobile_phone);  # ADDED LINE
    }
    else
    {
    sc_error_message({lang_error_not_active});
    sc_error_exit();
    }

  • #2
    Did you add #added line for demonstration here or did you actually added this into your code? It's between your sql statement and afaik it will not be removed from there. You have a syntax error in your sql. What you might try is to set all debugging options in application menuentry to on. Running the app will show you the generated sql to look at.
    Albert Drent
    aducom software netherlands
    scriptcase partner, reseller, support and (turn-key) development
    www.scriptcase.eu / www.scriptcase.nl

    Comment


    • #3
      Originally posted by aducom View Post
      Did you add #added line for demonstration here or did you actually added this into your code? It's between your sql statement and afaik it will not be removed from there.
      I added the comments just for demonstration purposes in the quoted code. The comments do not exist in the code in the application.

      Originally posted by aducom View Post
      You have a syntax error in your sql. What you might try is to set all debugging options in application menuentry to on. Running the app will show you the generated sql to look at.
      I am surprised that there is a syntax error in the sql, since I based my sec_Login application on the sec_Login application that is included in the security module provided by ScriptCase. I compared my code line-by-line with the code in the original login application from ScriptCase, and I believe that I introduced no other changes than the lines that I added, which I based on existing lines in the original application. My login application appeared to work as intended before I added the lines that I marked with comments, and it worked after I once again removed them.

      Are you suggesting that there is a syntax error in the sql included in the original sec_Login application from ScriptCase? Or is it in the lines that I added? I will set the debugging options in the morning and see what I can find, but can you tell me what the sql syntax error is?

      I appreciate your help!

      Comment


      • #4
        Hi Eric,

        as I see, you are posting two problems. For sure as I see, you got your second problem caused by the way to solve the first one. (my opinion ) So, let me describe how I would solve what you tried with the globals you set in the login-form.

        Why do you want to do that? May be I did not caught the reason, but you can also put a short SELECT statement in the starting event of the form, in which you like to set these information as initial values. In my eyes, this would make more sense ... Just put a small SELECT statement in Form Settings-> Events -> OnLoad and write the values directly in the fields.

        I am sure, this would solve your starting problem. The second problem, I do not see a solution at the moment. Would be intering to know what the problem is. My first idea, the sequence of the vars in your select statement is not the mistake. Did you tried that behaviour (not active) with different user?

        At the end, I want to give one useful hint. Do not touch this security system with any code. Even these globals you set, it would be better to set somewhere else. In case you like or you have to setup the securoty module again, I am expecting this code to be gone.

        Best regards

        Eric

        Comment


        • #5
          Maybe the comments inside the variable $sql is the problem

          ****Example from Security_sc Control App, Loging - Event - Validation****



          Originally posted by EricDahl View Post
          I am developing a project in which a form pulls data from a table named ResourceRequests. It includes fields for the user's telephone numbers, which are already stored in the sec_users table. When the form is loaded, I would like the initial value of the contact information fields in this form to be set from the values in the sec_users table.

          I added some lines of code in the Events > onValidate section of the sec_Login application to create global variables from the desired fields in the sec_users table. (These lines are marked below with the comment "# ADDED LINE".) However, after adding this code, when I ran the login application and entered my login credentials, the application generated the error message "Sorry, but user is not active!" I then deleted the added lines, and I was able to login successfully. I find this a complete mystery.

          How can I set these variables?

          Code:
          $slogin = sc_sql_injection({login}); 
          $spswd = sc_sql_injection(({pswd}));
          
          $sql = "SELECT
          priv_admin,
          active,
          name,
          email,
          uOfficePhone, # ADDED LINE **** Do not write comments in the sql statement
          uOfficeExt, # ADDED LINE
          uOfficeMobilePhone # ADDED LINE
          FROM sec_users
          WHERE login = $slogin
          AND pswd = ".$spswd."";
          
          sc_lookup(rs, $sql);
          
          if(count({rs}) == 0)
          {
          sc_log_add('login Fail', {lang_login_fail} . {login});
          sc_error_message({lang_error_login});
          sc_error_exit();
          }
          else if({rs[0][1]} == 'Y')
          {
          $usr_login = {login}; //SC example code line
          $usr_priv_admin = ({rs[0][0]} == 'Y') ? TRUE : FALSE; //SC example code line
          $usr_name = {rs[0][2]}; //SC example code line
          $usr_email = {rs[0][3]}; //SC example code line
          $usr_off_phone = {rs[0][4]};  # ADDED LINE  // Your new code line
          $usr_off_ext = {rs[0][5]};  # ADDED LINE // Your new code line
          $$user_mobile_phone = {rs[0][6]};  # ADDED LINE // Your new code line
          
          sc_set_global($usr_login);
          sc_set_global($usr_priv_admin);
          sc_set_global($usr_name);
          sc_set_global($usr_email);
          sc_set_global($usr_off_phone);  # ADDED LINE
          sc_set_global($usr_off_ext);  # ADDED LINE
          sc_set_global($usr_mobile_phone);  # ADDED LINE
          }
          else
          {
          sc_error_message({lang_error_not_active}); //"Sorry, but user is not active!"
          sc_error_exit();
          }

          Comment


          • #6
            The comments in the code in the message are only in the quoted code -- not the original.

            I am going to try EricB's suggestion to put the code in onLoad for the current application.

            Comment


            • #7
              Originally posted by EricB View Post
              Just put a small SELECT statement in Form Settings-> Events -> OnLoad and write the values directly in the fields.
              Hi Eric,

              I am following your suggestion by putting the following code in the onLoad event in my "app_form_ResourceRequest" application:

              /**
              * Pull data from the sec_users table
              */
              $sql = "SELECT
              uOfficePhone,
              uUfficeExt,
              uMobilePhone
              FROM sec_users
              WHERE login = $usr_login";


              sc_lookup(usr_data, $sql);

              $usr_office_phone = {usr_data[0][0]};
              $usr_extension = {usr_data[0][1]};
              $usr_mobile_phone = {usr_data[0][2]};

              sc_set_global($usr_office_phone);
              sc_set_global($usr_extension);
              sc_set_global($mobile_phone);
              The variable in the above SELECT WHERE clause is supposed to be defined as a global variable in the app_Login application. See lines 10, 23, and 27 in the following onValidate event from app_Login:

              $slogin = sc_sql_injection({login});
              $spswd = sc_sql_injection(({pswd}));


              $sql = "SELECT
              priv_admin,
              active,
              name,
              email
              FROM sec_users
              WHERE login = $slogin
              AND pswd = ".$spswd."";

              sc_lookup(rs, $sql);

              if(count({rs}) == 0)
              {
              sc_log_add('login Fail', {lang_login_fail} . {login});
              sc_error_message({lang_error_login});
              sc_error_exit();
              }
              else if({rs[0][1]} == 'Y')
              {
              $usr_login = {login};
              $usr_priv_admin = ({rs[0][0]} == 'Y') ? TRUE : FALSE;
              $usr_name = {rs[0][2]};
              $usr_email = {rs[0][3]};
              sc_set_global($usr_login);
              sc_set_global($usr_priv_admin);
              sc_set_global($usr_name);
              sc_set_global($usr_email);
              }
              else
              {
              sc_error_message({lang_error_not_active});
              sc_error_exit();
              }
              However, when I login and then run app_form_ResourceRequest, the error message "Undefined variable: usr_login" is displayed and the appropriate values SELECTed from the sec_users table are not displayed in the form.

              Why is the SELECT WHEN clause in my app_form_ResourceRequest application not recognizing the usr_login variable? Shouldn't it be available for use, since it is defined as a global variable by app_Login?

              What am I missing?

              Eric
              Last edited by EricDahl; 03-06-2013, 02:31 AM.

              Comment


              • #8
                Eric,
                First of all I advise you to code like:

                "SELECT uOfficePhone,uUfficeExt,uMobilePhoneFROM sec_users WHERE login = '".$usr_login."'";

                Using global variables it's necessary to know where they are created. I actually don't like the idea of creating them when needed as you might be declaring globals everywhere. I advise you to declare all globals once on a central spot, mostly your main application.
                I use the [] syntax, but that's personal.

                $usr_login is a php variable which you have declared global. But where is it declared in your code? In the onload I would do something like:



                [glob_slogin] = sc_sql_injection({login});
                $spswd = sc_sql_injection(({pswd}));

                $sql = "SELECT priv_admin, active, name, email, uOfficePhone, uOfficeExt, uOfficeMobilePhone FROM sec_users
                WHERE login = '".[glob_slogin]."' AND pswd = ".$spswd."";
                Albert Drent
                aducom software netherlands
                scriptcase partner, reseller, support and (turn-key) development
                www.scriptcase.eu / www.scriptcase.nl

                Comment


                • #9
                  Hi Eric,

                  may be you misunderstood me in one part. If you follow my suggestion to get the values you are using in that for in the onLoad you do not need the globals anymore ...

                  sc_lookup(usr_data, $sql);

                  {usr_office_phone} = {usr_data[0][0]};
                  {usr_extension} = {usr_data[0][1]};
                  {usr_mobile_phone} = {usr_data[0][2]};
                  expecting your field names in the form are usr_office_phone, usr_extension and usr_mobile_phone.


                  I hope the following Select will work:

                  $sql = "SELECT
                  uOfficePhone,
                  uUfficeExt,
                  uMobilePhone
                  FROM sec_users
                  WHERE login = '".[usr_login]."'";
                  BR

                  Eric
                  Last edited by EricB; 03-06-2013, 01:30 PM.

                  Comment

                  Working...
                  X