Stefan Cameron on Forms
Building intelligent forms using Adobe LiveCycle Designer

Selecting Specific Database Records

Every now and then, someone posts a comment with a question on how to do something and the answer requires more than just a quick response. In this case, it was Ricardo’s question on how to select a specific record from a data connection to a database for editing in a form.

If you read my previous post on Connecting a Form to a Database, you might’ve realized that the result was a single live data connection to the entire set of records in a database. This is great if you want to iterate through all records one at a time and update them on an individual basis. You might’ve also realized that you could narrow the scope of the data connection by specifying a more specific SQL statement (with a WHERE clause, for example). But what if you wanted the form to filter, on the spot, the data loaded from the data connection? For example, you might want to let the user pick from the different movie categories (action, comedy or drama) and then let them iterate through only that subset of the Movie Database.

If you’ve been scratching your elbow, pinching your nose and blinking your eyes in hopes that this might “just work”, well, it’s actually scratch your nose, pinch your elbow and roll your eyes — ok, just kidding…

The idea with this sample (based on the Movie Database) is to design a form which has a drop down list for picking a movie category and then a subform (which appears only once a category has been selected) that contains the movie data for all movies with the selected category.

The key to achieving this functionality is to use two data connections. (It’s important to note here that while you may only have a single data connection which loads data from an XML Data file, you may have any number of data connections to web services (WSDL) and databases (ODBC).) Furthermore, the use of SQL statement is crucial to making this work properly.

xfa.sourceSet.{DataConnectionName}

When you define a data connection in the Data View palette, you’re actually defining a <source name=”{DataConnectionName}”> node within the <sourceSet> packet inside the XDP file (which is then wrapped in a PDF if you save your form as a PDF file). Since this is defined in the XDP using XML, you can access its properties just like you can get at the properties of the objects you place on your form.

In this sample, I’ve defined two data connections to the Movie Database:

Two Data Connections

If you look at the XML Source which describes these connections, you can see that there’s an interesting command node which contains information about the query currently being used by each data connection. That’s what we ultimately want to modify once the user picks a movie category:

Source Set XML

//query@commandType

You should note that the query node’s commandType attribute value is very important. Setting it to text will let you specify the SQL statement used by the data connection. Other possible values are table (to let you specify a table name for the data connection) and storedProc for specifying a stored procedure.

Data Node Names

Another very important thing to note is the names given to the data nodes in the MovieCategories data connection. You’ll notice that the following SQL statement is used for the data connection:

SELECT id as catId, name AS catName FROM movie_categories
GROUP BY name ORDER BY name;

In particular, the id and name columns have been renamed to catId and catName, respectively. That’s because having data nodes with the names “id” and “name” in your data connection will give you a lot of headaches when attempting to iterate through the xfa.record.{DataConnectionName} node in order to find the data associated to the current record from a data connection (so that we can display the category names in the drop down list, for example). This is because the words “id” and “name” conflict with properties of the xfa.record object.

Building the Form

Category List

The first step is to use the Data Drop Down List object from the Custom tab in the Library palette. This is a really handy object that has code in its Initialize event that’s already setup to populate its item list based on data nodes from a data connection.

In the Initialize event of the object, set the data connection name to “MovieCategories”, the hidden value column name to “catId” and the display text column name to “catName”.

If you run the form at this point, you should get three values in the list: “Action”, “Comedy” and “Drama”.

Movies in the Category

Next, create a subform (let’s call it “movieData”) which contains fields with explicit bindings to the title and showTime data nodes from the MoviesInCat data connection using the Binding tab in the Object palette. Also, add the Data Connection Controls object from the Connecting a Form to a Database sample to this subform (making the proper adjustments for the data connection name in each button’s Click event script) and make this subform invisible.

At this point, you should have a form which displays a list of categories and contains an invisible subform.

Filtering Records Displayed by the movieData Subform

Finally, in the Data Drop Down List’s Change event, write a script which sets the SQL statement used by the MoviesInCat data connection, opens the connection and displays the movieData subform. For this sample, I chose to use FormCalc to script this event.

First, get the category selected by the user and determine it’s associated ID:

var sCategoryName = xfa.event.newText
var sCategoryId = $.boundItem(sCategoryName)

Given the XML structure of the <sourceSet> packet displayed above, you first set the query’s command type to “text”:

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

