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]

Thanks Stephan. Please let me know if you have any update on this issue. Thanks.



Hi LHigBee,



Please send me the sample on alwayscurious07@gmail.com when you have a chance. Thanks again.

[Former Community Member]

Hi:



I was having the same problems trying to commit data back to a data file in access.



I was wondering if you would be able to send me the sample talked about in this blog or any other samples on updating/writing data to files.



Thanks,



Dwight Beech

beechd@stf.sk.ca

[Former Community Member]

Dwight,



Does your Microsoft Access database table have an auto-incremented column? If so, this will get you into trouble when you try to write data to the database via the data connection because Acrobat will attempt to set a value for that column and the database will throw an exception stating that values can't be set for auto-incremented column data types.



Have a look at the
Connecting a Form to a Database article on my blog. It gives detailed instructions on how to create a data connection to a table in a database using Designer.



Stefan

Adobe Systems



Learn how to
build intelligent forms using Adobe LiveCycle Designer.

Have you registered for
Adobe MAX 2006?

[Former Community Member]

Ok I've been driving myself crazy to store data from a pdf into sql, but i'm not much of a java programmer so it's been a pain.



I know the connection works because the pdf will show the data that already exists in the table. What I want to do is when data is entered and the user hits a button, it inserts that data into the sql table. Can anyone give me some example code for this? I even tried using Ruben's code and tweaking it but I just can't get it to insert the data.



any help would be appreciated!

[Former Community Member]

I've started my own thread so ignore my question here.

[Former Community Member]

Newbie07,



I'm sorry for taking so long to get back to you on the subject of the Purchase Order sample that you were
having trouble with.



I tried reproducing the behaviour in Acrobat 8.0 and wasn't able to. After 20 minutes of inactivity, I was still able to change the record ID and load the information from the data base.



If there was an issue, it's possible it was fixed in Acrobat 8.0. Otherwise, you may have been having problems with your Microsoft Access ODBC Driver.



Stefan

Adobe Systems



More answers on
FormBuilder.

[Former Community Member]

Hi,

Can I use LiveCycle Designer 8.0 with MySQL server 4.1?



Thanks.



Luke

[Former Community Member]

Luke,



Yes, you can. I currently use data connections to MySQL Server 4.1 databases in my forms (built using Designer 8.0 as well as Designer 7.1). As long as you have an ODBC driver, it doesn't really matter what kind of database is sitting behind it. For MySQL, you'll need the
MySQL ODBC Driver.



Stefan

Adobe Systems



More answers on
FormBuilder.

[Former Community Member]

Hi Stefan,



Thanks for your answer.

I have another question, if you will be kind to answer me. Can I use SQL statements in LiveCycle? I saw that the only script available in LiveCycle is FormCalc and JavaScript. I know SQL, but I don't know JavaScript.



On this forum, I open a topic here named "LiveCycle Designer vs. MySQL 4.1", in hope someone will answer to me.



Thanks.

[Former Community Member]

Luke,



You may only use SQL statements when
modifying the query associated with a data connection. Otherwise, all scripts in XFA forms must be written either in FormCalc or JavaScript.



You should have a look at Adobe's recently-published guide on
Scripting Basics for Designer 8.0. It should get you going with both FormCalc and JavaScript.



Stefan

Adobe Systems



More answers on
FormBuilder.

[Former Community Member]

Hi Stefan,



Thanks very much.



Luke

[Former Community Member]

Hello. I have been trying to do the same thing and I still can't figure it out. Would you be so kind to send me the sample database, as well? My email is: tgajewski@ellsworth.com.



Thanks so much!

[Former Community Member]

LHigbee,



I'm interested in your sample file as well (form data to MS Access database). My e-mail address should be in my profile. Thanks.



Derek

[Former Community Member]

Hello,



Has anyone found an answer to this? I am using Acrobat 8. My form works fine with selects, but I get the same error when trying to update a record in my db. The record updates, but I get the same error.



Note: when I change the 'select' to 'update' I get the error "Index value is out of bounds" and it doesn't update at all.



var cDC = xfa.sourceSet.AllFields.clone(1);

