How to insert data into a Database?

[Former Community Member]

Dear All,



I am starting to become mad, Designer... Ahhhhhhh!



Ok, I have a great form with a Database connection, this connection has to be used for inserting and recovering data, I mean, once the customer open the pdf it tries to recover the data from the specified table (this works), once the customer modifies the data and close the pdf I attempt to insert the new data into the DB (just an insert! no more I promise)



My problem is, the first select * from... works perfectly:

...

DB.resolveNode("#command").query.commandType = 'text';

DB.resolveNode("#command").query.select.value = 'Select * from T2';

DB.open();

...



But when I try to insert the data (I have to perform several insert sentences) I get an error when I try to open the database. This is my code:

...

DB = xfa.sourceSet.ora1;

record = xfa.record.ora1;

DB.resolveNode("#command").query.commandType = 'text';

DB.resolveNode("#command").query.select.value = 'insert into t2 values (\'hello\',\'hello\')' ;

DB.resolveNode("#command").query.recordSet.setAttribute("stayBOF", "bofAction");

DB.resolveNode("#command").query.recordSet.setAttribute("stayEOF", "eofAction");

DB.open();

...



When the designer tries to open the database an error happen, but amazingly it inserts the data and after that show me this error:

GeneralError: Operation Error.

XFAObject.open:20:XFA:Formulario1[0].#subform[0].Botón1[1]:click

Adobe operation failed. The collection element that belongs to the name or the ordinal requested could not been found

(this is the english translation of the spanish error)



I have created a blank form, and the same error was shown. Also I have executed the insert directly in the database just to be sure and works perfectly.



Does anybody known whats wrong? Why the .open() fail after inserting the row? Does anybody has a simple sample?



Searching in the Forum I have found another way to do it, it is linking the database table fields to fields in the pdf and use the .addnew() method. So I have created the fields, I have linked it and created a button with the addnew() button. I write in the fields and click on the Button.... but nothing happens. Another time, what am I doing wrong here?



Any help will be apreciated, in two days I have to present it to my customers...



Thanks in Advance.

Ruben

[Former Community Member]

Hi Ruben,



There are a few different ways to access the databases from Designer. I'm still really new with this stuff, but I'll help with what I can. What type of database are you using (Access, Oracle, etc.)?



I have a sample using an ODBC Data Connection to a Microsoft Access database.



I also have another sample that scripts the data connection.



Lynne

[Former Community Member]

Lynne,

Could you please send me the sample using an ODBC Data connection to a Microsoft Access database?  I have been searching everywhere for this.

My email address is gmoran@ct.metrocast.net

Thanks,

Gail

[Former Community Member]

Hi Lynne

Please could you forward your example for writing to a database to me at gspaull@moog.com

This could help me put an end to a long running project.

Thank you in advance.

Kind Regards

Graham

[Former Community Member]

Hi Lynee,



I am using an Oracle DB, I have created the Data Connection linked to a ODBC so it will be no problem to change the DB.



Could you send me your samples please? It would be great. I have a dummy email marauder@mixmail.com you can send it here.



Meanwhile, following the european timetable, I wil go on searching...



Thanks a lot.

[Former Community Member]

Hi there,



I'm using Ms Access as my database..But i still can't figure out how to insert the data from the form to database..



Can anyone help me..I will really appreciate your help..thanx..

[Former Community Member]

I've got a sample file that imports data into an MS Access database that I can send to you.



One thing you have to watch out for with Access databases is that if you are going to write to a table that contains a primary key that is set to autonumber, you'll have to use the older ADBC connection method to connect to the database, rather than the built in data binding functionality in Designer. All other commands through the database will work okay with the data bindings, but you just can't write new data.



LHigbee

[Former Community Member]

Ruben,



LHigbee is correct in pointing-out that you'll run into problems when attempting to push data into a table which has an auto-increment column (of any value type) however it is definitely possible to get around this using a data connection in Designer.



The key lies in the way the data connection is created: First, you select "ODBC" as the data source for the connection. The next screen, in the New Data Connection wizard, will be titled
OLEDB Connection. This is where you'll specify the Connection String to the ODBC data source you've setup. Once you've done that, you then have 3 options:

• 
  
  
  


• 
  Table: This option lets you pick a table in the data source by name. If you pick one that has an auto-increment column, you'll run into problems when attempting to push (insert) data into the table via this data connection.


• 
  
  
  


• 
  Store Procedure: This lets you create the connection with fields representing the parameters of a stored procedure. I don't think this is applicable in your case.


• 
  
  
  


• 
  SQL Query: This option lets you specify an SQL query into the data connection to select the records that the data connection should read/write. With this option, you can use an SQL query to select all columns from a table
  except the auto-increment column.
  This is the option you need to use.


