Select specific Data from ODBC SQL Data Connection

[#1]

Hi,

so here is my Problem.

I created a Form with a DataConneciton (ODBC and an SQL Qerry).

Tried the "normal" next,first,add etc. Buttons and everthing works fine.

How can i navigate to/select a specific data record?

I want a Textbox/field where i insert the Customer number and the adressfields, fistname lastname etc. is filled automaticly.

some kind of "select * from NameOfTabel where CustomerID like NamedTextField.rawValue"

I've read a lot about filling drop down fields or listboxes, but this is not practical for me because of the large data amount. (tried it, Adobe crash CPU@100% etc)

thanks for your help and excuse my poor language skills.....

Christian

[#2]

Hi Christian,

Not something I've tried for some years, can't remember much of the process, but think I followed the instructions in this blog

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

Pretty old but don't think it would have changed.

Regards

Bruce

[#3]

So the answer to this specific question posted above, was to keep the field bound, then setting the value of the bound <textfield1>  to = 'null' at the start of the exit event.  The query 'SELECT UserID FROM FormsData WHERE UserID = 'DDuck', would return nothing not 'null'.  So by inference, setting the <textfield1> to 'null' before hand would ensure that the IF statement later on could evaluate if <textfield1> was null or returned something as != 'null'.  This worked like a charm.

I don't however have a good sense on working with variables for Database calls and working in that space.  The main reason is there are times where the Open, Save, Submit (push/pull) real time data bindings in the livecycle UI can be a mess, especially if there are multiples.  Currently with multiple bindings and mixed types, I am getting an unusual error where:

I am not sure if this is a binding string issue or not.  Curiously it commits this error to the last table column that I am attempting to bind. I like the idea of utilizing these standard bindings because, theoretically, they should be able to use the standard sourceSet.<DataConnection>.<SQL Functions, ie Addnew(), Delete(), First(), etc>.  Makes the coding easy.  But in practice, as it typical with LiveCycle Designer what should take an hour takes 14.    At least for the inexperienced.  We'll keep crack'a'lackin'.

Update:28MAY2019,  Looking through, oh 10 year old blogs,   I found this in the comments section of one of Stefan Cameron's blogs for a step back in time:

This is likely the explanation for why when I run app.alert(xfa.record.saveXML("pretty")); xfa.record.updatePassword node isn't represented, because I have another binding already loaded into the sourceSets model.  See below.

-------------

Stefan Cameron on June 22nd, 2009

Justin,

I’m glad to hear you find useful things on my blog!

I believe the problems you’re experiencing are related to the fact that with bindings to database data connections, there can only be one record that is active at any given time. When you do the “addNew()” call, the data in the fields currently bound to nodes in the data connection is set into the current record and a new record is created.

While you can display multiple database records at once in a form, you can’t edit multiple records at the same time.

To do this, I would recommend you read my post on inserting, updating and deleting records in a database. Using this technique, you could have the user add and fill multiple rows in the table and then have a single submit button that they click when they’re done. The submit button’s script would then insert new records into the database, one for each row in the table.

--------------

<me> So, I guess I utilize the .clone() method and open() close() methods to interact with the specific fields on the submit button click.  Brushing up on my SQL I am going to try to utilize some 'INSERT INTO SELECT' syntax and see if that gets me where I need to be. 

[#4]

Hi Bruce,

I have two quick questions about this tutorial if you have a chance to placate my inquiry.

When executing an SQL query such as:  "SELECT UserID FROM FormsData WHERE UserID = 'DDuck'" in a cloned node works fine if I have bound a field <TextField1> to the data connection I am using.  Makes sense.  You reopen the connection, the data connection re-queries the DB and the form remerges the data into the bound (from $record.dataconnectionname.User) object.

A problem I am having is if DDuck isn't a user in the DB.  The query seems to break, the code quits, and I don't get any errors. In SQL, if you were to query the above I might get a return of something like this:

           

I was hoping for a null or something I could test against.

The query is executed but returns no records(if the searched for UserID is not present).  Programatically I can't seem to get past a return like this to evaluate if the data is present or not with Javascript.  There likely is a better SQL statement for the query being presented here which makes this use case not happen, but I can't think of it with my limited SQL skills.  I tried opening the DB with a try/catch statement to see if I could get an error, but I seem to get nothing still.  Do you have any insight on what might be going on?

The second one is quicker I think.  Executing the DB connection with the string above from the cloned  node with the following command:

var dUser = "'DDuck'"

oDB.resolveNode("#command").query.select = "SELECT UserID FROM FormsData WHERE UserID = + dUser" <just an example>

How do I get the return value into a variable.  Does it simply enter into oDB.rawValue?  I don't think it's that simple right?  I think I am a bit frazzled on the subject and could use a pointer.

Thank you for your time.

Alex