Connecting a Form to a Database
In response to Lala and malik’s questions on connecting a form to a database (whether it’s Microsoft Access, MySQL, etc. doesn’t really matter), I decided to write a little tutorial on how to do it.
Even if you already know how to do it, I encourage you to pay special attention to the section on Auto-Incremented Table Columns because it might help you understand and resolve some of the issues you may have already run into.
Create a System DSN
First, you need to create a System DSN for your database using the ODBC Data Source Administrator Windows tool.
That will let Acrobat interface with your database when your form is opened in Acrobat in order to be read and/or filled. Because of the DSN, it doesn’t matter what kind of database you need to connect to.
For this tutorial, I’ll be using my FormBuilder database which contains the same kind of movie information found in the XML Data files from various other tutorials I’ve already posted:
This SQL file will create the database and a user if you’re using something like MySQL (for which you can also download a free ODBC driver). You should also be able to easily tweak it to create tables in a Microsoft Access database if that’s what you want to use.
Make a New Data Connection
The second thing you need to do is create a data connection in Designer: From a new or existing form, open the Data View palette (you can use the “Window | Data View” menu item to open it) and choose “New Data Connection” from the palette’s fly-out menu.
In the “New Data Connection” wizard, pick “OLEDB Database” from the first screen, using the “Build” button on the next screen to open the “Data Link Properties” dialog, go to the “Connection” tab and pick the name of the DSN you created in the first step from the first drop down list. Then click the “Test Connection” button to make sure a connection can be established to the database via the DSN.
Click on OK to close the “Data Link Properties” dialog and return to the “New Data Connection” wizard. Now that you’ve chosen a DSN, you’ll be able to specify the resource within that DSN to which the connection should be made: You may either pick a Table from the list, specify a Stored Procedure or specify an SQL Query. If you’re connecting to a single table, you may be able to simply pick its name from the list of tables. If you need to connect to multiple tables in the same data connection, then you’ll need to use a Stored Procedure or an SQL Query.
Auto-Incremented Table Columns
If the table you’re wanting to connect to contains any auto-incrementing columns, you must use the “SQL Query” option instead of simply choosing a table name from the “Table” option. If you pick a table with an auto-increment column, you’ll be able to read from it but you’ll get errors when you try to push data into it. If this is the case, write an SQL Query that selects all columns in the table except for those which are auto-incremented. In the image above, I chose “SQL Query” because the “movie” table I’m connecting to has an auto-incrementing column named “id” that needs to be excluded from the data connection.
Bind Fields to Data Connection Nodes
At this point, you should have a new data connection listed in the Data View palette which contains a list of “nodes”, one for each column in the table(s) you picked while setting-up the data connection:
The next step is to create fields to represent each node in the data connection and bind each field to its respective data node. The easiest way to do this is simply to drag & drop the nodes from the data connection onto your form. This is handy for two important reasons:
- The Data View palette has inspected the definition of each node and pre-determined the best type of field to use in order to edit its data.
- When you drop the nodes onto the form, the fields that are created are automatically setup to be bound to their respective data nodes.
The database I’ve connected to is one that uses the Movie Data I’ve used in previous tutorials. In this case, I’ve connected to the Movie table’s “title” and “showTime” columns. Since the “title” column is described as VARCHAR, the Data View palette figured it should be a text field. As for the “showTime” column, described as TIME, it’s set to be a date/time field with its Data Format property preset to Time.
After you’ve completed this step, the Data View palette now shows the data nodes in the data connection as “bound” with special icons:
Add Control Buttons
The last step in this process consists in adding a set of controls to manipulate the records in the database obtained via the data connection. The simplest way to do this is to use a set of buttons where each is assigned one of the following statements (each statement is one line and provided in FormCalc):
xfa.sourceSet.{DataConnectionName}.first()
xfa.sourceSet.{DataConnectionName}.previous()
xfa.sourceSet.{DataConnectionName}.next()
xfa.sourceSet.{DataConnectionName}.last()
xfa.sourceSet.{DataConnectionName}.addNew()
xfa.sourceSet.{DataConnectionName}.update()
xfa.sourceSet.{DataConnectionName}.delete()
xfa.sourceSet.{DataConnectionName}.cancel()
where {DataConnectionName} should be replaced by the name you gave to the data connection you created earlier (“DataConnection” by default).
Each statement above represents a different action to take with the data connection: Move to the first, previous, next or last record, add a new record, update or delete the current record and cancel changes to the current record, respectively.
Note that the “first”, “previous”, “next” and “last” statements imply an “update” by default which means that if you simply use
xfa.sourceSet.{DataConnectionName}.next()
to move to the next record and the user has made changes to the current record, those changes will be committed prior to moving to the next record. If you want those navigation controls not to commit changes (and therefore require the user to explicitly click on the “update” button in order to apply any changes to the current record), you must specify the cancel statement prior to the next statement:
xfa.souceSet.{DataConnectionName}.cancel()
xfa.souceSet.{DataConnectionName}.next()
To help you do this quicker now and in the future, here’s a Library Object Snippet that you can place into the Custom tab of the Library palette (you’ll have to save the XFO file to the following folder on your system: C:\Documents and Settings\{userid}\Application Data\Adobe\Designer\en\objects\custom where userid is your windows user id).
Download Data Connection Controls Snippet [xfo]
Minimum Requirements: Designer 7.0, Acrobat 7.0.
Once you place the file in the folder indicated above, you’ll then have a new object in the Custom tab of your Library palette named “DataConnectionControls”. Simply drag the object onto your form and the buttons will appear, all pre-configured and ready to go.
Run Your Form
Now that the DSN, data connection, fields, bindings and navigation controls have been setup, you should be able to preview your form in Acrobat Pro and see the first record in the database table(s) pre-loaded into the bound fields.
If you’re having problems getting this going, you can check-out my form (assuming you’ve created the FormBuilder database and a DSN for it) to see if you missed any steps:
Minimum Requirements: Designer 7.0, Acrobat Standard 7.0.
Updated: October 17, 2006
Posted by Stefan Cameron on September 18th, 2006
Filed under Data Binding,Tutorials
Both comments and pings are currently closed.
Two scripts failed, delete and update. Error message for both: “Empty row cannot be inserted. Row must have at least one column value set.”
Suggestions on how to fix this?
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?
Gar,
It sounds like you’re trying to do an “addNew” to add a new record to the table and then immediately doing an “update”. The call to “update” with no data may violate a “not null” rule set on one of the columns in the table the data connection is editing.
Try to specify data for all fields prior to calling “update”.
As far as getting a message stating that an “empty row cannot be inserted” when calling “delete”, that sounds a little strange because you’re trying to remove an existing record, not insert a new one.
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:
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:
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,
The Data Connection Controls are excelent… Do you have a way also to go straight to a record, possibly using a drop down box, where we select a record and every other field is updated acordingly?… Cheers.
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.
Ricardo,
I figured the answer to your question was best explained in detail with a sample. Check-out my latest post on Selecting Specific Database Records. Note that you could set the SQL statement to extract a single record from the data connection, thereby achieving the functionality you were seeking.
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?
Hi Cameron,
Thank you for your answer. It is awesome on the 2 data links.
But based on the solution Above “Connecting a form to a database”, I was wondering a solution to go straight to the record wanted, instead of going one by one through all the records. Something like a drop down box that once you choose the record wanted, all the other fields on the form, connected to the database show their values related to the chosen record on the form.
Cheers.
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.
I need to use XML files instead of a database. I have a load data function and record navigators. Is there an equivalent for the Add/Delete Record buttons for XML file data?
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?
Bill,
XML Data files aren’t edited in the same way as database tables are via data connections. As such, the concept of the add/remove buttons doesn’t really apply.
What happens is that you define a data connection based on a schema to which an XML Data file you load adheres. At this point, the data is loaded into the form and the form must use dynamic subforms and Instance Managers to create new instances (records) or to remove them. When the user is done modifying the form’s data, the data can then be exported via the same data connection to a new XML Data file (via HTTP or email). This new exported XML Data file would then contain the data in the state which it was at the moment it was exported from the form (including any modifications to subform instances — records — that might have been made).
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.
You say:
“You should also be able to easily tweak it to create tables in a Microsoft Access database if that’s what you want to use.”
Sorry, I don’t know how I create a Access DB with your SQL file, have a tip for me?
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.
Hi:
I am having a problem understanding how I can update a file in an Access database.
The issue is that I want to collect info from the form that is partially populated by tables in the Access database. I then want to post to the Access database but in a different file that I was using to populate the form. I am collecting expense data and wish to have it contained in a file in Access.
How can I achieve this by using the
xfa.sourceSet.{DataConnectionName}.addNew().
Thanks,
Dwight
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
Dwight,
The key here is to use multiple data connections. The very minimum would be two: one to read/gather the partial information you need for filling the form and another to write the data you’ve collected.
This is basically the same thing I’ve done in my sample on selecting specific database records.
Designer supports multiple data connection definitions to multiple ODBC data sources but only one connection can be open (allowing you to read from or write to the tables that it references) at any given time.
The trick then is to open the “read only” data connection when your form is initialized in order to populate the fields with the partial information you need and then close the data connection and open the second (“write”) data connection so that when the user enters data and clicks on the “add” button, the call to
xfa.sourceSet.WritableDataConnection.addNew()
will take the data from the fields bound to the data nodes in the WritableDataConnection and commit it to the tables to which they pertain.
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.
Stefan,
I am going nuts with this. I am trying to connect my form to my MSSQL 2000 database. I have all the dsn’s set up and connection test is fine. When I try to preview the form I get the following error: “connection for source “” failed because the environment is not trusted.” I have googled the tar out of this error and can’t seem to get a straight answer. You seem to know what you are talking about. Any suggestions? I realize that this is more focused on Access, but I thought I would give it a try. Thanks,
Kevin Williams
IT Manager
Quantell, Inc.
Kevin,
It’s difficult to figure-out what the problem is here. I’m thinking it could be one of two things:
I have yet to run into #2 myself so hopefully #1 fixes your problem.
Let me know how it goes.
Kevin,
I did a little more investigating and it sounds like you may have run into a bug in Acrobat 8 where it won’t trust a local DSN in a form even if the form has been certified and is trusted to execute highly-privileged JavaScript (to connect to the DSN).
That’s not to say that you can’t get a form to connect to a local DSN in Acrobat 8. It just seems that in certain circumstances (which, unfortunately, I can’t pin-point), Acrobat will fail to trust a local DSN connection.
It’s possible that it may something to do with the way the connection string is specified in the XML Source view. Amongst other things, it should start with “Provider=MSDASQL” and have the string “Data Source=” somewhere into the string. You can find this information by going to the XML Source view and looking for the <sourceSet> node about 3/4 ways down.
Also, someone else asked me about the same error message just recently and they seem to have gotten around the problem by setting some data bindings to “Normal” instead of “None”. I can’t say I would personally have ever thought to try that but they apparently found the answer on the Designer Forum.
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:
Have I missed a step? I have checked my Data Connection setup after reading previous posts, and it looks fine.
Thanks,
Katrina
I just started using Adobe LiveCycle Designer. My first problem is that I can´t insert a new record to my database. I use following script:
xfa.sourceSet.Datenverbindung_output.addNew();
Filling in new data
xfa.sourceSet.Datenverbindung_output.update();
Update()[existing record], First(), Next() are working fine. I do not get any error while inserting the data, but the data never get inserted into the table.
Thanks,
Claudia
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.
Claudia,
What you’re describing is a typical problem that occurs when the record being updated (after being inserted) in the database includes an auto-incremented cell.
Please see the Auto-Incremented Table Columns sub section in this post in case this might be the problem.
If it is, you’ll most likely need to specify an SQL Query instead of simply picking a table from the database or you may have to narrow your query (if you had already specified one).
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 am trying to pre-populate an Adobe form with SQL Data, but am getting the error of “Connection for Source DataConnection failed because the environment is not trusted”
Is there a way to do this without using a System DSN as I do not wish to setup a local System DSN for each user.
Thanks in advance,
Jin-oh Choi
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.
I have been trying to make FormBuilder with the record navigation buttons work with a MS Access 2003 DB.
When I run the form the first record in the DB appears, yet when I click on any of the buttons (first, last, previous…) I receive an error message similar to this “accessor ‘xfa.sourcset.mcteldir.last()’ is unknown”.
Any ideas of what I am doing wrong?
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.
Jin-oh Choi,
It sounds like you’re running into the same problem others have been running into where Acrobat 8 fails to trust a local DSN connection for some unknown reason.
The problem is currently being investigated however I haven’t seen anything conclusive yet.
The only other ways of importing data into a PDF form are via XML or a web service (WSDL data connection).
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).
Rick,
If the error message you’re getting is exactly as you’ve specified,
then the problem is likely due to the fact that “sourcset” is misspelt: It should be “sourceSet”.
If that was just a typo when you wrote what the error message looks like, then it’s something less obvious.
The fact that the first record appears when you load the form in Acrobat means that your data connection, “mcteldir”, should be properly setup. Is that the correct name for your data connection? If it isn’t, then it’s another reason why you might get this error.
Aside from that, it’s difficult for me to tell what’s going on. Please let me know if either of these pointers solves your problem.
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.
Hi,
Wonderful answers given here. Is there a contact in INDIA – New Delhi who can help develop some solutions for us on database connectivity for pdf forms.
Thanks,
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.
Hi Stefan,
thx alot for your guide. I followed your instructions and everythings works fine with connecting to the database and dynamically fill the form. But there’s a big problem!
It’s a dynamic order form, so the users are able to tell what they want to order and how many. Then the total price is calculated. After this they save the form (with Acrobat Professional). But when they reopen the previously saved form its refilled with data from the database and the calculated price and everything is cleared!
Is there any chance to save the form with filled data without reconnecting to the database?
Thx alot for your help,
Tobias
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.
Kim,
You’re welcome!
I think my article on displaying all records from an ODBC data connection is exactly what you’re looking in this case.
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.
Thx Stefan, Below is the code I use for iterating thru the DB – it works, displaying a message & moving onto next record. I have used various methods to try & print all records but it just seems to hang. How do I use the xfa.host.print parameters to print each page and move to the next record w/out user intervention? Thx again, Kim.
xfa.sourceSet.BJFCDATA.open();
xfa.sourceSet.BJFCDATA.first();
while( not xfa.sourceSet.BJFCDATA.isEOF() ) do
//$host.print();
$host.messageBox($record.BJFCDATA.Surname);
//xfa.host.print(1, “0”, (xfa.host.numPages -1).toString(), 0, 1, 0, 0, 0);
//$host.messageBox(xfa.host.numPages);
//topmostSubform.Page1.PrintButton2::click:
xfa.sourceSet.BJFCDATA.next();
endwhile
//$host.print(1, “0”, (xfa.host.numPages -1).toString(), 0, 1, 0, 0, 0);
//$host.print();
xfa.sourceSet.BJFCDATA.close();
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
Kim,
I think I understand better what you’re trying to do. Sometimes the term “print” is used synonymously with the term “display” so I didn’t realize you were actually wanting to print each record to paper.
I would strongly suggest that you model your form according to my sample on displaying all records from a database. This will give you a form which, when loaded in Acrobat, will list all data from all records in your database.
Once the form is generated, the user can simply print the form in the usual manner. You could even provide a print button if you like: just place it above or below the repeating subform which will contain the data for a single record.
If its necessary for you to print each record on its own page, then you could set a conditional break on the repeating subform (the one that contains the record data) set to break to the “top of next page” (by setting the conditional break’s “To” property) “before” the next subform instance is appended (by setting the conditional break’s “Break” property). This will result in a form which displays one record per page.
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.
Hi Stefan,
Big thanks for your blog.
I have a form that works with MS Access database that has 150 fields. I can add new records and go through records but when I try to delete a record I get “Query is too complex” error message. Any suggestions how to overcome this?
Thanks
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.
I have created a Survey form in Lifecycle Developer 7.0 and have it writing the results to an Access database via a local DSN. It was soooo easy to set up I knew it had to be too good to be true.
Now I am ready to test it with other users. The plan was to e-mail the link to the survey to the test group of users. There is no way I can create a DSN on the local machine for each of the users that will be completing the survey.
I need instructions on how to make my survey write to my database without using a local DSN. Can’t the DSN be set up on the server where the survey is located and the database resides? I have searched this but the answers are vague. Please help me figure out a way to be able to use my survey and write the results to an Access database WITHOUT a local DSN on each client.
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.
I had the same error as Katrina (12/8/2006). Everything works when I use Acrobat Pro 7.0.8, but when I use Reader 8, the list box prefills the database info, but when it runs the SQL statement to fill the binded fields I get an error. You mentioned reader extend option… where is that? I just see commenting extending in acrobat pro. I assume the reader extension product from Adobe is expensive?
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?
I am going crazy! I am an extreme novice developer…yet have been tasked with creating forms that send data to a database. I am able to use your controls just fine. However, I need a form that opens as a blank form, the user fills out data, and then submits it to the database. I do not want them to be able to read any data from the database. I just want them to add a new record each time without risk of updating older records. Make sense? Can someone PLEASE help?
Joanne,
I believe you have two options here:
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).
Stefan,
Thanks for the blog! I am a bit new to all of the design aspects and find this is my most used bookmark. Seeing as I am now a forms specialist? it is great to have this kind of resource. I do have a question though. I have a button that take xml data into my msacess database and brings data out. In the end it makes the form complete. Now I would like to change that button or add another that would save a pdf into the record in the database it created. Perhaps, as an ole object. This way I can refer to the form when I look at the record if I needed to. Is this possible?
Thanks,
Kerry
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
where “email@address.com” is the email address to which the PDF should be submitted.
Hi,
I am using the code for connect to the database with methods like first(),next(),previous(),last(),addNew(),update(),cancel() etc…
when i view the PDF form i am just able to get the first record, but onclick of navigation buttons the records are not moving. Neither i am able to insert new records or perform any DML operation. what could be the reason.
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.
Thank you! Thank you! Thank you Stefan. Your blog is a lifesaver and has helped me beyond belief.
But I do have one small problem described below..
All the buttons work fine until I click the Add New button. After this button is clicked, it blanks out the form as it should to allow new information to be posted. But then all the buttons stop working. I get an error message similar to this one when I clicked previous:
Script failed (language is formcalc; context is xfa[0].form[0].form1[0].#subform[0].previous[0])
Script=xfa.sourceSet.DataConnection.previous()
Error: next operation failed. Multiple-step operation generated errors.
Check each status value.[ID:11]
But it does place the updated record in the Access database (strange, huh). And even stranger, if I close and reopen the form the buttons work again until I click add new.
So what am I doing wrong? This is a very simple dynamic form created in Designer 8.
Any help will be most appreciated.
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.
Lyn,
The very last part of the error message you quoted is “[ID:11]”. This is usually the name of the column (and the value associated to it) for which there was an error.
Since the name is “ID”, I’m wondering if you might be attempting to set the value of an auto-incremented column. This is something that you cannot do using a data connection. In that case, you must provide an SQL Query, when setting-up your data connection, which excludes the “ID” column from the connection.
You can edit an existing data connection by right-clicking on it in the Data View palette and choosing the “Connection Properties” command.
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.
HI Stefan,
I followed the steps as outlined in this great blog.
I am using Adobe LifeCycle Designer 7.1 and am connecting to a Table in SQL Server.
I am able to get the first record from the table, when i preview the PDF, but when i click on the button “previous”, “next ” etc., I get a an error message as follows:
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
The Report pallette shows no error/warning.
The name of Data Connection is the default name.
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 fields do not change to show next/previous record.
Can you please guide as to how can i resolve this?
Regards,
Siddhartha
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?
Hi all,
Iam a newbie to adobe forms. I have the above example working on my system, which has the sql server installed and i created the dsn etc etc. Inserting updating and moving recrods are working. But Iam little confused here, I open the PDF on another machine, which has the same dsn name as my machine pointing to the same db etc. but when I try to click on the next button i get the error (basically none of the buttons work). Is it that the form has to be hosted over IIS or can this work on a stand alone machine with only the dsn.
Please point me to some basic material on about forms.
Thanks in advnace.
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.
Stefan,
Thanks, I was able connect to the DB from other machine from the same form. It works fine… I did not have the DSN set up properly on the other machine..
My next doubt is, I have a set of images in the SQL db stored as a blob (binary format). each image has a id, desc and the blob itself (Jpeg image). Whne I click on the next/previous buttons can I will move to the next record or the previous record. Can I somehow get the images (blob) form the table and display it on the form’s image control ?
I was searching for some tutorial in this regards, some help form u would be really helpful.
Thanks
-Venky
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:
If you give it a shot, please let me know how you make-out.
Hi Stefan,
Im new to Adobe Livecycle and your Blog has been of great use to me in understanding Database Connection with forms.
I ve created a Dataconnection with a table in SQL Server.
I had to create a System DSN for the Dataconnection to work in the form. It worked perfectly as i wanted!. I tried deploying my form in a Server Environment. But, when i access the form from any other machine, it throws an error saying “connection operation failed- Datasource name not found”
I figured out that i had to create a system DSN in the machine from which im accessing. But im afraid, if it is possible to create DSN s in all the machines from which im going to access. I tried using a FILE DSN instead of the System DSN. But it was not successful. Is there a workaround for this other than using Webservice as u had specified in ur blog? Moreover the form has to be added to my clients Form Manager and to be useed with Livecycle Workflow. What would happen in that scenario. Should i create DSN s in all the machines?
Thanks in advance
Srinath
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
Stefan
Can you point me in the right direction if my idea is posible?
I want to create an online form where users supply there name and address etc, answer questions and display key data from the database. At the end/form compleation all collected/displayed data is written back to the data base using a unique Person No. At the same time print the form out in the office and give the user the option to print.
Martin
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.
Rajasekhar,
If you wanted to navigate away from your form to this aspx web page, then you could’ve used the XFA Host Model’s gotoURL method in the button’s Click event (in FormCalc or JavaScript):
Since you need to open it in a new browser window when the button is clicked, you’ll have to resort to using the Acrobat Application (app) Object‘s launchURL method in JavaScript only:
This will open the Adobe.com web site in a separate browser window (when you specify “true” as the second parameter). Note, however, that the app object may only be accessed when the form is running in Acrobat and the launchURL method is only available in Acrobat 7.x or later.
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.
Martin,
Sorry — I forgot you also asked about printing the form. You could easily provide a print button for the user to print a copy of the form. Just drag one onto your form from the Library palette. As far as automatically printing a copy of the form at your office, that’s something that would have to be handled by a server process which would detect the new entry in the database and print a copy of the form that was submitted using the record data.
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
Bucaneve,
Based on the following line in your comment,
I’m guessing that you’ve defined a data connection named “Vakken”. The problem here is that you need to remove the “{” and “}” squiggly brackets. In my instructions above on scripting the buttons, this is what I indicate:
Note that I put the entire “{DataConnectionName}” part in italics because I wanted to indicate that the squiggly brackets are also part of what you need to replace with your own data connection name.
If you use the snippet I provided, then the buttons are configured to work with a data connection with the name “DataConnection” by default. Since you’ve named your data connection “Vakken”, you’ll need to update each button’s script to use “Vakken” as the data connection name or else you’ll get an error stating that
thank you very much, now it works!
I am working on a WAN, where we have a Test server and a Production server. On my local machine I have LiveCycle and Acrobat Pro and running IIS. Also on the local machine I have DSN for DB’s located on both the Test and Production Server. I have created a PDF with in LiveCycle the retrieves a specific record from the DB and populated the fields, a form letter of sorts. The PDF works like a charm from on my local machine. I uploaded the PDF to both the Test and Production servers, as part of an application. The Test and Production servers does NOT have Acrobat nor Livecycle. From my local machine I can call the PDF from application links, and the PDF works like a charm. The problem comes when I go to any other machine on the WAN, open the application, then open the PDF from a link, I get a blank page. If it was supposed to return 10 records, I get 1 of 10, but all blank. The problem seems to lie in DB connection or DSN. Any ideas or suggestion as to why it works from my loca machine where the files were created, but not from any other machines on the the WAN ?
Thanks
Ernest
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
Hi,
Thanks for the reply..
regarding opening a pop-up window
but the below line
app.launchURL(“http://www.adobe.com”, true);
open a new page..how do i get an aspx page as a modal dailog window.
and how can i send the parameters to the aspx page and return the results back to pdf.
Please give me a solution.
thanks,.
rajasekhar
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.
rajasekhar,
You’re welcome. Unfortunately, Acrobat doesn’t offer that kind of a feature. The app.launchURL is simply there to give you a way to open a web page in the user’s browser. I’m guessing the intended use is to display an information page rather than return something to the PDF which launched the URL, although that would certainly make for a very interesting feature!
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:
Hi Stefan,
Thanks a lot for your answers which they were a great helpful in designing our forms, my question is similar to Q.2 as Dwight Beech pointed about the drop down list from Db. I had applied the solution which worked fine for Pdf files, my question here : why it is not working for .Xdp files , cause in some cases we need to have forms saved as .xdp.
which procedure should I follow to get the drop down list from db.
our adobe designer is 7.1 and adobe reader is 7.0.
Best regards.
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,
Mariam Kadhimi,
With regards to your first question (comment #85), there shouldn’t be any difference in the configuration of the data drop down list object when saving the form as XDP rather than PDF.
As for your second question (comment #86), I think my article on selecting specific records from a database would help you out. Essentially, you would need to setup a second database connection for use with the second data drop down list. Then, you would use a script similar to the one found in the Change event of the “movieCategory” drop down list in the sample I mentioned earlier on the first data drop down list’s Change event. Finally, you would follow the
statements with basically the same script as which follows the same statements in the first data drop down list’s Initialize event, except you would tweak it to work with the columns you expect to get from the SQL query you specified in the Change event.
It’s a little difficult to explain in words but hopefully you understand what I’m trying to say. Let me know if you have any questions.
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?
Hi Stefan,
Thanks for reply, for issue #85 still if i save the file as .xdp the drop down list is not working. and for the 2nd issue #86 , i had tried to follow the article “selecting specific records from a database” , but it is not working or may be it is not for the type of Db connection that iam using where iam connecting the db using OLEDB data connction. is there any other method to solve the issue of #86. is it a must to Create a System DSN in my case.
another issue is : i have some forms designed as dynamic, i need to display a page total for some amounts entered by a user where the sum of the totals of a page automaticaly appears at the bottom of each page. which method should i follow.(for grand total ,the sum of all pages is working fine ).
thanks.
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.
My problem is similiar to the ones that have already beed discussed but little clarity has been provided on how to fix the problem. I have a simple form that is submitting data for three text fields to a database. When I have the database and form on my local computer no problem it works great but when I follow the same process and develop the same form and put the database on the server then I encounter all kinds of problems. I get the
“Error:accessor ‘xfa.sourceSet.contact.update()’ is unknown” and the same with ‘addNew’ –
The connection is working because the data is coming into fields but something about the update or addnew doesn’t work when the database is on the server. Again, it works just fine when the database is on my desktop. So my question is two fold, can this problem be fixed and if not what do you recommend as a way to submit data for forms on a database that will be on a server. As always – thank you.
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
Just a pointer to Bucaneve and Rick.
I also had the same issue with “Error:accessor ‘xfa.sourceSet.contact.update()’ is unknown” .
After toying around enough I tried changing the language type from “FormCalc” to javascript and everything went well after this. I’m not sure if this is the solution or not. Please advise if it worked for you.
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?
After reading through the article a couple more times I notice Mr. Cameron states specifically that the script is “provided by FormCalc”. So now I’m not sure if this was the problem or not.
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:
DavidfromSC,
Unfortunately, PDF forms with data connections must be extended using LiveCycle Reader Extensions in order to import data in Reader. The only other alternative is to have the form data merged prior to the form being served to the client (i.e. the browser) by using LiveCycle Forms.
Users with Acrobat Standard and Professional don’t need extended PDFs in order to import data.
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.
Hi:
I’m connection to a MySql DB and for some reason, although I can see the data fields in the Data View area, I can seem to get any data to show up when I preview and if I click on the Data control buttons I see errors like “there are no records in the datasource”. The DB I am testing has at least two records in it.
Are there any suggestions you have in this regard?
dawit..
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?
Dawit,
That’s very strange. The fact that you can see the data fields in the Data View palette is just an indication that Designer was able to connect to your database in order to extract the data structure of the table, query or stored procedure you pointed it to.
Is it possible you specified a query instead of picking a table and you filtered-out all the records by mistake?
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):
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.
Stefan,
I’ve tried what you said but there is no value appearing in my Code field. I even started afresh with a completely new form and data connection. The values appear in the drop-down fine but nothing in the Code field. One bit of strange behaviour I can describe though is that when I go to look at the Dynamic Properties of the drop-down by clicking the List Items link of the Field Tab of the Object palette it always defaults to “Default Data Connection” rather than the data connection I have created. This is quite weird considering the drop-down actually brings back values from the DB. Any ideas?
Cheers
Craig
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
Craig,
You might have a syntax error in the Change event script of your drop down list object. If you wrote the script in JavaScript, you can check for syntax errors by using Acrobat’s JavaScript Console: When in Preview mode, press “Ctrl + J”. That will open the console and you’ll be able to see any errors. If there’s an error in your script, it’s likely that which is preventing a value from showing-up in the “Code” field.
As for the drop down list being populated with data despite the data connection selected in the List Items Dynamic Property being “Default Data Connection” is likely due to the fact that the structure of the data being merged-in from the database somehow matches the structure of the fields on your form and the bindings are then inferred automatically. “Default Data Connection” means that you’re not binding to a specific data connection.
Finally, you’re not the first to mention the “environment is not trusted” error message when it comes to ODBC data connections. Adobe is aware of the issue however I don’t know of any specific workarounds other than making sure you’re using the latest version of Acrobat/Reader (which is 8.1) since the bug is supposed to have been fixed in that release.
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.
Mary,
For a basic setup, you should only need to worry about the Login tab in the MySQL ODBC Connector driver:
Once you’ve set this up, you should be able to create the data connection in your form using Designer’s Data View palette.
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?
arti koppar,
Could the problem simply be a typo in the “addnew()” function name since it’s documented as “addNew()” (notice the capital “N” in “New”)?
This would explain why it’s not working in JavaScript either. When working with JavaScript code, click on the form in the Preview tab and press Ctrl + J to view the JavaScript Console. There’s probably an error being generated there when you click on the button.
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
Stefan,
Thank you for this tutorial.
Got it to work in a Windows environment but am only looking for a submit to a mysql database in an Apache environment. Had to use your snippet and couldn’t get the Next… to work on my own. No errors, just wouldn’t work.
Thanks,
Warren
Rabab Tawfeek,
For security reasons, data connections cannot be created dynamically. They must be pre-defined in the form.
Warren,
You’re welcome! I’m glad you found it useful.
Have you read my article on inserting, updating and deleting database records? It might be better suited for your Apache+MySQL environment. This article is just meant to show how to iterate through records and have modifications automatically made to a database via an ODBC data connection.
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
Rabab Tawfeek,
Unfortunately, I don’t know of a way that you could do that. The only thing I can think of is that you can choose to delay the opening of a data connection by setting the “Delayed Open” property in the “ADO Properties” dialog when you configure an ODBC data connection. This will tell Acrobat not to attempt a connection immediately on open but wait until you open it manually using script. If you’re then able to determine whether the form is in an environment where it has access to the database, then you could choose either to open the connection or not in the form’s Initialize event.
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?
Morgan Roberts,
It sounds like there’s something out-of-sync between the form and the database. One suggestion is to try calling
prior to calling “addNew”. The “update” method should forcefully update the current record with all bound data.
Another suggestion is to try changing the scripting language to JavaScript, assuming you haven’t done that already (if you haven’t, don’t forget you’ll need semi-colons at the end of every statement). I’ve always had more success with JavaScript over FormCalc when it comes to scripting against objects.
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
tyler,
I can’t really tell what you’re trying to accomplish just from your script but I do see a couple of errors:
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
tyler,
Unfortunately, I don’t believe it’s possible to alter a field’s bindings once the form is running. That means the field would have to be properly bound to a data node at design-time. If the field is in a repeatable subform and there is no instance of that subform that exists in the form at the time the data is submitted from the form, a data node for the text field will not be included — I think that’s the only way you’ll avoid having an empty data node for a field that has an empty (null) value.
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!
Actually I just answered my own question! Everything is working besides inserting a new record, which I think stems from the auto-incrementing field. I will keep working on it. By the way, that .xfo file is great! That totally opened a whole new way to approach future problems.
Thanks for the walk-through!
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:
Stefan,
I have another (and hopefully final) question. After a sample database from your tutorial and getting it working (which the add new button is now working) I just need another mind to bounce an idea of off real fast to move forward. The project is a Room Request form. I really like the functionality of buttons you created so I would like to keep a “master form” with those, but I would like to have another form with the same data fields with only a submit-type button that could be given to customers. Is it possible to directly import the data into the “master form” after our clients send us their filled out forms? For example, open “master form” -> click “Add New” -> import recieved pdf (or xml). It seems that if there is no data connection, when I do the prescribed steps it basically does nothing. Nothing is added and nothing is changed since the form with no data connection is not in-sync like the “master form” is.
So I guess where I get stuck is, is it possible to import data from a form with all the same data fields that is not linked to any database into a form that is linked to a database?
Thank you so much for your help.
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
Alex,
Provided you’re using Acrobat Standard or Pro to do the data import, yes, you can import XML data into a form that has a connection to a database. You’ll define two data connections in your master form: One that’s the database connection and another that’s a schema data connection (Designer will auto-generate a schema data connection based on a sample XML data file if you have one, in case you don’t have a schema). The form that your clients fill-out would have an email or HTTP submit button which would submit the form’s data in XML format to the specified location. You can then take that XML data and import it into your master form in order to get the data into the database.
Another way to do this would be to have the customer form submit to an HTTP address that’s a PHP page. This is essentially like creating a web form and submitting it to a PHP page where you can get access to the data via the HTTP Request. From there, you can send the data to your database directly. You can then use your master form as a report which would simply display information about all the room requests in the database.
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 < and > 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.
Hello script I am Vipin ….
I am using one HTML page….. which has so many fields…
for one particular field user can’t able to enter anything (read only) in the side of the text field one button when user click that button one pop up window will open and list some values. If user select any value from the list and click OK that value want to come in the particular field…
how to do that….
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
Once the drop down list is visible, you can set its Change event script to be
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 Stefan,
First, thanks for your blog.
I want to generate several PDF forms from a database connection and a PDF form model. Like a mailing on Microsoft Word.
Is it possible ?
Thanks.
–stelo
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();
}
Hello Stefan,
(Working with Acrobat Pro 8 and LC Designer 8)
I have successfully incorporated your data controls to my livecyle form. My form is now connected to a MS Access db that pre-fills a number of fields. So far so good… I can step through each record from the database.
My form is to be used as a distributed survey. This is where the wheels seem to come off the tracks.
I have tried a number of scenarios to create individual pre-filled forms for distribution to departments within my organization. I propose to send each department a single form for each record instance from my database. I would ‘package’ these forms using acrobat pdf packaging, and in theory each form would be returned individually – updating its ‘dataset.pdf.’
I have discovered the ‘distribute’ function of acrobat is causing the current record information to be overwritten, and inserts the first record from the database. I have been successful using ‘Save as’ to create individual forms with unique records – if I open using Acrobat Reader. However, once this same form is opened by Acrobat Professional, it causes the form to reveal the first database record. Additionally, using ‘Save as’ does not offer the great functionality of data collection once the completed form is returned.
Can you tell me if there is a way to programmatically ‘cut’ the data connection as I move into form distribution? Or offer a work around.
Thank you for your time, and your great webpage!
Bill
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.
Stefan,
I have tried out your instructions regarding Auto-Incremented Table Columns and inserting data. But I still gets an error. I get the following:
GeneralError: Operation failed.
XFAObject.open:10:XFA:form1[0]:mysubform[0]:myEIFform[0]:overflowLeader[0]:Submit[0]:click
open operation failed. [Microsoft][ODBC Microsoft Access Driver] Number of query values and destination fields are not the same.
My OLEDB Connection Record Source is the SQL Query which reads: SELECT FedTaxID, LegalID FROM dbtest; My simple test DB is Access and its only 3 columns; dbID, FedTaxID, LegalID. dbID is the Auto-Incremented Column.
If I remove the Auto column from my DB table it inserts just fine but I get this error:
GeneralError: Operation failed.
XFAObject.open:10:XFA:form1[0]:mysubform[0]:myEIFform[0]:overflowLeader[0]:Submit[0]:click
ado2xfa operation failed. Item cannot be found in the collection corresponding to the requested name or ordinal.
I’ve looked over alot of your blog and other help forums and there’s info on Selects, I don’t find much on Inserts.
Can you direct me in the right direction? Thank you.
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,
Brad Gubanich,
Unfortunately, I’ve never seen these error messages before. All I can think of is that there might be something wrong with your Ms Access ODBC driver. Perhaps you need an updated version. It could be incompatible with either Access or Acrobat.
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).
Stefan, thanks for the information regarding connecting a form to a database. I am a newbie to LiveCycle and a really appreciate the postings from everyone. I was able to connect to my external MS Access database and used your navigation suggestions. About half of the fields that I am populating on the form come from the database and the other half come from the user. The user fields are date/time and text fields. The issue I am having is that if you enter any data into a user field (for example the date field) and move to the next record, the data from the first record is carried over. Any way to clear the manual entry fields when a user navigates to the next record? Thanks
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”:
Stefan,
I’m very sorry to bother you, but you seem like a genius in this area and I cant find answers to this question anywhere else.
The only question I have (and please realize that I’m VERY new to adobe and access) is how can I modify the purchases form (http://www.adobe.com/devnet/livecycle/articles/lc_designer_db_lookup_tip.pdf) to make the ID field a text value instead of a number or autonumber.
What I mean is that when I go into the database and change the ID column to “text” instead of “number” or “autonumber” the script throws up errors and the form doesn’t work. I have everything figured out that I need except that. Instead of searching by number, I want users to be able to prefill a form from something like “GOWER-1”. Please please please help. Thank you so much!
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.
Script failed (language is formcalc; context is
xfa[0].form[0].form1[0].#subform[0].Button1[0])
script=if (Len(Ltrim(Rtrim(SelectField.rawValue))) > 0) then
//Change the commandType from TABLE to TEXT. TEXT is the
equivalent of SQL Property
$sourceSet.DataConnection.#command.query.commandType =
“text”
//Set the Select Node. Select in this case will be whatever the SQL
Property you want.
$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
Error: open operation failed. [Microsoft][ODBC Microsoft Access Driver] Too
—
That’s the error I am getting, I have changed it to ‘text field’ already as you said. Pretty much this is what I have done in all to get this error:
1. In the database, change the ID field from “autonumber” to “text” and changed every number that was in the ID field to the text equivilent (ie. one, two, three, four).
2. In the pdf, change the Select ID field to “text field” and the ID field to “text field”.
Thank you so much for the help!
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”:
Notice the single quotes that will wrap the result of the Ltrim(Rtrim(SelectField.rawValue)) statement.
Stefan,
THANK YOU SO MUCH! YOU HAVE NO IDEA HOW HAPPY THAT MADE ME TO SEE IT WORK!!!!!!! THANK YOU THANK YOU THANK YOU!
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?
Hi stefan,
Your blog is very informative. I am new to livecycle and tried some of the examples specified in here.
I am trying to display a table of data in the pdf form .Using subforms i am able to display the table dynamically.
I am using a sql query in the data connection to connect to SQl database and display ID and Contract type.
Now I want to display a table of data from executing a procedure. Everytime I specify the procedure with parameter it gives an error saying it has non-optional parameters. How do I make use of procedures with parameters in live cycle .I am using client side Javascript in live cycle not formcalc.
Thanks,
Vidya
Jeremy,
How did you create this other form? Sounds like it’s not quite identical to the other one.
vidya,
Unfortunately, I tried SQL procedures but didn’t have any luck. Are you certain you can execute the procedure as you’re calling it from the SQL statement by executing it directly against your SQL server? If you figure it out, please let me know!
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
Mike,
Other than “flattening” the form as a PDF archive (PDF/A) — which means it no longer has form intelligence and it’s just a document with text, lines and images — the only thing you could do to disassociate your form from the web service and database is to manually control when those connections are executed and simply prevent their execution once a particular condition has been met (i.e. after the data has been submitted to the database).
For example, you can manually execute a web service connection by doing
(replace “{WebServiceConnectionName}” with the appropriate data connection name)
and you can manually execute a database connection by doing
(replace “{ODBCDataConnectionName}” with the appropriate data connection name)
If you had a hidden numeric field with a binding (either “normal” or explicit), you could set its value to 1 once the data is submitted to the database. Then, assuming the user has the ability to save the form (they’ll need Acrobat Standard or Pro or Reader with an extended PDF with Save Rights), the numeric field’s value will be saved. Upon re-opening the form, your code that executes the data connections would first look at the value of the hidden numeric field and, if it’s 1, wouldn’t take action.
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 ?
Xancholy,
You’re welcome!
You can set a border on each subform using the Border palette.
If you want a single border around all subforms together, then you would select them all, right-click, choose “Wrap in Subform”, select the new subform using the Hierarchy palette, set its “Object palette > Subform tab > Content property” to “Flowed” and set its “Object palette > Binding tab > Default Binding property” to “None” (to ensure the new subform doesn’t affect your existing bindings). Then set a border on that new subform. If you can’t see the border, it’s because there’s no space between the edge of the new subform and the edges of the subforms inside. To fix that, select the new subform and set its “Layout palette > Margins properties” to “1pt”.
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 ?
Xancholy,
The first thing that comes to mind is to use a table and set the borders on the right hand side column appropriately (i.e. the top cell will have a left, top and right border, the middle ones will have left and right borders and the bottom one will have a left, bottom and right border…).
Thanks. Hmmm. So I use a table, insert flowable subforms into cells and use a border for the table column ?
Xancholy,
That’s the idea! As the flowable cells grow in height, the cells in the adjacent columns will to which will result in the rectangle growing to the height of the table which will be the total height of all flowable cells.
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
Rob D,
I assume you have named your data connection “DataConnection”? And by “‘reader extended’ the form and certified it”, I assume you mean you’ve used LC Reader Extensions to enable the form for “data import”? (just checking the obvious)
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
Kirill,
It sounds like you’ve got an XFA form that’s using XFA 2.5 or later (Designer/Acrobat 8.0 or later). As of that version, you can’t modify the data connection directly. You have to clone it first, then modify the cloned version:
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&
Stephanie Jensen,
I’m glad to know my blog was helpful and thanks for the link. I’m sure it’ll be equally helpful to those wanting to update Excel spreadsheets.
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
Oops, error in my typing. The last character in the second line of the error message should be a “)” not a “0”.
Thanks.
Ok, I figured out the error of my ways. Darned autonumber fields.
Thanks again!
Chris,
Glad you figured it out!
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…
Brad,
See my reply here (same question, different post).
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.
Hi Stefan,
I had followed your tutorial to set-up the control buttons; however I keep getting the error message below.
Error: accessor ‘xfa.sourceSet.DataConnection.next()’ is unknown.
It seems I didn’t make any typo and I was able to load the first record. I just could not get the button working.
Thanks in advance for your help!
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.
Christine,
Are you using Acrobat or Reader to do this? By default, Reader cannot import data and usually results in the type of error you’re seeing.
Otherwise, are you certain you named your data connection, “DataConnection”? If you named it something else, say “MyDbConnection”, then you would change the button’s Click event script to this:
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
Patti,
My article on inserting, updating and deleting records should help you out.
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…
Christine,
What you should determine is whether you’re using Acrobat or Reader to preview the PDF. They are very different PDF applications. Though they are both made by Adobe, Reader has a lot of restrictions when it comes to filling forms (that can be optionally enabled using LiveCycle Reader Extensions ES or avoided by rendering the form using LiveCycle Forms ES).
Start your PDF application and go to the About box. That will tell you whether you’re running Acrobat or the free Reader.
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
Chris,
In response to your questions:
1. You can refresh/reload a data connection by scripting the following call: “xfa.sourceSet.MyConnection.close(); xfa.sourceSet.MyConnection.open();” (this closes the connection called “MyConnection” and then re-opens it).
2. You’re correct: Unless you do something special to your forms (enable them for data import), they will only work in Acrobat Standard or Pro. For this, you would need LiveCycle Reader Extensions ES. The other alternative is to do the data bindings on the server using LiveCycle Forms ES. Unfortunately, both solutions require purchasing a lot of expensive software.
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 tried to refresh the data connection using the .close() and .open() and get no changes at all. Nor any error message.
I also tried .resync() which gave me an error message: “resync operation failed. Insufficient key column information for updating or refreshing”.
My sql query in the data connection is pretty simple: “Select txtProjectName From tblProject Order By txtProjectName asc”
I’m using formcalc which I assume shouldn’t make any difference.
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.
Don,
Unfortunately, extending the PDF with Acrobat does not enable the PDF for “data import” — which is required for importing data from a data connection in Reader. You would need to extend the form using LC Reader Extensions to enable data import, or execute the data connection on the server using LC Forms.
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?
Hi Stefan,
I am trying to achieve something similar to Mike’s post (#150 and #151 above).
I have a form that where the users have Acrobat Standard and the form is connected to a SQL database. There are no issues when a user opens the form in the office (where they are connected to the database); however sometimes the user may open the form away from the office and I don’t want multiple messages / warnings.
I want to execute a script at initialise / form: ready that will look to see if the database is available. If it is, then the form / data dropdown lists will operate as normal. If the database is not available then I would like to have a single dialogue alerting the user and then turn off the data connection for that session only.
I suspect that it is a fairly straightforward if statement, but I am not sure what condition to test against and how to temporarily turn off the data connections. Any tips?
Also we have a Formcalc call to the database, looking for the contact details of the selected person’s name.
var oDC = Ref(xfa.sourceSet.ProgManagerDetails.clone(1))
oDC.#command.query.commandType = “text”
oDC.#command.query.select = concat(“SELECT EMail FROM staUsers WHERE ContactName = ‘”, $.rawValue,”‘”)
if ($.rawValue == null) then
$.rawValue = “”
else
oDC.open()
oDC.first()
oDC.close()
endif
While this works fine for most cases, any name with an apostrophe, throws an error. For example “John Murphy” is fine, but selecting “Niall O’Donovan” throws an error:
“Error: open operation failed. [Microsoft][SQL Native Client][SQL Server]Incorrect syntax near ‘Donovan’.” Unfortunately over here there are a lot of surnames with ‘.
I have tried to put the name to a string so that the O’D.. is within the string, but I have not got it working.
I would really appreciate your help solving these.
Thanks,
Niall
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.
Niall,
Unfortunately, the xfa.sourceSet.{ConnectionName}.open() command is the one that establishes the ODBC connection and it doesn’t return any value (which could’ve indicated success or failure). As far as I know, there’s no way to know.
If you were using a web service front-end which was communicating with your database at the back-end (more secure than exposing your database over the network), then the xfa.connectionSet.{ConnectionName}.execute() does return true/false indicating whether the connection was successful or not. You can also handle SOAP operation success/failure by using the PostExecute event on the connection.
As for your question about names like “O’Donovan”, I believe the problem is that you aren’t escaping the apostrophe before executing the SQL statement. Apostrophes are like quotes in SQL: they begin and end strings. Your SQL statement is basically being terminated prematurely. Rather than concatenating the raw “$.rawValue” into the SQL statement, you should check it for apostrophes and escape them, by preceding them with a backslash (\), then concatenate the escaped name into the SQL statement.
Try this:
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?
Thanks Stefan,
I have been struggling with the d/b. We are not using a web service.
I could not get an execute() to work with the OLEDB data connection.
I worked around it by creating a dummy data dropdown list, which in it’s doc:Ready event looked at another d/b field. If that is empty it means that the database is not available. An if statement then fires a preOpen event of same dummy data list, which has an app.alert, warning the user that the database is not connected.
Thanks very much for the solution to the ‘ it works a treat.
Thanks,
Niall
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?
Kumar,
You may be able to achieve greater control over when the data connection is established by ensuring that no fields are bound to nodes in the data connection. Otherwise, I believe Acrobat will attempt to open the data connection immediately when the form is loaded. This means, however, that you would have to manually (via script) open the data connection, extract the data and push it into the correct fields rather than letting the magic of data binding instantiate subform instances, for example.
You can find an example of iterating through all records in a data connection here as well as code to manually open (i.e. execute) a data connection here.
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
manuel,
I think my tutorial on selecting specific database records is what you’re looking for.
Sedat Reyhan,
You could try using dynamic properties to auto-populate a drop down list’s items from data.
Stefan,
I have found your posts very informative as I am just beginning to work with Adobe LiveCycle Designer. I continue to have issues connecting a form to a database. I have used your tutorial to create a System DSN and thought using an SQL Query (rather than Table with auto-incremented column) as a Record Source would address the error I continue to receive (Could not open table “Manual Info”.); however, now I receive (Could not execute the query “SELECT Title FROM Manual Info”.). I cannot seem to get this form connected to an access database. Any suggestions about what to check or what I might be missing?
Thanks,
Tammy
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:
Please help…. I have been trying to connect my form to mysql database using php, the code seems to be correct, I dont know what the problem really is what do I do?
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.
Hi Stephan,
My form seems to be working fine except that, if the user clicks the Add New button, they can’t cancel it out. If the user changes their mind and decides that they no longer want to add a new record, as soon as they try to leave the blank, they get a ‘a blank cannot be inserted’ error .
.cancel(); doesn’t work, nor does .delete();.
Actually, .delete(); never works…ever.
Help!
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
Mac,
Have a look at my tutorial on selecting specific records from a database. It does something close to what you’re trying to do. That should get you started down the right path.
After eight weeks of struggling with hyperlinking in Designer 8.0, this post solved my problem. You are my hero!
Stephan,
I’m a novice LiveCycle user, but I have some intermediate experience with ActionScript 3.0, so coding is familiar to me. That being said, here’s my situation:
I have hundreds forms saved on disk containing roughly 5 text inputs, 3 number inputs, and a singular image input. I had been manually entering in the values of each form referencing an excel sheet. After that time, I would save, and print these forms.
This is problematic, because a) I know it’s inefficient, and b) it presents me with the problem of not being able to change any element of the forms without manually entering every value again, by hand. What’s worse, I know it’s a terrible system.
I would like to have a single form that represents the template (for future editing), and allow the form to display information from a database, while enabling me to make that information display on the form (for printing purposes) by entering the number of the record (e.g. 00007381). This would allow me to keep the unchanging values in the database, and simply type in the number of the record I wish to print. I’m sure this isn’t a difficult task, but with no experience with LiveCycle, the process of creating that database, and making my form dynamic is unknown to me.
Can you please refer me to the necessary tutorials to prepare a database (that includes images), and how to create the solution above?
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.
After trouble shooting, I found that the ODBC administrator in the control panel opens up the 32 bit version. I had to open up the ODBC Administrator from within the SysWOW64 folder to make the driver visible… however, it is unable to connect to the SQL server.
Do I need to have a MySQL installed in addition to the connector 3.51 driver?
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).
I see.
Since my posting, I did rectify the problem, installed the appropriate database, and now have a working form in which to display and store the values, but I have two problems…
a) I’m trying to sort out the tutorial you listed, so I may be able to tackle it. Will update.
b) How do I populate the form with images, and more importantly, how do I store those images in MyPHP, so I can bind them to a image field in LiveCycle?
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
Sorry, one more thing…
Is there an easy way to do this?
C
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.
Christopher,
My tutorial on selecting specific database records will show you how to do the drop down list solution you’re talking about.
As for getting your data from an Excel spreadsheet, you could either bring that data into an Access database or create a DSN directly to the Excel spreadsheet using the Excel ODBC driver. As far as I can tell, both Access and Excel ODBC drivers are installed when you install Microsoft Office.
(Note that connecting a form directly to a database isn’t very secure. You’re better off putting the database behind a web service.)
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
Just to add one more thing, when I ran it the second time the database record did not show unlike the first time when I included the {}.
Thanks again,
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.
Hey Stefan,
How would I go about cloning the code in this example. The post you linked me to is an example of a drop down list. Sorry to be a pain, but Im very new to LiveCycle and coding.
Thanks,
Paddy
Paddy,
The important part is the one about cloning the Data Connection prior to modifying it. Just apply the following concept to your script:
or
Hey Stefan,
Thanks that worked, I also created a system DSN instead of a direct connection link and that solved the problem too (the environment problem). Then I got a similar error as Gar (the first post) however it was for the next button and previous.
error: next operation failed. Empty row cannot be inserted. Row must have at least one coumn value set.
Its wierd because on the backend of the SQL Server database I allow null values. What I ended up doing was placing the cancel code before the next and previous command code respectively and the error went away.
So now when I preview the PDF i get no errors and record number 112 out of 132 records appears and none of the buttons work. Any clues as to whats going on?
Thanks again for all your help,
Paddy
Just to add, each time I preview I get a different record! lol I feel like I’m very close!
Paddy,
Are you using Reader instead of Acrobat to preview, by any chance? Reader cannot preview PDF forms that import data without the PDF first being extended to allow data import using LC Reader Extensions. Sometimes, in Reader, you’ll see a single record if the PDF hasn’t been extended.
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
Robert,
By default, ODBC (database) data connections work with a single record at a time. You then use commands like next() and previous() to navigate from one record to the next. What you need to do is open a connection, iterate through all records that apply to your list and populate it accordingly — much like the drop down list in my tutorial on selecting specific database records.
You should also be able to achieve this using Dyamic Properties, which may be a simpler solution depending on your form.
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
Hello Stefan ,
I have a simple form which contains only UserID as a primary and name , primary key is not set to increment , still then iam not able to add new rows .All other opeartions are working fine . I also tried with SQL Query instead of selecting the table and in the query I also excluded the column having primary key . Still I am not able to add a new row.What’s problem exactly ?? I am confused . Please help .
Thanks .
Bibhu
@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”.
@Bidhu,
What’s the result of adding a new row? Is there an error message in the JavaScript Control in Acrobat? (press Ctrl+J to see the console).
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.
@Bibhu,
Unfortunately, I don’t have enough context to be able to know what might’ve went wrong. I’m glad it’s working for you, though. Do you know what the error message was?
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
Hello Stefan – If I use a ‘User’ DSN rather than a ‘System’ DSN should I still be able to have the functionality (at least on my computer)?
Thanks for the info, your site is a life-saver!
@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.
@Tchadd Siebken,
I don’t know of issues with User DSNs vs System DSNs.
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
@Ken,
I have just what you need: Please see my tutorial on inserting, updating and deleting records in a database.
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.
Stefan,
It seems that the form that I am developing is not working as well as I initially thought. When I run the form in Designer Preview, it runs fine. However, if I click on the pdf file to run it in Acrobat, the form runs exactly once and then produces an error if the form is run a second time.
I have setup three data connections in the form. For two of these connections, I am using bound fields to connect form fields to database fields. The third connection is used for running SQL insert, delete and update statements.
In docReady, I initialize the two bound data connections by updating their SQL statements. I have my ODBC connections setup using a generic SQL statement like “SELECT [KeyField] FROM [TableName]” and then inside of docReady I re-assign the SQL statement with something more specific to the form– like “SELECT * FROM [TableName] WHERE [Keyfield]= ‘value’.
The problem I am having is that if I try to open the form repeatedly in Acrobat, I get a null return when cloning my data connection in docReady. The first time I open the file, it works correctly and the data fields on the form are correctly updated. If I then close the form without saving and open it again, the data connections fail. Opening Designer and running the form in Design Preview seems to reset the system so that I can open it once more in Acrobat—but just once.
Here is the code I use to initialize one of my data connections in docReady:
var MainConn;
var strSQL= “Select * from [Table_2] Where [DocumentKey]= ‘” + strDocKey + “‘”;
try {
MainConn= xfa.sourceSet.Main_Conn.clone(1);
MainConn.resolveNode (“#command”).query.commandType= “text”;
MainConn.resolveNode (“#command”).query.select.nodes.item(0).value= strSQL;
MainConn.open();
}
catch (e) {
xfa.host.messageBox (“The main data connection was not opened.”);
console.println(e);
MainConn= null;
}
I have displayed the SQL statement just prior to its execution, so I am sure that it is the same each time the program runs. I have also tried adding a “MainConn.close()” statement, either immediately after opening the connection or later in docClose, but this has no effect.
When the data connection fails, the console message indicates that MainConn is returning null.
Any ideas what might be happening?
Thanks, Tom
@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 there – is there a way to prevent the form from showing the first record until you trigger the request yourself?
E.g. when I open the form up the first time I do not want the first record showing. I want to start the process myself.
Any help appreciate – the tutorial was fantastic and has guided me through the set up of my form easily.
Regards,
B.
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
Stefan,
I’m afraid I must apologize, but the problem I have been having turns out to be a trivial user error!!
Recently, I updated my version of Adobe Reader and apparently it made itself the default program to open
my pdf files– and I hadn’t realized it. So when I tested these forms, they were suddenly opening in Adobe Reader rather than Acrobat! Hard to believe how many times I opened these files without noticing this! Anyway, this code works fine whenever I open it with Acrobat!
I do have one more question for you, however. I am working on another form that uses a SignatureImage
box. When the user clicks on the box, a Windows dialog automatically opens and prompts the user to select a bitmap file to load into the SignatureImage control. In association with this, I would like to run some validation code, which I have placed in the “Click” script for the SignatureImage box.
This works well as long as the user first selects a bitmap by single-clicking it and then presses the “Select” button on the dialog. However after the dialog closes, the form shows some unexpected behaviors.
First, if the user double-clicks the file name (instead of single-clicking and then hitting “Select”), the dialog closes and loads the bitmap– as expected– but then the “click” script fails to run!
Second, if the user fails to select a file and then hits “Cancel”, the bitmap is not loaded into the image box (as expected)– but then the “click” script runs anyway!
So we have a situation where the “click” script runs whether the user hits either select or cancel, but fails to run if the user double-clicks the choice (and therefore fails to hit either button).
I would like to be able to execute my script reliably when the user loaded a bitmap and cancel the script if the user cancelled the bitmap selection. Any ideas? For example, is there a way to obtain a return value from the Windows dialog so that I could determine whether the user successfully chose an image or canceled ?
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.