ODBC Try Number 2

[Former Community Member]

I have a table in data base that has the following columns,



JobTitle

JobNumber

Language

Department

Contact

Production

History



The user of the form that accesses this table in the data base wants to key in a job number that consists of 5 digits and 3 more separated by a space (12345 000) in a text field called LookUp. Then the user clicks a button bring up the record in the data base. Here is the code I have been TRYING to use.



// First make sure the commandType attribute is set to 'text'.

xfa.sourceSet.DataConnection.nodes.item(1).query.setAttribute("text", "commandType");



// Get selected ID.

var myJob = this.parent.resolveNode("LookUp").rawValue;



// Set the query as per item selected.

xfa.sourceSet.DataConnection.nodes.item(1).query.nodes.item(0).value = "Select * from BillsItemList Where JobNumber = " + myJob;



// Open the data connection.

xfa.sourceSet.DataConnection.open();



This is NOT working. I click on the button that this code is attached to the click event and get nothing. I am connected to the data base and all the fields are bound to it.



Can anyone tell by looking at the code above what I am doing wrong?

[Former Community Member]

I should add that all of the data connection examples have to do with drop-downs or list boxes. What if I have a table that has 200 records that show up in the drop-down. That would be awkward to try and scroll through that many in the list in order to find one record. It makes more sense to be able to key in a specific number or even a name.

[Former Community Member]

I think your SQL query is wrong. I'm assuming that JobNumber in the database is a text data type, since 12345 000 would be an invalid value for a number data type. So you'll need to add '' around the value in myJob. Something like this:



"Select * FROM BillsItemList WHERE JobNumber = '" + myJob + "';"



Chris

Adobe Enterprise Developer Support

[Former Community Member]

Eureka! Finally that did it. Of course it would be something simple like that. Now I just need to be able to also use my navigation buttons ie. Next, Previous, First, Last, Add New, and Update buttons so a user could cycle through items or key in a specific number.



Do I need to work with opening and closing the connection? I tried to uncheck the Delayed Open and I can still enter text in the field and bring up the record requested but then the navigation buttons don't work.

[Former Community Member]

Well, you'll definately need to have the connection closed before changing the SQL query and then open it after to pull the new record or else nothing will happen.



But if you use a data connection that returns only 1 record then you won't be able to cycle through records using those buttons because there is only 1 record in the record set.



Chris

Adobe Enterprise Developer Support

[Former Community Member]

In still more attempt to learn SQL and how to get Designer data base connections to work. Does that mean that I can use most any SQL language in my JavaScript as long as they are placed inside the right quote marks?



I have been using the W3Schools tutorial to try and learn how to use it and I notice that there is nothing in there about opening and closing the data base so I gather that that part or working with a data base connection is a Designer thing.

[Former Community Member]

Well, opening and closing a database isn't a Designer thing, it's a programming against a database thing. You can't tell a database to do something via SQL until it's opened, and closing it when you're done is just good programming practice.



Did you have something specific in mind when you ask what SQL you can use? For the most part it depends on what the particular database supports.



Chris

Adobe Enterprise Developer Support

[Former Community Member]

Ok. Thanks for the advice about closing a data base when done. You can tell I'm not a programer trying to be one. Anyway could I do this?



SELECT * FROM Persons

WHERE FirstName='Tove'

AND LastName='Svendson'



Seems simple enough.



or if one wanted to join two tables like this.



SELECT Employees.Name, Orders.Product

FROM Employees, Orders

WHERE Employees.Employee_ID=Orders.Employee_ID



BTW I'm working with Access data bases right now but there are other folks in the company who are working with others such as Oracle.

[Former Community Member]

In my example above the last line of code opens the connection. Would I but in a close statement after that?



xfa.sourceSet.DataConnection.close();



Or when is the time and place to put it.

[Former Community Member]

Well, you wouldn't want to close it right after doing the query, because the it needs to be open in case the form needs to communicate changes back to the DB (ie: if you change a value and want that updated in the DB too).



In your case, you want to make sure the DB connection is closed before changing the SQL query so that when you open it it executes the new query and populates the fields with the data from the record set. So you'd do something like (in pseudocode):



try {

dbConn.close();

catch (e) {}

changeQuery();

dbConn.open();



I put the try/catch because the first time you do this the connection will be closed (if you chose the delayed open option) so calling close() will generate an exception.



Hope that helps clear it up. I know it can be confusing.



Chris

Adobe Enterprise Developer Support

[Former Community Member]

Yes this does help to clear it some. And so far I am relieved that this is starting to work.



Another thing I have noticed is that when I set up the file in Designer to connect to the data base and use the line of code,



xfa.sourceSet.DataConnection.next();



then I can click on the button as many times as it takes to get to the end of the records and the very last record in the DB that is not empty is shown. However since I have been working at being able to make a specific query and also to open the connection again and be able to cycle through the records when I click on the next button (that has the same line of code as above and get to the end of the records I end up seeing a blank record which I don't want to see. Why is that happening?

[Former Community Member]

Not sure, I don't get that here. Did you set add new as the EOF action? That would do it.



Chris

Adobe Enterprise Developer Support