check to see if record exist in the DB or not

[Former Community Member]

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

[Former Community Member]

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

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.

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

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.

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

The form is attached for reference.

Steve

[Former Community Member]

Thank you very much for your helpful tips, Steve.

Han Dao

[Former Community Member]

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

[Former Community Member]

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

Thanks.

Han