Storing data in SQL-connection problem

[Former Community Member]

hello,



I'm a new user with pdf and not much of a programmer. I've built a fillable form and added some buttons to email/print. I've set up a data connection with sql. The connection works, I can bind the fields.



Now what i'm looking to do is when the form is opened, it's blank. After the form is completed by the user, they can hit a button to store the data into sql. I'm not interested in pulling data up from sql in the form, I strictly want to store the data once they hit a button.



From reviewing these forums, I believe I need to open the connection, then store the data. I probably want to keep the connection open to allow the user to make edits prior to closing the file, then close the connection before closing the file.



I've added the following code on the click of a button:



connect = ADBC.newConnection("Intranet");

statement = connect.newStatement();



var query = 'INSERT INTO HR_DisciplinaryWarning ( employeename, warningtype, suspensiondays, EmployeePosition) values ("employeename", "warningtype", "suspensiondays", "EmployeePosition")';



statement.execute(query);



When I try this I get an error...ADBC is not defined.



Any help is greatly appreciated!

[Former Community Member]

James,



While I don't recommend using ADBC for connecting to a database from an XFA form running in Acrobat, I believe you're running into the same problem others have been with their forms in Acrobat 8 because ADBC is
disabled by default.



Please see
the comment I posted on my blog in regards to this issue. It details how to enable ADBC on your system.



As an alternative, I suggest you use Designer to setup a
data connection for pushing data into your database. I can elaborate on this if you'd like to know more.



Stefan

Adobe Systems



More answers on
FormBuilder.

[Former Community Member]

Thank you Stefan.



Here is what I'm trying to accomplish. I have a form that I want users to pull up (it should be blank when they do), enter some data then email it to a rep at corporate. I then want the corporate rep to be able to open the form with the data that the user entered, make changes if necessary, then save/store the data in sql when they hit a button.



Here is where I'm at. I have the form, I have a data connection set up and the fields are bound to the form. I can see the data that is currently in my sql table. I can use

xfa.sourceSet.Intranet.addnew() (to create a blank record)

xfa.sourceSet.Intranet.update() (to store the data after it's entered)



The form if emailed after entering data does not retain entered data if the corp rep that opens it has access rights to the dataconnection, instead it shows an existing record from the database (I assume because the connection is open and the fields are bound)



So I obviously still have work to do. What I need now is to do the following:



1) open the form without connecting to the sql initially for the first user who enters the data (I never need the form to pull up data that already exists in the sql table)



2) allow the corp rep to open the form without the entered data being overwritten by the existing data in the sql table.



3) connect and append the data once the corp rep hits a button.



Could you maybe point me in the right direction with this?



Thank you!

[Former Community Member]

James,



It sounds like an interesting project.



I would suggest starting with creating the form as the user would use it to enter the initial data.



Second, define a data connection to insert data into a particular database and add a button to the form which, when clicked, will execute the data connection and insert a new record into the database using the data supplied by the user. To make this even better, I would make the button initially invisible and would use a hidden text field to trigger its appearance: In the text field's "Form:Ready" event, if its value is not empty, set the button's presence to be visible. This way, when the user fills-in the form and submits it, they don't see the button but once the data is imported into the form by the Corp Rep, the button appears so that they can save the data to the database. To create the button's script, you'll need to read the article I just posted to my blog which demonstrates
how to insert, update and delete records in a database. You'll also need to generate the "INSERT" SQL statement pulling-in the necessary values from the correct fields on the form.



Third, to ensure that the hidden text field's value is set prior to the user submitting the form's data in the first step, you should use what I call the
two button submit technique. When the user clicks the visible (regular) button, the Click event sets the value of the hidden text field to some value of your choice and then executes the invisible submit button's Click event, causing the form's data to be submitted with the user's supplied values and a value specified for the hidden text field.



Fourth, when the Corp Rep receives the form's data in XML format via email, they would open the form and import the data into it. Since the hidden text field's "Form:Ready" event will be fired after data is imported, the "insert into database" button will appear (since the text field's value will be set to something other than empty from the imported data).



Finally, the Corp Rep can make any necessary adjustments when then click the "insert" or "save to database" button to create the new record in the database.



Stefan

Adobe Systems



More answers on
FormBuilder.

[Former Community Member]

great information Stefan, especially the insert/update article on your blog.



I've got the code in and I get an error when trying to Insert...



TypeError: Database.ExecSQL is not a function



I have the Database code in the variables so I'm unsure what the problem is. Any suggestions are welcome but i'm still trying to find it.



Thanks again!

[Former Community Member]

James,



I'm glad to hear you found my blog sample useful.



The times I've seen that error occur, it was usually because I had a syntax error in the function within the script object that I was trying to call (in this case, you're trying to call the "ExecSQL" function inside the Database script object).



First, please make sure that you've copied-over the Database script object defined in the
sample form. Then, if you've modified the script object, make sure there are no syntax errors and if you didn't place it under the root subform, either move it there using the Hierarchy palette or specify the correct expression to access it from the object that's calling it.



Stefan

Adobe Systems



More answers on
FormBuilder.

[Former Community Member]

I recopied the script. Changed the connection name, and modified the insert statement to what I needed and it works! My data is all storing.



Now I'll work on the two button submit and see how that goes.



Thanks again Stefan, you've been a great help!