Stefan Cameron on Forms
Building intelligent forms using Adobe LiveCycle Designer

Databases: Inserting, Updating and Deleting Records

Since all of the previous tutorials I’ve posted with regards to data connections have dealt with searching for records and displaying the results, I thought I should post a little tutorial on how to use data connections for inserting, updating and deleting records as well.

You may recall that the tutorial on connecting a form to a database did demonstrate one method of inserting, updating and deleting records from a database. The problem with using bindings to fields in order to modify records in a data connection is that you must set those fields to the values for the current record and then you have to use the data connection object scripting methods like "addNew()", "update()" or "delete()". This can get really awkward if all you’re wanting to do is insert a new record, for instance, and just doesn’t cut it if you want to avoid having to load-in data when the form is opened (the only purpose for the data connection may be such that new records can be inserted or existing ones can be deleted yet displaying existing records is not required).

This small tutorial uses a basic ODBC data connection defined in the form and then modifies it, via script, in order to be able to execute SQL statements which either insert, update or delete records from any table in the database for which the data connection was setup to work with. It then uses a separate data connection to the same database for query purposes only since there’s no sense complicating things with an all-purpose data connection — especially when it comes to running "select" SQL statements in order to iterate through records returned by the query.

Key Concept

The key concept with this tutorial is the fact that the <query> node inside an ODBC data connection (as we saw in the tutorial on selecting specific database records) can be used to execute all sorts of SQL statements — not just "select" queries. This means that if you set the <select> node (inside the <query> node) to be an "insert" SQL statement and open the data connection, the result will be a new record in the database as per the insert statement’s parameters (and the same goes for "update" and "delete" SQL statements). In fact, the XFA 2.4 Specification states, on page 772, that "despite the name [of the <query> node], this element can also be used to delete, insert, and update records."

BOF and EOF Actions

One very important thing to note is that the "Beginning of File" and "End of File" actions on the <query> node’s <recordSet> child node (which describes how records in the data connection are navigated) must be set to "stayBOF" and "stayEOF", respectively, otherwise you may run into serious problems. That is, the result of reaching the beginning or end of the record set when opening the data connection must be to "stay" where you (the record set navigator) are when the SQL statement is one or more of "insert", "update" and "delete".

See the script in the "Database" script object in the sample form for more details on how to specify this. Use the Hierarchy palette to locate it under the root subform.

Sample Form

I’ve designed a form that should put this all into perspective for you as well as give you a very useful script object which you can place in your Custom Library tab and re-use in other forms.

In short, these are the steps I followed to design this form:

  1. I created the "RunSQLDataConnection" ODBC data connection to my "FormBuilder" database, specifying a short SQL query to the new "movie_comments" table (although any other table would’ve been just fine).
  2. I created the second "ListComments" ODBC data connection to my "FormBuilder" database, specifying an SQL query that exposes the "username", "title" (movie title obtained from a join on the movie table) and "comment" columns.
  3. I inserted the various buttons and fields and wrote the scripts.

I’ve included lots of comments through the scripts to detail what’s going on at each stage as well as why certain things are being done so please have look at the sample (note that you don’t have to have the data connections setup in order to open the form and look at the script) and let me know if you have any questions.

Download Sample [pdf]

Download FormBuilder Database Definition [sql]

Minimum Requirements: Designer 8.0, Acrobat Pro/Std 8.0


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

