Selecting Specific Database Records Stefan Cameron on Forms.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 Ricardos question on how to select a specific record from a data connection to a database for editing in a form.If you read my previous post on Connecting a Form to a Database, you mightve realized that the result was a single live data connection to the entire set of records in a database. Adobe Premiere Missing Codec Avi Per Mac on this page. This is great if you want to iterate through all records one at a time and update them on an individual basis.You mightve 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 youve been scratching your elbow, pinching your nose and blinking your eyes in hopes that this might just work, well, its actually scratch your nose, pinch your elbow and roll your eyes ok, just kiddingThe 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.Its 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.Set. Data. Connection.NameWhen you define a data connection in the Data View palette, youre actually defining a lt source nameData.Connection. Name node within the lt source.Set 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, Ive defined two data connections to the Movie Database If you look at the XML Source which describes these connections, you can see that theres an interesting command node which contains information about the query currently being used by each data connection.Thats what we ultimately want to modify once the user picks a movie category querycommand.Type. You should note that the query nodes command.Selecting Specific Database Records Stefan Cameron on Forms Building intelligent forms using Adobe LiveCycle Designer.Type 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 stored.Proc for specifying a stored procedure.Data Node Names. Another very important thing to note is the names given to the data nodes in the Movie.Categories data connection.Youll notice that the following SQL statement is used for the data connection SELECT id as cat.Id, name AS cat. Name FROM moviecategories.GROUP BY name ORDER BY name In particular, the id and name columns have been renamed to cat.Id and cat. Name, respectively.Thats 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.Data. Connection.I have an SSIS package SQL 2008 that I need to connecto to an Oracle DB 11g with.What do I need to install to connect to oracle Whats the terminology All.Windows applications in Visual Basic 5 Introduction Best Practices.Forms should contain UIrelated only Any logic should live in modules or even better class modules.Top VIdeos. Warning Invalid argument supplied for foreach in srvusersserverpilotappsjujaitalypublicindex.Name 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.Building the Form.Category List. The first step is to use the Data Drop Down List object from the Custom tab in the Library palette.Microsoft Data Link Error Initializing Provider Mysql Odbc' title='Microsoft Data Link Error Initializing Provider Mysql Odbc' />This is a really handy object that has code in its Initialize event thats 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 Movie.Categories, the hidden value column name to cat.Id and the display text column name to cat.Name. If you run the form at this point, you should get three values in the list Action, Comedy and Drama.Movies in the Category.Next, create a subform lets call it movie.Data which contains fields with explicit bindings to the title and show.Time data nodes from the Movies.In. Cat data connection using the Binding tab in the Object palette.Also, add the Data Connection Controls object from the Connecting a Form to a Database sample to this subform making the proper adjustments for the data connection name in each buttons Click event script and make this subform invisible.At this point, you should have a form which displays a list of categories and contains an invisible subform.Filtering Records Displayed by the movie.Data Subform. Finally, in the Data Drop Down Lists Change event, write a script which sets the SQL statement used by the Movies.In. Cat data connection, opens the connection and displays the movie.Data subform. For this sample, I chose to use Form.Microsoft Data Link Error Initializing Provider Mysql Odbc' title='Microsoft Data Link Error Initializing Provider Mysql Odbc' />Calc to script this event.First, get the category selected by the user and determine its associated ID var s.Category. Name xfa.Text. var s. Category.Id. bound. Items.Category. NameGiven the XML structure of the lt source.BMxpl.png' alt='Microsoft Data Link Error Initializing Provider Mysql Odbc' title='Microsoft Data Link Error Initializing Provider Mysql Odbc' />Set packet displayed above, you first set the querys command type to text xfa.Set. Movies. In. Cat.Type textThis ensures that the data connection will use an SQL statement.Note the pound prefix to the command property of the Movies.In. Cat node. Then, set the SQL statement, on the Movies.In. Cat data connection, which will filter the records from the movie table in order to show only those that belong to the selected category xfa.Set. Movies. In. Cat.SELECT title, show.Time FROM movies WHERE category.Id. s. Category. Id, ORDER BY title Finally, open the data connection, move to the first record and show the invisible subform xfa.Set. Movies. In. Cat.Set. Movies. In. Cat.Data. presence visibleOpening the data connection will cause the explicit bindings you set earlier on the fields in the movie.Data subform pertaining to the movie title and show time data to be used in order to load data from the xfa.Movies. In. Cat record which will now contain the data from the first record of the Movies.In. Cat 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 zipMinimum Requirements Designer 7.Acrobat Standard 7.Use the Form. Builder.DB2. 00. 60. 92. 9.ODBC Connection named Form.Builder. DB and load the form.Update for DesignerAcrobat 8.If youre attempting to reproduce this sample or something similar in your own forms using Designer and Acrobat 8.Acrobat 8. 0. This is due to new restrictions imposed on modifying data connections at run time in XFA 2.Updated February 6, 2.Posted by Stefan Cameron on September 2.Filed under Data Binding,Scripting,Tutorials.Both comments and pings are currently closed.