Loop through the DataConnection records

[Yair-Nevet]

Hello,

I made a connection to excel file in my form.

The connection is good and I'm able to bind data to my objects with out any problem.

Now, someone can tell me how can I loop through the records I received from the excel file in my code? any example?

I need it for some reason.

Thank you in advanced,

Yair

[Former Community Member]

OK .. here's an example:

StudentNameText.rawValue = xfa.event.newText;

var sDataConnectionName = "MyConn"; // name of the data connection from which to retrieve data

var sColName = "myColumnName";

//    Search for <source name="{sDataConnectionName}"> node

var nIndex = 0;

while(xfa.sourceSet.nodes.item(nIndex).name != sDataConnectionName)

{

    nIndex++;

}

var oDB = xfa.sourceSet.nodes.item(nIndex); // the <source> node pertaining to the data connection specified in sDataConnectionName is our "database"

oDB.open(); // load the data from the connection into the form, resolving any bindings

oDB.first(); // move to the first record in the connection

//    Search node with the class name "command" (this is <source><command/></source>)

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");

// Search for the record node with the matching Data Connection name (looking for <{sDataConnectionName}> node within the current record data)

nIndex = 0;

while(xfa.record.nodes.item(nIndex).name != sDataConnectionName)

{

    nIndex++;

}

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

var oNode = null;

for(var nColIndex = 0; nColIndex < oRecord.nodes.length; nColIndex++)

{

    if(oRecord.nodes.item(nColIndex).name == sColName)

    {

        oNode = oRecord.nodes.item(nColIndex);

    }

}

var lFinished = false;

while(!(oDB.isEOF()||lFinished))

{   

    // As this loop iterates, the variable "oNode" is set to the value of the requested column

    // you could use this to add instances to a subform or items to a dropdown etc

     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();

[Yair-Nevet]

Thank you very much!

This is a fantastic example.

Yair

Sent from my BlackBerry® smartphone from orange

[Yair-Nevet]

Hi Tim,

Line: while(xfa.sourceSet.nodes.item(nIndex).name != sDataConnectionName) throwing: "Index value is out of bounds" exception.

And my form is already containing the DataConnection settings to the excel file...

Any idea why?

Yair

[Former Community Member]

Hi Yair,

Sorry, you did say "any" example :-)

There may be a better way to do what you are doing:

See the sction "Using a button to populate fields from a database" here: http://help.adobe.com/en_US/livecycle/9.0/designerHelp/index.htm?content=000329.html

See that the javasxript references the data connection by name explicitly..

Hope this helps

Thanks

Tim