Expand my Community achievements bar.

Dive into Adobe Summit 2024! Explore curated list of AEM sessions & labs, register, connect with experts, ask questions, engage, and share insights. Don't miss the excitement.

Update and Insert Into query into Access DB

Avatar

Level 2

I have seen examples (and have them working) of the select * queries with Access to prepopulate a form.

My question is is it possible to use a data connection with an update or INSERT INTO query

I have seen an example on PDF Scripting with hidden fields and connecting to the Database, then update the hidden fields to update the database

I see how this would work, but means I need to have 2x the data fields in my form. To get away from this, I was hoping to have another option.

What I would like to do is have a separate data connection (different from the prepopulate connection that is bound to the Database) set up as a Delayed open Read/Write

Open the database, then execute JS code to update specific fields with

ie:

UPDATE TEST SET TEST.WriterName = "New Writer Name Test" WHERE ((TEST.ClientID.value)=1001 AND (TEST.YrRef.value)=2010);
or

var myNewName = "New Writer Name Test";

UPDATE TEST SET TEST.WriterName.value = myNewName WHERE ((TEST.ClientID.value)=1001 AND (TEST.YrRef.value)=2010);

Is this possible, does anyone have an example that I can be pointed to or provided?

The above would be for the update clause, then if say the year does not exist, I would use the Insert query to first add a new record, then update the fields with the update clause. So if it is possible, looking for the Insert Into option too.

Many Thanks in advance

7 Replies

Avatar

Former Community Member

Yes you can do that. You can configure the data connection to execute a SQL statement so that SQL statement can be anything you want. Stefan Cameran has an example where he is retrieing a specific record (after the user selects an item from a DDList to indicate which record to get). That technique uses a SQL statement so you could use that code and simply update the SQL statement to do what you want.

Here is a link to Stefan's blog entry:

http://forms.stefcameron.com/2006/09/29/selecting-specific-database-records/

Hope that helps

Paul

Avatar

Level 2

Unless I am missing something, your link provides a sample on how to use a select  query

ie: SELECT id as catId, name AS catName FROM movie_categories GROUP BY name ORDER BY name;

I have the SELECT query  working using the WHERE clause to get a spcific record. Works great.

I use this to pre-populate the form. Once I have the forms edited via data input from the users, they send the forms back to an administrator who has the ODBC connection and this admin person will update the database. I would prefer NOT to have invisible fields bound to the database, which is the sample in the PDFScripting website (A great source of info for users by the way).

what I am looking for is a sample on how to (if possible to do) use an Insert into (if need a new record) and the Update queries (to update existing records) with the specific data.

So my question is not how to use a SELECT query, but how to use the other 2 queries.

Avatar

Former Community Member

Right ....the sample shows how to add a field reference (invisible or not it does not matter) into a SQL statement. it is the technique that I was referring to not the exact statement. You woudl have to duplicate that technique when building your Insert statement. So something like:

INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...)

where value1, value2 and value3 are replaced by field references.....like Field1.rawValue. The script can get quit complicated as you are building a string that contains the command but it is possible. I suggest building it up in a variable then writing that var to the screen (that way you can check if the SQL sytax is correct). Once you have the syntax right you can remove the writing to the screen.

Hope that helps

Paul

Avatar

Level 2

Yes, this is what I am looking for.

Where can I find the syntax (or an example) for this?

Avatar

Former Community Member

I do not have one .....sorry. Why not try and build it yourself .....it is just an exercise in string manipulation.

Paul

Avatar

Level 2

I have been trying to build one.

But if I do not know the exact syntax options, it becomes difficult :-)

If I knew where to look for the documentation for this code, then the building of a sample becomes much easier.

Ken

Avatar

Former Community Member

This is where it gets difficult for documentation. You are using Javascript to build a SQL statement. Neither are adobe technologies so you can do web searches for the proper syntax of the Insert statement and you can use web searches for the javascript synatx to manipulate strings. If you follow the example of how Stefan built his SQL statement yours will be very similar.

Hope that helps

Paul