Expand my Community achievements bar.

Radically easy to access on brand approved content for distribution and omnichannel performant delivery. AEM Assets Content Hub and Dynamic Media with OpenAPI capabilities is now GA.

Populating data from Access database - Tutorial

Avatar

Level 5

Would anyone tell me how I can get a tutorial of Populating data from Access database?

I have got http://forms.stefcameron.com/2006/09/18/connecting-a-form-to-a-database/

I want more. It will be better if I get tutorial from adobe documentation.

Is it possible?

19 Replies

Avatar

Former Community Member

I did a webinar on that subject but it is not as deep as what Stefan woudl talk about. I do not believe that a tutrorial exists in the docs for that topic. Here is a link to the webinar.

Paul

http://adobechats.adobe.acrobat.com/p69655795/

Avatar

Level 5

Thanks.

I am going to see the tutorial.

Avatar

Level 5

Nice!

Now I am clear. I am trying to create such kind of form.

Regarding 1st sample described in your tutorial and also cameron tutorial:

All button works well except "add new". After clicking add new Button, I entered new data. After that when I  click another button it appears following error:

GeneralError: Operation failed.
XFAObject.last:1:XFA:form1[0]:#subform[0]:Button4[0]:click
last operation failed.  Multiple-step operation generated errors. Check each status value.[ID:11]

What should I do?

Avatar

Former Community Member

The AddNew() creates a new blank record and the DB cursor remains on that record .....the form field swil clear allowing you to input the information that you want (into the form fields). Then when you are finished you click the Update() function and that will put th enew information into the blank record.

make sense?

Paul

Avatar

Level 5

I have followed your instruction but yet following error appears.

GeneralError: Operation failed.
XFAObject.update:1:XFA:form1[0]:#subform[0]:Button6[0]:click
updateRecord operation failed.  Multiple-step operation generated errors. Check each status value.[ID:11]

In addition update button works in other case.

What am I making mistake?

Avatar

Former Community Member

That message is being passed through from teh DB ...are you sure that the data that is being entered matches the types that were defined for the columns? Are you using a Date type in your DB if so that is usually the issue?

Paul

Avatar

Former Community Member

I am not able to use your DB. I keep getting an error saying that it is already opened.

I tried your form with another DB and it worked fine.

Paul

Avatar

Level 5

That problem has been gone. Now I see another problem sometimes new data is added, sometimes not. After clicking Add new button, other buttons do not work.

I seem .mdb file problem. Would you send me any DB file so that I can check DB file. Obviously if you have no problem.

Avatar

Level 5

Hi Paul,

I have place folowing code click event of refresh button.

if (Len(Ltrim(Rtrim(SelectField.rawValue))) > 0) then
xfa.sourceSet.DataConnection.#command.query.commandType = "text"
$sourceSet.DataConnection.#command.query.select.nodes.item(0).value = Concat(“Select * from OfficeSupplies Where ID = “, Ltrim(Rtrim(SelectField.rawValue)) ,””)
//Reopen the Dataconnection
$sourceSet.DataConnection.open()
endif