This ensures that the data connection will use an SQL statement. Note the pound (#) prefix to the command property of the MoviesInCat node.

Then, set the SQL statement, on the MoviesInCat data connection, which will filter the records from the movie table in order to show only those that belong to the selected category:

xfa.sourceSet.MoviesInCat.#command.query.select =
  concat("SELECT title, showTime FROM movies WHERE categoryId = ",
    sCategoryId, " ORDER BY title;")

Finally, open the data connection, move to the first record and show the invisible subform:

xfa.sourceSet.MoviesInCat.open()
xfa.sourceSet.MoviesInCat.first()
movieData.presence = "visible"

Opening the data connection will cause the explicit bindings you set earlier on the fields in the movieData subform pertaining to the movie title and show time data to be used in order to load data from the xfa.record.MoviesInCat record (which will now contain the data from the first record of the MoviesInCat data connection as per the SQL statement we just built using the selected category ID).

If you want to “run” this sample, you can download the form and Movie Database here:

Download Sample [zip]

Minimum Requirements: Designer 7.0, Acrobat Standard 7.0.

Use the FormBuilderDB20060929.sql file to build the database, create an ODBC Connection named “FormBuilderDB” and load the form.

Update for Designer/Acrobat 8.0 forms: If you’re attempting to reproduce this sample or something similar in your own forms using Designer and Acrobat 8.0, you’ll most likely run into security errors when attempting to run the form in Acrobat 8.0. This is due to new restrictions imposed on modifying data connections at run time in XFA 2.5 forms.

Updated: February 6, 2007


Posted by Stefan Cameron on September 29th, 2006
Filed under Data Binding,Scripting,Tutorials
Both comments and pings are currently closed.

164 Responses to “Selecting Specific Database Records”

  1. Michael Thierauf on October 16th, 2006

    I am using this code to find records but if a record is not in the database i gte an ugly error message. Is there a way to use error handling in FormCalc??

  2. Stefan Cameron on October 17th, 2006

    Michael,

    Unfortunately, I don’t think FormCalc has error handling capabilities (I can’t find any documentation on it).

    You can, however, convert the script to JavaScript and use its “try..catch” statement for error handling purposes when using the “open”, “first”, “addNew”, etc., data connection commands.

  3. Michael Thierauf on October 17th, 2006

    Thanks Stefan. Is there a way to use this same logic to retrieve a specific database record using javascript??

  4. Michael Thierauf on October 17th, 2006

    I think I’ve got it worked out using javascript, thanks for you help Stefan.

  5. Stefan Cameron on October 17th, 2006

    Michael,

    I’m glad you figured it out. Let me know if you have any other questions.

  6. Ernest on October 18th, 2006

    The above info has been very informative and useful… Thanks

    Can I, or how can I open a PDF filtered to one specific record? What I would like to do is, use an asp page to search for a specific record, then pass by URL or Post, the RecordID to the PDF. Populate a hidden field with the RecordID, which would then be used in the WHERE clause of the sql query.

    Thanks in advance for any info.

  7. Stefan Cameron on October 20th, 2006

    Ernest,

    I love questions like that because they’re challenging. Check out my new post on URL Requests in response to your question.

    Now that you know how to pass-in some values via the URL, you should be able to pass-in the RecordID value and use it in the data connection’s SQL statement in order to retrieve data for that record.

  8. Ernest on October 21st, 2006

    Thanks for the response. I tried it in its simplest form and does the trick. Now I will try a real world scenario.

  9. Robert G on October 23rd, 2006

    I’m curious though since I’m considering switching from Infopath to LiveCycle. I have an Access database with so far a total of 2,500 patient records based on a form that I have no converted to LiveCycle. Two things I’d like to do is to create a submit button so that when they open up the PDF form it’s a blank record not one that has data filled into it (which I noticed) enter new data and it gets stored into Access. Secondly I’d like to create this search button but I’d like my staff to either search by SS# or the patients last name/first name and the record will appear. Can I do this with LiveCycle? I noticed when I opened my PDF form I saw the 1st client’s info from the Access Database and was kind of hoping it would be blank and then let me add a add new button and take it from there. Sorry for the rambling. I won’t be using this PDF through a webserver. I just wanted to copy the PDF to each computer and fill it in with Adobe Reader. Thank you.

  10. Stefan Cameron on October 24th, 2006

    Robert,

    LiveCycle can most definitely get you there. That’s just some of the stuff we’ve designed it to do.

    You say you have two use cases: The first is showing a blank form which your staff can use to enter new records into an existing Access database. The second is showing a form that lets your staff pick from a list of IDs or names and show the record(s) that match the query.

    The first case can be achieved by tweaking the sample I posted in my instructions on how to connect a form to a database. The trick there would be first to remove all buttons except the “Update” one and change its caption to “Submit”. Then, you would simply place the following FormCalc script in the root subform (named “form1″) node’s Form:Ready event:

    xfa.sourceSet.DataConnection.addNew()

    Note that you must use the Hierarchy palette in order to select the root subform.

    This script will cause a new record to created — but not added — for entry into the database. Filling the fields and clicking on the “Submit” button (renamed from “Update” and still using the original script) would then commit the new record to the database.

    The second use case can be addressed by the sample I provided in this post. By following the sample, you should be able to design a form that uses multiple data connections: One for picking an SS#, another for picking a patient’s first/last name and another one for loading the record(s) that match the query (either the single record that matches the SS# or the one or more records that match the first/last name that was selected).

    Please let me know if you have any further questions.

  11. Dave Nottingham on November 15th, 2006

    Hi there

    Has anyone any idea how to format an SQL SELECT statement based on a asp.NET querystring? I’ve searched high and low for this and would have though it to be the most obvious way to populate a web based PDF form, but….

    Thanks!

  12. Stefan Cameron on November 15th, 2006

    Dave,

    You may find this post to be helpful in your situation: Using URL Requests in PDF Forms.

    While it’s not directly related to ASP.NET, it shows you how to use the URL Request in a form (which means you could then easily use the concept to format a custom SQL Statement for a data connection).

  13. Dave on November 15th, 2006

    Stefan

    Thanks for the prompt response. I have been looking at the URL Request link (and apart from being puzzled about the step after the initialize event – sigh) I’m really trying to get the variable into the SELECT statement!

    I think this is the part that has also stumped Ernest!

  14. Dave on November 16th, 2006

    Stefan

    I downloaded your sample and examined the code. Now I am able to pass a value to a field. Soooo I thought, if I make that a hidden field, make the SELECT statement just
    Select * FROM Incidents;
    set the connection to ‘delayed open’ then use your code and add a bit to it:-

    this.ui.oneOfChild.border.fill.presence = “hidden”;
    this.rawValue = null;

    var sURL = event.target.URL; // URL used to access this PDF form
    var nRequestStart = sURL.indexOf(“?”);

    // show the URL
    URL.rawValue = sURL;

    if (nRequestStart > 0) // must be larger than zero or else this is a weird URL!
    {
    var sRequest = sURL.substr(nRequestStart + 1);

    // show the request
    Request.rawValue = sRequest;

    var aRequests = sRequest.split(“&”); // 1 request per array element

    for (var i = 0; i Stefan

    I downloaded your sample and examined the code. Now I am able to pass a value to a field. Soooo I thought, if I make that a hidden field, make the SELECT statement just
    Select * FROM Incidents;
    set the connection to ‘delayed open’ then use your code and add a bit to it:-

    this.ui.oneOfChild.border.fill.presence = “hidden”;
    this.rawValue = null;

    var sURL = event.target.URL; // URL used to access this PDF form
    var nRequestStart = sURL.indexOf(“?”);

    // show the URL
    URL.rawValue = sURL;

    if (nRequestStart > 0) // must be larger than zero or else this is a weird URL!
    {
    var sRequest = sURL.substr(nRequestStart + 1);

    // show the request
    Request.rawValue = sRequest;

    var aRequests = sRequest.split(“&”); // 1 request per array element

    for (var i = 0; i < aRequests.length; i++)
    {
    var aNameValue = aRequests[i].split(“=”); // name=value
    var sName = aNameValue[0];
    var sValue = “”;

    try
    {
    // attempt to decode the various URI encodings (regular and component)
    sValue = decodeURI(decodeURIComponent(aNameValue[1]));
    }
    catch (e)
    {
    // invalid URI encoding found — skip
    continue;
    }

    if (sName == “Sessionfield”)
    {
    // show message value in text field
    this.rawValue = sValue;
    }
    else if (sName == “color”)
    {
    // expecting RGB value: “R,G,B”
    this.ui.oneOfChild.border.fill.color.value = sValue;
    this.ui.oneOfChild.border.fill.presence = “visible”;
    }
    }
    }
    xfa.sourceSet.DataConnection.resolveNode(“#command”).query.select.value = “Select * FROM Incidents WHERE Claim_Ref=” + sValue + “;” ;
    try {
    xfa.sourceSet.DataConnection.close();
    } catch (e) {
    }
    xfa.sourceSet.DataConnection.open();

    Then in theory it should work and populate the fields, right?

    Wrong! The reference ends up in the (what will be) hidden field, but the first record is displayed regardless of the number entered in the variable! Sigh

    Any ideas?

    Oh – my JavaScript like C# is non-existant – VB is more my poison and certainly more forgiving!

  15. Dave on November 16th, 2006

    Seems there is a bug in Designer 8.0! If I use any pdf form originally built using Designer 7.x and build my 8.0 form using that as a base template the code runs! Weird or what?

    I have another question – filling a claim form with data based on the passed url variable works fine, but to complicate matters I now wish to add a few details from another related table. Somehow I need to populate two or three fields based on a WHERE statement from a field populated by the original query – so how on earth can I trigger this AFTER the first SELECT has already run and the second SELECT has the data to use to generate the statement and populate the other fields?

  16. Stefan Cameron on November 16th, 2006

    Dave,

    You’re too fast for me! There are a number of changes in Designer 8.0 and Acrobat 8.0 combined with respect to the new version of XFA (2.5, up from 2.4 in Designer 7.x and Acrobat 7.x days) — one of which is a security feature that basically prevents you from scripting against anything but the template model (that is, the <template> node, if you were to look at the XML Source). I just haven’t had time to blog about this yet.

    Therefore, you can’t do this in an XFA 2.5 form:

    xfa.sourceSet.DataConnection.resolveNode(“#command”).query.select.value = “Select * FROM Incidents WHERE Claim_Ref=” + sValue + “;” ;

    because this is scripting against the <sourceSet> model.

    The reason why your form works when you use an old 7.x form as a template is most likely because you’re getting a form that’s already defined as an XFA 2.4 form which isn’t restricted by the security policies of XFA 2.5.

    Bottom line: Keep using your 7.x forms as templates for this project until I post information about “legacy mode”.

    With respect to your other question, the key thing here is that only one data connection can be open at any given time but you can open, navigate and close them in sequence. Depending on what you need to do (like the tutorial from this post), you may need to store information from those data connections in a data model that you design in JavaScript (sorry — I know you’re a VBScript kind a guy ;) within the form (e.g. a series of nested arrays) so that you can retrieve the information after the data connection is closed.

    This is basically what’s happening in my tutorial with the drop down list because I’m storing record IDs as the values of the items in the object, closing the first data connection, setting the query on the second one based on the selected record ID and the opening the second data connection in order to display the results.

    I should also mention that data connections to ODBC Data Sources don’t work like data connections to XML Data files in that they don’t display all records in one shot. They only load one record at a time and it’s up to you to write the code to iterate through them and populate subforms, typically one subform per record.

  17. Dave on November 16th, 2006

    Stefan

    Thanks for that – I was going right round the twist with what seemed like ok script not running! Sigh

    I have tried adding a bit to the existing script :-

    …. if (sName == “Sessionfield”)
    {
    // show message value in text field
    this.rawValue = sValue;
    }
    else if (sName == “color”)
    {
    // expecting RGB value: “R,G,B”
    this.ui.oneOfChild.border.fill.color.value = sValue;
    this.ui.oneOfChild.border.fill.presence = “visible”;
    }
    }
    }
    xfa.sourceSet.DataConnection.resolveNode(“#command”).query.select.value = “Select * FROM Incidents WHERE Claim_Ref= ” + sValue + “;” ;
    try {
    xfa.sourceSet.DataConnection.open();
    } catch (e) {
    }
    xfa.sourceSet.DataConnection.close();
    // add connection to Customer
    var CustRef = Customer_Ref.rawValue
    xfa.sourceSet.DataConnection.resolveNode(“#command”).query.select.value= “SELECT Insurer_cover, Image_File FROM Customer WHERE Customer_Ref=” + CustRef + “;” ;
    try {
    xfa.sourceSet.DataConnection.open();
    } catch (e) {
    }
    xfa.sourceSet.DataConnection.close();

    Is there a reason why it won’t open another connection and popupate the field(s) based on the data now in a field from the first ‘run’ – this idea works in in VB.Net

  18. Stefan Cameron on November 16th, 2006

    Dave,

    It’s a little difficult to tell exactly what’s going here because of a lack of context. Some reasons why this may not be working as you expect it to are:

    • Implicit data binding where data nodes in the data connection you open have a name that matches the name of a field on your form. Data from those data nodes will automatically be pushed into those fields when the connection is opened, based on the first record.
    • In your script, you’re using the same data connection to connect to two different tables. I don’t think this is a good idea, again for binding reasons, whether implicit or explicit, simply because the structure of the two tables is likely completely different.

    I would recommend you try using two separate data connections (like in the tutorial in this post) and keep in mind that it’s (unfortunately) your responsibility, using script, to carry along the various values from one connection to the other.

  19. Dave on November 16th, 2006

    Stefan

    Data Connection – Dah! I was asleep! I used a seperate connection, DataConnection2, and didn’t use it in the code! Thanks for pointing that out, wood and trees ‘n’ all that!

    One other question – I have defined a ‘time’ field, but it insists upon displaying the format current date – data related time (17/11/2006 12:00:00) – even though the field is ‘Time’ ‘HH:MM:SS’ – any ideas on that?

    By the way, thanks for all your help.

    Oh – odd thing – 7.x forms are maked up as 2.5 but run – 8.0 forms won’t, but they will if marked down to 2.4 in the XML!

  20. Dave on November 17th, 2006

    Here is another interesting one! The form we discussed now runs fine and I deployed it on an internal W2K server inside a .NET service. Once again the pdf form ran fine from my workstation! Then I had an issue with OLEDB – the backend Sybase database runs on a Netware server under SPX – I was unable to connect after a Windows security update, but I noticed that the pdf file running on the server also failed to retrieve data (although the connection settings on the server are the same as locally and they worked fine).

    So after the rambling, the question is – does something need to be installed on the server for it to run and make direct connections? Or am I missing something more obvious?

  21. Stefan Cameron on November 19th, 2006

    Dave,

    With respect to your question about displaying only the time and not date and time information, you should set the field’s Display Pattern property (on the Object palette’s Field tab) to “HH:MM:SS”. This will ensure that the data gets represented as “time only”. Otherwise, the current date will probably get displayed by default.

    As for your question about having to install something special on the server, I don’t think so but this is starting to get away from my areas of expertise. Is it possible that the process on the server is executing with reduced security credentials that would prevent it from seeing the DSN as opposed to when it’s executing on your local workstation?

  22. Dave on November 19th, 2006

    Stefan

    The time thing is a real puzzler! The field is Date/Time the Validation Pattern is HH:MM:SS, the data pattern is HH:MM:SS and the data format is ‘Time.’

    Bit of a mystery! Brings in the databound time, but it also has the current date.

    As for the DSN, the server is running .NET on the same credentials without any problem. It’s very odd!

  23. Stefan Cameron on November 21st, 2006

    Dave,

    Have you set the Display pattern property to “HH:MM:SS”? It’s on the Field tab of the Object palette.

    This property controls how the data is displayed.

    Also, is it possible that the data coming from the data source contains default date information?

  24. Dave on November 21st, 2006

    Stefan

    Sorry – forgot to mention that the display pattern was set to ‘HH:MM:SS’ as well! The data from the back end is purely time – and outputs as that in every other front end!

  25. Stefan Cameron on November 22nd, 2006

    Dave,

    I know you’ve already told me that the data being pushed into the date/time field is purely time but the data is most likely the place where the problem is.

    Even though you have the Display, Validate and Data Patterns set to “HH:MM:SS” and you have the Data Format set to “Time”, if the data pushed into the field doesn’t exactly match “HH:MM:SS” (the Data Pattern), the data will be seen as erroneous and Acrobat will display the entire data — date included — in the field. This is exactly the same behaviour you would get if you entered “asdf” as the time value: “asdf” would be displayed in the field instead of being implicitly converted to some time value.

    Whether this is correct behaviour or not is a separate conversation but this is how it works.

    If I create a form with a date/time field setup as you said your is (Display, Validate and Data Patterns set to “HH:MM:SS” and Data Format set to “Time”) and set its value to “13:20:35″, I’ll get “13:20:35″ in the field. If I attempt to set it to “11/22/06 13:20:35″, I’ll first get a validation error message because that value doesn’t match the “HH:MM:SS” Validation Pattern that’s specified and then the field will display “11/22/06 13:20:35″ even though it’s format is purely time data.

    Also note that when you don’t specify a Data Pattern but you set the Data Format to “Time”, the implied (default) Data Pattern used is still “HH:MM:SS”.

    Please have a look at the data being pushed-in again and see if it’s actually carrying date information as well or if there’s a way you can pre-format it not to and let me know what you find-out.

  26. Dave on November 23rd, 2006

    Stefan

    This is the odd thing – if I open Sybase Central and check the details the column is defined as ‘Time’ – in the ‘data’ tab it shows nothing but time as HH:MM:SS. If I open the table with another desktop application, like ‘DataEase’ it shows the same, as does any .NET control (Like DataGrid of DetailsView). The interesting bit is if I change the pdf field to text – and it STILL drags in the current date! Major heavy sigh!

  27. Stefan Cameron on November 23rd, 2006

    Dave,

    While I completely understand what you’re saying about the behaviour in other applications being correct, those behaviours are specific to those applications (to Sybase Central, DataEase, etc.).

    The behaviour in Acrobat for an XFA PDF form is that if the data doesn’t exactly match the Data Pattern specified on the Binding tab in the Object palette, the data will be treated as incorrect and result will be the data displayed in the field verbatim (no formatting applied) — and this for any field type.

    I agree that it really seems strange that Acrobat wouldn’t simply parse-out the data information that may have come along with your time data and whether this is right or wrong, I couldn’t really say. It’s just what we have to deal with in Acrobat today.

    If you could tell whether the data being pushed into the field is for certain purely time information or if it happens to contain date information as well, that could help me suggest an alternative but unless I know this, it’s difficult to properly assess the problem.

    One way you could test this is by binding the data to a text field with no patterns set. Since all data can be represented in a textual way (and it usually comes across the wire in text format anyway), the text field should give you your data in a WYSIWYG format (i.e. exactly as it is without any implicit pattern formatting changes).

  28. Dave on November 23rd, 2006

    Stefan

    I have found a workaround – make the time field hidden, drag another textfield onto the form and write the following JavaScript under inialize:-

    var newtime = Time_Reported.rawValue;
    slicer = newtime.slice(10,16);
    TextField1.rawValue = Slicer

    This now returns 15:00 instead of 23/11/2006 15:00:00

    I’m not sure if I might run into problems later, but…

  29. Willie on December 29th, 2006

    Ok, I’ve struggled with this for some time now. Time to ask questions.

    The change event in the downloadable example does not work for me when I place my tables in an Oracle 10.2g database. It fails on:

    oDataConn.open()

    with the following error:

    open operation failed. [Micorsoft][ODBC driver for Oracle][Oracle]ORA-00911: invalid character.

    The database open command in the combo box initialize event works just fine. The two of my data connections “tested” fine when I built the connections. The only differences I can wiggle a finger at are the fact that one script is written in Java, while the other is in FormCalc. Also how the database nodes are referenced are different. I looked up the meaning of the Oracle -00911 error code which just expounds a little bit more to say that an illegal identifier was used. That identifiers must start with valid ascii characters. I don’t know how or where to validate what the oDataConn.open() is using other than connection properties and the sql string this example dynamically builds.

    Any suggestions?
    Thanks, Willie

  30. Willie on December 29th, 2006

    To add to my previous post, I suppose it might be useful to know what version of livecycle designer I am using:

    8.0.1291

  31. Willie on December 29th, 2006

    A suggestion if you would…when you display code snippets in the main article of the blog, please make sure they match the code in the downloadable example. I followed the cited code in the blog for a long time before downloading the example to find contained within the example a comment that you must create a reference to the data node otherwise you will encounter an error. (I still can’t get my code to work using a reference, but thats another posting).

    I saw in one of the comments posted in this link the use of try catch error handling. Contained in the catch clause, the user just displayed “e”. From version 8 of designer, this shows nothing for me. Which leads me into another issue, not all objects contain intellisense. “e” is one such object for me. I made a couple educated guesses at properties (name, code, description, text) and found only the name property to be functional. However, the name “refernce error” or “generic error” are not sufficient enough for me to debug with. Is there something I am doing wrong that I am not getting full intellisense?

    I have been bouncing back and forth between this link and the URL reference link a lot. I have the code functioning to pass the URL parameter into the PDF and I have parsed apart the values. I next want to take this value and use it in the where clause to the entire form. Besides not being able dynamically build a sql query against Oracle, I was wondering what event would be best to house the code to extract the url parameter and update the sql string?

    Right now, I am testing this logic in the initialize event of one generic text field control. When I tried moving this logic to the initialize event of the Form itself, I encountered untrappable errors (try catch failed to catch them). Im don’t know in what order all controls are initialized, if not all xfa objects had been instantiated yet or not. Being a VB.Net programmer, I am use to utilizing the form_load events in these cases.

    Ok, and heres the last item I had issues with in the database links. The xfa.sourceSet.DataConnection.next and previous functions were iterating through each of the records I expected to see returned during testing, plus a blank record before the first record, and another blank record after the last returned rows. I guess I was expecting BOF and EOF to stop on the first and last records not blank records. Again, this may be some quirk from my having created a data connection to Oracle 10.2g. (I didn’t want to install mysql when I already had a working ODBC database, furthermore my project would be utilizing Oracle in the end, so I might as well start tutorials using it as well.)

  32. Stefan Cameron on January 6th, 2007

    Willie,

    • There’s a difference between “testing” the connection to the ODBC data source and opening the data connection when running the form in Acrobat. When you “test” the data source connection, you’re just making sure that you can communicate with the data source using the ODBC drive and settings you’ve specified. When you execute script like “MyDataConnection.open()”, you’re now attempting to execute a connection string to get access to data within the data source. If the data connection you’re using is an SQL Query type and you’ve attempted to generate the SQL Query syntax programmatically, it’s possible you might have a syntax error in the string you’re specifying as the SQL Query for the data connection — which would explain the “invalid character” error you’re getting when you attempt to open the data connection.
    • I appologize if an inconsistency between the sample and my post led to confusion. I’ll try to keep things synchronized as much as possible.
    • Unfortunately, “intellisense” is something Designer and Acrobat lack right now — not to mention any sort of debugging environment. We know about these issues and are presently working on some things for an up-coming version.
    • I guess you’re referring to my post on URL Requests. This information is available immediately when the form is being loaded. Since you’re wanting to use information from it to affect the query used by the main data connection for the form, I would use the Initialize event on the root subform (usually “form1″, located at the very top of the tree in the Hierarchy palette). I realize you say you’ve tried it here already and got some “untrappable” errors but without knowing more about your script, this is still where I would recommend you put it. As far as XFA objects go, they all exist the moment the form begins execution so you can get and set properties on any object in the root subform’s Initialize event. Of course, there are exceptions like attempting to get the width of a text field with a minimum width set as zero before the text field’s “Layout:Ready” event has fired. There are timing issues involved if you’re using AcroForm object properties in your script since some properties cannot be read and/or set before certain events occur on the form.
    • It sounds like your data connection’s BOFAction and EOFAction properties have been set to “StayBOF” and “StayEOF”, which has the effect of moving to a blank BOF or EOF record before the first or after the last record in the set, respectively. What you want to do is specify “StayBOF” and “StayEOF” values for the BOFAction and EOFAction properties of the data connection. You can do this either by editing the properties of the data connection using the Data View palette or by script like in the Initialize event of the Data Drop Down List object found in the Library’s Custom tab.

    Hopefully this answers all your questions.

  33. Alex on January 25th, 2007

    Stefan,

    All,

    First, thank you for you blog, it is by far the best on Designer. I am using Designer 8.0 and am pulling data from a database (dataConnection1) and populating a drop down list. Then I am have binded Corp_Zip code field that resides in the same database via a separate connection (DataConnection3). This is to keep the primary uniform and the secondary to manipulate. When the form first displays it does pull the first record in the database. After I pull the drop down list the Corporate.rawValue is produced. I created a separate text field and feed it in, but my query (launched on click) does not pull the data from the database and reload into the approriate fields.

    I am using an access database via an access work group,

    Any thoughts.

    Code on “button”

    topmostSubform.Page1.Button3::click: – (JavaScript, client) —-

    xfa.sourceSet.DataConnection3.#command.query.commandType = “text”
    xfa.sourceSet.DataConnection3.#command.query.select.nodes.item(0).value = Concat(“Select * from Corporate Where Corp_SeqNo = “,Corporate.rawValue, “”)
    xfa.sourceSet.DataConnection3.open()

  34. Stefan Cameron on January 30th, 2007

    Alex,

    Thank you very much! I’m glad you find the information posted here to be useful.

    Before we investigate this any further, are you previewing/saving your form as an Acrobat 8.0 Dynamic PDF? If so, then you’re saving your form as an XFA 2.5 form and the problem is likely the fact that you’re running into security issues when you attempt to modify DataConnection3′s SQL statement on-the-fly.

    Please refer to my article on designing forms for XFA 2.5 (XFA 2.5 is new in Designer/Acrobat 8.0) and try using the cloning technique it describes.

  35. Alex on February 14th, 2007

    Stefan, the cloning technique worked on the refresh button, The trick was in the line “ref($sourceSet.DataConnection2.clone(1))” Thanks for your help.

    if (Corporate.rawValue > 0) then
    var aBC = ref($sourceSet.DataConnection2.clone(1))

    aBC.#command.query.commandType = “text”
    aBC.#command.query.select.nodes.item(0).value = Concat(“Select * from Corporate Where Corp_SeqNo = “,Corporate.rawValue,”")
    aBC.open()

    endif

  36. ashaari on February 28th, 2007

    Refer to your sample of the Selecting Specific Database Records, I couldn’t manage to resolve and some of the issues especially for Data (Select from sample xml data / field from asp.net) it’s not functioning at all. how im suppose to set the variable and to do the query for this sources. Kindly brief me as detail as u can.

  37. Colin on March 4th, 2007

    Stefan,

    I have used a combination of your examples on URL Request, Selecting specific records and display all records but seem to have a problem when trying to filter the database with the passed URL.

    This is a snippet of the code used in the form initialise section

    // obtain RJID value from Request

    var sURL = event.target.URL; // URL used to access this PDF form
    var nRequestStart = sURL.indexOf(“=”);

    if (nRequestStart > 0) // must be larger than zero or else this is a weird URL!
    {
    var sRequest = sURL.substr(nRequestStart + 1);

    }

    var oDB = xfa.sourceSet.TestConn
    oDB.resolveNode.(#command).query.select.value = “Select * From tblItemNDT Where RJID = ” + sRequest + ” ORDER BY ItemNDTID Asc;”;
    oDB.open();
    oDB.first();

    The code then goes on to display the contents of the database making new instances for each row item.

    When I don’t filter all the entries come up as expected but when I use the filtering statement no items appear. It looks as though the code below this point is not executed.

    I realise that the example for the Selecting Specific records uses FormCalc and I am not sure how to convert this to Javascript.

    I am using version 7,0,041126.

    Any help would be greatly appreciated.

    Colin

  38. Stefan Cameron on March 9th, 2007

    ashaari,

    This sample is meant specifically for data connections to ODBC data sources. If you’re attempting to import data from an XML data file or some source other than ODBC, then you’ll need to use other methods.

    If you’re importing XML data, then it would either be imported via data merge on the server when the PDF is generated from your XDP form or imported manually via the Import Data dialog (from Acrobat 8.0′s “Form | Manage Form Data | Import Data” menu).

    At that point, any bindings you’ve setup should function properly.

    Note that importing XML data into a form implies that you have a data connection based on a schema and you may only have one of that kind of data connection per form (whereas you can have multiple data connections to ODBC or web service data sources in a single form).

  39. Stefan Cameron on March 10th, 2007

    Colin,

    I believe it’s a simple case of a syntax error in your filtering statement:

    oDB.resolveNode.(#command).query…

    It should be

    oDB.resolveNode(“#command”).query…

    The syntax error would result in the stop of code execution once that line is reached.

    If you ever run into another situation like this, just press “Ctrl + J” in Acrobat Standard or Pro to show the JavaScript Console (you can do this from the Preview tab in Designer as well). It’ll probably be reporting an error which will contain the event code and line number to investigate.

  40. Colin on March 11th, 2007

    Stefan,

    Thanks for that.I have it working now and as you suggested it was just a syntax problem.

    Now I can get cracking with the rest of the application.

    Thanks again for your help.

    Colin

  41. chris on March 21st, 2007

    Hi. I am not sure what I have done as everything was working perfectly and now after my machine crashed my form no longer works.

    I used the sample code for how to select 1 record from a database and it worked. Now I get an error message saying:

    Error: This operation violates your permissions configuration. I read another article you wrote about getting that error when my document is certified, but what certified? It’s still in development – I have 4 fields on it – no signature.

    I created a new form from sratch and same thing. It’s like a setting has been turned on, but I can’t for the life of me find it.

    Doing my head in :-)

  42. Stefan Cameron on March 21st, 2007

    Chris,

    Am I correct in thinking that you re-created your form using Designer 8.0 in conjunction with Acrobat 8.0?

    If that’s the case, then your problem is most likely that you aren’t cloning your data connection prior to modifying its properties.

    New forms created in Designer 8.0 are based on XFA 2.5 by default and that subjects your form to stricter rules in Acrobat 8.0. One of these rules is that you can’t modify the form directly when it’s running in Acrobat in case someone were to decide to sign it. Because XFA 2.5 supports what I call “on-the-fly certification”, this means that your form could get signed at any point in its workflow, even without having a signature field on it to sign with. Once a form is signed, any direct modification would inadvertently invalidate the signature. Therefore, Acrobat 8.0 restricts such inadvertent modifications right from the start (even if the form never gets certified) so that you don’t risk running into problems later.

    If you use the cloning method I described in my article on Better Form Design with XFA 2.5, you’ll end-up modifying an in-memory copy (clone) of the data connection instead of modifying the original one directly in the document. Your form will therefore keep functioning properly — even if someone were to decide to sign it somewhere along the way — and that “permissions configuration” error will go away.

  43. Colin on March 29th, 2007

    Stefan,

    I have worked through your tutorials and examples, and I have managed to complete several forms where a report number is passed to the form in the URL link, the datasources are filtered for this report number and the relevant information used to populate the form template. I have also managed to dynamically create the number of item rows in the form depending on the number of items in that report, flowing onto continuation pages where necessary.

    This all works fine if your computer is attached to the database either on the client machine or on a network. If the database was on a webserver and you do not want clients to have direct open database connections for obvious security reasons, is there a way of rendering the information on the server side, produce the PDF file and serve it to the client without upgrading to form server? I am not using the Form templates for anything other than “read-only” Report viewing.

    I am using Lifecycle designer 7.0. Is there such a faciltiy in Version 8.

    Your assistance would be greatly appreciated.

    Colin

  44. Stefan Cameron on April 1st, 2007

    Colin,

    What you’re asking for is one of the reasons why we offer a Form Server solution.

    By itself, Designer doesn’t have the capability of doing what you’re asking for.

    The only alternative I can think of is to build a web service as the interface between the form (external) and your database (internal). It’s not necessarily the easiest thing to do but it would get you around having to purchase LiveCycle Forms and, depending on your requirements, would probably be easier on the budget as well.

  45. Willie on April 2nd, 2007

    Stefan,

    Can you provide an example or two of how to interact with a stored procedure…the only part I caught in this link is replacing the “text” commandType parameter with “storedProc”. Could you illustrate 1) how to call a stored proc without any paramters, 2) how to call a stored proc with input parameters, 3) how to call a stored proc with both input and output parameters / return status.

    Thanks,
    Willie

  46. Willie on April 3rd, 2007

    A little more info:

    The oQueryNode.commandType I was referring to is located inside the script code for the Database object variable.

    I have now since gone back to redefining my connSQL database connection (previously set up for SQL Query (aka text)). It is in here that I found radio buttons for the Record Source, and I have tried to select Stored Procedure. Unfortunately, I am getting a “could not execute stored procedure ” error message. I am not sure what to do to fix this error. When I test the connection string I built, it connects ok.

    I also checked the assigned database premissions to guest and to dbo for the stored procedure that I am trying to use…they have full privs. (My connection string is using user sa.)

    Any thoughts?
    Thanks,

  47. John on April 6th, 2007

    I have just started using pdf forms and I think they are great. I have read your articles on Connecting a Form to a DataBase and Selecting Specific Database Records. These articles have been a big help.
    The question that I have is if you are connecting to a password protected database and you know the password, how do you program adding your user name and password? My code is in FormCalc and I am attaching to an Access DB that is linked to an oracle database and is password protected.

  48. Stefan Cameron on April 9th, 2007

    John,

    If the data source that your form must access is password-protected, the password must be specified in the connection string that you build using the Data Connection Wizard (the dialog that you get when you create a new or modify an existing data connection).

    In your case, you would create an ODBC data connection and then select the DSN which is configured to connect to the database in question. The password is usually already specified in the DSN but you can also specify it in the data connection settings as well (on the last step there are “User” and “Password” fields you can optionally fill-in).

  49. Stefan Cameron on April 11th, 2007

    Willie,

    I’ve been trying in vain for the past few days to get Designer to find the stored procedures I defined in my MySQL database in order to test this out since I haven’t done extensive testing with stored procedures yet. I think there’s something wrong with the new MySQL 5.0 ODBC Connector (not surprising since it’s still in beta).

    Anyway, I finally got hold of an SQL Server box which I could for testing purposes and found that Designer only supports data connections to stored procedures which take no parameters (neither input nor output). When I attempted to connect to a stored procedure with parameters, I would get the same “unable to execute” error you’re getting.

    Essentially, the data connection gets generated based on the data set that the ODBC Driver provides for the procedure. Once the data connection defined, it’s just like connecting to a table where you can browse, modify remove or add records.

  50. Kristin on April 13th, 2007

    I am having problems getting this to work. I have the following code in the initialize of the JobAppID field:

    var sURL = event.target.URL; // URL used to access this PDF form
    var nRequestStart = sURL.indexOf(“?”);

    if (nRequestStart > 0) // must be larger than zero or else this is a weird URL!
    {
    var sRequest = sURL.substr(nRequestStart + 1);
    var aRequests = sRequest.split(“&”); // 1 request per array element

    for (var i = 0; i I am having problems getting this to work. I have the following code in the initialize of the JobAppID field:

    var sURL = event.target.URL; // URL used to access this PDF form
    var nRequestStart = sURL.indexOf(“?”);

    if (nRequestStart > 0) // must be larger than zero or else this is a weird URL!
    {
    var sRequest = sURL.substr(nRequestStart + 1);
    var aRequests = sRequest.split(“&”); // 1 request per array element

    for (var i = 0; i < aRequests.length; i++)
    {
    var aNameValue = aRequests[i].split(“=”); // name=value
    var sName = aNameValue[0];
    var sValue = “”;

    try
    {
    // attempt to decode the various URI encodings (regular and component)
    sValue = decodeURI(decodeURIComponent(aNameValue[1]));
    }
    catch (e)
    {
    // invalid URI encoding found — skip
    continue;
    }

    }
    }

    this.rawValue = sValue;

    And the following code in the JobAppID MouseUp:

    xfa.sourceSet.DataConnection.#command.query.commandType = “text”;

    xfa.sourceSet.DataConnection.#command.query.select =
    concat(“SELECT JobAppDate, JobAppLName, JobAppFName, JobAppMName, JobAppAddress, JobAppCity, JobAppState, JobAppZip, JobAppHPhone, JobAppBPhone, JobAppSSN, JobAppEighteen, JobAppWkgPapers, JobAppLegal, JobAppFelony, JobAppFelonyDesc, JobAppNameDesc FROM JobApplicants WHERE JobAppID = “, 6, “;”);

    xfa.sourceSet.DataConnection.open();
    xfa.sourceSet.DataConnection.first();

    I have hardcoded the JobAppID in the SELECT statement for simplicity. Once I get this to work, I will be passing the ID in a URL. I have tried putting all of this code in the Page Initialize, all of the code in the JobAppID Initialize, with no luck. Can you tell me what I am doing wrong? I only have 1 DataConnection, because I am only working with 1 table.

  51. Kristin on April 18th, 2007

    After I added this to my XML Source:

    The following lines of code worked for parsing the URL Request and selecting the correct record in the database:

    var sURL = event.target.URL; // URL used to access this PDF form
    var nRequestStart = sURL.indexOf(“?”);

    if (nRequestStart > 0) // must be larger than zero or else this is a weird URL!
    {
    var sRequest = sURL.substr(nRequestStart + 1);
    var aRequests = sRequest.split(“&”); // 1 request per array element

    for (var i = 0; i

    The following lines of code worked for parsing the URL Request and selecting the correct record in the database:

    var sURL = event.target.URL; // URL used to access this PDF form
    var nRequestStart = sURL.indexOf(“?”);

    if (nRequestStart > 0) // must be larger than zero or else this is a weird URL!
    {
    var sRequest = sURL.substr(nRequestStart + 1);
    var aRequests = sRequest.split(“&”); // 1 request per array element

    for (var i = 0; i < aRequests.length; i++)
    {
    var aNameValue = aRequests[i].split(“=”); // name=value
    var sName = aNameValue[0];
    var sValue = “”;

    try
    {
    // attempt to decode the various URI encodings (regular and component)
    sValue = decodeURI(decodeURIComponent(aNameValue[1]));
    }
    catch (e)
    {
    // invalid URI encoding found — skip
    continue;
    }

    if (sName == “JobAppID”)
    {

    xfa.sourceSet.DataConnection.resolveNode(“#command”).query.commandType = ‘text’;

    xfa.sourceSet.DataConnection.resolveNode(“#command”).query.select.value = ‘SELECT JobAppDate, JobAppLName, JobAppFName, JobAppMName, JobAppAddress, JobAppCity, JobAppState, JobAppZip, JobAppHPhone, JobAppBPhone, JobAppSSN, JobAppEighteen, JobAppWkgPapers, JobAppLegal, JobAppFelony, JobAppFelonyDesc, JobAppNameDesc FROM JobApplicants WHERE JobAppID = ‘ + sValue;

    xfa.sourceSet.DataConnection.open();

    }
    }
    }

    Just an FYI for anyone else that might be struggling. This code works for me.

  52. Stefan Cameron on April 18th, 2007

    Kristin,

    Thanks for posting your solution.

    From what I can see, it looks like your first attempt was using an invalid JavaScript function named “concat“. It’s valid in FormCalc but not in JavaScript.

    The use of “contact” in your script would’ve been causing a JavaScript error which would’ve been stopping script execution prior to opening the data connection.

    Your second attempt removes the use of “contact” and simply uses the plus operation to concatenate the query string with the value you got from the URL Request which is the right way to do it.

  53. Ernest on April 30th, 2007

    The above is the script is what I’m looking for. Can I ask you to please post a simple example for download, so
    that I might be able to better understand what is happening. Also, the data is need to pull is in two seperate tables.
    Using a sql query, should I be able to join that data, or will I be restricted to one table.

    I’m using Acrobat Pro & Designer 7.0

    Thanks…

  54. Stefan on April 30th, 2007

    Ernest,

    When you say, “the above script”, do you mean the script that Kristin posted or some other script?

    As for pulling-in data from two or more tables, you can do that simply by using an SQL “join” query.

  55. Ernest on April 30th, 2007

    Yes.. The one by Kristin on April 18th, 2007.

    Thanks

    Ernest

  56. Ernest on May 1st, 2007

    I have tried the example above, and had success to some degree. I have been able to retrieve the “RecordID” from the URL. The database connection works when I hard code it, but fails when I try to use the “RecordID” parsed from the URL. I get error: open operation failed. [Microsoft][ODBC SQL ServerDriver][SQL Server] Conversion failed when the varchar value ‘sValue’ to data type int.

    I’m using Acrobat 7.0 Pro & Designer – SQL Server 2005

    The code for both the URL parsing and database connection is below:

    Thanks in advance for suggestions on how to correct this issue.

    Ernest

    —————URL—————–

    // Requests:
    // RecordID >> The RecordID to display, URI-encoded

    // start by clearing the values first
    this.ui.oneOfChild.border.fill.presence = “hidden”;
    this.rawValue = null;

    var sURL = event.target.URL; // URL used to access this PDF form
    var nRequestStart = sURL.indexOf(“?”);

    // show the URL
    URL.rawValue = sURL;

    if (nRequestStart > 0) // must be larger than zero or else this is a weird URL!
    {
    var sRequest = sURL.substr(nRequestStart + 1);

    // show the request
    Request.rawValue = sRequest;

    var aRequests = sRequest.split(“&”); // 1 request per array element

    for (var i = 0; i < aRequests.length; i++)
    {
    var aNameValue = aRequests[i].split(“=”); // name=value
    var sName = aNameValue[0];
    var sValue = “”;

    try
    {
    // attempt to decode the various URI encodings (regular and component)
    sValue = decodeURI(decodeURIComponent(aNameValue[1]));
    }
    catch (e)
    {
    // invalid URI encoding found — skip
    continue;
    }

    if (sName == “RecordID”)
    {
    // show RecordID value in text field
    this.rawValue = sValue;
    }

    }
    }

    ———————————–Database Connection—————-

    Provider=MSDASQL.1;Persist Security Info=True;User ID=XXXXXXXX;Data Source=CRMS;Extended Properties=”DSN=CRMS;Description=CRMS;UID=XXXXXXXX;APP=Adobe Designer;WSID=XXXXXXXX-NB;DATABASE=CRMS;LANGUAGE=us_english;Network=DBMSSOCN”
    XXXXXXXX
    XXXXXXXX

    SELECT dbo.Juvenile.JuvenileID, dbo.Juvenile.TroopID, dbo.Juvenile.PostID, dbo.Juvenile.OffenseType, dbo.Juvenile.Violation, dbo.Juvenile.ViolationDate, &#xD;
    dbo.Juvenile.FName + ‘ ‘ + ISNULL(dbo.Juvenile.Middle, ”) + ‘ ‘ + dbo.Juvenile.LName AS Juvenile, ISNULL(dbo.Juvenile.Title, ”) &#xD;
    + ‘ ‘ + dbo.Juvenile.ParentFName + ‘ ‘ + ISNULL(dbo.Juvenile.ParentMiddle, ”) + ‘ ‘ + dbo.Juvenile.ParentLName AS Parent, dbo.Juvenile.Address, &#xD;
    dbo.Juvenile.City + ‘ ‘ + dbo.States.StateAbbr + ‘ ‘ + dbo.Juvenile.Zip AS CSZ, dbo.Juvenile.NCO, dbo.Juvenile.DateNotified, &#xD;
    dbo.States.StateAbbr&#xD;
    FROM dbo.Juvenile LEFT OUTER JOIN&#xD;
    dbo.States ON dbo.Juvenile.State = dbo.States.StateID&#xD;
    WHERE (dbo.Juvenile.JuvenileID = ‘sValue’)

  57. Stefan Cameron on May 1st, 2007

    Ernest,

    How are you specifying the new select statement for the data connection? Did you simply define it the way you posted it in the Data Connection Wizard or are you setting it on the data connection after your script to extract the value from the URL Request like Kristin is doing in her script?

    The only way you can set that SQL statement is by doing it the same way Kristin is in her script. If you set this SQL statement in the wizard, the data connection then includes an unknown token named “sValue” (i.e. the value you assign to the “sValue” variable you declare in the “for” loop in your script won’t make it to the “sValue” you’re attempting to use in your SQL statement).

    Also, note that you should be declaring the “sValue” variable outside the “for” loop in the script you posted. At the moment, it’s declared inside the “for” loop and so attempting to access it outside the loop in order to include it in your SQL statement would produce a syntax error.

  58. Greg Blodgett on July 16th, 2007

    Not sure where to post this on this site so figured this looked best. I am trying to follow a tutorial you had posted a while back and cannot get it to work.
    Basically I need someone to choose a value from a drop down list (which is pulling from an oracle db). Then based on that drop down I want it to populate other fields on the screen. Right now I have 2 fields on the screen ZZ_CODE and ZZ_DESC. When they choose ZZ_CODE I want it to populate ZZ_DESC.
    Here is my script:

    —– form1.#subform[0].Body.ZZ_CODE::change: – (FormCalc, client) ———————————

    var sCODE = xfa.event.newText
    var sZZ_CODE = $.boundItem(sCODE)
    var oDataConn = Ref(xfa.sourceSet.AbraData )

    oDataConn.#command.query.commandType=”text”

    oDataConn.#command.query.select = Concat(“Select distinct ZZ_DESC from PS_ZZ_HRTABLES Where ZZ_CODE = “, sZZ_CODE,”")

    oDataConn.open()
    oDataConn.first()
    AbraSub.presence=”visible”
    AbraSub.ZZ_DESC = sCODE

    I get an Script Failed error – Error: syntax error near token ”

    If I can get this working I would then like to be able to take multiple fields into my where clause to populate the other fields on the form. For example, i will have a country code and a state code which will drive combined the data that shows on the form. So where clause like so ” Where state = “, sZZ_State,” and country = “,sZZ_COUNT,”")

    Any help would be greatly appreciated. In fact I could even fax you a beer! :)

  59. Ernest L. Kendricks on July 19th, 2007

    Hopefully this is the correct area to ask this question, if not please forgive me.

    I need to print multiple PDF in one click. Currently I’m using the FDF toolkit to populate a PDF from the database. That works
    great. The trouble is I can only populate and print one FDF at a time. Is is possible, and if so, how, to send a range or criteria to the FDF script, and thus populate and and be able to print more than one FDF at a time.

    Thanks in advance

    Ernest

  60. Stefan Cameron on July 21st, 2007

    Greg Blodgett,

    Please see my response over on the FormCalc Expressions (If and For) article.

  61. Mark Richman on July 22nd, 2007

    I’m a little lost here … I have a form bound to an ODBC data source. I just want to move the cursor in the recordset to a specific record…i.e. “where lastname=”jones”). I’ve looked at the API, and I only see first(), last(), prev(), next(), etc….I don’t see how to do moveTo(), find(), select(), filter(), etc.

    Am I just using the wrong tool?

  62. Stefan Cameron on July 29th, 2007

    Ernest L. Kendricks,

    Unfortunately, I don’t know much about the FDF toolkit. Perhaps there’s an Acrobat Forum that you could post into in hopes to get an answer from someone more knowledgeable in that area. Sorry I couldn’t be more helpful!

  63. Stefan Cameron on July 30th, 2007

    Mark Richman,

    Unfortunately, it’s not as simple as using a find() command on the record set obtained from the ODBC data connection. What this article explains is how to use two ODBC data connections to the same source but where the first is used to provide the user with a way to make a sub-selection within all the records (by selecting a movie category) and the second is modified on-the-fly (by specifying a specialized SQL query, at runtime, which filters the records based on the movie category selected by the user), executed and the records it produces are displayed in a special section.

    You could easily eliminate the drop down list and the first data connection and simply provide the user with various search fields (e.g. name, address, department, etc.). You could then use those values in the query you generate to apply to the data connection. At that point, if the user’s criteria yielded no records, there simply won’t be any records loaded (and the user won’t be able to scroll through them).

    Does this clarify the concept a little better?

  64. Rodolfo Hernandez on August 8th, 2007

    Hi Stefan,
    I have a form like “Purchase Order” sample, I have 1 dropdown’s binded to 1 Data Source using XFA 2,5+ custom control, but i have the following strange efect: once i selected any item from list, the text change to another record usually the 2nd register of db, any idea ?

    This is my code of DropDown

    On Initiatialize Event

    same as script default exept:
    var sDataConnectionName = “ListaDeArticulos”; // DBConnection Name
    var sColHiddenValue = “PrecioVenta”; // Item Price asociated
    var sColDisplayText = “NombreArtículo”; // Text show in Drop Down

    On Change Event

    var sNombreArt = xfa.event.newText // Item’s Name Selected (This change before selected)
    PrecioVenta.rawValue = $.boundItem( sNombreArt ); // Get Price asociated
    numQty.rawValue = 1.00 ; // Default Value

    Once select the item desired, the price is correct, but the Text of dropdown change to anther record

    Thanks in advance for any help.

  65. Meng on August 15th, 2007

    Hi Stefan,

    Firstly I would like to thank you for the great blog which provides comprehensive Designer tutorials.
    I have tried your example “Connecting a Form to a Database” with MS SQL Server and it worked. But I am having problem with connecting MySQL database as data connection.

    Here are the details.

    MySQL Server: MySQL 5.0
    ODBC Driver: MySQL ODBC 3.51 Driver

    An error always occurs when I try to test connection at “Data Link Properties” dialog.

    Here goes the message: “Test connection failed because of an error in initializing provider. Catastrophic failure”.

    And I wonder what have I done to make such an “catastrophic” failure.

    Thanks in advance.

  66. Stefan Cameron on August 22nd, 2007

    Rodolfo Hernandez,

    That’s strange. When you say the text in the drop down list changes to “the 2nd register of db”, do you mean that the selection in the drop down list changes to the second item?

    What script language are you using in the Change event? When I look at the script you posted, it looks like a mix of FormCalc (because of the “$”) and JavaScript (because of the semi-colons “;”). If your Change event script language is JavaScript, you should first try changing the “$” to “this” and add a semi-colon at the end of “xfa.event.newText”.

  67. Stefan Cameron on August 26th, 2007

    Meng,

    It’s difficult to tell and since I’m not a MySQL expert, I’m afraid I won’t be able to offer much help.

    One thing does come to mind, however, which is that the format of user passwords in MySQL changed at some point in a later version 4 release or in version 5. Since the ODBC driver is quite old compared to the version of MySQL that you’re using, it’s possible the ODBC driver can only authenticate against passwords which are in the “old” MySQL password format as opposed to the new one. For example, you may also have this problem when using PHP with MySQL if your MySQL PHP module is old enough.

    To set a password for a user in the “old” format, you can use the following MySQL command:

    set password for '[username]'@'[host]' = old_password('[new password]');

    where you would replace each value in square brackets (including the brackets) with pertinent information.

  68. cja103392 on August 30th, 2007

    I have hunted high and low, but as a newbie at scripting LCD forms I’m stumped — I want to do something very similar to this, but instead of using a dropdown menu, I would like the user to be able to enter an ID number into a text field, click a button labeled “get employee info,” and have a handful of bound fields populate from my database after the onclick action with the employee information tied to that ID number.

    I don’t feel like this should be stumping me…but it is!

    Thank you!

    Chris

  69. Stefan Cameron on September 13th, 2007

    cja103392,

    You should be able to get this to work simply by taking the script that’s in the sample form’s drop down list (in the Change event) and move it into a button’s Click event. You’ll then need to use the value entered into the text field within the button’s Click event (as the ID number to filter on) and you can do this by using the text field’s “rawValue” property:

    TextField1.rawValue
  70. Meng on October 1st, 2007

    Hi Stefan,

    I have forms with ODBC connection and would like to enable modification to the database eg. addition or removal of data records.
    With your “Database” script object which uses the function ExecuteSQL(), I am able to do different kind of SQL operations including SELECT, UPDATA, DELETE and so on.
    My question is that how would it be to implement ROLLBACK & COMMIT in the form?

  71. Michael on October 4th, 2007

    Hi Stefan,
    I have been following this intently with hopes of making a transmittal form that users can select the last name from a dropdown box and it will populate the respective info (phone numbers, address, etc) on a transmittal form

    I thought about making the lastname the primary key but this isn’t possible since more than one person can have the same last name.

    using the purchase.pdf as an example… is there a way to make the dropdown box be any other field except for the primary key field? I ask this because no one will remember which # number any given contact is.
    I’ve made the odbc connection (using geniusconnect) for outlook/excange.
    I haven’t been able to get the “Exchange OLE DB PROVIDER to work” from ms access 2003
    I’m not sure what is the best connection method.

    I’m new to livecycle and designing forms and javascript but i have done a lot of vba.
    thank you and i really appreciate this site.

  72. Stefan Cameron on October 11th, 2007

    Meng,

    I’m not quite certain if this would work. You would have to try to see if the ODBC driver you’re using supports the execution of the ROLLBACK and COMMIT actions. In the end, it’s just SQL so you would include it in the data connection’s SQL query. SQL statements are separated by semi-colons (;) so there’s no reason why you couldn’t include multiple statements in the data connection’s SQL query. For instance, you should be able to include multiple INSERT statements in the SQL query to execute multiple insertions rather than opening the data connection for a single INSERT statement on every repetition of a loop in order to insert multiple records into a database.

  73. Stefan Cameron on October 11th, 2007

    Michael,

    I’m not familiar with the “purchase.pdf” example but it sounds like it has a drop down list that’s populated from a database which you use to select a record ID and then pertaining information is loaded into other fields.

    In your case, you’re correct: You likely cannot use the lastName field as the primary key as there will undoubtedly be duplicates. I suggest you use the lastName field as the item text and the contact number (presuming this is a unique value) as the item value in the drop down list.

    When you add items to the drop down list, you can use the addItem method which takes the item text as its first parameter and the item value (hidden in the list) as the second parameter. The second parameter is optional and if not specified, the item value becomes the same as the item text. See the script in the CategoryList drop down list in this post’s sample file for an example of populating a drop down list with items that have text and value data and then extracting the value from the selected item later on using the drop down list’s boundItem method.

  74. YY Julie on April 11th, 2008

    Hi Stefan,

    I’m newbee in designer, javascript and formcalc. i’m using designer 8.

    There is a form with 2 drop-down list (ddl). 1st ddl populates company’s department, according to user selection, staff names will be populated in 2nd ddl based on 1st ddl selection.

    I found similar questions posted in Adobe Forum but no answer.

    I followed your example, i can populate departments in 1st ddl, but i don’t know how to populate staff names in 2nd ddl.

    —– form1.#subform[0].DropDownList1::change: – (FormCalc, client) ——————————–

    var sDept = $.boundItem(xfa.event.newText)

    var oDC = Ref(xfa.sourceSet.cnStaff.clone(1))

    oDC.#command.query.commandType = “text”

    oDC.#command.query.select =
    concat(“select StaffNo, Name from vwActiveStaff where Dept = ‘”, sDept, “‘ order by Name”)

    oDC.open()
    :
    :
    —————————————————
    i found difficulties…..

    Thanks in advance.

    YY Julie

  75. Mega on April 11th, 2008

    Hi Stefan,

    I’m pretty new to this sort of form development.
    I am having an issue whereby I’m developing a form in liveCycle designer 8.0. I’ve set up an ODBC connection…to which I have bound fields from that database on the form. The direct connection works and it always returns the first record in the table I’m connecting to.
    I want to look up a particular record in a form by entering an ID in a text box and have it search for the record…then repopulate the bound fields with the data from that record…
    No luck so far.
    I’m currently using this code in an effort to achieve that….but it doesn’t work…The form just doesn’t do anything when I click the button.

    —– form1.#subform[0].Button1::click: – (JavaScript, client) ————————————-
    if (SelectField.rawvalue > 0) then
    var pRO = ref(xfa.sourceSet.DataConnection.clone(1))

    PRO.#command.query.commandType = “text”
    PRO.#command.query.select.nodes.item(0).value = Concat(”Select ID, Title, AGENCY, PROGRAM from PROPOSALS where ID = “,Ltrim(rtrim(SelectField.RawValue)),””)
    //Reopen the Dataconnection
    PRO.open()
    //xfa.sourceSet.DataConnection.open()
    endif

    Please pass on your wisdom.
    Thanks

  76. YY Julie on April 14th, 2008

    Hi Stefan,

    i found solution to add database items to list box, however, the isEOF() cannot be detected in while loop, it hangs.
    i’ve used $host.messageBox($record.cnStaff.Name) to debug, it was “trapped” at last record. what do i missed?

    —– form1.#subform[0].DropDownList_Dept::change: – (FormCalc, client) —————————-

    DropDownList2.clearItems();

    var sDept = $.boundItem(xfa.event.newText)

    var oDC = Ref(xfa.sourceSet.cnStaff.clone(1))

    oDC.#command.query.commandType = “text”

    oDC.#command.query.select =
    concat(“select StaffNo, Name from vwActiveStaff where Dept = ‘”, sDept, “‘ order by Name”)

    oDC.open()
    oDC.first()

    while (not oDC.isEOF()) do
    DropDownList2.addItem($record.cnStaff.Name, $record.cnStaff.StaffNo)
    oDC.next()
    endwhile
    oDC.close()

  77. Stefan Cameron on April 20th, 2008

    YY Julie,

    This is a tricky one. The endless loop problem you’re experiencing is likely due to the fact that the action taken by the “cnStaff” data connection when it gets to the last record is to loop back to the first record which means that the isEOF property is never true. What you need to do is specify that the action to be taken by the data connection when the last record is reached is to stay on the last record. Also, you’ll want to specify that when the first record is reached, the record “pointer” should stay on the first record (and not loop back to the last record).

    This can be done either by setting the “BOF Action” and “EOF Action” properties to “Stay BOF” and “Stay EOF” in the “ADO Properties dialog when configuring the data connection or it can be done via script as follows (shown in FormCalc here since that’s the scripting language you’re using):

    oDC.#command.query.recordSet.setAttribute("stayBOF", "bofAction")
    oDC.#command.query.recordSet.setAttribute("stayEOF", "eofAction")
  78. Stefan Cameron on April 20th, 2008

    Mega,

    I think you’re getting a few syntax errors. If you’re using Designer 8.1, there’s a new Syntax Checker tool available from the toolbar in the Script Editor palette.

    Based on the script you quoted, you’ve got the script language set to “JavaScript” yet your syntax is much more like FormCalc than it is JavaScript. You should set the language to “FormCalc” to start off. Also, here’s a few pointers related to your script:

    • In FormCalc, you don’t need to specify the “rawValue” property. To access the value of the “SelectField” field, just use “SelectField”.
    • “PRO.#command.query.select.nodes.item(0).value” should simply be “PRO.#command.query.select”.
    • The “Rtrim” function name in the concat statement is incorrectly spelled.
    • You should fix the name of your variable to be “PRO”, not “pRO”, since you’re accessing it as “PRO” in the rest of the script. FormCalc variable names are case-sensitive so “pRO” is not the same as “PRO”.
    • After calling “PRO.open()”, you should call “PRO.first()” to make sure the first record is selected.

    Fixing those issues should help you get your script working correctly.

  79. YY Julie on April 20th, 2008

    Thanks a lot Stefan :) .

  80. Mega on April 22nd, 2008

    Thanks alot Stefan…. I really needed those tips…my script now works flawlessly.

  81. Mega on May 6th, 2008

    Stefan,

    In the binding properties of say a text box. Is there anyway to put SQL like expressions in there that would enable you to pull specific data to the form. I’m basically trying to do a value comparison for a particular field, and populate a box with the data corresponding to the row of the value specified. I’m getting clustered with too many data connections and I’m guessing it’s not really healthy to reference so many data connections for one form.

    Thanks

  82. Nolan Smith on May 8th, 2008

    HELP!!!

    I am using this code this link and I can get it working is says column name undefined.

    Please Help!!

    Nolan

  83. Nolan Smith on May 8th, 2008

    Quoted from Kristin

    I tried the code but nothing works and what I have bolded doesn’t make sence to me since it should break the code.

    After I added this to my XML Source:

    The following lines of code worked for parsing the URL Request and selecting the correct record in the database:

    var sURL = event.target.URL; // URL used to access this PDF form
    var nRequestStart = sURL.indexOf(”?”);

    if (nRequestStart > 0) // must be larger than zero or else this is a weird URL!
    {
    var sRequest = sURL.substr(nRequestStart + 1);
    var aRequests = sRequest.split(”&”); // 1 request per array element

    for (var i = 0; i

    The following lines of code worked for parsing the URL Request and selecting the correct record in the database:

    var sURL = event.target.URL; // URL used to access this PDF form
    var nRequestStart = sURL.indexOf(”?”);

    if (nRequestStart > 0) // must be larger than zero or else this is a weird URL!
    {
    var sRequest = sURL.substr(nRequestStart + 1);
    var aRequests = sRequest.split(”&”); // 1 request per array element

    for (var i = 0; i < aRequests.length; i++)
    {
    var aNameValue = aRequests[i].split(”=”); // name=value
    var sName = aNameValue[0];
    var sValue = “”;

    try
    {
    // attempt to decode the various URI encodings (regular and component)
    sValue = decodeURI(decodeURIComponent(aNameValue[1]));
    }
    catch (e)
    {
    // invalid URI encoding found — skip
    continue;
    }

    if (sName == “JobAppID”)
    {

    xfa.sourceSet.DataConnection.resolveNode(”#command”).query.commandType = ‘text’;

    xfa.sourceSet.DataConnection.resolveNode(”#command”).query.select.value = ‘SELECT JobAppDate, JobAppLName, JobAppFName, JobAppMName, JobAppAddress, JobAppCity, JobAppState, JobAppZip, JobAppHPhone, JobAppBPhone, JobAppSSN, JobAppEighteen, JobAppWkgPapers, JobAppLegal, JobAppFelony, JobAppFelonyDesc, JobAppNameDesc FROM JobApplicants WHERE JobAppID = ‘ + sValue;

    xfa.sourceSet.DataConnection.open();

    }
    }
    }

    Just an FYI for anyone else that might be struggling. This code works for me.

  84. Nolan Smith on May 8th, 2008

    For got to mention where does each piece of this code go?

    Thanks Nolan

  85. Stefan Cameron on May 14th, 2008

    Mega,

    No, you can’t set an SQL-like binding expression for a field. Typically, a field’s binding expression references data from a specific data connection. A form can certainly have many data connections but if you feel you have too many, you could look into defining views in your database and then connecting to the views rather than the tables. The views could provide consolidated information to the form rather than having multiple data connections on the form and having to manipulate/consolidate the data there.

  86. Stefan Cameron on May 22nd, 2008

    Nolan Smith,

    I’m not sure I follow what you’re getting at. Does your code work now? I see that you’re not cloning the data connection which could be causing you some grief (some security error messages).

  87. Bong Guinto on June 3rd, 2008

    Hi Stefan,

    I’m following your discussion regarding ‘Selecting Specific Database Records’ and I would like to ask if you can help me provide solution on how I can print a PDF file created in Adobe Designer 7 using ABAP/4 programming language.

    I’m using the same SQL QUERY SELECT statement wherein I will pass a value using WHERE condition.

    Kindly help me where I can get some references.

    Thank you!

  88. Stefan Cameron on June 8th, 2008

    Bong Guinto,

    I’m afraid I can’t help you on this one. I don’t know anything about ABAP/4. You programmatically cause a PDF, created in Adobe Form Designer, to be printed from Acrobat by using a print button from the Object Library palette.

  89. Newbie on June 16th, 2008

    Using Formcalc:
    How can I determine if a resulting recordset returned from a SQL query is empty?

  90. Stefan Cameron on June 21st, 2008

    Newbie,

    You could try using the following FormCalc script after the data connection executing the query has been opened (ie. after the query has been executed):

    if (xfa.dataWindow.recordsBefore == 0 && xfa.dataWindow.recordsAfter == 0) then
        // empty recordset
    else
        // there's at least one record
    endif
  91. Javier on August 1st, 2008

    Hello, it is my first post, sorry for the english, i am from argentina.
    I have a form with an xml connection, this have some combobox filled dynamically. This works ok, but the problem is on submit button. This action submit all data which are inside my xml file of the data connection. Do you understand me? The fields filled and the items selected of the combobox are not in the submit file, only the data of the xml connection file.
    For expample, i have this xml connection file :

    This is the file that i am using to fill two combobox, one with Tarjetas and when it is selected the other fill with Establecimientos.
    When boths combobox are selected and i press submit button(send as xml) the data that is sending is a file with this inside:

    I need to send the data that i selected on the form.
    Please help!.
    Javier

  92. Stefan Cameron on August 8th, 2008

    Javier,

    It looks like there was a problem when you tried to include the contents of your XML files since I don’t see their contents in your post.

    Nonetheless, I would suggest you make sure the fields whose values you want submitted are bound to a node in your data connection. You can check/set this using the “Object palette > Binding tab > Default Binding property”.

  93. Javier on August 11th, 2008

    OK, i have a question for you:
    If i use a connection to an xml file and i have some field filled with data of that xml file, when i submit the form, this fields would be submitted?

    I resolve my problem putting the xml content into a string(dataToLoad) and then use xfa.datasets.data.loadXML(dataToLoad,0,1);.
    Ex :
    var dataToLoad = “This is the new string”;
    xfa.datasets.data.loadXML(dataToLoad,0,1);

    Here i haven’t any field bound to any connection, and i fill the comboBox like this :
    for (var nItemCount = 0; nItemCount < xfa.datasets.data.nodes.item(0).Informacion.nodes.item(0).Articulos.nodes.length; nItemCount++)
    {
    if(xfa.datasets.data.nodes.item(0).Informacion.nodes.item(0).Articulos.nodes.item(nItemCount).idarticulo.value == idArticulo)
    {
    IdArticulo.addItem(xfa.datasets.data.nodes.item(0).Informacion.nodes.item(0).Articulos.nodes.item(nItemCount).combinado.value, xfa.datasets.data.nodes.item(0).Informacion.nodes.item(0).Articulos.nodes.item(nItemCount).idarticulo.value);
    }
    }

    But i realy need load the fields with a xml out of the code, not harcoding like the Ex.
    Can I load the dataset with importData?
    Can i attach some file here or send you the files to show you the problem?
    Again, sorry for my english.
    Javier

  94. Javier on August 12th, 2008

    I probed an exaplme (Purchase Order (Designer 8.0\ES\Samples\Purchase Order\)) it comes with livecylce designer 8.0. It’s use an xlm connection to populate the form, and when you submit the form the same xml of the connection is submited.
    For example, i add a dropdownlist and bound it to a list of items. When i submit, this field doesn’t appears on the xml submit file.
    I repeat, can i attach a file or send you one?
    Javier.

  95. Stefan Cameron on August 13th, 2008

    Javier,

    It sounds to me like you’re using the word “bind” to describe adding items to the drop down list using script and values from the XML data however what you need to do is set the “Object palette > Binding tab > Default Binding property” of that drop down list. Otherwise, its value will not be submitted in the XML data.

    Note that if you have created a schema data connection, the schema is how the data will be submitted. If you explicitly bind a field to a node that isn’t in the schema, that field’s value will not be submitted (even though it’s bound) because the XML data submitted must adhere to the schema.

  96. Lane on August 21st, 2008

    Stefan

    Just wanted to say I’ve learned more on your blog than numerous posts on other Adobe forums.

    I had an issue connecting a drop-down and populating it from an Access database. Just one hour here solved a problem I’ve been asking for help on for over a week.

    Thanks for all the good info…..

  97. Parwez on September 11th, 2008

    Hi Stephan,
    First of all I would like to thank you for all the supports you have provided in Adobe LiveCycle Designer through your site. I searched the internet for tutorials in adobe forms and your site is the best resource centre I came through.
    I am a newbie in Adobe forms and currently using LiveCycle Designer 8.0 to build my form. I got through the tutorial on selecting a specific database record using a dropdown menu. http://forms.stefcameron.com/2006/09/29/selecting-specific-database-records/ and had been able to run the sample form successfully.
    I tried to do the same, but here using a text field instead of a dropdown menu. The user will have to enter an ID in the text field and the related records will be displayed in the form itself. I am using a MySQL ODBC connection for data connection.

    I tried it using several ways, but without success. Can you please help me on how to solve the problem?
    Regards,
    Parwez

  98. Stefan Cameron on September 12th, 2008

    Parwez,

    If you’re problem is how to know when the user has entered a value in the text field, just put the script in the field’s Exit event (just like it is in the drop down list’s Change event) and get the new value of the text field using the “rawValue” property.

  99. Thomas on October 7th, 2008

    I have an Acrobat solution which use a list box object to select a social security number from the list then populate the entire form with data from a SQl Server 2005 database via ODBC. It works without a hitch.

    However, there is an obvious security problem with personal information (SSN) being seen via the list box.

    I would like to allow the users to search from the SSN field. For example, type the SSN number directly in the field and press the refresh button or press the enter key to return only records relating to the one SSN.

    As it stands now, all SSNs are listed from the list box and all records can seen via a next button which moves to the next record. I can not hide or disable the next button because more than one record relates to a SSN. I want to allow viewing for one SSN.

    Thanks

  100. Thomas on October 8th, 2008

    Hi Stefan,

    I followed your advise in post 98. It is the perfect solution for the issue I was facing.

  101. Don Roessler on October 13th, 2008

    Hi Stefan,

    I have designed a form with an ODBC connection to an Access database where I can select a subset of database items through the DataConnection SELECT statement and populate three text boxes with data from linked columns in the database. Then using the buttons you have described, Previous, Next, Last, First etc., the user can scroll through the database items. However, ideally I would like to select a subset of data from the original subset and have this subset appear in a Data Drop Down list. The user would then pick an item in this Drop Down List and the three text boxes would be populated with the bound data. I realize that I could make numerous New Data Connections with more selective SELECT statements, but is there a different way, say using an If Then statement in the Specify Item Values on the Binding tab or the List Items on the Field tab of the Drop Down List?

    Thanks

  102. Don Roessler on October 16th, 2008

    I think I have this figured out by adding two database connections. However, I am running into an error trying to write the select query. The variable “sElevationId” is a numeric text. In other words the database column is a text column but the text is numeric with 12 characters in a 20 character field with the 8 spaces at the beginning of the text. I get various errors when trying a variety of syntax. In the statement below, I get the error from Access that I have too few parameters, expected 1. I am sure that all of the column names are accurate.

    var sElevationName = xfa.event.newText
    var sElevationId = $.boundItem(sElevationName)
    Options.OptionName = sElevationId

    var oDataConn2 = Ref(xfa.sourceSet.DataConnection3.clone(1))

    oDataConn2.#command.query.commandType = “text”

    oDataConn2.#command.query.select =
    concat(“SELECT OPTION_DESC, OPTION_NAME, SALESOPTIONS.SALESOPTIONS_ID, CURR_SELLING, SALESOPTIONSAREA.SALESOPTIONS_ID FROM SALESOPTIONS INNER JOIN SALESOPTIONSAREA ON SALESOPTIONS.SALESOPTIONS_ID = SALESOPTIONSAREA.SALESOPTIONS_ID WHERE OPTION_NAME = “, “sElevationId”, ” ORDER BY OPTION_NAME;”)

    oDataConn2.open()
    oDataConn2.first()

  103. Stefan Cameron on October 19th, 2008

    Don Roessler,

    I think the problem is in your concat statement where you’re inserting the value of sElevationId. What you have is “sElevationId” with the quotes. This is actually literally inserting sElevationId as the value to compare to OPTION_NAME rather than inserting the value of the sElevationId. Since OPTION_NAME appears to be a text column, what you need is to wrap the value of the sElevationId variable in single quotes:

    concat("... WHERE OPTION_NAME = '", sElevationId, "' ORDER BY...");

    Notice the single quotes at the end of the first and the start of the third parameter.

  104. Don Roessler on October 21st, 2008

    Thanks very much. That was the problem. Could you help some more please? I have now added script based off of Ann Meekers blog, http://forms.stefcameron.com/2008/03/07/livecycle-designer-811-sp2-posted, adding three drop down lists to my form. Each drop down list narrows the database selection until I have only one item to be selected. Is it possible to have the final selection appear in a text box or drop down list without using the change event to select the single item? In other words, in the Options.SALESOPTIONS_ID::change drop down list, only one SALESOPTIONS_ID item exists in the database with a corresponding CURR_SELLING item. However, this does not appear in the drop down list until the button is clicked. Can the CURR_SELLING item appear automatically in a text box or in a list where the item does not need to be selected?

    Thanks,
    Don Roessler

    —– form1.#subform[0].Options.DataDropDownList::change: – (FormCalc, client) ———————

    var sCategoryName = xfa.event.newText
    var sCategoryId = $.boundItem(sCategoryName)

    var oDataConn = Ref(xfa.sourceSet.DataConnection3.clone(1))

    oDataConn.#command.query.commandType = “text”

    oDataConn.#command.query.select =
    concat(“SELECT OPTION_DESC, OPTION_NAME, SALESOPTIONS.SALESOPTIONS_ID, SALESPLANS_ID, CURR_SELLING, SALESOPTIONSAREA.SALESOPTIONS_ID FROM SALESOPTIONS INNER JOIN SALESOPTIONSAREA ON SALESOPTIONS.SALESOPTIONS_ID = SALESOPTIONSAREA.SALESOPTIONS_ID WHERE SALESPLANS_ID = “, sCategoryId, ” ORDER BY OPTION_NAME;”)

    oDataConn.open()
    oDataConn.first()

    Options.Model = sCategoryId

    var sBOFBackup = oDataConn.#command.query.recordSet.getAttribute(“bofAction”)
    var sEOFBackup = oDataConn.#command.query.recordSet.getAttribute(“eofAction”)

    oDataConn.#command.query.recordSet.setAttribute(“stayBOF”, “bofAction”)
    oDataConn.#command.query.recordSet.setAttribute(“stayEOF”, “eofAction”)

    Options.OptionDesc.clearItems()
    while(not oDataConn.isEOF()) do
    Options.OptionDesc.addItem($record.DataConnection3.OPTION_DESC,$record.DataConnection3.OPTION_NAME)

    oDataConn.next()
    endwhile
    oDataConn.close()

    —– form1.#subform[0].Options.OptionDesc::change: – (FormCalc, client) —————————

    var sElevationName = xfa.event.newText
    var sElevationId = $.boundItem(sElevationName)

    var oDataConn = Ref(xfa.sourceSet.DataConnection3.clone(1))

    oDataConn.#command.query.commandType = “text”

    oDataConn.#command.query.select =
    concat(“SELECT OPTION_DESC, OPTION_NAME, SALESOPTIONS.SALESOPTIONS_ID, SALESPLANS_ID, CURR_SELLING, SALESOPTIONSAREA.SALESOPTIONS_ID FROM SALESOPTIONS INNER JOIN SALESOPTIONSAREA ON SALESOPTIONS.SALESOPTIONS_ID = SALESOPTIONSAREA.SALESOPTIONS_ID WHERE OPTION_NAME = ‘”, sElevationId, “‘ ORDER BY OPTION_NAME;”)

    oDataConn.open()
    oDataConn.first()

    Options.OptionName = sElevationId

    var sBOFBackup = oDataConn.#command.query.recordSet.getAttribute(“bofAction”)
    var sEOFBackup = oDataConn.#command.query.recordSet.getAttribute(“eofAction”)

    oDataConn.#command.query.recordSet.setAttribute(“stayBOF”, “bofAction”)
    oDataConn.#command.query.recordSet.setAttribute(“stayEOF”, “eofAction”)

    Options.SALESOPTIONS_ID.clearItems()
    while(not oDataConn.isEOF()) do
    Options.SALESOPTIONS_ID.addItem($record.DataConnection3.SALESOPTIONS_ID[1],$record.DataConnection3.CURR_SELLING)

    oDataConn.next()
    endwhile
    oDataConn.close()

    —– form1.#subform[0].Options.SALESOPTIONS_ID::change: – (FormCalc, client) ———————-

    var sSalesOptionId = xfa.event.newText
    var sCurrSell = $.boundItem(sSalesOptionId)

    var oDataConn = Ref(xfa.sourceSet.DataConnection3.clone(1))

    oDataConn.#command.query.commandType = “text”
    oDataConn.#command.query.select =
    concat(“SELECT OPTION_DESC, OPTION_NAME, SALESOPTIONS.SALESOPTIONS_ID, SALESPLANS_ID, CURR_SELLING, SALESOPTIONSAREA.SALESOPTIONS_ID FROM SALESOPTIONS INNER JOIN SALESOPTIONSAREA ON SALESOPTIONS.SALESOPTIONS_ID = SALESOPTIONSAREA.SALESOPTIONS_ID WHERE SALESOPTIONS.SALESOPTIONS_ID = “, sSalesOptionId, ” ORDER BY OPTION_NAME;”)
    oDataConn.open()
    oDataConn.first()

    Options.CurrSelling = sCurrSell

  105. Don Roessler on October 21st, 2008

    I think I solved it! I merely removed the script on the Third Drop Down List (Options.SALESOPTIONS_ID::change) and removed the following script on the second Drop Down List (Options.OptionDesc::change):

    var sBOFBackup = oDataConn.#command.query.recordSet.getAttribute(”bofAction”)
    var sEOFBackup = oDataConn.#command.query.recordSet.getAttribute(”eofAction”)

    oDataConn.#command.query.recordSet.setAttribute(”stayBOF”, “bofAction”)
    oDataConn.#command.query.recordSet.setAttribute(”stayEOF”, “eofAction”)

    Options.SALESOPTIONS_ID.clearItems()
    while(not oDataConn.isEOF()) do
    Options.SALESOPTIONS_ID.addItem($record.DataConnection3.SALESOPTIONS_ID[1],$record.DataConnection3.CURR_SELLING)

    oDataConn.next()
    endwhile
    oDataConn.close()

    Then I added a text box from my data view corresponding to the CURR_SELLING database column which automatically added the binding tab to the item.

    Thanks for your assistance and the example with which I started all of this.
    Don Roessler

  106. Don Roessler on October 27th, 2008

    I have rewritten my script so that the initial data drop-down list filters the database items for all subsequent drop-down lists. The items in this initial data drop-down list is populated by Specifying item values in the Binding tab. Then using this subset of database items, the subsequent drop-down lists filter the database items further through use of the Enter and Change events. Then two text boxes are populated through scripting that are linked to the database item chosen in the drop-down list. The script is shown below. Is it possible to have the user view the items in the drop-down list and the corresponding values of one of the text boxes at the same time? I would like for the user to be able to view the items in the drop-down list and the corresponding costs for that item which is displayed in one of the text boxes before the user decides which drop-down item to select.

    An alternative method is to display both values in the drop-down list, the item and the cost by concatenating the two database lists, however, the format of the display is undesirable. I would like to show the Item at the left side of the drop-down list and the cost at the right side border.

    Thanks,
    Don Roessler

    —– form1.#subform[0].Options.DataDropDownList::change: – (FormCalc, client) ———————

    // Get the id for the SalesPlan the user just selected (which is the id of the SalesPlan to filter on):
    var sSalesPlanName = xfa.event.newText
    var sSalesPlanId = $.boundItem(sSalesPlanName)

    // get a *reference* to the data connection object (you’ll get an error if you try to get it by value)
    var oDataConn = Ref(xfa.sourceSet.DataConnection3.clone(1))

    // First, we need to set its query command type to “text” so that we can specify an SQL statement for it.
    // Otherwise, we would have to specify a table or stored procedure.
    // Set the sourceSet/DataConnection/command/query@commandType attribute to “text”.
    oDataConn.#command.query.commandType = “text”

    // Next, we need to specify the SQL statement for the data connection. This is done by setting the contents of
    // the query/select node.
    oDataConn.#command.query.select =
    concat(“SELECT OPTION_DESC, OPTION_NAME, SALESOPTIONS.SALESOPTIONS_ID, SALESPLANS_ID, CURR_SELLING, SALESOPTIONSAREA.SALESOPTIONS_ID
    FROM SALESOPTIONS INNER JOIN SALESOPTIONSAREA ON SALESOPTIONS.SALESOPTIONS_ID = SALESOPTIONSAREA.SALESOPTIONS_ID
    WHERE SALESPLANS_ID = “, sSalesPlanId, ” ORDER BY OPTION_NAME;”)

    // Set the SalesPlans ID in the Options subform.
    Options.Model = sSalesPlanId

    —– form1.#subform[0].Options.OptionDesc::enter: – (FormCalc, client) —————————-

    var sOption = “___10____”
    var oDataConn = Ref(xfa.sourceSet.DataConnection3.clone(1))

    oDataConn.#command.query.commandType = “text”
    oDataConn.#command.query.select =
    concat(“SELECT OPTION_DESC, OPTION_NAME, SALESOPTIONS.SALESOPTIONS_ID, SALESPLANS_ID, CURR_SELLING, SALESOPTIONSAREA.SALESOPTIONS_ID
    FROM SALESOPTIONS INNER JOIN SALESOPTIONSAREA ON SALESOPTIONS.SALESOPTIONS_ID = SALESOPTIONSAREA.SALESOPTIONS_ID
    WHERE SALESPLANS_ID = “, sSalesPlanId, ” AND OPTION_NAME LIKE ‘%”, sOption, “‘ ORDER BY OPTION_NAME;”)

    oDataConn.open()
    oDataConn.first()

    var sBOFBackup = oDataConn.#command.query.recordSet.getAttribute(“bofAction”)
    var sEOFBackup = oDataConn.#command.query.recordSet.getAttribute(“eofAction”)

    oDataConn.#command.query.recordSet.setAttribute(“stayBOF”, “bofAction”)
    oDataConn.#command.query.recordSet.setAttribute(“stayEOF”, “eofAction”)

    Options.OptionDesc.clearItems()
    Options.CurrSellElevation.clearItems()

    while(not oDataConn.isEOF()) do
    Options.OptionDesc.addItem($record.DataConnection3.OPTION_DESC,$record.DataConnection3.OPTION_NAME)

    oDataConn.next()
    endwhile
    oDataConn.close()

    —– form1.#subform[0].Options.OptionDesc::change: – (FormCalc, client) —————————

    var sOptionName = xfa.event.newText
    var sOptionId = $.boundItem(sOptionName)
    var oDataConn = Ref(xfa.sourceSet.DataConnection3.clone(1))

    oDataConn.#command.query.commandType = “text”

    oDataConn.#command.query.select =
    concat(“SELECT OPTION_DESC, OPTION_NAME, SALESOPTIONS.SALESOPTIONS_ID, SALESPLANS_ID, CURR_SELLING, SALESOPTIONSAREA.SALESOPTIONS_ID
    FROM SALESOPTIONS INNER JOIN SALESOPTIONSAREA ON SALESOPTIONS.SALESOPTIONS_ID = SALESOPTIONSAREA.SALESOPTIONS_ID
    WHERE OPTION_NAME = ‘”, sOptionId, “‘ ORDER BY OPTION_NAME;”)

    oDataConn.open()
    oDataConn.first()

    Options.NameElevation = sOptionId
    Options.CurrSellElevation = $record.DataConnection3.CURR_SELLING

  107. Stefan Cameron on October 30th, 2008

    Don Roessler,

    Looks like you’re making some good progress!

    If you were to display the cost associated with a selected item in a text box prior to the user deciding what to actually select, I think you would have to store the associated cost somehow (either set it as the item’s value data or, if you’re already using that for an ID of some sort, you could create an array in a script object and populate the array with the cost items) and use the list’s Change event to show the cost in the text field and the Exit event to actually do the selection against the database. You’ll need the Change event in order to display the cost without doing the rest of the work on the database.

  108. Don Roessler on October 30th, 2008

    Thanks Stefan,

    Looks like I will need to learn more. But that is good. I will post another note after more progress. Thanks very much for your advice.

  109. Thomas on October 31st, 2008

    Dear Stefan

    I am using a database connection snipet with the following code. All of the other buttons (First,Last,Update,Cancel) on the form execute properly with the exception of the AddNew button which is suppose to create a new record within a SQL database with an ODBC connection. Here is the error:

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

    —– Form1.InjuryNotice.dbAddNew[1]::click: – (FormCalc, client) ———————————-

    // Database control button >> add a new record.

    // Changing records implies updating the current record with any changes. Enabling this statement would ensure that no changes are committed
    // without the user explicitly using the Update button.
    //xfa.sourceSet.DataConnection.cancel()

    xfa.sourceSet.SupvrRprt.addNew()

    I can only add a new record after selecting an existing record from the database.

    Thomas

  110. Stefan Cameron on November 10th, 2008

    Thomas,

    You might need to use an INSERT statement then instead of using the Add New button without first selecting a record.

  111. Xancholy on December 12th, 2008

    Hi Stefan,

    Great stuff, thanks.

    I have a list of people in a drop-down list on my form taken from an access mdb table.

    Depending on which name I choose from this list….

    Can the pre-submit event be coded
    so that the entire pdf is attached
    and submit url is set to
    the email address of the person selected in the drop-down list ?

    ie: If John Smith is selected then submit url will be jsmith@email.com, etc, etc..

    Appreciate any help

  112. Stefan Cameron on December 12th, 2008

    Xancholy,

    You can do much more than just set the email address depending on a selection in a list: See my article on submitting by email.

    As for submitting the entire PDF, you can do that provided you’ve extended the PDF to enable advanced data export features in Reader using LC Reader Extensions (or if you’re submitting from Acrobat Standard or better). To submit the PDF instead of just the XML data, use a regular submit button, set its type to “submit” and then, on the “Object palette > Submit tab”, set the submit data type to “PDF”.

  113. Peter Ilias on January 16th, 2009

    This may be a silly question. I’ve installed the sample, database and odbc connection so it works fine in preview mode. How do I look at a data connections “xml source” properties as per the image at the beginning in this article. I have another project that uses an odbc connection and would like to view the xml source of the connection. Is this straight forward?

  114. Stefan Cameron on January 19th, 2009

    Peter Ilias,

    To view the XML Source, click on the “XML Source” tab next to the “Design View” or “Body Pages” tab. You can also use the “View menu > XML Source” command to show the XML Source. Then scroll almost to the bottom to since the <sourceSet> node.

  115. Milton Grant on February 12th, 2009

    Hi Stefan,

    You have a very informative site.

    I use your selecting specific database records examples to successfully retrieve data from an ODBC Database into xfa.sourceSet.

    I ‘m selecting specific database records for four employee fields using the formCalc language. Two of the fields has explicit binding to the database and are automatically populated with data when I click on submit button. The problem is retrieving a data record from the “xfa.sourceSet.Dataconnecton” for the other two fields. Here is an example of the code.

    var sEmployee_Number = Employee_Number.rawValue
    Employee_Number.nodes.

    var oDataConn = Ref(xfa.sourceSet.DataConnection2.clone(1))

    oDataConn.#command.query.commandType = “text”

    var SQLStatement = concat(“select * from Employee_Information where EMP_NUMBER = ‘”,sEmployee_Number,”‘;”)
    oDataConn.#command.query.select = SQLStatement

    oDataConn.open()
    oDataConn.first()

    //I would like to know how to assign values to the following Fields retrieved from the oDataConn object?

    Name.rawValue = $record.DataConnection2.Name_Full.value
    JoBTile.rawValue = oDataConn.JOB_TITLE.item

    Your advice would be appreciated.

    Milton

  116. Stefan Cameron on February 13th, 2009

    Milton,

    I don’t quite understand. Why can’t you bind the other two fields that aren’t working?

    And what are you meaning for the following code to do?

    Name.rawValue = $record.DataConnection2.Name_Full.value
    JoBTile.rawValue = oDataConn.JOB_TITLE.item
  117. JR on March 1st, 2009

    Hi. Can anyone assist with the following please:

    I have a 2 page form in Livecycle. When a user enters a value in a required field on page 2, a specific field on page 1 is filled with a color and a word. I tried an if statement to return the word I want and it works, but I can’t get the fill color to work with this statement. Example: if (form1.#subform[1].Platinum[6] >1) then “SELECTED” endif

    Can you mix both if statement and fill statement in the same formula > ?
    If someone could help, I would be very much grateful.
    James

  118. Bob on March 4th, 2009

    I have been reading through your post and I have a very simple and basic problem for the life of me can’t figure out.

    This is my connection string: Provider=SQLNCLI;Server=DBSERV\SQL2005;Database=SIVAPPLICATION; Trusted_Connection=yes;

    This is my select statement: SELECT EmpId, Name, Addr1, City, State, Zip, BirthDate, Department, DfltWrkloc, Phone, User5, StrtDate, PayType, Status
    FROM dbo.Employee AS Employee order by empid

    I have them EmpID field on the form as a drop down list, listing all the EmpID’s to choose from. The problem is I want the rest of the fields to populate according to the record row chosen with EmpID. So there for the SQL statement needs to adjust with “WHERE EmpID = ‘what ever I choose in the EmpID drop down”

    Thats where I am scratching my head.

  119. Stefan Cameron on March 5th, 2009

    JR,

    It sounds like you’re using the Calculate event on the field on page 1. That’s fine for setting its value to “SELECTED” when the field on page 2 gets a value greater than 1.

    To get the fill color to work, you just need a statement like:

    $.fillColor = "0,255,0"

    The statement above will fill the field with the color green. The fillColor property is an RGB value.

    The problem is you aren’t supposed to use the Calculate event for anything other than setting the field’s value. If you put the statement above in there, it may or may not work. To make it right, put your script on the Change event of the field on page 2 instead.

  120. Stefan Cameron on March 5th, 2009

    Bob,

    Have you looked at the sample form at all? You’ll need two separate data connections in order to achieve what you want: The first will query the database for a list of EmpID values while the second will execute the SQL query you create dynamically after the EmpID value has been selected. Just take the sample form and reconfigure the data connections to use your database and then modify the code in the drop down list’s Change event to use your query. Once you get that working, you can use the pieces in the form to create your own.

  121. Bob on March 9th, 2009

    Could you please give me link to the sample? I have two connections established. Not sure how to tie them together so that it would be as if I used ” Where EmpID = ‘xxxxx’ ” in the SQL statement.

    Thanks

  122. Stefan Cameron on March 10th, 2009

    Bob,

    It’s the sample in this article…

  123. Cesar Rodriguez on March 30th, 2009

    Hi Stefan…

    I’ve been trying to get this to work on my side. I have Livecycle 8.0.

    I have managed to get the value from the url. But the whole cloning of the dataconnection thing is not working for me.

    You specified some code on how to do it in FormCalc. I am a C# guy and can follow javascript, but I’m having a tough time trying to figure out why is my code not working.

    Thanks for all the valueable information here. Its a great resource.

    Thanks

  124. Stefan Cameron on April 3rd, 2009

    Cesar Rodriguez,

    Here’s the script in JavaScript in case it helps:

    var sCategoryName = xfa.event.newText;
    var sCategoryId = this.boundItem(sCategoryName);
    var oDC = xfa.sourceSet.MoviesInCat.clone(1);
    
    oDC.resolveNode("#command").query.commandType.value = "text";
    
    oDC.resolveNode("#command").query.select.value = "SELECT title, showTime FROM movies WHERE categoryId = " + sCategoryId + " ORDER BY title";
    
    oDC.open();
    oDC.first();
  125. Katherine on April 14th, 2009

    Hi Stefan,

    First off, I’m using Designer v7.0. I worked with your “Using URL Requests in PDF Forms” tutorial to pull two querystring variables (UserID and JobID) into my PDF document with success.

    Now I want to use the UserID variable to filter my first connection (AppConnection), and the JobID variable to filter my second connection (JobsConnection). I tried using your “Selecting Specific Database Records” tutorial to accomplish this without any success. Because I don’t want to use a drop-down to filter the results for AppConnection and JobsConnection, and instead want to use the querystring variables, I’m not having much success.

    Could you please post an easy way to accomplish this? I’d really appreciate your help. Thanks again for all of your great tutorials!

  126. Stefan Cameron on April 15th, 2009

    Katherine,

    I’m glad to hear you’ve found my posts useful!

    For your situation, I would recommend you simply follow the script that retrieves the UserID and JobID from the URL with 2 blocks of script like you’ll find in the CategoryList field’s Change event (from the sample I originally posted in this article). In the first block, “sCategoryId” would be the value of UserID and in the second, it would be the value of JobID.

  127. David Wolf on May 3rd, 2009

    Hi Stefan,

    I’m using a dropdown to choose from available Drug Names in a MS Access DB, and then populate the Drug ID, ATC Code, and ATC Code Text fields. I’ve hooked up and tested the ODBC but can’t get the dropdown to work at all. Can you look at this script and see if there’s anything wrong. Any other advice for a rookie would be extremely helpful.

    Thanks,
    David

    Select Preferred Name

    current_field_somExpression = this;

    if (typeof xfa.host.appType != “undefined”){if (this.rawValue == “netrm_empty_string”){this.rawValue = null};}
    else{if (this.rawValue == “netrm_empty_string”){this.rawValue = “”};}

    // dropdown lists should be configured so that the object list items are configured as follows:
    // The topmost text value = a text value. Do not set to a single space ” “. In HTML rendering, any leading spaces are trimmed off of the text string.
    // The topmost value item = “netrm_empty_string”
    // A specific default value other than “netrm_empty_string” may be set. If the field has no default value, the default value should be set to < None >. Do not set the default value to “netrm_empty_string”

    // if (typeof xfa.host.appType != “undefined”){if (this.rawValue == “netrm_empty_string”){this.rawValue = null};}
    // else{if (this.rawValue == “netrm_empty_string”){this.rawValue = “”};}

    /* This listbox object will populate two columns with data from a data connection.
    sDataConnectionName
    - name of the data connection to get the data from.
    - Note the data connection will appear in the Data View.
    sColHiddenValue
    - this is the hidden value column of the listbox.
    - Specify the table column name used for populating.
    sColDisplayText
    - this is the display text column of the listbox.
    - Specify the table column name used for populating.
    These variables must be assigned for this script to run correctly.
    */
    var sDataConnectionName = “DataConnection”;
    var sColDisplayText = “PrefferedName”;
    var sColDisplayValue = “DrugID”;
    var sColDisplayText = “ATCCode”;
    var sColDisplayText = “ATCCodeText”;
    // Search for sourceSet node which matchs the DataConnection name
    var nIndex = 0;
    while(xfa.sourceSet.nodes.item(nIndex).name != sDataConnectionName)
    {
    nIndex++;
    }
    var oDB = xfa.sourceSet.nodes.item(nIndex);
    oDB.open();
    oDB.first();
    // Search node with the class name “command”
    nIndex = 0;
    while(oDB.nodes.item(nIndex).className != “command”)
    {
    nIndex++;
    }
    // Need to set BOF and EOF to stay
    oDB.nodes.item(nIndex).query.recordSet.setAttribute(“stayBOF”, “bofAction”);
    oDB.nodes.item(nIndex).query.recordSet.setAttribute(“stayEOF”, “eofAction”);
    Adobe LiveCycle Designer 7.0
    Providing interactive database lookup from forms
    // Search for the record node with the matching Data Connection name
    nIndex = 0;
    while(xfa.record.nodes.item(nIndex).name != sDataConnectionName)
    {
    nIndex++;
    }
    var oRecord = xfa.record.nodes.item(nIndex);
    // Find the value node
    var oValueNode = null;
    var oTextNode = null;
    for(var nColIndex = 0; nColIndex < oRecord.nodes.length; nColIndex++)
    {
    if(oRecord.nodes.item(nColIndex).name == sColHiddenValue)
    {
    o
    ValueNode = oRecord.nodes.item(nColIndex);
    }
    else if(oRecord.nodes.item(nColIndex).name == sColDisplayText)
    {
    o
    TextNode = oRecord.nodes.item(nColIndex);
    }
    }
    while(!oDB.isEOF())
    {
    this.addItem(oValueNode.value, oValueNode.value);
    //IDList.addItem(oValueNode.value, oTextNode.value);
    oDB.next();
    }
    // Close connection
    oDB.close();

  128. Stefan Cameron on May 8th, 2009

    David Wolf,

    I see a couple of issues with your script:

    1. I don’t understand the beginning. What is this doing?

     Select Preferred Name
    
    current_field_somExpression = this;
    
    if (typeof xfa.host.appType != “undefined”){if (this.rawValue == “netrm_empty_string”){this.rawValue = null};}
    else{if (this.rawValue == “netrm_empty_string”){this.rawValue = “”};}

    2. You are declaring 3 variables with the same name:

    var sColDisplayText = “PrefferedName”;
    var sColDisplayText = “ATCCode”;
    var sColDisplayText = “ATCCodeText”;

    You can only have one variable named “sColDisplayText”. I’m guessing you’re trying to have a multiple-column display however listboxes and drop down lists only display a single value unless you concatenate values together into a single value. Each item may also have a hidden data value (“sColHiddenValue” is the name of the column that contains the hidden data value).

    3. In the “while(!oDB.isEOF())” loop, the items aren’t being added to the list properly:

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

    should be

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

    Once these errors are corrected, the list field should give you a list of drugs to choose from with the drug ID as the hidden data value. You would then follow the sample code I included in this post’s tutorial to script the list’s Change event in order to get the selected drug ID from the list and execute a query against the database to select the pertaining record. Bindings in the remaining fields to this secondary data connection (to the same database) would then be automatically populated with further information about the drug.

  129. Angela Warren on July 1st, 2009

    I just wanted to say that your site rocks! I have been able to find answers to everything and find those answer with keywords that a novice would use.

    Thanks again

  130. Hilary on July 7th, 2009

    Hi Stefan,

    This is the best site that I can always find solution for Acrobat Designer.

    I have tried your sample here, I encountered the problem that I cannot modify the data connection under dropdown list’s change event, so I solve it by cloning it first:

    var oDataConn = Ref(xfa.sourceSet.MoviesInCat.clone(1))

    when I click any category of the dropdown list, the movie data did show properly. But when I press one of the navigation controls, say dbNext button, with coding:

    “xfa.sourceSet.MoviesInCat.next()”

    record that is not under the selcted category may be retrieved, that I guess is based on the original selecting criteria on MoviesInCat:

    “SELECT title, showTime FROM movies ORDER BY title;”

    I wonder how I can refer to the variable oDataConn (defined in CategoryList’s change event) for dbNext control? It could not be simply coded “oDataConn.next()” under dbNext. Should I define a global variable in Designer for ? And if yes, how can I do that?

    Thanks in advance

  131. Stefan Cameron on July 13th, 2009

    Angela Warren,

    Great feedback. Thanks!

  132. Stefan Cameron on July 15th, 2009

    Hilary,

    Thanks for the complement!

    You are correct in your findings. My article on XFA 2.5 explains why you need to close the data connection prior to modifying it if your form is based on XFA 2.5 or later.

    What you need to do is create a script object (right-click on the root subform in the Hierarchy palette and choose “Insert Script Object” from the context menu), define a variable in it to hold the reference to the data connection (oDataConn) and, after cloning and modifying it, set the reference into the variable in the script object. Afterward, in the dbNext button, use the oDataConn variable from the script object, not “xfa.sourceSet.MoviesInCat”.

  133. Aditya on August 10th, 2009

    I am new to the world of connecting a PDF to a database and run queries.

    I am trying to retreive some data for validation before the form is submitted. The ODBC has been setup and so is the data connection to it.

    I have a form with 2 fields ID and DATA. The id field is the field of input and the data field is to be used to spit out the information from the database. This function should be triggered by the button’s click event. Stefan can you please help me achieve this functionality using JS.

    Aditya

  134. Stefan Cameron on August 18th, 2009

    Aditya,

    You basically need to set the button’s Click event to be the script to set the query on the data connection and execute it (see the script in the “Filtering Records Displayed by the movieData Subform” section of this tutorial). Once executed and the record pointer moved to the first record (calls to the open(), then the first() method on the data connection), you need to find the nodes in xfa.dataset.record that represent the columns you’re interested in and iterate through the records in the data connection, getting the values of each record node (as in the sample code in the “Making the script look for the right data” and “Generating MovieSF instances for each record” sections in my tutorial on displaying all records in a database).

  135. Sean Hepburn on August 26th, 2009

    Hi,

    I’ve followed this tutorial several times now, using my own access db, by modifying the the original movies form. No matter what I do I seem to end up with one of 2 errors.

    1. Modifying this value would violate this documents permissions settings.
    2. Too few parameters, expected 1 (or 2 or 3).

    The code is below, I’ve seen post #102 which explains the use of single quotes and I’ve had the WHERE = value show in a test field to make sure it’s correct. I’m trying to work up a simple contacts front end for customers details. I’m at my wits end with this…please help.

    var sCategoryName = xfa.event.newText
    var sCategoryId = $.boundItem(sCategoryName)
    var oDataConn = Ref(xfa.sourceSet.MoviesInCat)

    oDataConn.#command.query.commandType = “text”
    oDataConn.#command.query.select =
    concat(“SELECT Company, postcode, addy1, addy2, city FROM contacts WHERE Company = “,sCategoryId,”;”)
    oDataConn.open()
    oDataConn.first()

  136. Stefan Cameron on September 7th, 2009

    Sean Hepburn,

    Unfortunately, I can’t see anything in your code that would cause these errors. Are you perhaps trying to run your form in Reader rather than in Acrobat? Reader cannot access data from a database unless the PDF is extended to permit data import.

  137. Shaun on September 24th, 2009

    We need to use your solution to filter records that need be loaded into another drop down list box vs. a subform with text fields that will be iterated through. Can you point me in the right direction?

    Also, when we run the current tutorial we get an error on our last sourceset.open saying “we have too few parameters: Expected 1″, this is from the ODBC driver.

    thanks Shaun

  138. Stefan Cameron on September 27th, 2009

    Shaun,

    To get items into another drop down list, you would simply use script similar to the Initialization script used to populate the first drop down list in my sample — just use the second data connection rather than the first and make sure the second data connection’s BOF and EOF Action properties (set on the last panel in the Data Connection Wizard) are “stayBOF” and “stayEOF”. stayBOF and stayEOF will ensure that you can successfully loop through the records in the second data connection like this:

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

    As for the error on open(), what ODBC driver are you using?

  139. Jane on November 22nd, 2009

    Hi Stefan,

    Could you help me with my dilemma? I have a similar requirement but using different objects. I want to be able to display a user’s information based on an entry in a dialog box that displays via button click. This is my first attempt on creating livecycle forms so please bear with me. I followed the instructions on this article but I couldn’t figure out why it’s not working. I currently have a button that calls has the script you provided in your article in its click event. Because you used FormCalc, I decided to put the textbox on my form instead of displaying it in a dialog box as I intended. (Btw, does FormCalc have something similar to app.response()?) So, after the user enters something on the textbox and clicks on the button, another textbox will be populated with the user info. It doesn’t seem to work for me :(

  140. Stefan Cameron on December 3rd, 2009

    Jane,

    First things first: Are you using Acrobat Standard (or better) to test this or are you using Reader? Reader alone cannot communicate with a database.

    When you test your form, are there any errors in the JavaScript Console in Acrobat? (Press Ctrl+J while in “PDF Preview” to view the console.)

    Yes, FormCalc has an equivalent to app.response() which you can use in either FormCalc or JavaScript:

    xfa.host.response(question[, title[, defaultValue[, useMask (1/0)]);
  141. Michael K. on December 5th, 2009

    Stefan, how would I make this tutorial applicable for a form where I enter in the number of the record (e.g. 1508), and have it populate the values of the form based on the values associated with that record? I have successfully done the “Connecting a Form to a Database” tutorial.

  142. Michael K. on December 5th, 2009

    There isn’t a means of performing a query on a number value (1508) supplied to the field, that would replace the values of fields x y and z based on the row (1508) in the sql database?

    I suppose I am having trouble converting your tutorial because it relies on a drop box with options, where I want to have fields populate based on the record number. What is different, what stays the same?

  143. Shai on December 10th, 2009

    Hi Stefan,

    Simple question. Trying to tie PDF from created in LC ES8.2 to MS Access 2007. Connection tested fine, fields come over in LC, binding is automated, path is correct, everything is internal (local host). But when I open the form in Adobe Acrobat 9 pro, it says that connection could not be established due to permission. I set no restrictions on either end. Am I missing something trivial ?

    thanks

    Shai

  144. Stefan Cameron on December 14th, 2009

    Michael K.,

    If you have fields x, y and x bound to nodes within the second data connection (the one whose SQL statement you modify), then you would simply take the Change event script from my sample form’s drop down list Change event and put it in the Exit event of your text field (in a text field, the Change event occurs on every character whilst the Exit event occurs only one the field loses focus — the user moves away from it).

    In the Exit event script, you would assing “$.rawValue” to “sCategoryID” rather than “$.bountItem(sCategoryName)”. The rest should be the same.

  145. Stefan Cameron on December 14th, 2009

    Shai,

    Sounds like you might be running into security problems due to your code attempting to modify the data connection in a form that is using XFA 2.5 or later.

  146. Pat Melanson on December 15th, 2009

    Stefan, I’m getting the following error when opening a form I created (first form). It loads the correct data on the subform based on the value of TextField1(tested multiple records) but I still get the below error. I have set up a System DSN and testing the connection works. I’m thinking that the LiveCycle connection string is not correct. Can you give me an example a LiveCycle connection string? Or if you see that there is an issue with my code, please let me know. Thanks

    Script failed(language is formcalc; context is
    xfa[0].form[0].topmostSubform[0].#pageSet[0].State[0].StateSubform[0])
    script=

    var sProductId = xfa.form.topmostSubform.#pageSet[0].State.TextField1.rawValue

    var oDataConn = Ref(xfa.sourceSet.DataConnection.clone(1))

    oDataConn.#command.query.commandType = “text”

    oDataConn.#command.query.select =
    concat(“Select ProductID, LegalName, PresFName, PresLName, PresAddress, PresCity, PresState, PresZip, PresContact, County, City, FStreet, CStreet, Zip, PrintName, Title, SignDate From tblState WHERE ProductID = “, sProductId, “”)

    oDataConn.open()
    oDataConn.first()

    Error: open operation failed. [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ‘=’.

  147. Pat Melanson on December 16th, 2009

    Stephan, I got this working by changing the code to the below:
    topmostSubform.State.PageArea1.StateSubform::initialize – (FormCalc, client)

    $sourceSet.DataConnection.#command.query.commandType = “text”
    $sourceSet.DataConnection.#command.query.select.nodes.item(0).value = concat(“SELECT ProductID, LegalName, DueDate, PresFName, PresLName, PresAddress, PresCity, PresState, PresZip, FStreet, CStreet, Zip, PrintName, Title, SignDate From tblState WHERE ProductID = “, Ltrim(Rtrim(xfa.form.topmostSubform.State.PageArea1.TextField1.rawValue)) ,”")

    //Reopen the Dataconnection
    $sourceSet.DataConnection.open()

  148. Ds on June 30th, 2010

    Hi Stefan,

    I have difficulty in populating a drop down list box values taken from a database if there is another XML dataconnection to a XML Schema

    I have gone through your web sites, and figure out the way to connect to the database. Those postings are really useful.

    These are the problems i am facing in the XDP file;

    1. If there is another DATA Connection to an XML Schema,
    The Data Drop Down list do not populate the values from the Data base. If I remove the XML Data Connection, then the Data Drop Down list works fine, by showing all the records pulled from a table.

    2. The form may contain more than 10 DD List, is it necessary for me to have 10 Database open and close connections, as in the initialize script of the Custom Data Drop down list has that. I read the clone Database connection, but now sure how to apply that in the code.
    Also i am not able to use the normal DD list, and populate the database values, using its dynamic properties, nothing is loaded if i use the normal DD.

    Please note that, I have checked the Delayed Open, extended the form with rights,(Reader Extensions in the server), created a system DSN all that.

    But still not sure why if i an XML Dataconnection, the Database connection is not being pulled in and also why normal DD is not working with the dynamic properties set.

    Any lead and suggestions would be very helpful. I have been struggling to complete this. Thanks for your time.

    Ds

  149. Stefan Cameron on July 13th, 2010

    @Ds,

    I’m able to reproduce the issue you’re describing but I don’t have a suggestion for getting around it yet. I’ll add another comment when I do.

  150. Stefan Cameron on July 19th, 2010

    @Ds,

    I finally figured-out what’s going-on here. The issue lies in the fact that you have added a schema data connection which means that elements added under xfa.datasets.data must conform to the schema.

    When you use ODBC data connections, while data is loaded under “xfa.record”, “xfa.record” corresponds, by default, to the first element below xfa.datasets.data. Since the first element below “xfa.record” is the name of the ODBC data connection, this name must match the name of a schema element directly under the schema’s root.

    For example, consider the following schema:

    <movieRental>
        <movie>
            <title/>
        </movie>
    <movieRental>

    Let’s also say you have a database that has a “movie” table in it that has a “title” column.

    If you were to create a schema connection to the schema, a database connection to the database would have to named “movie” so that the resulting data element created, upon opening the DB connection, would be

    xfa.record.movie.title

    If you were to name your DB connection something like “MyMovieDB”, the resulting data element would be

    xfa.record.MyMovieDB.title

    Since “xfa.record” maps to “xfa.datasets.data.movieRental”, you would get an error like this in the JavaScript console because “MyMovieDB” is not a valid schema element:

    GeneralError: Operation failed.
    XFAObject.open:23:XFA:movieRental[0]:#subform[0]:Button1[0]:click
    Unable to create SOM expression $record.MyMovieDB.title

    John Brinkman explains this in more detail here.

  151. Art on July 31st, 2010

    Stefan,

    I am having the same issues as chris on March 21st, 2007

    I can’t seem to get to first base in the first cut at developing this form for our local Food Pantry. It is a simple one table Access data base for our clients. Here is the error I get:

    Error: This operation violates your permissions configuration.

    I get this error when trying to change the commandType and using the SQL Select.

    Thanks,
    Art

  152. Art on July 31st, 2010

    Stefan,

    Sorry, actually the error is:

    property ‘commandType’ cannot be set because doing so would violate the documents’ permissions setting

    Art

  153. Stefan Cameron on August 7th, 2010

    @Art,

    Please see my reply to Chris on March 21, 2007.

  154. helary on August 10th, 2010

    Stefan,

    I have found a sample from Acrobat on browsing records stored in a XML data file.

    This example demonstrates how to navigate through records stored in an XML data file by using buttons to navigate between records.

    But it has to modify the XML source by adding:
    entry
    in order to work ok.

    (DataConnection => entry => data fields) under DataView

    I have tried it and works ok on a new pdf.

    However, when I add to a complicated pdf, it crashes on opening after saving.
    Following is the original source that I try to add after
    -
    ….
    ….
    ….


    1.7

    1

    *

    Any ideas?
    Rgds.

  155. helary on August 10th, 2010

    Stefan,

    Sorry somehow I can’t post the less than and greate than sign, so I post again the XML source by replacing lt and gt sign with bracket signs :

    [common]-[data]
    ….
    ….
    ….
    [present]
    [!-- [0..n] –]
    [pdf]
    [!-- [0..n] –]
    [version]1.7[/version]
    [/pdf]
    [common]
    [data]
    [uri/]
    [adjustData]1[/adjustData]
    [xsl]
    [uri/]
    [/xsl]
    [outputXSL]
    [uri/]
    [/outputXSL]
    [/data]
    [messaging/]
    [locale/]
    [/common]
    [cache]
    [renderCache/]
    [macroCache/]
    [/cache]
    [incrementalMerge/]
    [script]
    [runScripts/]
    [exclude/]
    [currentPage/]
    [/script]
    [copies/]
    [layout/]
    [xdp]
    [packets]*[/packets]
    [/xdp]
    [output/]
    [/present]

  156. Stefan Cameron on August 13th, 2010

    @helary,

    The XML source you posted looks like the contents of the Config DOM. I’m not sure what this has to do with browsing the records.

    Are you using an ODBC connection to an XML file (ODBC driver for XML files) in order to use a Data Connection to said file?

  157. Dennis on September 17th, 2010

    I am developing a form that is connected to a database . The database has three columns of data and I want to be able to select the product number from column 1 in a drop down list and populate my form with the data in the two corresponding columns. Can you help with the code for this

  158. Kim Miller on October 4th, 2010

    All the examples you show seem to use a drop down list that the user can choose from that returns another subset of data. I’d like to know if the user can open a form, enter an ID into an appropriate form field and have the form retrieve that one row of data (and no others) from the DB. Is this possible?

    Thank you!

  159. Stefan Cameron on October 13th, 2010

    @Kim Miller,

    Yes, that’s certainly possible and the solution would look almost identical to this tutorial sample except that you would replace the drop down list with a text field to accept the ID and you would make sure that the query executed on the second data connection is designed to return only one record (or you only look at the first record returned).

  160. Lena Hill on October 18th, 2010

    Hi Stefan,

    I’ve downloaded your sample but can’t get the connection set up to see how it works.

    I’ve created the DB using Access 2000 but can’t figure out how to ‘load’ the sql file so that the data is there.

    I’m new to LiveCycle and have been advised to ask someone familiar with Access as to how that’s accomplished. The software doesn’t offer the ability to import that file type and haven’t found the answer using any other means.

    I’m told that by modifying your sample we’ll be able to have a user type text into a text field (probably their last name) and when you tab out of that field it activates the look-up to the DB which will then show only those records that match what the user has typed. It must show numerous columns such as lastName, firstName, middleInitial etc. (all info contained in the DB) so that the user can select the correct entry which will then automatically populate other fields on the form. We need to do this with employees, establishments etc.

    Step-by-step instructions would be greatly appreciated as this is a first for me and we need to create some forms that need this functionality.

    Thanks,

    Lena

  161. Stefan Cameron on October 23rd, 2010

    @Lena Hill,

    I’ll try to provide some instructions for you.

    First, I don’t believe it’s possible to use the MySQL database file I provided in a Microsoft Access database. You will need to create a new database and create appropriate tables and add the necessary data.

    Second, in order to use the ODBC data connection feature, you will need to run your PDF form in Acrobat, not the free Reader, since that functionality is not available in Reader unless you extend your PDF to enable data import.

    Next, I would suggest using a combination of this tutorial and my tutorial on displaying all records from an ODBC data connection where you would display, in a table with a repeating row (similar to my table with totals but without the “totals” part), all records returned from the search (e.g. last name data entered by the user).

    Finally, each row in the table would have a column that is a button that the user could then click in order to “select” that row/record. Clicking on the button would populate the other fields on the form with the data from the selected row/record.

  162. Lena Hill on October 26th, 2010

    Hi Stefan,

    Thanks, but from your response I realize that what you’re doing in your example is really not what we’re needing to do. Therefore, I would prefer to continue this dialogue offline in order to provide more detail to you as to what we’re trying to accomplish. If that’s possible, then please email me.

    Thanks,

    Lena

  163. Stefan Cameron on November 6th, 2010

    @Lena Hill,

    Unfortunately, I don’t have the time to help you offline.

  164. allan on November 8th, 2010

    Stefan

    Can you provide a sample PDF with xml file that will populate a dropdown(30 values), and script that will fill 30 textfields(values from same xml) based on the DDL selected. I am running LC8.2. I have not seen “any” examples other than “switch” with one(value/text), but that coding for 30 fields is too much.

    -Allan