I am trying to create a form that links to an Access database. I am using the library.custom.data drop down list object. I drop it on the palette and then from the data viewer drag and drop the field on top of the DD list field. The data file is only about 100 records and when I test the form and click on the drop down list field it only shows the first record. In fact that is all it does and I do not get the list of items to choose from.

What am I doing wrong?

Dwight,

The “Data Drop Down List” object provided in the Library palette’s Custom tab is designed to load all records from a data connection using a data node for the item text and another for the item value.

By dragging the data node onto the Data Drop Down List from the data connection in the Data View palette, you’re simply creating a binding between the Data Drop Down List (which is essentially a simple drop down list with some script in its Initialize event) and the data node in the data connection. The default behaviour of data bindings is to load the first record on form load in Acrobat.

What I suggest is to set the Data Drop Down List’s Default Binding property to Normal using the Object palette’s Binding tab and then modify the Initialize script so that it works with your data connection.

If you open the Script Editor palette (use the Window menu to locate it) and select the Initialize event (with the Data Drop Down List object selected on the canvas), you’ll see the script that’s used to load all values from specified columns in a specified data connection. Near the top, you’ll see the following statements:

var sDataConnectionName = "<value>";
var sColHiddenValue = "<value>";
var sColDisplayText = "<value>";

The idea here is to change the “<value>” entries with information that pertains to the data connection you’ve created. For instance, if it’s called “MyDataConnection” and contains an ID and a Name column, you might do the following to have the drop down list automatically populated with items which show the Name and have the ID set as their value:

var sDataConnectionName = “MyDataConnection”;
var sColHiddenValue = “ID”;
var sColDisplayText = “Name”;

Another solution — which doesn’t required any scripting, by the way — is to use the Dynamic Properties feature of Designer 7.1 to automatically populate a regular drop down list’s item list with values from a data connection.

Hi Stefan,

I am Newbie07 from the adobe designer forum and thanks for this turorial. All the controls, except for addnew() are working for me. I do not get any error while inserting the data, but the data never get inserted into the table.

Newbie07,

What exactly is happening when you attempt to use the xfa.sourceSet.{DataConnectionName}.addNew() function? Do you get an error message?

Have you looked at Designer’s Report palette while previewing and testing your form to see if any warnings/errors are listed there that may be related to updating/adding new records in the database?

Ricardo,

The way data connections to ODBC data sources work in XFA is that you’re expected to iterate through all records or display (report-style) all records at once. The only way to show/update only one specific record at any given time is to specify a very specific SQL query that produces zero or one records for the data connection.

What I’ve done with my “Selecting Specific Database Records”:http://blogs.adobe.com/formbuilder/2006/09/selecting_specific_database_records.html tutorial is show you how to change the SQL statement used by a data connection dynamically (i.e. at run-time with respect to other user selections).

The idea would be to follow that tutorial and allow the user to select enough information in order to build an SQL query that would produce one record and then update the second data connection’s SQL query such that only that specific record is subsequently available for viewing/editing in the form.

How to add data within two tables. I have use

xfa.sourceSet.DataConnection.addNew()

in a AddNew button. but this only adding just a table. I’ve also make a New Data Connection “DataConnection2” to the second table.

How to solve this problem?

Yuda,

While it’s possible to define multiple data connections to ODBC data sources (typically database tables), only one may be active at any given time.

If you need to update data in two tables concurrently, I would suggest that you use an SQL statement as the definition for the data connection. This way, you can define an SQL statement that joins two or more tables together and cause data to be updated in multiple tables with a single active ODBC data connection.

Jan,

Unfortunately, I’m not an Access expert so I can’t give you a definite answer or procedure to follow but I do know that while Access supports SQL, it either doesn’t support the entire language set or doesn’t expose most of it by default.

I was able to find information on Create Table queries in Access which you may find useful in translating the code in the SQL file I provided into code that can be interpreted by Access in order to create the various tables you need.

I just started using Adobe LiveCycle designer to create an online competency test for a client. Everything is working well thus far with respect to filling out the test and emailing back to me for automotic grading using the XML data that has been submitted via email.

My client at this point has a concern regarding the PRINT option that is made available to the individual sending his/her results. My client does not wish to have the PRINT option available.

How can I create an “email to:” dialogue that only has the options for “Send data” or “Cancel” ??

Your advice would be most appreciated.

Regards,

Bob

Bob,

If I understand correctly, you’re basically wanting to by-pass the intermediate dialog that opens when the user clicks on an email submit button where the user must choose their email client or print the data.

This dialog can be by-passed by programmatically causing the data on the form to be submitted.

When the user clicks on an actual email submit button to submit the form’s data, Acrobat sees that as being a result of a user action (clicking on the button) and displays the intermediate dialog.

If you were to place a regular button on the form, make the actual email submit button invisible and then, in the regular button’s Click event, cause the email submit button’s Click event to be executed, you would then cause the form’s data to be submitted programmatically, resulting in a new email message with the data as an attachment to be displayed, having by-passed the intermediate dialog.

To execute the email submit button’s Click event from another button’s Click event, simply use the following script:

EmailSubmitButton.execEvent(“click”)

where EmailSubmitButton is the name of your invisible email submit button.

Kevin,

It’s difficult to figure-out what the problem is here. I’m thinking it could be one of two things:

1. You’re trying to do basically what this post’s sample does (browse records from your MSSQL 2000 database on a form) but you’re using Designer 8.0 and Acrobat 8.0. In this case, your problem could be related to attempting to modify the SourceSet model directly instead of using a cloned version.
2. (Shot in the dark…) Your current system environment (i.e. the user under which Acrobat is being executed) doesn’t have permission to access the DSN. For example, if you’re previewing the form in Designer, then Acrobat actually thinks it’s running in a browser which means it may be running under a lowered-permissions user (like IUSR_{username} — the Internet Guest Account) which may not have permission to access the DSN.

I have yet to run into #2 myself so hopefully #1 fixes your problem.

Let me know how it goes.

I have setup a data connection and some formcalc script in a form created in designer 7 which works fine in preview mode, but I get the following error when opening the form using Reader 7.

Error: accessor ‘$sourceSet.DataConnection2.#command.query.commandType’ is unknown.

I have used the following script from Adobe Designer Help:

$sourceSet.DataConnection2.#command.query.commandType = "text"
$sourceSet.DataConnection2.#command.query.select.nodes.item(0).value = Concat("Select*from BRANCH_IN Where branch_number = ", TextField8.rawValue,"")
$sourceSet.DataConnection2.open() 

Have I missed a step? I have checked my Data Connection setup after reading previous posts, and it looks fine.

Thanks,

Katrina

Katrina,

By default, the Adobe Reader is not capable of importing data. Forms that need to do this in Reader need to be Reader-Extended to allow data import. I’m guessing this is why you’re seeing the error in Reader and not when previewing the PDF in Designer.

When viewing the form in Preview Mode, you’re probably using Acrobat Pro which is capable of importing data by default.

Acrobat Pro is capable of Reader-Extending forms to enable certain capabilities on a certain number of forms. You may be able to use it to enable data import in your form when it’s viewed with Reader 7.

Just wanted to drop a quick note of praise.

I know how rare it is to get positive feedback, and this post covered everything I needed to know.

Thanks!

I have been following your tutorials and they work quite well. I have a need for users to open a designer pdf form from the internet, fill it out, transmit the data so that it populates a database, then print the form and mail it in. Could you put together such a tutorial or do you know where one exists? Thanks.

Is Acrobat 6.0 Professional, that is packaged with Adobe CS capable of connecting to an ODBC data source or do I need to upgrade or get LiveCycle? I have not been able to get data to import from a text tab delimited file successfully.

DW,