cDC.resolveNode("#command").select.commandType = "text";

cDC.resolveNode("#command").query.select.nodes.item(0).value = ("UPDATE VendorMaster SET VENDOR = 'Maxwell' WHERE VID = '1'");

cDC.open();

cDC.close();

[Former Community Member]

chrissurtees,



Unfortunately, I don't think using something other than a "SELECT" statement in a data connection's query is as straight-forward as you would like it to be: It's possible the error is being caused by bindings defined in the data connection which Acrobat attempts to resolve against the query you've specified but fails to do so because an "UPDATE" statement doesn't return any records.



In order to get your "UPDATE" statement to execute properly, you should "clean" the data connection prior to opening it by removing any bindings that might be defined in it. Note that you should do this on a separate data connection from the one being used to bind to fields on your form.



Have a look at the script object in the sample form I posted on my article on
inserting, updating and deleting records in a database for an example on how to do this.



Stefan

Adobe Systems



More answers on
FormBuilder.

[Former Community Member]

Hello out there...



I have made a form from an existing PDF file. The file happens to be a map. What I am trying to do is allow the customer to comment on the map and then submit that to an MS Access Database on a webserver. I have created the data connection just like the tutorial suggests...http://blogs.adobe.com/formbuilder/2006/09/connecting_form_to_database.html



Using only one field within the one table that I am attempting to insert the document as a pdf file. The submit button is bound to that single field in a table.



Does anyone have any idea how to script this submit button or the data connection so that it will INSERT INTO the database directly without going through a cgi-script?



I am attempting to use a button configured as submit, But this is not working...does anyone have any suggestions? Does anyone have a sample form similar to this????

[Former Community Member]

hi



I have created two dataconnection where i call it dataconnection (table: user1) and dataconnection2 (table:staff).

Dataconnection is for me to get data from database and appear on the form. As for dataconnection2 , it is for me to insert data to the database.



I type in this code :



----- form1.Page2.Submit::click: - (JavaScript, client) --------------------------------------------



xfa.sourceSet.DataConnection2.open();

xfa.sourceSet.DataConnection2.addNew();



$record.DataConnection2.PV_No.value = PVNo.rawValue;

$record.DataConnection2.Requestor.value = Requestor.rawValue;

$record.DataConnection2.Ref_No.value = RefNo.rawValue;



xfa.sourceSet.DataConnection2.update();

xfa.sourceSet.DataConnection2.close();



at the submit button, but it didn't insert into the database. May i know the reason behind it?



thanks

[Former Community Member]

Can any one send me the sample also?

jrock87@gmail.com

thanks...

[Former Community Member]

I have been reading of the struggle with inserting data into Access Databases while experiencing the same difficulties. I have created a datasource using an SQL statemeent, exluding the autonumbering field. I have even went so far as removing the field altogether to eliminate it as a root cause. I have created a button behind which I use:



xfa.sourceSet.Orders.addNew()



Yet when I test, one two things always happens. 1.) No errors, but no inserts into the database either. 2.) An error stating that I am attempting to insert empty rows. I am truly at the end of my rope. I have read of so many others having the same problem, yet still not seeing the solution. I know my database connection is good because my fields are reading the data that exists in the database. For something that doesn't seem that difficult, it sure is causing major headaches! Any help would be greatly appreciated.

[Former Community Member]

Hi,



I was hoping to get a sample Access Database and pdf file using SQL to insert into the database. If anyone can send it to citizenarcane@comcast.net it would be appreciated. Thanks...

[Former Community Member]

I made a form that my clients can use with AdobeReader8 to create barcoded labels and Submit by Email. But to make this procedure more solid I have to publish this form to our intranet and have it automatically update an access database everytime my users fill the form.

So far I managed to insert TextField1 value into a connected access database. The problem is the TextField1 is in a repeatable subform. So only the first instance of TextField1 is written into the attached database. I would like it to read each instances of TextField1 values and write each values in different rows into the access database. I am a beginner and I have scoured the web for any idea but to no avail. I already climbed a mountain to learn this program and build this form from scratch and I'm exhausted. Please help!