Form does not show "new" records from SQL Database

[Eric_Horde]

I have a PDF form that pulls data from a SQL Server.  The fields in
the PDF are populated from the database after selecting a specific
record from a drop down and then clicking on a button labeled "Fill".
The problem is that the dropdown does not display new records that
have been recently added to the database.  I have to open the form up
in designer then save it, (*note - I change nothing at this point.)
Then when the form is opened back up in Adobe the dropdown show all
the records including the new ones.  I even put a manual refresh on
form to try and fix this an it did not help. Seriously stumped.

Any help is greatly appreciated.

Here is my code for the dropdown.

++++++++++++++++++++++++++++

topmostSubform.Page1.JobSelect::initialize - (JavaScript, client)
var sDataConnectionName = "BBCC"; // example - var sDataConnectionName
= "Test";
var sColHiddenValue = "ContractAdmin_Key"; // example - var
sColHiddenValue = "Dept_ID";
var sColDisplayText = "JobDescription"; // example - var
sColDisplayText = "Dept_ID"

// Search for sourceSet node which matchs the DataConnection name
var nIndex = 0;
while(xfa.sourceSet.nodes.item(nIndex).name != sDataConnectionName)
{
nIndex++;

}

var oDB = xfa.sourceSet.nodes.item(nIndex);
oDB.open();
oDB.first();

// Search node with the class name "command"
var nDBIndex = 0;
while(oDB.nodes.item(nDBIndex).className != "command")
{
nDBIndex++;

}

// Backup the original settings before assigning BOF and EOF to stay
var sBOFBackup =
oDB.nodes.item(nDBIndex).query.recordSet.getAttribute("bofAction");
var sEOFBackup =
oDB.nodes.item(nDBIndex).query.recordSet.getAttribute("eofAction");

oDB.nodes.item(nDBIndex).query.recordSet.setAttribute("stayBOF",
"bofAction");
oDB.nodes.item(nDBIndex).query.recordSet.setAttribute("stayEOF",
"eofAction");

// Clear the list
this.clearItems();

// Search for the record node with the matching Data Connection name
nIndex = 0;
while(xfa.record.nodes.item(nIndex).name != sDataConnectionName)
{
nIndex++;

}

var oRecord = xfa.record.nodes.item(nIndex);

// Find the value node
var oValueNode = null;
var oTextNode = null;
for(var nColIndex = 0; nColIndex < oRecord.nodes.length; nColIndex++)
{ if(oRecord.nodes.item(nColIndex).name == sColHiddenValue)
{ oValueNode = oRecord.nodes.item(nColIndex); } else
if(oRecord.nodes.item(nColIndex).name == sColDisplayText) { oTextNode
= oRecord.nodes.item(nColIndex); } }

while(!oDB.isEOF())
{
  this.addItem(oTextNode.value, oValueNode.value);
   oDB.next();

}

// Restore the original settings
oDB.nodes.item(nDBIndex).query.recordSet.setAttribute(sBOFBackup,
"bofAction");
oDB.nodes.item(nDBIndex).query.recordSet.setAttribute(sEOFBackup,
"eofAction");

// Close connection
oDB.close();

++++++++++++++++++++++

Here is code for the refresh button

+++++++++++++++++++++

topmostSubform.Page1.Button27::click - (JavaScript, client)
sourceSet.BBCC.requery();

+++++++++++++++++++++

[Former Community Member]

It sounds like some sort of caching issue where the query result is getting cached locally. Are there any settings on the ODBC connection that tell the connection to get a fresh result each time it is called?

Paul

[Eric_Horde]

Paul, I agree that it appears to be a caching issue.  I am tweaking the ADO settings but I confess I don't know much about the various ADO parameters.  I will follow up if any of the ADO settings I change make a difference.

[Former Community Member · Accepted solution]

The other thing that might be happening is a refresh issue on the DropDownList. Try adding the command xfa.layout.relayout() after the database connection has been closed.

Paul

[Eric_Horde]

Good catch Paul!  Would you believe that I have been trying to get this resolved for almost two years! Works perfect now.

Thank you,

  - Eric