There are a few different ways you could go about doing this and without a little more detail, it’s difficult to suggest one way over another.

For instance, you could have the PDF form served from your web site and then the data could be submitted to the database via a LiveCycle Forms solution.

The fact that you would like the user to first submit the data, then print and mail-in the form, however, tends to tell me that you need a signature from them. An alternative solution would be to use LiveCycle Barcoded Forms whereby you would encode, in real time, the data entered into the form electronically (prior to printing) into a 2D barcode (usually “PDF 417”). Once the form is filled, the user would print the form, sign it and mail it in to your office. On the receiving end, you would use a 2D barcode scanner to quickly scan the information entered into the form directly into a database and then simply keep the paper copy of the form with the user’s signature for your records.

A 3rd option would be to create a web service to which the form’s data could be submitted over the Internet.

Unfortunately, I don’t have the resources necessary to put together such a sample in a working state. As far as an existing sample, I don’t know if we have one but if we did, it would be on the LiveCycle Developer Center Samples site (you’ll find LC Designer and LC Forms samples there).

Peter,

Acrobat 6.0 Pro should be able to import data from ODBC data sources and you should be able to create ODBC data connections in Designer 6.0 that comes with it.

Chandresh,

Thank you for the complement!

I’m currently looking into this for you and will let you know as soon as I have an answer.

Thanks Stefan – great info! I have used this article to provide my form with access to my data – using ‘previous’, ‘next’ and ‘print’ buttons. The latter prints only the current form – how could I iterate thru my dataset and print ‘all’ records? Thx, Kim.

Tobias,

I think the cause of this problem may lie with the bindings set on the various fields which you’re using to fill the order.

When you drag a field from the Data View palette, it automatically gets an explicit binding to the pertaining data node. If you look in the Binding tab of the Object palette, you’ll find the Default Binding property and it’ll probably contain something like “$record.{DataConnectionName}.DataNodeName”.

If that’s the case, then what will happen on save and re-open is that the data from the database will be pulled into the form after the data is merged-in and it’ll replace all the saved data.

Usually, when you have a dynamic order form such as the one you’re designing, you tend to have drop down lists and list boxes that let the user choose an item from the database which they would like to order. In those cases, you still create a data connection to the database but you use it in a different way than the one I detailed in this article. In particular, you use the “data drop down list” and “data list box” objects found in the Library palette’s Custom tab. These objects contain pre-defined Initialize scripts that will connect to the data connection you specify and populate their lists with the text and value data nodes which you also specify. The difference here is that the drop down lists and list boxes defined in this manner end-up with “Normal” binding types instead of explicit ones to the database. As long as you keep the names of these objects different from the names of the data nodes in your data connection, when you save and re-open the form, they’ll still contain the saved data and yet will still be re-populated with values from the data connection.

You can find an example of using the data drop down list object in the sample form from my article on selecting specific database records. See the “CategoryList” drop down list.

I am to design a database by use of Microsoft access 2003. The data base includes names and with each name the data for specific person is entered. How can I display information of a particular individual by i.e tying on the first letter of a name

Kapuulya,

I think my tutorial on selecting specific database records is exactly what you’re looking for. In fact, the sample form uses a drop down list to allow the user to select a type of movie and then filters the records which the user can browse using the fields below based on that criteria. Any modifications made to the data in a record based on the filter are immediately reflected in the database.

Based on that sample, you could provide a drop down list containing a list of unique letters, those being the first letters of each name in the database. That can all be done with a carefully crafted SQL statement used to populate the drop down list which would essentially serve as your index. A selection in the drop down list would then apply an SQL statement to the second data connection which would simply select all records from the database where the name begins with the letter the user selected.

Svet,

You’re very welcome!

I’ve never personally come across that error.

I found a Microsoft Knowledge Base article which offers suggestions on how to solve the problem which might do the trick for you.

Please let me know if it helps.

M Llewellyn,

Unfortunately, I don’t believe it’s possible to create a DSN on a server and have the form connect to it.

What you can do is share the database file and have everyone create a local DSN to it. This way, people don’t also have to have a local copy of the database. I can’t speak to how secure this is but you can do it quite easily in Windows.

If this still isn’t good enough, then I think you’re only option is to write a web service that runs on the server where the database is located. Your form would then use the web service to set and obtain information and the web service would take care of working directly with the database. This model would actually also be much safer since it wouldn’t directly expose the database.

Mike,

Before we start down the road of Reader Extensions (which is quite expensive if you’re only using it for a small number of forms), what specific error message are you getting when you try to use your form in Reader 8?

Based on what you wrote, it sounds like you are still capable of importing data from a data connection in Reader 8 since your list box is getting pre-filled with database information. Does the user then make a selection, prompting your form to run an SQL query which is supposed to retrieve specific records from the database and display them in bound fields?

Are you getting some sort of security error?

Joanne,

I believe you have two options here:

1. You can use the sample form I provided in this tutorial and modify it such that a new record is added automatically when the form is opened in Acrobat and the only button available is the “update” button (which you could rename “insert”). This way, the user is presented with empty fields ready for new record data which gets inserted into the database when they click on the “insert” (update) button.

   To do this, you would simply remove all buttons except for the “update” button and move the script from the “add new” button into the root subform’s (named “form1” at the very top of the tree in the Hierarchy palette) Form:Ready event (using the Script Editor palette).

2. A more advanced option would be to use some of the techniques I describe in my tutorial on inserting, updating and deleting records in a database.

Kerry,

I’m happy to hear that you find my blog is a good source of information. It’s always encouraging to hear that it’s useful to people!

Unfortunately, I don’t believe that there’s a way to get a copy of the PDF automatically inserted into a database record via a button on a PDF form you create with Designer. That doesn’t mean it isn’t possible, however, it just means that you’ll have to resort to other technologies, which come into play once the form has been submitted, to help make it work.

For instance, you could design your form such that when the user clicks on the submit button you use today, the last step in the script is to execute an invisible submit button’s Click event, thereby resulting in the submission of the entire PDF, including the filled data, to a destination of your choice. This destination could be a server or even an email address. Once the PDF is received, it could be inserted into the appropriate record in the database, either as a binary blob or linked via OLE (depending on the type of database you use). The idea here would be to do everything in one step: the submission of the data to the database and the submission of the PDF to some destination (i.e. the user still only needs to click once).

This solution is essentially the “Two Button Submit” Technique where the invisible button is a submit button which submits the entire PDF instead of the data only.

You can create this submit button in Designer by using a regular submit button (use a button object from the Library palette and select “Submit” from the “Control Type” property on the Object palette’s Field tab) and specifying “PDF” in the “Submit As” property on the Object palette’s Submit tab (if you’re using Designer 7.1, the property will be named “Submit Format” instead).

To submit the PDF to an email address, simply enter the following Submit

mailto:email@address.com

where “email@address.com” is the email address to which the PDF should be submitted.

Chaitali,

In case you’re using the buttons from the snippet I provided in this tutorial, is it possible that you’ve given a name other than “DataConnection” to your data connection?

If that’s the case, then the scripts on all the buttons would fail (and so you wouldn’t be able to navigate through the records) because their programmed to work with a data connection with a name of “DataConnection”.

Of course, you can change this by editing the scripts on each button using the Script Editor palette available via the top-level Window menu.

I was wondering if there was a way to encrypt the database username/password information on these documents, in case the user saves the document from my website to their local machine. Thanks.

Kristin,

Usually, it’s possible to put the username and password for database access directly in the DSN so that you don’t have to specify it on the data connection itself.

