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”:http://blogs.adobe.com/formbuilder/2006/09/connecting_a_form_to_a_database.html#comment-34179 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”:http://blogs.adobe.com/formbuilder/2006/09/connecting_a_form_to_a_database.html, 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.h2. 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 ==h3. //query@commandTypeYou 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.h3. Data Node NamesAnother 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:bc. SELECT id as catId, name AS catName FROM movie_categoriesGROUP 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.h2. Building the Formh3. Category ListThe 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”.h3. Movies in the CategoryNext, 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”:http://blogs.adobe.com/formbuilder/2006/09/connecting_a_form_to_a_database.html 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.h3. Filtering Records Displayed by the movieData SubformFinally, 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:bc. var sCategoryName = xfa.event.newTextvar sCategoryId = $.boundItem(sCategoryName)Given the XML structure of the <sourceSet> packet displayed above, you first set the query’s command type to “text”:bc. 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:bc. 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:bc. 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]”:http://blogs.adobe.com/formbuilder/samples/DataBinding/SelectSpecificDBRecords.zip*Minimum Requirements:* Designer 7.0, “Acrobat Standard 7.0”:http://blogs.adobe.com/formbuilder/2006/08/importing_data_in_acrobat.html.Use the FormBuilderDB20060929.sql file to build the database, create an ODBC Connection named “FormBuilderDB” and load the form.%{color:red} *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”:http://blogs.adobe.com/formbuilder/2006/12/better_form_design_with_xfa25.html.====~*Updated:* February 6, 2007~

52 Responses to Selecting Specific Database Records

  1. Michael Thierauf says:

    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. 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 says:

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

  4. Michael Thierauf says:

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

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

  6. Ernest says:

    The above info has been very informative and useful… ThanksCan 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. 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 says:

    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 says:

    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. 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 says:

    Hi thereHas 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. 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 says:

    StefanThanks 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 says:

    StefanI 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 justSelect * 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 formvar nRequestStart = sURL.indexOf(“?”);// show the URLURL.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 requestRequest.rawValue = sRequest;var aRequests = sRequest.split(“&”); // 1 request per array elementfor (var i = 0; i

  15. Dave says:

    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. 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 says:

    StefanThanks for that – I was going right round the twist with what seemed like ok script not running! SighI have tried adding a bit to the existing script :-…. if (sName == “Sessionfield”){// show message value in text fieldthis.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 Customervar CustRef = Customer_Ref.rawValuexfa.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. 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 says:

    StefanData 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 says:

    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. 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 says:

    StefanThe 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. 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 says:

    StefanSorry – 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. 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 says:

    StefanThis 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. 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 says:

    StefanI 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 = SlicerThis now returns 15:00 instead of 23/11/2006 15:00:00I’m not sure if I might run into problems later, but…

  29. Willie says:

    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 says:

    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 says:

    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. 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 says:

    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. 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 says:

    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) thenvar 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 says:

    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 says:

    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 Requestvar sURL = event.target.URL; // URL used to access this PDF formvar 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.TestConnoDB.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. 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. 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 says:

    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 says:

    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. 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 says:

    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. 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 says:

    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 says:

    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 says:

    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. 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. 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 says:

    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 formvar 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 elementfor (var i = 0; i

  51. Kristin says:

    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 formvar 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 elementfor (var i = 0; i

  52. 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.