• 
  
  
  

By using the SQL Query option when creating the data connection, you shouldn't have any problems pushing data into the database via the data connection (using the addNew method, for example) because Designer won't attempt to write to the auto-increment column as part of the insertion of the new record.



Give this a try and please let me know if you have any questions.



Stefan

Adobe Systems



For more on Designer, check-out my
FormBuilder Blog.

Have you registered for
Adobe MAX 2006?

[mmgiath]

Hi Stefan,

With a ODBC Microsoft Access setup I try follow your sample(selecting specific Database record) to

make it work for my needs...

I think I am almost there but I think something wrong with the SQL statement for the Main2 connection..

When I pick a department from the drop down list does not populate the required data!

What I am doing wrong?

Please HELP!!!!

Thanks

[Former Community Member]

Hi LHigBee,



Will you be able to send that sample file that imports data to MS access? I am facing hardtime trying to insert data into MS Access.

[Former Community Member]

Newbie07:



Yes, I can send a sample if you'd like. Please specify your email address. I am intrigued by the information that Stefan has posted, and suspect that in many (if not most) cases, the method he has outlined would be preferable over the method I used in the sample file.



Stefan:



I am so glad you posted that! I hadn't thought about going that route. One question - could that connection method still be used if I need to write a new record (SQL INSERT) to a table in MS Access, where the primary key of that table is AutoNumber?

[Former Community Member]

Hi

Can youe send me the sample of how to get form data to an MS Access database also?

bibek.prajapati@gmail.com

[cubixguy77]

On the off chance that anyone is still looking at this thread, I'd love the sample form that imports data to ms access as well.

ras12 AT albion.edu

[Former Community Member]

Thank you so very much.



My email address is alwayscurious07@gmail.com



Thanks again.

[Former Community Member]

LHigBee,



I'm glad it's proving useful to you. Yes, you can use that same data connection to read and write values in the database it connects to but you can't specify an SQL INSERT statement as the SQL Query for the data connection. You have to use methods like
addNew and
update on the data connection in order to add records to and update records in the database.



Stefan

Adobe Systems



For more on Designer, check-out my
FormBuilder Blog.

Have you registered for
Adobe MAX 2006?

[vinaym41351813]

Hi Stephan ,

I am stuck in a situation where I am trying to Apply these DB operations using SQL server 2012.

I have created a DSN to access my database and I have created a simple form to store and fetch First name and Last Name of employee. I have created a Data Connection and able to fetch Records from database. I am only getting the first record from my database which is shown automatically on my binded form fields and I am using the following the Scripts to navigate to other records by applying click action to buttons.

xfa.sourceSet.ApplicationData.next(); // Application Data is my Data Connection Name

xfa.sourceSet.ApplicationData.addNew(); // To Add new record.

Both these scripts are not working and not returning anything using client JavaScript.  Please also see the attached form for more Info.

As I am using Trial version of Livecycle Designer ES4, so is it some limitation or am I missing something . Is it an issue of reader extension .

please let me know ASAP.

Thanks,

Vinay Mahajan

email :- vinay.mahajan25@gmail.com

[Former Community Member]

Why is there so less litreture on this topic in Adobe help:(...? I found one sample on Adobe website (purchase.mdb,purchase.pdf). It is very unstable - it just works half the time or maybe less.

[Former Community Member]

Newbie07,



You say the sample only works part of the time. What problems are you running into?



As far as documentation on data binding and data connections is concerned, I agree it's an area where we're lacking however there are still some sources of information that can provide some help (aside from these forums and a few blogs like
FormBuilder and
SteveX). One of the best ones I know of is the
Adobe XML Form Object Model Reference (in the XML section). It does a good job at giving you an overview of how things work and goes into some details.



Stefan

Adobe Systems



For more on Designer, check-out my
FormBuilder Blog.

Have you registered for
Adobe MAX 2006?

[Former Community Member]

Hi Stephan,



I think the MS Access DB gets locked. It will work



1)if I leave the application for more than 10-15 mins and try selecting the record.

2)if I repair the DSN going into Administrative tools.



I was playing with it the whole day....



The error that I get if I try it immediately without doing the above steps



"Error:Connect operation failed.Unspecified error."

[Former Community Member]

Also are there any books on this topic. TIA

[Former Community Member]

Newbie07,



Thanks for the feedback on the sample. I'll do some investigating based on your instructions.



As for books, I'm not aware of any that Adobe publishes at the moment (unfortunately).



Stefan

Adobe Systems



For more on Designer, check-out my
FormBuilder Blog.

Have you registered for
Adobe MAX 2006?