If you can’t do that, then you would obviously want to encrypt that information since the username and password are stored in plain text in your form. What you can do then is go to the “Form Properties” dialog (via the top-level File menu) and switch to the “PDF Security” tab. If you set the permissions to “any except extracting pages” (it’s very important to select this specific option or else your data connection will stop working because any other set of permissions would prevent the data connection from changing field values) and then save your form as PDF, you’ll be asked for a “permissions” password. Once you’ve specified a password, the generated PDF is entirely encrypted (including the form content) yet anyone can open and use the form in Acrobat without having to provide the password. Finally, the next time you open the form in Designer, you’ll be prompted for the permissions password before Designer opens the form which means that if someone downloads your form and attempts to modify it or extract the username/password information, they’ll get blocked at the password prompt.

Siddhartha,

That’s very strange. Based on what you described, you shouldn’t be getting this error.

What the “accessor unknown” error tells me is that the data connection’s name somehow isn’t “DataConnection”. Have you verified that the name of the data connection in the Data View palette is “DataConnection” and not “DataConnection2” or something of the sort?

You could also try replacing “xfa.sourceSet” with “$sourceSet” (a FormCalc shortcut for “xfa.sourceSet”) in FormCalc to see if that makes a difference although I don’t think it would.

If none of this helps, what version of Acrobat are you using to run the form?

venkatesh,

As long as the other machine has the identical DSN setup (same name, same database, user and password, if any, specified if they were specified in the DSN on the primary machine, etc.), you should be able to use your form on both machines without any problems.

Are you able to test that the DSN is properly configured by clicking on the “test” button in the ODBC configuration applet (in “Administrative Tools” on Windows)?

Are you running the form in Acrobat Professional or Standard on the second machine? You need Acrobat Standard at minimum in order to import data into a form unless you’ve reader-extended it, at which point you can import data into it using the free Adobe Reader.

venkatesh,

That’s a good question. Theoretically, if your images were UTF-8 encoded and stored as text blobs in your database, you might be able to set the value of an image field to the value of the text blob by binding that column to the image field but I’ve never tried to do this in practice. You would also have to set the image field’s content type to specify the type of image contained in its value. For example, if it’s a JPEG, you would set the content type like this in JavaScript:

ImageField1.value.resolveNode("#image").contentType = "image\\jpg";

If you give it a shot, please let me know how you make-out.

Hi,

I have desinged a form in adobe livecycle designer 7.0.

In that i have a button. i want a open a aspx web page as a pop when the button is clicked..

how can i write javascript for this on the button clikc event.. plz help me.

thanks
rajasekhar

Srinath,

Unfortunately, I believe your only alternative, aside from using a web service, is to define the DSN on each system which will use the form.

Martin,

That sounds like an interesting project. Since your form will be online, I’m assuming it’ll be publicly accessible in which case it wouldn’t be a good idea to connect your form directly to a database for security reasons. I would start by creating a web service which would act as the “bridge” between the database on the server and the form on the Internet which you could then connect to your form. This web service could then have methods which let you retrieve the “key data from the database” that you need as well as a method to submit the data to the database (which would also take care of automatically assigning a unique “Person No.” to the new record being submitted).

Once you have that mechanism in place, you would simply add fields to your form for the person’s name, address, etc., and data from the database and provide an execute button which would execute the web service’s database submission method to submit the form’s data to the database.

Hi,