83 Responses to “Databases: Inserting, Updating and Deleting Records”

  1. Willie on January 3rd, 2007

    I am trying this example using an Oracle 10.2g database, and when the ExecSql script gets to the oDC.open(); statement I get a “GeneralError” exception. Any ideas on what I am doing wrong?

    Thanks, Willie

  2. Willie on January 3rd, 2007

    Ok, I now have access to a SQL database to test with in addition to my Oracle database. Keeping the same code, but altering the data connection to point to my sql database, it now works. Both data connections were created using Microsoft OLE DB provider for ODBC drivers and a system DSN. I “tested” both connections via the data link properties test connection button, and both passed. Any thoughts?

    Thanks, Willie

  3. Stefan Cameron on January 8th, 2007

    Willie,

    That certainly is strange behaviour. Unfortunately, I’ve never encountered this before. I have heard of problems when not using the Microsoft OLE DB provider but that’s the one you say you’re using so I’m at a loss for suggestions.

    Are you trying this with Acrobat 8.0 (Pro or Standard) or an older version?

  4. Willie on January 9th, 2007

    Stefan,

    I am using Adobe Acrobat Professional 8 (trial download version).

    Willie

  5. Stefan Cameron on January 17th, 2007

    Willie,

    I would encourage you to have a look at Oracle’s ODBC FAQ to see if there might be an answer there.

    If not, then you could post a message to the Acrobat Forums or report the bug.

  6. jim on February 6th, 2007

    Ive setup everthing up, but seem to not working properly. it keeps providing this error prompt.
    “script failed(language is formcalc;coontext is xfa[0].form1[0].button[0])script=xfa.sourceSet.dataconnection.update(), Error:accessor’xfa.sourceset.dataconnections.update()’”

    using SQL Server connection

  7. Stefan Cameron on February 7th, 2007

    Jim,

    The first thing to check for in this case is if there are any syntax errors in your script. Based on what you wrote,

    Error:accessor’xfa.sourceset.dataconnections.update()’

    I see a syntax error in the “sourceset” object’s name. It should be “sourceSet”, with a capital “S” in “Set”.

    The problem could also be the “s” in the “dataconnections” object name. Are you certain its plural?

  8. Khader Meeran on March 2nd, 2007

    We are developing an application wherein we design forms using Adobe Acrobat LiveCycle and, then extract and import data to and fro from the application. We are using C#.Net and Oracle as backend. I would like to know a way to export, import data as well as manipulating controls such as show/hide controls, locking/unlocking controls from C#.NET. Can anyone sugget me with an example or material for this purpose.

  9. Carsten Sallaba on March 5th, 2007

    Hi Guys,
    Situation:
    *-Database connection
    *-prepopulated filed

    I want to build a script where a user can select e.g.Actor and a subform should dynamically prepopulate the related movies in textfield. The user should be able to add or delete movies.

    Me code is based on the following examples:DisplayAllDBRecords.pdf
    and
    SelectSpecificDBRecords.pdf

    therefore I tried to combine them, without any success :-( It seems that the filter doesn’t work, and I don’t know how to call customized function in formcalc

    Problem:
    Does anybody have an example for this typical example?

    Hope anybody can help me
    Thanks in advance
    Carsten

  10. Stefan Cameron on March 10th, 2007

    Khader,

    Are you asking how to import data into and export data from a LiveCycle form using Oracle as the back-end database, and then control the appearance and state of LiveCycle form fields using C#.NET?

  11. Stefan Cameron on March 10th, 2007

    Carsten,

    I’m confused: Based on the description you gave of your problem, it seems that my tutorial on selecting specific database records is exactly what you’re wanting to do.

    There must be something more specific that that tutorial isn’t covering which is part of your list of requirements. Let me know and we’ll take it from there.

  12. Doug Einstadter on March 22nd, 2007

    I also tried to combine the scripts in the ‘how to connect to a database’ example with the drop box and select button in the ‘selecting a specific database record’ example and ran into problems. By selecting a specific record, the SQL query results in a record set with only one record. This means that hitting the ‘next’ button results in no movement – there is no ‘next’ record to display. Same with ‘previous’, ‘first’, and ‘last’. What I’d really like to do is have a ‘go to a specific record in the database’ script that keeps all the records loaded, but jumps to the one record selected. I’ve tried looping through using next() until I get to the selected record, but with a database of 300 records and some 500 fields linked to my form, this can take a long, long time. Any suggestions on how I can accomplish this more efficiently?

  13. Stefan Cameron on March 27th, 2007

    Doug,

    I’m thinking the sample form in my tutorial on selecting specific database records is almost exactly what you need, given the large number of records in your database.

    I would suggest using that sample and coming-up with a more extensive way to specify information about the record(s) sought so that the query can return one specific record or a small subset of them.

    If the query only returns a single record, you could always disable the “next”, “previous”, etc. (i.e. the buttons that don’t apply).

    To determine if the query returned zero, one or more records, you can use the “isEOF” function of the Data Connection object which returns true if you’ve reached the end of the recordset.

    The following FormCalc script is a modification to the original script that’s in my “selecting specific database records” sample, in the drop down list’s Change event:

    var sOriginalBOF = oDataConn.#command.query.recordSet.bofAction
    var sOriginalEOF = oDataConn.#command.query.recordSet.eofAction

    oDataConn.#command.query.recordSet.bofAction = “stayBOF”
    oDataConn.#command.query.recordSet.eofAction = “stayEOF”

    var nRecordCount = 0

    oDataConn.open()

    if (oDataConn.isEOF()) then
        xfa.host.messageBox(“no records returned”)
    else
        oDataConn.first()
        nRecordCount = 1

        oDataConn.next() // move past the first record to see if we reach EOF

        if (oDataConn.isEOF()) then
            xfa.host.messageBox(“one record only”)
            oDataConn.previous() // move back to the one and only record
        else
            nRecordCount = 2
        endif
    endif

    oDataConn.close()

    oDataConn.#command.query.recordSet.bofAction = sOriginalBOF
    oDataConn.#command.query.recordSet.eofAction = sOriginalEOF

    oDataConn.open()

    if (nRecordCount > 0) then
        oDataConn.first()

        // Set the category name in the movieData subform and make it visible.
        movieData.presence = “visible”
        movieData.movieCategory = sCategoryName
    else
        movieData.presence = “invisible”
    endif

    This script changes the “BOF” and “EOF” Actions (what happens when the record pointer goes beyond the first record in reverse order or past the last record in forward order) such that the record pointer remains in that position (the default is to loop around to the last or first record, respectively). It then moves beyond the first record and checks for “EOF” status. If we’ve reached EOF, then there’s a single record, otherwise, there are more records.

    As for disabling buttons, you can do that by setting their “access” attribute to “readOnly”:

    dbNext.access = “readOnly”;

    To re-enable them, you simply set their access property to “open”.

  14. Doug Einstadter on April 9th, 2007

    Stefan:

    Thanks for all your helpful suggestions. I finally got my form to work as desired. In the process I discovered several “quirks.” First, with radio button fields, if the field is blank, trying to update the record will fail; the field must contain a non-null or non blank (i.e., “”) value. Second, I came across what seems to be a limit to the length of a query which can be sent using the Database.ExecSQL() function you outlined in your form. My form has about 250 linked fields. When I tried to create an update query with all fields, it resulted in a GeneralError: Operation failed message. After some trial and error, I discovered that if I split the query into two pieces, it worked fine, but if I tried to send it as one long query, it fails.
    Do you know if this is a LiveCycle specific limitation or something related to JavaScript or the ODBC driver for Microsoft Access?

  15. duncan on April 9th, 2007

    your answers are quite satisfactory thus continue posting. my request is idea on how to code for a command button in access form which will help do some calculation based on available records. please help

  16. Stefan Cameron on April 9th, 2007

    Doug,

    Thanks for the report on the quirks you discovered.

    I asked a colleague of mine familiar with the inner-workings of data connections within Acrobat and he informed me that they don’t impose a limit on the length of the query string.

    It would seem, then, that the limitation is most likely imposed by the ODBC driver you’re using.

  17. Stefan Cameron on April 9th, 2007

    Duncan,

    I’m afraid I’ll need a little more context in order to provide you with a good answer.

    Generally speaking, once the data from one or more records has been imported into fields on your form, you can then script simple or complex calculations based on the values of those fields (which are the values of the records).

    For instance, if you had three fields and you wanted to show their sum in a fourth field, you could use the following FormCalc script on the fourth field’s Calculate event:

    Field1 + Field2 + Field3

  18. Kristin on April 11th, 2007

    I was wondering if you had any information on injections on the Javascript being used to insert records into the database. I will be inserting very sensitive information, so I want to make sure the Javascript in the form is secure for that reason. Thanks!

  19. Stefan Cameron on April 14th, 2007

    Kristin,

    Unfortunately, I don’t have any specific information on that.

    Since this affects your form when it’s running (being filled) in Acrobat, I think your question would be better-suited for the Acrobat Forums

  20. Robert on April 15th, 2007

    Stefan,

    I am about to give up! I have built my form and made a connection to my MS Access db. I can not get the “Submit” button to work or populate my db. I thought this would be easy in LiveCycle Design.

    I just want to have a simple form to collect people’s info so I can send them a sales packet. My form includes:

    Current Date
    First Name
    Last Name
    Street Address
    City
    State
    Zip Code
    E-Mail Address
    Phone Number
    Submit Button
    Reset Button

    Some basis static text about the business (Name, phone, address, etc.)

    What am I doing wrong?

    Thanking you in advance,

    Robert

  21. Stefan Cameron on April 18th, 2007

    Robert,

    It’s not quite clear to me how you’re expecting the data from those fields to be inserted into your database.

    Are you using script from this tutorial to execute an SQL “insert” statement on the data connection you’ve defined in order to insert a record with the field data into your database?

    If not, the only other alternative is to use the “addNew” and “update” methods of the data connection object and bind your data fields to nodes in the data connection itself (by dragging and dropping data nodes from the Data View palette onto the appropriate data fields on your form):

    In the Initialize event of your “Submit” button, put the following statement (in JavaScript):

    xfa.sourceSet.DataConnection.addNew();

    where “DataConnection” is the name you gave to your data connection when you defined it.

    This will cause a new record to be created (but not inserted) and all the bound data fields will be emptied (rather than being populated with data from the first record).

    Then, in your “Submit” button’s Click event, put the following JavaScript statement:

    xfa.sourceSet.DataConnection.update();

    This will update the new record with the data entered into the data fields and insert it into the database.

  22. Darko on May 3rd, 2007

    Stefan,

    I have a pdf form that imports xml data file. Would it be possible, after importing xml data to the form, to insert the same data into an existing Access database?
    For this purpose, when creating data connection, should I use table or sql query as a record source?

    Big thanks for your blog!

    Darko

  23. Stefan Cameron on May 3rd, 2007

    Darko,

    There are a couple of ways you could go about doing this:

    1. You could write a script in the root subform’s (usually named after the root data node in your schema and found at the top of the hierarchy tree in the Hierarchy palette) Initialize event which gathers data from the fields and submits it to a database using an ODBC data connection that you would define. The trouble with this approach is that the Initialize event gets called twice: A first time when the form is loaded and a second time after data merge. You would need to define a variable (either a Form Variable via the Form Properties dialog or a variable in a script object) which you could use to trigger your script to execute only on the second call to the Initialize event (i.e. every time the event is triggered, increase the counter variable and when it’s value is exactly 2, the data should be merged-in and you can let the remainder of your script execute).
    2. You could use a script similar to option #1 except you would associate it with the Click event of a button which you could call “Save to database”. When clicked, the data would be gathered and inserted/saved to the database.

    In either case, I think you would find it much easier to use an SQL statement.

  24. Kristin on May 4th, 2007

    Hi Stefan,

    I was wondering if there is a way to change the default value of the textboxes on these forms. I have fields on my form which are not required. But when I use the URL Request to retreive specific information, if these fields which aren’t required are left blank, they actually insert a value of ‘null’ into my SQL table, thus, showing up as ‘null’ when I use the URL Request. I would really like these values to be actually nothing, like the real NULL in SQL. Is there somewhere I can change the default value? Or how do you suggest going about changing these, without programmatically looping through each field and changing the value to ” if the value is really ‘null’. Thanks much!

  25. Stefan Cameron on May 7th, 2007

    Kristin,

    Unfortunately, there’s no way for you to change the default value of an XFA field in the way you’re asking to do it. You can certainly specify default values for fields but you can’t specify a default of the SQL “NULL” value — although I can certainly see how this would be handy.

    I’m afraid that in this case, you’ll have to do the work in script behind the scenes and I can think of a couple of ways to do that:

    The first is basically what you didn’t want me to suggest so I won’t elaborate on it.

    The second is to use a Stored Procedure in your SQL database. The advantage of this solution is that you could write a little bit of SQL to substitute string values that come across as “null” for the real NULL value, prior to the value being inserted into the database via an INSERT statement in your stored procedure, rather than putting the script directly in the form and building a special SQL statement. Furthermore, you could use the same Stored Procedure in other forms without having to re-write the “null to NULL” code.

    I can’t say that I’ve tried the Stored Procedure option myself but it’s definitely possible to use them in data connections so this might be worth a try.

  26. Ernest Brown on June 25th, 2007

    Stefan

    I am creating a form in Designer 7.0 with a data connection to an Access database. I am using a form button in Access to open the pdf form in Reader 8.1 with it populated with data from an Access table. The form works the first time, but if the table data is changed the newly opened pdf has the old data displayed. I have configured Reader to not cache data. Do I need a refresh method for the pdf form?

  27. Stefan Cameron on June 27th, 2007

    Ernest Brown,

    Since Acrobat doesn’t maintain a live connection to the database table, you’ll need a way to reload the data from the database if you know that it changed. Data connections simply contain the information that Acrobat needs in order to connect to the database and extract the information required or write the information specified via field bindings or the SQL statement when required (i.e. the user must do something to prompt Acrobat to access the database, such as opening the form, clicking on a button to move to the next record, etc.).

    For example, if your form is loading records into a table similarly to the way in which the script in my displaying all records from an ODBC data connection is displaying all the records in a database, then you would simply extract the script in the root subform’s (named “form1″) Initialize event and put it in a script object function. (You can insert a script object by right-clicking the root subform at the top of the Hierarchy palette and choosing the “Insert Script Object” command. Then give it a name by selecting it in the Hierarchy palette. Finally, open the Script Editor palette and paste the script into a function.) Then you call the function from the root subform’s Initialize event so that the records are loaded when the form is opened and then you could add a button to the form which would also call the same function. The last step would be to add a call to “_MovieSF.setInstances(0);” at the beginning of the function so that any existing instances are removed prior to being re-added.

  28. Nener on September 12th, 2007

    I placed my pdf and my database in the same file on the network and my connections all work beautifully. A coworker at another location uses the form without any problems, but two other coworkers get the “Error:accessor’xfa.sourceSet.dataconnections.update()”. The first record appears in the form so it does make a connection but none of the buttons with the script “xfa.sourceSet.dataconnections” work on their pdfs. They do have a microsoft access mdb driver. This form needs to be accessible to a large number of people with or without microsoft access installed on their machines. Any ideas?

  29. Stan on September 14th, 2007

    Stefan,

    Thank you for your expertise. I do need some focus though. I have developed a form that I want to extract the data from. The data comes from the various fields that are filled out. I want to export to an access database. I am very novice with scripting, but have managed, using some of your examples, to build a data connection that “pulls” data from a database. Can you provide me some guidance on how to take data that I enter on a form and export that to a database? I see several options, but get lost in the details, “update”, append, EOF, etc”. Which to use and how? Any guidance you can provide will be most appreciated.

    Thanks,

    Stan

  30. Stefan Cameron on September 16th, 2007

    Nener,

    Do all users of the form have a DSN defined which points to the database and is their DSN name the same name as was used to create the data connection in the form? As long as this information is properly specified on each machine which uses the form, then their shouldn’t be a problem with connecting the form to the database.

  31. Nener on September 17th, 2007

    Thank you for your response. Actually I chose not to create a new dsn. I used the resident MS Access Database to avoid setting up a dsn on the users machines. My connection string is Provider=MSDASQL;Extended Properties=”DSN=MS Access Database;DBQ=\\Wlsfiles\Srv01\WILSON PUBLIC FOLDER\FORMS\SALES FORMS\Pricing Environment\Pricing Environment.mdb;DefaultDir=\\Wlsfiles\Srv01\WILSON PUBLIC FOLDER\FORMS\SALES FORMS\Pricing Environment;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;”
    I checked one of the machines is wasn’t working on and she does have the MS Access Database DSN on her machine. Is there a connection string I could set up on the server side? There will be to many users to change their computers.

  32. Stefan Cameron on September 22nd, 2007

    Nener,

    Unfortunately, I’m not all that familiar with ODBC. It looks like you opted to create a connection string that connects directly to the database file which is on a network share. In the end, all that matters is that Acrobat be able to open the data connection and use for retrieving and setting data.

    When you open the form on a system that isn’t working, are there any errors in the JavaScript Console (press “Ctrl + J” to see it in Acrobat Standard or Pro) that might allude to a connection problem?

  33. Linda on September 26th, 2007

    Stefan,

    I have a form that I created in Adobe Professional 8 (LiveCycle Designer). I have used your other examples and created the form using subforms that will add an additional subform when a button is clicked. This form is based on 10 different subforms. (One for each table that I have in a database)

    I am connecting this form to an Acces 07 database. In other examples, you said to create multiple data connections for each table using an SQL statement if your table has an autonumber, which mine does. I have created a few sample connections for each of my table. (I have 10 tables in the database). I know that I can do this process, but because of the number of tables and fields, it will take some time to complete.

    What I need this form to accomplish is:
    1. Have the customer complete the form.
    2. When I have played around with the form, I get a message that the form is trying to connect to the database, and I have to click yes to allow or no to not allow the connection. I do not want the customer to see this message. However when I receive the form, I need to use this connection. How can this be done?
    3. When the form is received by me via email, I need to have the information put into the database. (I have tried importing the form using the dataset export with Adobe Professiona 8, but since there are 10 tables, the export function produces 10 NEW tables. It will not update the already existing tables, even though that choice is seletected. Needless to say this makes a mess out of the database.) It would be much cleaner to be able to take the form and put it directly into the database using a submit button that only I would be able to see when I receive the e-mail back.

    So this brings me to this blog. I have tried using the sql example connection that you have given, but I really don’t understand it. (i am not a programmer, html programmer, and database person) I tried using the tutorial on creating an sql data connection, and have been very unsuccessful. I simply do not understand it. Is there a simple way to do this that I may be able to do without using the sql data connection?

    In an earlier post, you had given an example of a javascript

    xfa.sourceSet.DataConnection.addNew();

    where “DataConnection” is the name you gave to your data connection when you defined it.

    This will cause a new record to be created (but not inserted) and all the bound data fields will be emptied (rather than being populated with data from the first record).

    Then, in your “Submit” button’s Click event, put the following JavaScript statement:

    xfa.sourceSet.DataConnection.update();

    This will update the new record with the data entered into the data fields and insert it into the database.

    I tried inserting this into the form, and LiveCycle will not allow it, because the subforms were created in formcalc. Is there a way to make this script work in formcalc?

    In simple language, what is the difference between using this script and using a my sql connection? From what I have read, it just means that you have to bind the data of each field, is this correct?

    I really need help with this, I have worked for weeks trying to do this connection and getting it to work, and have become very frustrated!!!!

  34. Stefan Cameron on October 8th, 2007

    Linda,

    I’m sorry to hear it’s been such a frustrating experience so far. Data connections and data bindings aren’t the easiest concepts to explain nor to understand.

    Before we get started on data connections, however, I’m afraid I’m going to have to alert you to an important point: The free Adobe Reader is, by default, unable to import data into a PDF form. The form must be extended, using Adobe Reader Extensions (a LiveCycle server application which enables Reader to import data, amongst other things, when viewing a particular PDF form), in order to do that. This means that without Reader Extensions, your form, on the user’s machine, will only be able to submit data (likely in XML format) and you will have to load that data back into the form on your system. This could, however, be a good thing because since you have to load the data back into the form, you could load it into another form which contains the data connections and then you wouldn’t have to worry about the user seeing that “do which to allow Acrobat to connect to…” dialog when they open your form.

    The reason why Acrobat shows that warning message prior to opening a data connection is for security reasons. In the user’s best interest, Acrobat will not establish any connections without first receiving the user’s consent to do so. I believe you may be able to get around this by certifying the form but that would require a digital signature which isn’t readily available to everyone these days. That being said, Acrobat shouldn’t issue the warning unless the form attempts to open a data connection so as long as you don’t provide the user with any way of clicking on a button that would trigger such an event, they shouldn’t see the warning message when they’re filling-out your form. But then if the form the user fills-out is different form the form you use to load-in the data they submitted and insert it into the database, then the user’s form wouldn’t have any data connections defined in it and you wouldn’t have to worry about this problem.

    Assuming you created a second form, the second form would have all the data connections you need although if the data collected from the user form needed to be inserted into various tables, you could use the concepts described in this blog post in order to execute many INSERT statements on the database. Of course, the Access database ODBC driver must support the INSERT statement. Otherwise, since you have so many tables to insert data into, using a PDF form to insert the data into the database may not be the best option. Having your data in XML format should be a good start in finding an alternate way to get the data into the database. Recent versions of Access may even have some sort of XML data import feature but I don’t know for sure.

    If you wanted to use the same form, then you would have to toggle the visibility of the various controls you would use to insert the data into the database based on whether a particular field has a value when the form is being initialized. Since the Initialize event occurs on all objects after data is merged into the form, if this field has no value, then it must mean (assuming it’s a mandatory field) that the form is being viewed by the user and the control buttons should remain hidden. Otherwise, it contains data and that should mean that you’re viewing the form with imported data, in which case the control buttons should become visible so that you can execute the data connections and insert the data into the database.

    Finally, about using FormCalc instead of JavaScript, it should be possible however I’m not sure why LiveCycle wouldn’t allow you to use JavaScript. Perhaps you simply need to set the scripting language property in the Script Editor to “JavaScript” (since the default is “FormCalc”)?

    I’ve probably just added more mud to the water but hopefully this makes a little more sense. Unfortunately, I don’t believe you will be able to avoid writing script for this kind of solution.

  35. Roland on February 23rd, 2008

    For those having the same problem as Nener, where you are able to connect to the Datasource but get the error:
    “Error:accessor xfa.sourceSet.dataconnections.update()” it is most likely that you are using Adobe Reader to connect to preview your PDF form. Adobe Reader is unable to connect to datasources you need Acrobat Professional for this.

    One way to get around this (sort of), if you have the Reader Extensions component is to extend the document to enable “Database and web service connectivity”. Then open the document with Adobe Reader and test, it should work. This would be as a last resource since you’d have to extend the document every time you modify it, which is not cool at all.

    Hope this helps.

  36. Warren Worth on March 3rd, 2008

    Stefan,

    You directed me to this article from http://forms.stefcameron.com/2006/09/18/connecting-a-form-to-a-database/.

    We are hoping to send data from an Acrobat form to a mysql database on our website (Apache environment). Will the site visitor need an ODBC driver on their system or is there a way explained here or elsewhereelse on how to link the form and database without a client driver?

    Thanks,
    Warren

    PS – I got my form, referenced on the above site, to work without the snippet once I corrected MY error (lack of understanding of scripts). Thanks again.

  37. Stefan Cameron on March 7th, 2008

    Warren Worth,

    I’m glad to hear you got your form to work!

    Unfortunately, the system using the form (visiting the site) will need to have the DSN configured on it in order for Acrobat/Reader to communicate with the database.

    An alternative (which would also be more secure) would be to create a web service that interfaces with the database and connect the form to the web service. This way, the system using the form wouldn’t need any configuration.

    Before you go to far down this road, however, you should note that Reader cannot import data from databases or web services without the form being extended using LiveCycle Reader Extensions to enable that PDF, when used in Reader, to communicate with databases/web services. Another way of importing data is by using LiveCycle Forms and obtaining the data on the server and merging it into the form prior to it being served to Reader on the client.

  38. Warren Worth on March 11th, 2008

    We are not looking to retrieve data from a data base. This would be a one way trip. The site visitor would fill out a form and submit it. The data would be added to our database at our site. No data would ever flow back to the user.

    In another solution we would need to encrypt the data submitted by the user. Once it arrives at our site (and I guess a possible email) we would need to decrypt it and store it in a database (automatically if possible but password would be OK. Again only one way.

    Thanks,

    Warren

  39. Stefan Cameron on March 12th, 2008

    Warren Worth,

    How about posting the form data to a web page which then connects to the database and inserts the data? If you needed this to be secure, you could connect to the web page using HTTPS (assuming you have a certificate)…

  40. parwez on August 13th, 2008

    Hi Stefan,
    Your Site and this forum is very interesting and the best forum I’ve ever came across. Your site had been very helpful to increase my knowledge and skills in LiveCycle Designer.. and I would like to thank you for all your supports.
    I need one help from you. In fact, I created a form in LiveCycle designer 8.0. I made a connection to MySQL using ODBC driver(DSN). I tested the form on my PC and two other colleagues PC from the network. I installed the ODBC driver on both PC both having adobe professional 8.0 and it works fine. However. when I test the form in a PC having Adobe Reader 8.0 only, I got the following errors:
    accessor ’xfa.sourceSet.dataconnections.update()’ is unknown
    accessor’xfa.sourceSet.dataconnections.addNew()’ is unknown

    I used the same DSN. Can you please tell me more about what I can do to solve this problem. Thanking you in advance.
    Best regards,
    Parwez

  41. Stefan Cameron on August 13th, 2008

    parwez,

    I believe this is caused by the fact that you cannot import data in the free Reader without first extending the PDF to enable data import capabilities using LiveCycle Reader Extensions. Another option is to use LiveCycle Forms to render the PDF to the client (e.g. browser) since the data import will be handled on the server rather than on the client (in Reader).

  42. Tan Tran on September 9th, 2008

    Hi Stefan,

    I have created a PDF form using Live Cycle Designer ES ver 8.2. Users can fill out the form, print it. However, I also want the data that they populate the form with to save to the database. How can I make the database update part work using this application? Any advice, online references or books on how to achieve is much appreciated. BTW, we are using Oracle version 10g.

    Thanks in advance.

    Tan

  43. Stefan Cameron on September 12th, 2008

    Tan Tran,

    You’ll need to connect your form to your Oracle database using an ODBC data connection, at the very least. Depending on your security requirements, you may have to connect it to a web service which then communicates with the database on the server (which is a much more secure solution than exposing your database on the Internet).

    Have a look at the database-related articles in my blog’s Data Binding category. You’ll find examples there.

  44. Tan Tran on September 14th, 2008

    Thanks, Stefan. The information you provided is very helpful. It is much appreciated!

  45. Martin Nehez Posony on October 8th, 2008

    Dear Stefan,
    first I’d like to thank you for your enthusiasm with which you’re helping us, it’s a great resource of knowledge. I have been going through your posts on connecting to a database and selecting a single item from a database, and encountered two basic problem.
    1. When I tried to connect to the Movie database you created, I couldn’t find the MySQL Driver among the possible drivers offered by LiveCycle, although I had installed it, and created the DSN.
    2. I tried to connect to an Access database that did not have auto incrementing fields, used the script to retrieve data, but all I could achieve was the displaying of the first record.
    What did I do wrong. Also, I could use a general description of the idea of Data Drop-down Lists, and the script that is shipped with it, because I guess it would make things much easier. Is there a basic readout out there about connecting to a database?
    Thanks in advance:
    Martin

  46. Stefan Cameron on October 14th, 2008

    Martin Nehez Posony,

    You’re welcome! I’m glad you’re finding useful articles.

    For the DSN issue, did you create a System DSN? If not, that could be the issue.

    As for the form only displaying the first record, I’m not sure what the problem is. If you’re following my example on connecting a form to a database, then that’s what I would expect. You would then have “next” and “previous” buttons to iterate through the records one by one. If you’re wanting to display all resulting records, then you should have a look at my article on displaying all records from an ODBC data connection.

    The idea behind the Data Drop Down List is that it starts by opening the data connection and moving to the first record. Then it ensures that the BOF and EOF (“Beginning of File” and “End of File”, respectively) actions are set to “stay”, otherwise, iterating through the records in the data connection would be an endless loop. Next, it locates the “display text” and “hidden value” columns in the current record (xfa.record) loaded via the data connection and gets the nodes that represent each of them. Finally, a While loop iterates through the records in the data connection by advancing the record pointer (oDB.next()). Every time the record pointer is advanced, oTextNode and oValueNode point to new values because the current record changes. You might find more information on this in the Adobe DevNet Form Design section.

  47. Martin Nehez Posony on October 14th, 2008

    Stefan,
    thanks again, actually I did create a System DSN, still the MySQL Driver wouldn’t show among the drivers in LiveCycle.
    On the other issue, I think I put it in a wrong way: of course the form should display the first record. The problem was the Data Drop Down List simply didn’t drop down (maybe I shouldn’t bind it to the ID-field?).
    Let me have another question: is there a way to retrieve data from a database and populate fields with the results simply by typing a value in a field, using the field’s exit event. Like, for example, when you have a database containing several thousand subscribers (in which case a drop down list wouldn’t be a very good way to access data), each having an account number, and you only need to type in the account number to the appropriate field to have the other fields automatically filled with the clients name, address, etc. I’d be very happy to hear yes :)
    Thanks for the link.
    Bye:
    Martin

  48. Stefan Cameron on October 19th, 2008

    Martin Nehez Posony,

    If the Data Drop Down List isn’t dropping down, that means it doesn’t have any items, which means the connection didn’t work or the query didn’t return any records.

    As for your question about the account number, you can do it: It’s very similar to my tutorial on selecting specific database records. In that tutorial, the drop down list has an Exit event which creates a query based on the selection in the list. You would just need that second data connection (not the first one) and you probably wouldn’t need the navigation buttons since the query will always return a single record.

  49. Anthony on November 7th, 2008

    Hi Stefan

    Just wondering if you know in script how I can retrieve the default value of a textbox?

    For example i have in a textbox a default value which contains instructions of what to fill in that textbox.
    Then what i want it to do is that on enter it compares the value of the textbox and the default value and if the same blank the textbox ready for entry from the user.
    Also on exit i want it to see if the textbox is blank then if it is put back the default value.

    Thanks in advance

  50. Stefan Cameron on November 7th, 2008

    Anthony,

    this.rawValue

    returns the current field’s value in JavaScript. So in the Enter event, check “this.rawValue” to be the default text and assign it “null” if it is. In the Exit event, check it again and it it’s null or an empty string, set it to the default text…

  51. Anthony on November 10th, 2008

    Hi Thanks for the reply

    So are you saying I need to set a variable to the defaulted text?
    Is there no way to call the defaulted text from the properties you get in javascript Textbox1.SetToDefault for example?

    The problem i’m finding with the variable method is the way javascript handles multiple lines and how you compare that with the text in the textbox.

  52. Stefan Cameron on November 10th, 2008

    Anthony,

    Unfortunately, it’s not as easy as “Textbox1.SetToDefault”. If you really don’t want to keep the original value in a variable (either a Form Variable or a variable in a script object), then you would have to retrieve the original default value from the Template DOM.

    Long story short, when you design a form in Designer, you define the Template DOM. When you run the form in Acrobat, a Form DOM is created on-the-fly which contains just enough information to render the form in its current state. The Template DOM is never modified. This means that when the field’s value changes, the new value is written to the Form DOM and subsequent accesses to MyField.rawValue retrieve the value from the Form DOM’s information on MyField rather than its Template DOM counterpart.

    So, in the field’s Exit event, you would have to explicitly access MyField from the Template DOM like this:

    xfa.template.{rootSubformName}.{pathToField}.MyField.value.text.value

    You would replace {rootSubformName} with the name of your root subform and {pathToField} with any named subforms in between.

  53. Anthony on November 11th, 2008

    Thanks stefan thats exactly what i wanted.

    So i have now on enter a check with the currentvalue with the template value and if the same empty the field and set the focus. And on exit i have if the field is empty put back the template value.

    Can’t see any problems.

    I think this way of using the template is great, also makes the code look a lot neater.

    Just one quick question, will there be any problem if a user saves the form with some text but then at later date deletes that text and with the script i’ve put in put back the template value? I presume the template is in the pdf form so its always there.

    Thanks again stefan this website has been a great source of information on pdf forms.

  54. Stefan Cameron on November 11th, 2008

    Anthony,

    You’re welcome. I’m glad you’ve found my blog to be useful!

    I haven’t done much testing with code that accesses the Template DOM however it should always remain intact. Any changes that occur take place in the Form DOM so the script should keep working after a save.

  55. Christian Gaulea on January 29th, 2009

    Hi all,

    I am getting this error “GeneralError: Operation failed.” when executing oDC.open();

    Up to this line, the script clones my sql DataConnection without problems.

    Any ideas on how to fix this?

    Thanks you,
    Chris.

  56. Stefan Cameron on January 30th, 2009

    Christian Gaulea,

    Since like there might be a configuration problem with your data connection or the DSN it’s using.

    Does the DSN require a username/password? You can specify this in the data connection’s properties.

    Is the database running? If it’s a MySQL database, for example, you have to make sure you MySQL server is running.

    Can you create a new form with a simple data connection to the database (like in my connecting a form to a database tutorial, for example) and pull data from it?

  57. Christian Gaulea on February 3rd, 2009

    Hi Stefan,

    Thanks for your reply. I ended up using a DSN and it works fine. I am using LiveCycle designer and Acrobat pro 9. My form works fine in either. But when I wanted to test it on Acrobat Reader, after installing it i get this error message:

    oDCNode has no properties
    41:Doc:Init

    I am printing out nCount = oDataConnList.length and it is 0.

    Do you have any idea what could be wrong here? If I uninstall the Reader it works fine again.

    Thanks in advance,
    Chris.

  58. Christian Gaulea on February 3rd, 2009

    I forgot to mention that after installing Reader 8 or 9 the same happens. In LiveCycle or Reader, I get that error message. In Acrobat Pro it works. After uninstalling the Reader, the connection work works fine in LiveCycle.

    Also, I am using your Database variable like in your form.\

    Thank you.

  59. Stefan Cameron on February 6th, 2009

    Christian Gaulea,

    Unfortunately, you can’t import data into the free Reader without first extending the PDF to enable data import rights using LiveCycle Reader Extensions. That’s one of the drawbacks of using a data connection to a database. Without this extension, your data connection will not function in Reader.

  60. Han Dao on July 10th, 2009

    Hi Stefan,

    Thanks alot for posting this tutorial, I found it very helpful for me. I even try to create a similar form to insert another record to the movies table in addition to the comments table but got this Adobe Arobat error: “Invalid property set operation; dataGroup doesn’t have property ‘id’ “when I try to run it. Do you have any idea about it? If so, could please help me to fix it.

    Thanks in advance,
    Regards,
    Han Dao

  61. Han Dao on July 10th, 2009

    Hi Stefan,

    I think I figured it out why I have that error. I have to change the query to select all the column exclude the id instead of select * from that table.

    Thanks again,
    Han Dao

  62. Stefan Cameron on July 10th, 2009

    Han Dao,

    That sounds right — glad you figured it out!

  63. Monia on July 15th, 2009

    Hi Stefan,
    I have a problem with my pdf document. This doc reads some information from Oracle database and at the end of filling in this should write on the same database but I receive this error:

    Warning: JavaScript Window
    GeneralError: Operation failed

    and it doesn’t write anything.
    Can you help me???
    Thanks a lot.
    Bye
    Monia

  64. Stefan Cameron on July 17th, 2009

    Monia,

    This can happen if the record you’re trying to add includes an auto-number column. You can narrow the column view by using an SQL statement in the Data Connection Wizard (rather than simply choosing the table into which records will be added, which will include all columns by default).

  65. Jon Robershaw on September 9th, 2009

    Stefan,

    I am using LiveCycle Designer ES8.2 to create forms/reports that connect to a Microsoft SQL Server Database. These forms are going to be accessed by field/office employees and also by clients via a website that is currently under construction.

    Question #1:
    After reading this forum and its threads it seems as though there would need to be Adobe Professional installed on each computer that accesses the forms. And/or there would also need to be a local DSN for every user that will use the forms to connect/retrieve/and update data on our Microsoft SQL Server. Is this true? Because this will pose a problem for us; in that they might not want Adobe Professional, nor do we have access to clients’ computers to install DSNs.

    Question #2:
    Assuming we can work around the problem above. The second issue we have is creating a special script. We have several current Jobs running and many future jobs to follow. In all of our SQL Tables, we have a column specified for ProjectID so we can write a SQL statement to call up all information regarding the specific Jobs.

    We need to make a ‘log in’ that will allow the user to select the job they are working on, enter their password and submit! When they submit they will be transported to a navigation page where they can find whichever forms/reports they need. In order to reduce the amount of duplicate forms, I will need an initialize script that will call up the projectID for whoever is logged in. This way I will have not have to duplicate the myriad of forms and update their queries for each new job we take on in the future. I want one universal set of forms and reports that will call up the information specific to the user who is logged in. Do you have any ideas on how to make this happen?

    Any light you can shed on this is very much appreciated!

  66. Stefan Cameron on September 17th, 2009

    Jon Robershaw,

    In response to your questions:

    1. My post on importing data in Acrobat/Reader explains this. Unless you (a) use LiveCycle Forms ES to merge data into your form on the server prior to serving it out to the client (Acrobat/Reader) or (b) use LiveCycle Reader Extensions ES to enable the PDF form for data import, you will need all computers to have Acrobat Standard/Pro and the DSN installed/configured. This is a way in which Adobe monetizes the free Reader…

    2. Sounds like you would need to pass the ProjectID of the current user session into the PDF when it is loaded in the browser. If you were using LiveCycle, then you would do this server-side however since it sounds like this will all be done client-side, you might be able to leverage my post on using URL requests in PDF forms. Though this is by no means a secure solution, I think it would let you do what you need to do (if you don’t mind exposing the ProjectID in plain text in the browser’s address bar).

  67. Stu Dotson on January 14th, 2010

    I pretty much have your example working on my computer but I’m experiencing problems trying to translate the principles to my own project. I created a button to submit the contents of a few fields to an Access database. Here is the script:

    Database.ExecSQL(“INSERT INTO equipment_list (equipment_number, description, serial_number) VALUES (‘TextField5.rawValue’, ‘TextField6.rawValue’, ‘TextField8.rawValue’);”);

    It keeps dumping in “TextField5.rawValue” into the database instead of the contents of that field. I’m sure this is just some silly mistake but I can’t see it for the life of me.

    I don’t want to use the xfa.setsource method because I want to be able to create more than one entry simultaneously with the form (there’s a table in the form).

  68. Stu Dotson on January 19th, 2010

    Never mind I figured everything out.

  69. Stefan Cameron on January 19th, 2010

    Stu Dotson,

    Glad you figured it out. Thanks for letting me know!

  70. Jerry W. McLeod on April 9th, 2010

    Stefan, just a simple questiop on for you I think. I connected to a Access database on my laptop to go through some of your Adobe database tutorials. My first, last, next, previous buttons work fine, so does my update button. However, it is another story with the add New and delete buttons. I get an error to the effect of:

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

    when I enter information for the new record and and hit any other button. Same thing for the delete button. It seems like something simple I am missing here. maybe not. Am I missing something here. The Access database has automatic numbering of records under the VendorID field because i have entered information into the database manually. I would like to rectify this problem before I move on to the tutorial you wrote on deleting, updating, and inserting records as an alternative. Thanks for your help as usual.

    Jerry McLeod

  71. Arron Javal on April 14th, 2010

    I have read all your tutorials and it definitely helped me. I have already know how to connect into a database, deleting, updating, adding new etc. But, now i want to have a filter for it. I have already read your “displaying specific data records” and it help me to filter the data, but whenever im saving it, im having problems. It just, that the field that i want to be in the data drop down list box is the name field, and at the same time. i also have another textbox which this name field is also bind, so whenever i click the update button, it says something about “duplicate fields” or something. Can you please help me.

  72. Stefan Cameron on April 14th, 2010

    @Jerry W. McLeod,

    If your data connection is using a table that has an “automatic” column, like the VendorID column you describe, you will get an error when you try to add records because the data connection will attempt to set the value for that column but the database doesn’t let you do that.

    To remove VendorID from the data connection, edit the connection and use an SQL statement instead of choosing the entire table. Your SQL statement just needs to select all columns except for the VendorID column. Then try adding/removing records to see if that fixes the issue.

  73. Stefan Cameron on April 14th, 2010

    @Arron Javal,

    I’m guessing the error is occurring because you have more than one field bound to the same column (node) in the data connection.

    Are you using the techniques I describe in this tutorial? If so, just make sure the update statement you build for the query doesn’t include the name column twice…

  74. Arron Javal on April 14th, 2010

    Stefan, let me elaborate my problems. So, i have made a form that just like your “Connecting a form to a database” which includes, deleting, updating, adding new, etc. And i was succesful in it. So, i saw your other tutorial, the “selecting specific records in the database” and think that its a nice addition in the first form i have done, i dont have to click next,previous to update a record. I have been succesful merging the 2 techniques, the data drop down list box is showing all the records i want him to show, specifically, the “NAMES” of all the clients. So, yup, you are right, im using 2 field that uses the “NAME” column, 1 is for the filter, and one is for the Textbox for filling up client names. So how to resolve this using formcalc? help me please

  75. Arron Javal on April 15th, 2010

    And is this even right?

    Database.ExecSQL(“UPDATE TblTest SET Fname = “+Fname.rawValue+”, Lname = “+Lname.rawValue+”, SurName = “+Surname.rawValue+” WHERE Fname = “+SelectField.rawValue+”;”)

    What im trying is, Updating the field where the first name is the same as the value of the present value of the drop down list.

  76. Gerrit Thomson on April 21st, 2010

    Hi Arron.
    the sql statement will cause an issue because the data is not quoted so will be assumed to be table column references.

    Database.ExecSQL(“UPDATE TblTest SET Fname = ‘“+Fname.rawValue+”’, Lname = ‘“+Lname.rawValue+”’, SurName = ‘“+Surname.rawValue+”’ WHERE Fname = ‘“+SelectField.rawValue+”’”);

    Note the single quotes around where the data is inserted from the form.
    You may be better off constructing the sql statment before trying to use it show you and catch it and try it manually.
    Cheers,
    Gerrit

  77. Ian Hockett on April 27th, 2010

    This works great! I do have one question though. Is there a way to check that a record exists before running an update query?

  78. Stefan Cameron on May 1st, 2010

    @Ian Hockett,

    If you set the query to a SELECT statement that would list any existing record, you could check to see if the query returned is empty by testing:

    // JavaScript:
    if (xfa.sourceSet.{DataConnectionName}.isBOF() && xfa.sourceSet.{DataConnectionName}.isEOF())
    {
        // query returned nothing so the record must not exist...
    }

    where “{DataConnectionName}” is the name of the data connection whose query command you set to the SELECT statement.

  79. Sany on August 18th, 2010

    Hi Stefan Cameron,
    I have been following your post and I’m new to livecycle currently using designer es2 version 9. I do have acrobat 9 standard as well.

    I’m trying to create a form that has few field for testing I’m only test with two fields. Emname and Empno.
    I have the table named Performview in sql and I have aslready created a odbc connector in dsn.
    I also created a dataconnection in livecycle form with default name “dataconnection”

    I have made binding to the two fields empno and emname in the forms to the respective fields in performview table.
    I have a dropdownlist back named selectfield.
    I want to be able to drop down(on the selectfield list) and select the empno and populate the two fields empno and emname.
    I have a button and I want the populating of the fields to happen when that’s pressed.
    When I open the form, I see the form filled with the first record from the table so the two fields in the form emname and empno have the first record.
    I have two issue, first the dropdown list does not have any data when I view the form.
    2nd when i hit the button when there is no data in dropdown list I get error accessor selectfield.rawvalue unknown.

    I event tried to use a textfield(which I prefer to use instead of dropdown as the form user can just put the empno and hit the button, but I wasn’t sure how to do intialize for textfield. I still had the same issue. so I stuck with the dropdown as I saw example for it on how to set up itialize.
    so here is the code for both dropdown intialize and button click
    I’ll really appreciate if you can tell me why the drop box is not having any data and why I’m getting the error
    .I do see the 1st record from the table on the form when I open the form so I know the connector is work and the fields binding is working
    Please see code below.

    form1.#subform[0].Selectfield::initialize – (JavaScript, client)

    var sDataConnectionName = “DataConnection”;
    var sColHiddenValue = “Empno”;
    var sColDisplayText = “Emname”;

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

    // 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)
    {
    oValueNode = oRecord.nodes.item(nColIndex);
    }
    else if(oRecord.nodes.item(nColIndex).name == sColDisplayText)
    {
    oTextNode = 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();

    form1.#subform[0].Selectfield::click – (JavaScript, client)

    var sDataConnectionName = "DataConnection";
    var sColHiddenValue = "Empno";
    var sColDisplayText = "Emname";

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

    // 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 0) then
    $sourceSet.DataConnection.#command.query.commandType = “text”
    $sourceSet.DataConnection.#command.query.select.nodes.item(0).value = Concat(“Select * from performview Where empno = “, selectfield.rawvalue ,”")
    //Reopen the Dataconnection
    $sourceSet.DataConnection.open()
    endif

  80. Sany on August 18th, 2010

    sorry on my last post, I put the wrong code. Please see right code here
    FOR THE REFERESH BUTTON

    if (len(Ltrim(rtrim(selectfield.rawvalue))) >0) then
    $sourceSet.DataConnection.#command.query.commandType = “text”
    $sourceSet.DataConnection.#command.query.select.nodes.item(0).value = Concat(“Select * from performview Where empno = “, selectfield.rawvalue ,”")
    //Reopen the Dataconnection
    $sourceSet.DataConnection.open()
    endif

    fOR THE DROP DOWN
    ———————–

    form1.#subform[0].Selectfield::initialize – (JavaScript, client)

    var sDataConnectionName = “DataConnection”;
    var sColHiddenValue = “Empno”;
    var sColDisplayText = “Emname”;

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

    // 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)
    {
    oValueNode = oRecord.nodes.item(nColIndex);
    }
    else if(oRecord.nodes.item(nColIndex).name == sColDisplayText)
    {
    oTextNode = 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();

  81. Anders on August 20th, 2010

    Hi Stefan,

    We have a problem with tab order.
    I want the tab to stop on a text field.

    We are designing forms in Designer 8.2.

    Thanks, Anders

  82. Stefan Cameron on August 26th, 2010

    @Sany,

    The problem is probably related to the fact that you’re mixing JavaScript and FormCalc syntax: Your events are set to be in JavaScript yet you have statements like

    for (...) then ... endif

    and

    $sourceSet.DataConnection.#command.query.commandType = “text”

    which are not valid in JavaScript. That means that your scripts are executing. The fact that you see the data for the first record is just a result of the data bindings established between the fields and your data connection and doesn’t mean that your scripts are actually running.

  83. Stefan Cameron on August 26th, 2010

    @Anders,

    By “text field”, do you mean an actual text field that you can type into, or static text like a label that cannot be modified by the user?

    What seems to be preventing you from including this “text field” in the tab order?