(Got from   http://partners.adobe.com/public/developer/en/livecycle/lc_designer_db_lookup_tip.pdf)

When I check with "check script syntex", it shows pink mark following line:

xfa.sourceSet.DataConnection.#command.query.commandType = "text"

Also, in acrbat shows illegal character.

Would I get any solve?

Thanks

Avatar

Former Community Member

You are mixing javascript and formcalc commands together instead of xfa.sourceSet.DataConnection.#command.query.commandType = "text" ..it shoudl be $.sourceSet.dataConnection.......

Paul

Avatar

Level 5

Thanks for reply.

I apoogize that I have seen yet error.

With regards

Mehedee

Avatar

Level 5

Hi Paul,

Am I missing anything? If you'd help more I'd be grateful to you.

Avatar

Former Community Member

These things are very hard to debug without having the components on my own system to see what is happening. The

code looks OK ...what is happening now?

Paul

Avatar

Level 5

Now I set code in click event.

Changed code line to: $.sourceSet.DataConnection.#command.query.commandType = "text"

Yet pink mark is shown.

In Acrobat:

SyntaxError: illegal character
2:XFA:form1[0]:#subform[0]:Button9[0]:click

Avatar

Former Community Member

If you copied and pasted from somewhere else the quote marks get messed up. Try retyping the quotes and see if that fixes it. Also the ID that you are passing in nededs to be in quotes so in your SQL statement where ID = you will need to add a single quote then a double quote and at th eend of the line you have two double quotes ...you will need a dougle, single double quote.

Hope that helps

Paul

Avatar

Level 5

Hi Paul,

I have retype all. Now no error in script edit field.

However, when I click on refresh button I have seen following error.

Error message.jpg

What should I do?

Avatar

Former Community Member

You are running into a security issue in Acrobat. In version 9 they added code that woudl not allow you to modify an existing Dom. To get around this you must clone the DOM and operate on the cloned Dom instead. Here is code that will show you how to cline the connection (See how the oDB object is created by cloning the sourceSet Dom and then all operations are don on the oDB object):

var nIndex = 0;
while(xfa.sourceSet.nodes.item(nIndex).name != "DataConnection2")
{
nIndex++;
}

var oDB = xfa.sourceSet.nodes.item(nIndex).clone(1); // the node pertaining to the data connection specified
//set up sql call to DB to get specifics about employee

oDB.nodes.item(1).query.setAttribute("text", "commandType");
oDB.nodes.item(1).query.select.nodes.item(0).value = "Select * from table1 where AcctNumber = '" + inName + "'";
//now connect to DB and get a record
oDB.open()
oDB.close();

Avatar

Level 5

It is regretful that Paul is no more here. However We are getting continuous help from Nial, BR001, Srini Dhulipalla, Radzmar, Jono Moore, Kingphysh, Nith.........

I still hope this thread will be ended successfully.

I have placed following code under dropDown but I do not get the value in it.

form1.p.DropDownList2::initialize - (JavaScript, client)

var sDataConnectionName = "Connectiona";

var sColHiddenValue = "Name";

var sColDisplayText = "Age";

var nIndex = 0;
while(xfa.sourceSet.nodes.item(nIndex).name != "sDataConnectionName")
{
nIndex++;
}

var oDB = xfa.sourceSet.nodes.item(nIndex).clone(1); // the node pertaining to the data connection specified

oDB.nodes.item(1).query.setAttribute("table", "commandType");
oDB.nodes.item(1).query.select.nodes.item(0).value = "Select * from Table1 where Name = '" + inName + "'";

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

oDB.first();

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

// Remove all items currently in the list (if any).

this.clearItems();

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

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

XML source file:

<sourceSet xmlns="http://www.xfa.org/schema/xfa-source-set/2.8/">

   <source name="Connectiona" db="ado">

      <connect delayedOpen="0" timeout="15">

         <connectString>Provider=MSDASQL.1;Persist Security Info=False;Data Source=Connectiona;Extended Properties="DSN=Connectiona;DBQ=C:\Users\Dream_Peak\Documents\Database2.accdb;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"</connectString>

         <user/>

         <password/>

      </connect>

      <command timeout="30">

         <query commandType="table">

            <select>Table1</select>

            <recordSet cursorType="static" cursorLocation="client" lockType="optimistic" bofAction="moveFirst" eofAction="moveLast"/>

            <map from="Name" bind="#bind5">

               <?templateDesigner DataType 130?></map>

            <map from="Age" bind="#bind6">

               <?templateDesigner DataType 3?></map>

            <map from="memo" bind="#bind7">

               <?templateDesigner DataType 130?></map>

            <map from="First Name" bind="#bind8">

               <?templateDesigner DataType 130?></map>

            <map from="Last Name" bind="#bind9">

               <?templateDesigner DataType 130?></map>

         </query>

      </command>

      <bind id="bind5" ref="$record.Connectiona.Name"/>

      <bind id="bind6" ref="$record.Connectiona.Age"/>

      <bind id="bind7" ref="$record.Connectiona.memo"/>

      <bind id="bind8" ref="$record.Connectiona.First_Name"/>

      <bind id="bind9" ref="$record.Connectiona.Last_Name"/>

   </source>

</sourceSet>

What should I do in this case? Where am I making mistake?