i have a question about the buttons. I did it the same way like it is described above but actually if i want to go to the pdf-view the program gives an error back:
Error: syntax error near token ‘{‘ on line 1, column 11.
Script failed (language is formcalc; context is xfa[0].form[0].Formular1[0].#subform[0].Cancel[0])
script=$ourceSet.{Vakken}.next();
Error: syntax error near token ‘{‘ on line 1, column 11.
for i think every button. So when i click on the button in the acrobat modus hij says the same like also like by Siddhartha
Jain Script failed(language is formcalc;context is xfa[0].form1[0].myform1[0].dbNext[0])
script=xfa.sourceSet.DataConnection.next()
Error:accessor ‘xfa.sourceSet.DataConnection.next()’ is unknown.
so i changed also the xfa.s to $ but it didn’t help. Perhaps somebody has an idea wat is going wrong here?

thanks,
Bucaneve

thank you very much, now it works!

Hi,

I have an XDP form with 2 pages which i am displaying as HTML uisng ASP.NET application.
before rendering the form i am passing some xml data in to renderform method.
and i have set the formpreference option as “MSDHTML”. when i try to go to next page i get an error
saying “this._peer is null or not an object”. how should i configure this script. i know that the xfasubset.js is not loaded properly. how to do make ths work.
the second part is that i have another xdp form which is of only one page and am rendering as html.
when the formpreference option is set to “MSDHTML” am not able read the inputs in a xml format.
when i set the formpreference to “AUTO” am able to read this data in xml format but the page is not aligned properly.
Can you please help me how to do this working.

Thanks,
NewAdobeUser

Ernest,

It sounds like the problem could be that the DSN isn’t defined on the other machines you’re using the form on. The data connection you defined in your form on your local machine simply tells Acrobat which DSN to look for on the local machine for database connectivity. This unfortunately means that any machine that will use the form needs to have the DSN defined on it.

If you’re looking to deploy this form without requiring users to define the DSN locally, you would have to create a web service to act as the bridge between the form and the database and connect your form to the web service instead since the web service would be available to anyone on your network. Unfortunately, this is easier said than done but it’s really the only way you’ll get around the DSN problem.

NewAdobeUser,

Since I’m not very familiar with Form Server, I asked one of my colleagues who worked on Form Server if he might have an idea of what could be causing these errors. Here’s what he had to say:

The first issue regarding xfasubset.js not getting loaded could be due to an incorrectly set FSWebRoot. This value is set automatically but may need to be customized if the webserver and form server are on different computers.

As for issue #2, MSDHTML and AUTO should be identical when the client browser is IE. I suspect the client browser is not IE and if the user requests MSDHTML for any other browser odd behaviour will occur. When rendering HTML the AUTO form preference should always be used if there is no compelling reason to specify the HTML type explicitly. Using AUTO lets Form Server determine the best flavour of HTML to use and whether client-side scripting can be used.

Hi Stefan,
i had created 2 datadropdown list to read 2 tables from a same DB using the same method as in your answer #4 for mr Dwight, were one table is for ministry , other one for all directories of all ministries.
i want to link between 2 tables in a way that a user will pick a ministry from the datadropdownlist , but in a directory field datadropdownlist it will list only the directories that are belongs to the specified ministry. can you please assist me in this issue.

thanks,

This will be my first LiveCycle application. I have an Access 2007 database that contains contact data (first name, last name, etc.). This data will be editable in a PDF of a scanned paper form with databound fields that I’ve overlaid (that part is already done). I’d like to implement a “Find Contact” dialog that allows for search and selection of a single record from the database, which then gets its details populated into the PDF form. Any suggestions on how to do this?

Mark Richman,

I think my article on selecting specific database records is almost, if not exactly, what you’re looking for. Hopefully you can figure it out from there.

I should amend my above comment – I got the form working on my computer but when I have someone open it with Reader they get the above error – “Error:accessor ‘xfa.sourceSet.contact.update()’ is unknown” and the same with ‘addNew’ -So I guess my new question is can someone with Reader access and submit data through my form that has a database connection to an Access database on the server without having to buy Reader Extension and if so, how do I enable my form to be used by the lowest level of reader possible? Thanks

Well, I myself being fairly new to using LiveCycle I figured I should add that I discovered the following in the documentation:

“The host accessor is valid on any form design object that has events for scripting. You specify the host accessor by using the following syntax:
FormCalc
$host.property_or_method
JavaScript
xfa.host.property_or_method”

So, I assume I stand correct that this was my problem with “Error:accessor ‘xfa.sourceSet.contact.update()’ is unknown”.
Anyone willing to confirm?

Mariam Kadhimi,

With respect to your troubles with the drop down list, what exactly is it that isn’t working when the form is saved as XDP as opposed to PDF? Note that if you’re saving your form as an XDP, then I’m assuming you’re deploying it to a browser in HTML or PDF format using server technology such as Adobe LiveCycle Forms, which also implies that your server must have a DSN defined so that LC Forms can obtain the necessary data from the database and merge it into the form prior to the form being deployed to the client (browser).

As for whether or not a DSN is necessary to connect to a database, it is definitely necessary. If you’re using LC Forms to deploy the form, then the server must have the DSN configured on it; if you want everything to happen on the client side, then every system that loads the form must have the DSN configured on it. The only way I know around this problem is to create a web service which acts as the bridge between the client and the database (which also makes the entire solution much safer since you wouldn’t be giving-out information about the database itself to each client for the “client-only” case) and then connect your form to the web service instead.

Finally, for displaying a sum of particular fields on a page, I would simply place a numeric field at the bottom of each page and use a simple FormCalc expression to set its value using the Calculate event. For example, if page 2 had 3 fields on it, named “NumericField1”, “NumericField2” and “NumericField3”, and you wanted the “PageTotal” numeric field always to display the sum of their values, you would simply use the following FormCalc expression in the PageTotal’s Calculate event:

NumericField1 + NumericField2 + NumericField3

Andrew,

FormCalc has the advantage of supporting “shortcuts” or “aliases” to certain document properties while JavaScript cannot support them. For example, “$host” in FormCalc is the equivalent of “xfa.host”. You also have “$sourceSet” which equals “xfa.sourceSet”. Another useful one is “$record” which refers to the current data record and is short for “xfa.record”.

When you write scripts in JavaScript instead of FormCalc, you have to use the more verbose accessors (e.g. “xfa.record” instead of “$record”) because the JavaScript scripting engine doesn’t support the “$” character in an identifier name.

In short, when using FormCalc, either syntax should work although I’ve always had more success using JavaScript than FormCalc, especially when it comes to repeating subforms and Instance Managers.

Stefan,

I’m a newb to forms and really don’t have any scripting knowledge at all so please be patient. I’ve been looking at your examples and I can now successfully connect to my DB with the text values showing up nicely in my drop-down. I’ve used both the Custom Data Drop-down List example and the Dynamic Properties example with designer 7.1. What I want to do now is have another field popluated with the hidden value from the drop-down but I just can’t seem to get it to work. If I have another field called for example “Code” in the same subform as my DB populated drop-down what script should i use to get the hidden value into it?

Craig,

I’m glad to see you’re giving Adobe LiveCycle Designer a shot for creating your forms!

You mentioned you have a field named “Code” in the same subform as the drop down list you’re populating from your database. In order to get the drop down list’s hidden value into your “Code” field, you simply need to add a little script to your drop down list’s Change event which will get the hidden value associated with the item the user selected and set it as the “Code” field’s value. The following script should work in both FormCalc and JavaScript (which ever one you’re more comfortable with):

Code.rawValue = this.boundItem(xfa.event.newText);

In the statement above, we’re using the “xfa.event.newText” property to get the text data from the item that was selected. We’re then using that as a parameter into the drop down list’s “boundItem” method which will return the hidden value data associated with the item. Finally, the hidden value is set as the “Code” field’s value.

In addition to the above, I’m also getting the “Connection to the source DataSource failed becuase the environment is not trusted” error when i initially open the form. The form does actually retrieve values from the DB though so this message seems a little strange also.

Cheers

Craig

Hi I just created my first form in Adobe Acrobat 8.0 and I would like to be able to set up a database to extract the data from the fields to. I downloaded the sql driver from the first step and I was able to make it as far as naming the database. I am not sure which fields on the advanced, login, and connect options to fill in to configure the OODBC. Any help would be appreciated.

Hi Stefan,
I am using reference of this site : http://forms.stefcameron.com/2006/09/18/connecting-a-form-to-a-database/
but i am getting this error on all data controls button if i make it as formcalc script and if i change it to javascript data is not inserted in database. I have created system dsn

Script failed(language is formcalc;context is xfa[0].form1[0].PJ-AdminStaff[0].PJF-Faculty[0].dbAddnew[0])
script=//Data connection contol button>> adds new record
Error:accessor ‘xfa.sourceSet.adminform.addnew()’ is unknown

adminform is the name of my DataConnection

The Report pallette shows no error/warning.

If i change the script language to JavaScript, i don’t get any error/popup but the buttons don’t work,i.e the data is not inserted in database, my database is not having any primary key at present.

I followed the steps as outlined in this great blog.
I am using Adobe LifeCycle Designer 7.0 and am connecting to a Table in Access Database.

Can you please guide as to how can i resolve this?

Regards,
Arti

I also followed your suggestions which you gave to Siddartha Jain just for your reference pasting it here below:

May 24th, 2007 at 5:45 pm
That’s very strange. Based on what you described, you shouldn’t be getting this error.
What the “accessor unknown” error tells me is that the data connection’s name somehow isn’t “DataConnection”. Have you verified that the name of the data connection in the Data View palette is “DataConnection” and not “DataConnection2″ or something of the sort?
You could also try replacing “xfa.sourceSet” with “$sourceSet” (a FormCalc shortcut for “xfa.sourceSet”) in FormCalc to see if that makes a difference although I don’t think it would.
If none of this helps, what version of Acrobat are you using to run the form?

hi Stefan ,
iam new to adobe environment , but i have a case i wonder how it can be done using designer 8?

our client (a government authority )have an oracle database want to create pdf forms that its clients can fill offline and bring it back to the authority so they want to post these data again to the database

my question is : can i create a database connection programically (not predefine on the data view )

this is because the following :

we just need to connect the database in two case

1- first time to fill all the drop down list and other lookup data (populate the fields) then save the pdf to save this data

2- on the submit on the our client site after their customer fill it offline and bring it back to client (we want to post this data to the database )

( cause the form will be filled offline and the client not have access to the database)

i hope my questions is clear enough for you

Rabab Tawfeek,

For security reasons, data connections cannot be created dynamically. They must be pre-defined in the form.

Stefan ,

thanks for your replay , But how can i at least stop displaying the database connection failure message to appear to the clients when the open the form,

is there any way like raise the error message level , or like exception handling to this issue

Stefan,

Your sample is very helpful except for one problem I am having.

When I try to do the xfa.sourceSet.DataConnection.addNew() on my button it clears the form almost like it inserted it into my SQL database, but when I check it the information is not there. Then I fill the form out again and hit the Add New button again and it pops up an error stating the following:

Error: addNew operation failed. Empty row cannot be inserted. Row must have at least one column value set.

All of my fields are bound to the database and I do not have any auto-incremented fields.

Any ideas?

Hi Stephen,

I’m wondering if you can tell me how to use javascript for binding.

I have a dynamic subform, and this is what I’m trying to do

var test = xfa.resolveNodes(“pg1.assigned.ASSIGNED2[*]”);
for(i=0;i<test.length; i++){
if(id != null){
test.item(0).id.bind.match = “normal”;
}
}

thanks,

tyler

Ok…what I am trying to do is have a repeatable subfield and in that sub field there is a text field. This text field will have it’s initial binding set to “none”…this way when I export to xml nothing comes through

I want to go through all the subfields and if the sub field is not equal to null then change the binding to “normal”…now if I export to xml only those fields with data come through.

I did realize that field ‘id’ causes problems.

I will try using “once” and see if that does the trick.

Or maybe now that I’ve explained it a little better you might understand what I am trying to do.

Thanks,

Tyler

Stefan,

First, thank you for the guide. It’s helped solve a lot of issues so far and cleared up a lot of questions on implementation. However, the project I am undertaking will be using an Access Database with an auto-incrementing field. I am not familiar with any SQL Query statements so all the ones I googled and tried did not work. With access, what is the best way (or SQL statement) to select all the fields except the auto incrementing field? Thanks!

Alex,

I’m glad you’re finding the answers you’re looking for! Thanks for the feedback.

An auto-increment field in a table will cause trouble. When you create the data connection, you should choose “SQL” as the type and then specify an SQL statement that selects all fields except the auto-increment field from that particular table. That will get around the auto-increment issues.

For example, if your table “info” has 3 fields, “auto-inc”, “field1” and “field2”, the SQL would look like this:

SELECT field1, field2 FROM info;

I’m using LiveCycle 8.0. I have copied and pasted the data connection control snippet and even tried using the group properties to let livecycle know it’s there, but it doesn’t appear in the library. I even tried adding a bunch of buttons and dragging them to the library and adding the script to each button individually. The buttons have an icon in library until I alter the code and save the file. Then the icon disappears from the library. I am being careful to save it as xfo. What am I doing wrong?

Thanks in advance

Nener,

It’s possible that the updates you’re making are invalidating the XML in the XFO file, at which point the Library stops displaying the objects because it can’t parse the files to tell what objects they represent. One common mistake is to use angle brackets (< and > for smaller-than or greater-than) in script blocks without encoding them using &lt; and &gt; instead (assuming you’re editing the files outside of Designer since Designer’s Script Editor palette takes care of the encoding for you). Random brackets like this cause the XML inside the XFO to become invalid. One way you can check for this is to change the extension to XML and double-click the file. This will cause it to open in Internet Explorer (by default, anyway) which will then attempt to display the contents as pure XML. If you have a syntax error in the XML, IE will tell you where it is. Fix it, rename the file extension to XFO and then restart Designer. The object should appear in the Library.

Vipin,

Unfortunately, you can’t cause a popup window to appear which lists values that a user can pick from. You can cause message boxes and input prompts to appear but that’s it.

I think you would have to use a drop down list object on the form which you show and hide using the button rather than using a popup window. You can make the text field read-only by choosing “read-only” from the “Object palette > Value tab > Type property”. You can make the drop down list hidden by choosing “hidden” from the “Object palette > Field tab > Presence property”. You can then make the drop down list appear when the user clicks on the button by setting the button’s Click event script, using the Script Editor palette, to

MyDropDownList.presence = "visible";

Once the drop down list is visible, you can set its Change event script to be

MyTextField.rawValue = xfa.event.newText;
this.presence = "hidden";

This will take the text value of the item the user selected and set it in the text field and then hide the drop down list.

Hi the blog,

I found a solution with a lib java…

ResultSet rs = stmt.executeQuery(“SELECT * FROM database”);
while(rs.next()) {
PdfReader reader = new PdfReader(“C:\\temp\\collecte.pdf”);
PdfStamper stamp1 = new PdfStamper(reader, new FileOutputStream(“C:\\temp\\collecte-” + rs.getString(“acronym”)+”-out.pdf”));
AcroFields form1 = stamp1.getAcroFields();
form1.setField(“Entity”, rs.getString(“name”));
stamp1.close();
}

BillA,

I’m glad you found this page useful!

I’m a little unsure as to what you’re doing exactly however to answer your question about programmatically “cutting” the data connection, I think you mean you simply don’t want the data connection to be “executed” once you move the form into distribution mode.

To do this, you would need to configure your form to execute the data connection only if it hasn’t been executed already (e.g. only if some field on the form that gets filled by data from the connection isn’t filled). To do this, you’ll need to control if and when the data connection is executed and my tutorial on selecting a specific database record will help. It shows you how to manually “open” (execute) an ODBC data connection after a user makes a selection from a drop down list. For example, you could put script to open the data connection when the form is initialized (using the Initialize event) only if that special field isn’t filled.

Extremely helpful example and discussions. Fairly new to designer and databases for that matter. I have run into problem with trying to submit data from a form to MS Access with two linked tables (Linked by an ID field). One table contains a persons demographics and the linked subdatasheet contains that persons contacts (one-to-many relationship, where there may be multiple contacts for a person). The designer form I’ve created has fields for the persons demographics and a table for a persons personal contacts. The table of personal contacts uses instance manager to add rows for each personal contact named. I am having problems submitting the table data into the subdatasheet when there are multiple items in the table. Any way to do this…or only able to submit one row at a time to subdatasheet?

Appreciate any insite you or others may have. Thanks,

Ben D.,

I think the problem is that the ODBC data connection sees everything as individual rows and wants to update everything as an individual record. In your case, it sounds like each “record” is a person that has one demographic and many contacts. You would probably need to do the join in the form itself using two data connections: The first to the demographics table and the second to the contacts table. Perhaps a third data connection would be necessary to obtain a list of persons. Selecting a person would modify the SQL statements used in the other two data connections to search on the person’s ID and pull-up relevant information. Once those data connections are opened, they’ll produce records which the form should be able to update.

See my tutorial on selecting specific records from a database for an example on how to modify a data connection’s SQL statement at runtime (using script).

Dan V,

It sounds like the “manual entry fields” aren’t related to the data connection (i.e. they aren’t bound to a data node in the data connection). If that’s the case, it makes sense that they don’t change when you move to the next record (since no information in the following record will be pushed into those fields). To clear the “manual entry fields”, just add some lines to the “next” button’s Click event script to set their “rawValue” properties to “null”:

MyTextField.rawValue = null;
MyDateField.rawValue = null;
xfa.sourceSet.DataConnection.next();

Jeremy Gower,

It’s no bother at all!

I had a look at the scripts in the purchase.pdf form and couldn’t find a spot where there may be errors if the type of the ID column in the table was changed to be a string (text) rather than a number so I’m going to guess that the errors you’re seeing are coming from the fact that the data retrieved from the database and set into the ID field is text rather than a number yet the ID field is a numeric field, not a text field. Assuming that’s the problem, you can easily change the ID field to a text field by setting the “Object palette > Field tab > Type property” to “Text Field”.

Do that and give it another try. If you still get errors, I’ll need to know what the errors are exactly.

Jeremy,

Given you’ve changed the type of the ID column in the database to “text” and that the error is “open operation failed” (which means the ODBC drive attempted to execute the query and an error occurred), I think there’s something wrong with your query syntax: You should make sure that you wrap the ID you’re filtering on in quotes since you’re comparing the SelectField’s value to a column of type “text”:

$sourceSet.DataConnection.#command.query.select.nodes.item(0).value =
    Concat(”Select * from OfficeSupplies Where ID = '“,
    Ltrim(Rtrim(SelectField.rawValue)), ”'")

Notice the single quotes that will wrap the result of the Ltrim(Rtrim(SelectField.rawValue)) statement.

Stefan,

Sorry to bother you again, but I’m having another problem. After I got the first form working I wanted to do the same thing but with another form…The scripts look identical but I keep getting ‘script failed’ and then it gives the script for the button. In addition, on this new form it is already populating the form with the first item in the database rather than being blank until i enter the ID name and hit refresh. Any ideas?

Jeremy,

How did you create this other form? Sounds like it’s not quite identical to the other one.

Stefan, after going thru a number of your articles and LiveCycle ES help files etc.etc.. I have pdf forms up and running loading data from a web service and then submitting form data to a backend SQL database.

My question which I have yet to find anything written about?? … Is AFTER the submitting data portion, is there a way to dynamically unbind the form? Disconnect the pdf from web service, unbind the fields…etc.etc.. so a user could save the pdf on their desktop (or on server) with static data that no longer accesses database?? In effect, ending up with a pdf that will no longer load up up web service data or submit data back to dbase…..

thanks much, Mike

Stefan, thanks for the great info on your site.

I use a dynamic pdf that populates from an access mdb.

Data is displayed on number of sub-forms all set to flowable layout allowing page breaks.

My question is a simple one.

How do I display a simple rectangle around the subforms that expands/contracts according to the amount of ‘stuff’ in the subforms ?

Thanks very much for that info. I can definitely try that.

I need to display a stretchable rectangle for a page right hand side column from the top of the page to the bottom of the data.

Is there any way to accomplish this ?

Thanks. Hmmm. So I use a table, insert flowable subforms into cells and use a border for the table column ?

Hi Stefan-

I hope your still monitoring this thread…
I’m designing a form in Acrobat/Designer 8.0 modeled after your excellent articles. My ultimate goal is to have a form like yours with a data dropdown list which shows a list of workOrderID from my workOrders table and then upon list item selection queries my SQL database and brings back several fields the table.This form needs to be used by my users in Reader 9. I have created my system ODBC in Windows Admin Tools and then my DataConnection. I’m starting small and working my way up to the SQL Query part. For now I have a form with just 2 fields and your great Navigation Buttons. I have “reader extended” the form and certified it. When I open it in acrobat/Designer the buttons work but in reader I initially see the first record in my table but when I click the ‘Next’ button, I get an error when :
accessor ‘xfa.sourceSet.DataConnection.next()’ is unknown

If you’re out there and have a moment I could sure use a hand.
thanks,
Rob

Stepan,
I have followed your instructions and my form works great with (First, Previous, Next, Last) Functionality. However when I am trying to make a simple SQL lookup I am getting the following error.
Error: The Operation violates your premission configuration

here is how I am doing it.
I’ve added a button and on click event i execute this script;

$sourceSet.epics.#command.query.commandType = “text”
$sourceSet.epics.#command.query.select.nodes.item(0).value = Concat(“Select * from foy.Cust Where custnum = ‘1120’”)
$sourceSet.epics.open()

Any help is appretiated.
Thanks a lot

Hi Stefan,

Thanks for all of your wonderful tutorials and information. The new aspects of my job would have been a NIGHTMARE with out you!

I just wanted to pop up an additional tutorial for connecting to an excel file.

I had difficulties with the one you posted and found that it was because you REALLY need to name a range. It SHOULD work with the sheet name, but it does not always. So here ya go!

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q195951&amp;

Hello Stefan,

First off, thank you for your blog. Most everything I’ve learned about livecycle form design has been from your blog. It’s not very easy to find sources on the web for help with Livecycle. So thank you!

I’ve been using an odbc connection to connect a livecycle form to an access database. It has been working fine. Then I added another field to the database, and added the field to the form. I now get an error when I try to update the record on the form to the database. I can’t say for sure if the error has anything to do with adding the new database field, but that’s the only thing I can think of that I’ve done recently.

The error message is:

Script failed (language is formcalc; context is
xfa[0].form[0].form1[0].#subform[0].Button4[0]0
script=…
Error: updateRecord operation failed. Multiple-step operation generated
errros. Check each status value.[Id:11]

Any insight would be appreciated.
Thanks!

Chris

Ok, I figured out the error of my ways. Darned autonumber fields.

Thanks again!

Hi, great blog!

Do you know how I would be able to add a duplicate record in access from the form? I have the form working nicely with the database, but simply need to be able to add the same record twice if need be. As of now, the database just updates with whatever newer information I put in the fields I have designated for the particular record called, but does not replicate the record number. I am using the “purchase” database/form. PLEASE help! Thanks!

I am thinking the “addnew” command might have something to do with it…

In short, when I try to set up a button to submit values to an OLEDB Database, the only available option is the WSDL File. Would like to understand why this is happening.

In detail:

I’m trying to create an Employee of the Year nomination form that will insert information into a database on our Microsoft SQL 2005 server. When I set up a New Data Connection (and the object-field associations) through the File menu, everything seems like it’s working. When I preview the PDF, it is displaying the first test entry from the database… not quite what I want, but it illustrates that the connection is at least functional.

I’m assuming that all I need to do now is create a button that submits the information from the form to the database. So I created a button, selected the “Execute” control type, clicked on the “Execute” tab, and selected “New Data Connection…”. However, the only option available to me (ie. selectable, not grayed out) under “Get Data Description from:” is the WSDL file. I’m not familiar with WSDL, and i’d really like to make use of the OLEDB connection.

Is there a reason why the OLEDB is grayed out? Am I going about this in a completely misguided way?

Any assistance anyone can provide would be greatly appreciated.

Emily,

ODBC (database) connections are designed only to work with one record at a time. Once a record is loaded into bound fields, modifying one of those fields will actually update the record’s data in the database via the data connection so there’s no need for a “submit” button to submit changes back to the database.

If you’re wanting to see and update more than a single record at a time, then you’ll need to do some scripting in order to display some or all of the records and update many in a single query.

HI,

I need to update Database column on button click event

script on the click event

oDB.#command.query.select.nodes.item(0).value = Concat(“update employee set name = “,empname.rawValue,” where employeeId=” ,(Ltrim(Rtrim(employeeId.rawValue))),””)

This updates DB column but i get “ado2xfa operation failed. Item cannot be found in the” error

Any help is appriciated
Thanks

Stefan,

I created my file with Adobe LiveCycle Designer ES 8.2 and when I used the PDF Preview option I could not get the buttons working. I did check my data connection to make sure it’s named as “DataConnection”. I am completely lost…

Hello Stefan, I have two questions I’m hoping you can help me with:

1. I have a form which uses two data connections (both connecting to the same access database). The first connection is used to display a list of project names in a listbox. The second connection is used to add a new project into the database (if it’s not displayed in the listbox).

My question is: after a new project is added, is there a way that I can ‘refresh’ or ‘reload’ the data in the listbox to display the newly added project in the listbox list?

2. I’m finding that these forms I’m building work fine for anyone that has the standard or professional versions of Adobe Acrobat software on their PC’s but that they don’t work for anyone that just has Adobe Reader installed (version 8 in our case). Is there a setting I’m missing in my form to allow them to work with Reader, or do forms only work with the standard/professional software?

Thanks for any help Stefan,

Chris Holmes

Stefan,

Thank you very much for taking the time from your vacation to answer my question. Appreciated very much!

But, I do have two other one’s:

1. When my forms open up, they open in a page width view. How do I set things so they will open to a full page view? I assume there must be a way to set forms to open at different zoom levels?

2. When my forms get closed by the user, they are presented with the “Do you want to save changes…” dialog box. Is there a way to make this not display, as nothings been changed and the data has already been written to the database?

Thanks!

Stefan, I have read your reply #26 (Dec 12, 2006) and have tried to apply it to a short test Live Cycle form I’ve written to capture a single database value and display it on my form. The field binding works perfectly when I run the form on Adobe Acrobat 9 Pro v9.1.1, but when we run it on a different machine with Adobe Reader 9 Version 9.1.2, the field does not populate from out database. I have saved the form in LiveCycle Designer ES Version 8.2.1.4029.1.523496 and then opened it with Acobat 9 Pro v9.1.1. In Acrobat 9 Pro, I selected the Advanced | Extend Features in Adobe Option and resaved the document. After copying it to a PC running Adobe Reader 9, the form field would no longer get the most recent value from the database. I assured the ODBC System DSN was defined exactly as the ODBC System DSN on the system running Adobe Acrobat 9 Pro. Do you have any thoughts as to why this will not work in Adobe Reader? Thank You.

Stefan,

I’m struggling with the question of a Data Source Name being required to be set up on each machine that needs to use the data connection. I am trying to distribute a form to respondents who will choose their ID number from a drop-down field populated by the data connection, then four additional fields are populated via the bound fields from a second data connection (per your tutorial “Selecting Specific Database Records”). Do I understand that using OLEDB databases for the two connections will require that each respondent have the Data Source Name set up on their individual machines? If so, is it possible to accomplish this form using XML schemas?

Kelly Kraus,

Yes, each person would have to (1) have Acrobat Standard or Pro (unless you’re extending the PDF using LC Reader Extensions so that they can use the free Reader) and (2) have each DSN setup on their computer.

Using an XML Schema wouldn’t help much either. It could, perhaps, get around the DSN issue but it won’t circumvent the fact that the free Reader must be enabled for data import in order to get data into your form. This means LC Reader Extensions would still be needed to extend the PDF for use in Reader or everyone would still need to have Acrobat Standard or Pro to use with the PDF.

Another option is to do the data merging on the server using LC Forms. This would get around needing the DSNs, Acrobat Standard/Pro and LC Reader Extensions because the data import would occur on the server, not on the client.

Hi Stefan:

Thanks for answering all my earlier questions. This question has been bugging me for sometime now. The situation described here could be unique to me.

My PDF form is a dynamic XML 8.0 PDF form designed using LC designer 8.1. I use ODBC to connect to the DB and load data onto the form. I have been able to successfully retreive data from my database and updatethe form.

Once I load the data on the form, I reader-extend the forms (filled with data)using Acrobat professional 8.1. using Advanced->Enable Usgage Rights in Adobe Reader and save that form filled with data.

Now the problem is: When I use the form in Reader-extended PDF form in Adobe Reader everythign works perfect. By mistake I open teh same Reader-extended form using Adobe Acrobat it automatically thinks that it should make a ODBC data connection.( the reader extension becomes invalid)

My question: To make Reader-Extended PDF, I need the data connection. Once Reader Extended using Acrobat Professional, I want to disable the data connection. it makes no sense for the form to connect to DB after that stage in my business process. This form needs to be filled by the end users and signed and scanned ( old fashion..I cannot help it). How can I suppress the data connection information in the Reader-Extended PDF.

Is there any other way I can achieve this?

Hi Stefan, I have reviewed examples of your blog, from the adobe’s forums… but I have a problem with the data binding in an invoice form: I have a DB with 500 customers (and the name of its establishment, address, bank account number, zip code…). I have connect the form with the DB, but when I select a customer from the drop-down list I designed, the rest of its data not appear in the different fields. I have no scripting experience. Can you help me, please?

Dear Stefan,

I have a question for the info you gave at message number “186”.

OK, i will use data import on the server using Forms ES from The Process Management. I can query db or execute web service and get data and bind it to the form fields. But, i could not find a way to bind data o drop-down list. I can retrieve the data even in xml format from db, but how can i bind it (add all data in dd list) to drop-down. Could you please advice ?

Kind regards
Sedat

Sedat Reyhan,

You could try using dynamic properties to auto-populate a drop down list’s items from data.

Tammy Hand,

Could it simply be that you have a space in your table name? As far as I know, table names can’t have spaces in SQL. You query should look like this:

SELECT Title FROM ManualInfo;

Angelika,

If you simply want to submit data from your form to your database via PHP, you should look at how to submit data via HTTP.

If you want to import/export directly from your MySQL database, then you would need to install the MySQL ODBC Driver, though this method is not considered very secure since you may be exposing your database on a public site and everyone using the form would need to install/configure the ODBC driver on their systems. Ideally, you would read/write from/to your database via a web service. This could provide a layer of security over direct ODBC connectivity.

I am trying to create an expert form in Livecycle where my users could select a field from a drop-down list say LastName and the form will auto populate the rest of the fields such as FirstName, Address, and SS# automatically.

I also created a simple MS Access database with a few fields such as FirstName, LastName, Address, City, State, phone, and SS#. I added a drop-down list for the LastName and bind it to one of the LastName field in Access, I also added the other fields in the form such as FirstName, Address, City, State, and SS# by draging them from my data connection to the form. When I load the form, the form is populated with the first record in the database. The problem is I was able to see the list of the LastName from the drop-down and able to select the different LastName, but the rest of the fields are not updated. Please advice.

Thanks in advance.

Mac

After eight weeks of struggling with hyperlinking in Designer 8.0, this post solved my problem. You are my hero!

Also, I have downloaded the MySQL 3.51 driver, and it does not appear on the driver list when attempting to create a new data source. Only SQL Server 6.00.6001.18000 appears on the list.

Michael K.,

Yes, you need a MySQL server in order to use the driver. You could also use a Microsoft Access database, or Oracle, DB2, whatever — as long is there’s an ODBC driver for it.

I don’t have the exact tutorial you’re asking for but my tutorial on selecting specific records from a database is basically what you would need to do. The tutorial shows how to capture information in order to query specific records from the database and display only those records (of course, in your case, the query might always return a single record, but that’s no different).

Help!!!

Well, I am new to developing forms but somewhat computer savvy. I need to design a form that would allow the user to select his or her name from a drop down box. Once they have chosen their name, their employee number and brach number would populate. Does anyone have any thoughts on how to do this? I have all of this information contained in an excel spreadsheet but have had NO luck connecting it to the form.

Thank you,

Christopher

Michael K.,

In regards to (b), you would have to store the images in your database and bind those cells/rows to image fields or you would manually include them in your form and show/hide them as required.

Unfortunately, you cannot load any image into a PDF. See this post for an explanation of how images are dealt with in PDF forms.

Hey Stefan,

This is a great tutorial and I feel I am almost at the finish line to get this thing to work, but I do have a few errors. I am connecting an SQL 2005 Server DB to a PDF. I created a connection within LiveCycle ES 8.2 using an OLEDB Database Connection entitled RCTS. I clicked on the build button and used the SQL Server Native Client 10.0 as the Data Link Property Provider. For the Data Link Property Connection I entered in the SQL Server Name, I selected “Use Windows NT Integrated security” (I did not enter the Server SPN), then I selected the Database. In the advance panel for Data Link Property I set the Trust Server Certificate to True. I then tested the connection and it was successful and I selected the table from which I will need data to update and where the inserted data will be going to. I selected the fields from the data view and created the buttons as you did. When I preview the PDF I get the dreaded “Connection for Source RCTS failed because the environment is not trusted”. I guess my first question is, has anybody been able to solve this? I have seen people have had this problem on your blog already, but there has been no solution. I have searched the web far and wide and have come up short. I’m curious if you have seen solution since the last time someone has had this issue. It’s driving me nuts! After I click okay I get”

Script failed (language is formclac; context is xfa[0].form[0].Button1[0].#subform[0].dbfirst[0]) script=xfa.sourceSet.{RCTS}.first()

Error: syntax error near token “{” on line 1, column 15″.

And I get that same error for the rest of the command buttons. HOWEVER THE FIRST RECORD DOES APPEAR! Now I saw that Siddhartha Jain on May 22nd, 2007 had the same issue and you advised her to take the “{” and “}” out of the connection name. However, when I do that I again get the environment can’t be trusted error and now I get a new error stating

“Script failed (language is formcalc; context is xfa[0].formButton1[0].#subform[0].dbfirst[0])script=xfa.sourceSet.RCTS.first()

Error: first operation failed, because there was no connection specified.

And I get this for the rest of the command buttons.

Any suggestions for the trusted environment error and the errors that occur when I preview the form.

Thanks a lot, and sorry for the long read

Paddy

Paddy,

Sometimes the “environment is not trusted” error can be related to attempting to modify a data connection in an XFA 2.5+ form.

Paddy,

The important part is the one about cloning the Data Connection prior to modifying it. Just apply the following concept to your script:

var oDB = xfa.sourceSet.nodes.item(nIndex).clone(1);

or

var oDB = xfa.sourceSet.YourDataConnectionNameHere.clone(1);

Just to add, each time I preview I get a different record! lol I feel like I’m very close!

I would like to connect a listbox or combo box to a database which would enable me to populate the list from a query. I have a table with one column and 100 rows. I want the column data to show up in the list box. Does any one have an example of this. When I connect to my SQL database, I can only see the first row of data in the list box. I am not able to populate the entire list into the list box. Any examples would be helpful.
Robert

I’ve spent that last 8 hours trying to get my form to work. My problems seem similar to Patty.

Facts:
I’m using SQL Server 2008
On Windows Server 2003
Using LiveCycle 8.2
MySQL is not installed on my server

My problem my be with the first step in setting up the ODBC Connection. I tried using Native Client 10.0 as Patty did but I’m not sure if I filled it out right. I couldn’t find any examples of how to properly connect a SQL Server database to LiveCycle and I found very little information about LiveCycle working with SQL Databases in general which seemed odd.

When I open my form I get the first record in the database but none of the buttons work.

I keep getting the error:

Error: Accessor ‘xfa.sourceSet.DataConnection.”buttonname”() is unknown.

I tried creating a new button and changing the code to the following to see if it would at least show a different record:

———————————
//Change the commandType from TABLE to TEXT. TEXT
//is the equivalent of SQL Property
xfa.sourceSet.DataConnection.#command.query.commandType = “text”

//Set the Select Node. Select in this case will be
//whatever SQL Property you want
xfa.sourceSet.DataConnection.#command.query.select.nodes.item(0).value = “Select*from age Where ID = 3”

//Reopen the Data connection
xfa.sourceSet.DataConnection.open()

———————————

It gave the error below:

Error: Accessor
xfa.sourceSet.DataConnection.#command.query.commandType’ is unknown.

Your help would be greatly appreciated.

Adisa

@Adisa,

Are you trying this with Reader or Acrobat? Note that Reader, unless the PDF is extended to allow data import, cannot open database connections. You should be testing this first with Acrobat which has all necessary “rights”.

Yes Stefan there was an error message . But what I did is after adding by add button I placed an Update button to update the DB.Then it worked fine . Could you please let me know where was I wrong ?

Thanks.

Bibhu.

Hello Stefan,

I have been reading a lot of your blogs and they have helped me a greatly, Its nice that these sites are here.

I have a questions about your process, I followed what you did for my own form and I do not receive any errors but I do not get any information populating my fields. I tried working with this type of process in the past to see how it works and I finally made it work. But, I remember I had to save the form a certain way once I brought into Acrobat or import some information.

If you have any ideas of what could be my problem please let me know.

Using LiveCycle 8.2, pulling the data from an Excel Spreadsheet from a SharePoint site.

I have done this before from a SharePoint site, but with only using a small amount of information.

Please Help

@Todd,

The issue is likely that you’re using Adobe Reader instead of Adobe Acrobat to run your form. Reader cannot < a href="http://forms.stefcameron.com/2006/08/12/importing-data-in-acrobat/">import data without certain permissions being set on the PDF.

Hi, have a question on updating access databases.
using LC2 designer and Adobe Pro 9. I have a a query working using the slect … with a WHere to get a specific record. Works great to prepopulate a form with specifc data. Now I am trying to get 2 new SQL querys to work, the INSERT INTO and the UPDATE query.
I am not talking about these:
xfa.sourceSet.{DataConnectionName}.addNew()
xfa.sourceSet.{DataConnectionName}.update()

Does Adobe support these SQL queries, and if so, do you have a sample that I can see?
Ken

I have been developing a form in Designer 8.2 based upon your articles and I have been successful at loading data onto the form from an Access database and then later modifying the associated fields in the database using insert, delete and update SQL statements.

To do so, however, I have created a couple of data connections within the form and I find that whenever the form loads and whenever my script accesses a data connection, the user gets a message indicating that the form is trying to access the data connection. Could you tell me what I should do in order to suppress these prompts? My users will know that the form is database enabled and should not be required to answer these pop-up prompts multiple times while using the form. Whatever you can tell me about this issue will be appreciated.

@Tom,

First, the confirmation dialogs that are displayed when Acrobat attempts to connect to the database cannot be suppressed programmatically. The dialogs are part of a security feature that ensures that a user’s data isn’t access without the user giving explicit consent to the access.

Second, about your form’s strange behaviour. When you preview in Designer, you get a new PDF file every time you preview your form. Having a new PDF must have something to do with this behaviour. What is the generated error message (output of “console.println(e)” in your code above)?

If it is tied to not closing the connection properly (though I haven’t experienced this myself), are you certain that you’re closing the right connection in your tests? You should be storing the cloned (opened) connection (“MainConn”) in a Script Object (as a declared variable) and then closing that connection, not closing the connection again and closing the second clone. Is that what you were doing in your tests?

Hi Cameron,

Thanks for your reply. Because this code exhibits such odd behavior, I decided to rule out any possible problems with my system. So I did a complete uninstall of Adobe Pro and re-installed it. Then I started with a new form, adding no
scripting other than the code I am having a problem with. This version exhibits the same strange behavior as I described previously. Here is the entire script for this program.

form1::docClose – (JavaScript, client)
try {
if (g_HdrConn != null) g_HdrConn.close();
if (g_MainConn != null) g_MainConn.close();
}
catch (e) {
xfa.host.messageBox (“An error occurred closing the data connections.”);
console.println(e);
}

form1::docReady – (JavaScript, client)
var strDocKey= new String (“”);
var strSQL= new String(“”);

strDocKey= “123”; // set document key here to test code

strSQL= “Select * from [Table_1] Where [DocumentKey]= ‘” + strDocKey + “‘”;
try {
g_HdrConn = xfa.sourceSet.Hdr_Conn.clone(1);
g_HdrConn.resolveNode (“#command”).query.commandType= “text”;
g_HdrConn.resolveNode (“#command”).query.select.nodes.item(0).value= strSQL;
g_HdrConn.open();
}
catch (e) {
xfa.host.messageBox (“The header data was not initialized: ” + strSQL);
console.println(e);
}
strSQL= “Select * from [Table_2] Where [DocumentKey]= ‘” + strDocKey + “‘”;
try {
g_MainConn= xfa.sourceSet.Main_Conn.clone(1);
g_MainConn.resolveNode (“#command”).query.commandType= “text”;
g_MainConn.resolveNode (“#command”).query.select.nodes.item(0).value= strSQL;
g_MainConn.open();
}
catch (e) {
xfa.host.messageBox (“The main connection was not initialized: ” + strSQL);
console.println(e);
}

form1.#variables[0].g_Globals – (JavaScript, client)
var g_HdrConn= null;
var g_MainConn= null;

This executes correctly in Designer and will execute correctly exactly once from Acrobat. When it does so, the console window displays no errors. After running once in Acrobat,
the above error messages are displayed, indicating that the connections were not initialized (or later closed). However, when the connections fail, the console window also fails to open (even with Ctrl-J) so I am unable to get feedback other than my own messages.

Another oddity: if I open Designer and run Preview once and then exit Preview mode– but then leave Designer open while I run Acrobat, the Acrobat version will continue to run
correctly when opened multiple times as long as Designer remains open. After I close Designer, it will run once and then fail.

I really would appreciate a solution for this one! I would be happy to email you my pdf file if that would be any help. My Access database consists of two simple tables, each
with a single key field and just 2-5 other data fields, so the data connection would be easy to replicate.

Thanks for any advice! Tom

@Bev,

When you configure the data connection, the last page in the wizard has a “delayed open” option. Check that box. The result will be that the connection is not opened (and the first record loaded) until you make a call to the connection’s “open()” method, which you can do as a result of a button Click event, for example.