4 Replies Latest reply on Oct 18, 2010 9:23 AM by Han Dao

    check to see if record exist in the DB or not

    Han Dao Level 1

      Hello,

       

      I am working on a form that has a web service that connects to the external database in workbench to check some record if it does exist in the database or not by using the query below but it did not work. I have tried Query Single Row, Execute SQL Statement, and Call Store Procedure but still could not make it work as expected. Can any one please advise. I am still using Designer and Workbench 8.2.1 with SP3

       

      IF EXISTS (SELECT NULL FROM tablename where empcode = 1)
      BEGIN        
      PRINT 'Empcode 1 does not exists, please continue!'
      END
      ELSE
      BEGIN       
      PRINT 'Empcode 1 exists, please try another Empcode!'
      END

       

      Thanks,

      Han Dao

        • 1. Re: check to see if record exist in the DB or not
          Steve L Walker Level 5

          Han,

           

          The Query Single Row operation has a property called 'Number of Rows Retrieved' where you can capture the row count in a process variable of type 'int'. In my case I called the variable 'rowCnt'.

           

          Untitled.png

           

          In the query, above, the SQL statement uses a parameterized query that takes the input process variable 'partNum' and returns 'description' and 'unitPrice'. Since I want to get 'description' and 'unitPrice' in my form I define those process variables as output. I defined 'rowCnt' as an output also so I can query the value in my form to determine whether or not the query yielded a result.

           

          Untitled1.png

           

          In my form I then built a data connection to the process WSDL and mapped the inputs and output to form variables.

           

          Untitled3.png

           

          In the form I added the script below to execute the data connection on the exit event of 'partNum'. My form contains a hidden field called 'rowCnt' which is bound to the invocation response 'rowCnt'. Once I execute the call to the Web Service I can then check the form field 'rowCnt'.

           

           

          // form1.page1.subform1.partNum::exit - (JavaScript, client)

           

          if (!(this.isNull)) {

            xfa.connectionSet.DataConnection.execute(0);

            if (form1.page1.subform1.rowCnt.rawValue == "0") {

              form1.page1.subform1.errrorMsg.rawValue = "A database record was not found for part number " + this.rawValue + ".";

            }

          }

           

          If the Web Service finds a record the part description and unit price are populated.

          Untitled5.png

          If the Web Service does not find a record then I emit an error message.

          Untitled4.png

          The form is attached for reference.

           

          Steve

          1 person found this helpful
          • 2. Re: check to see if record exist in the DB or not
            Han Dao Level 1

            Thank you very much for your helpful tips, Steve.

             

            Han Dao

            • 3. Re: check to see if record exist in the DB or not
              Han Dao Level 1

              Hi Steve,

               

              I modified the script to work with the 4 options: new, modify/transfer, or delete record.

              Based on the requrirement of new record, the user will need to check the record first, if it does exist then it will not populate the data from the web service so the user can continue to fill the form. I want to the form to populate data only when the user select the option of modify/transfer, or delete record but for some reasons, it does not work that way, it keep populate the data for no matter what option is selected. I try "xfa.host.resetData();" but it reset the whole form that make the user to re-fill the form from the beginning which is not right.

               

              My question, how can I stop the populating the data when the record exist in the database. Here is re-written codes:

               

              ======== start

              if

               

               

              ((form1.Form1.RequestSub.RequestType.rawValue == 1) && (!(this.isNull))) {

               

              // To clear the DDO Designator List if Type of Request is "Create End User-ID"

              form1.Form1.EmpInfoSub.DDOfield.clearItems();

               

               

              // To execute the web service to populate employee information.

              xfa.connectionSet.GetEmployeeInfo.execute(0);

              xfa.connectionSet.WS2.execute(0);

               

               

              if (form1.Form1.LANSub.rowCnt.rawValue == "1") {

               

               

              xfa.host.messageBox("This End-user ID "

               

              + this.rawValue + " is already assigned in the system, please try another End-user ID", "Form Tip" ,2);

              xfa.host.resetData();

              }

              }

              else {

              xfa.connectionSet.GetEmployeeInfo.execute(0);

              xfa.connectionSet.WS2.execute(0);

              }

              ====== end

               

               

              Thanks,

              Han

              • 4. Re: check to see if record exist in the DB or not
                Han Dao Level 1

                Well, I think I got it work by resetting the subform command: xfa.host.resetData("xfa.form.form1.mySubform");


                Thanks.

                Han