Polulate textfield(s) from a MS SQL Database based on selection from DropDown List

[Jeremy_Axe]

I am new to LiveCycle Designer. I am trying to create a Master Bill of Lading form. I want the user to be able to enter a Store Number from a in a text field and, based on what they enter I want to populate the address information from a database on my SQL Server.

When I bind the address fields from the database, I only see the first record in the database.

I'm not sure where to go from here...

Thanks in advance,

Jeremy

[Former Community Member]

There is a desdription of retrieving a specific record from your DB here:

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

Paul

[Jeremy_Axe]

still not getting it!

Paul,

I'm sill not "getting it"

I am rather new to LCD.

Here is my code:

btnClear.MainPage.btnGetInfo::click - (FormCalc, both)

tfResultsTest.rawValue = sStNo.rawValue

xfa.sourceSet.dcStoreData.#command.query.commandType = "text"
xfa.sourceSet.dcStoreData.#command.query.select = concat("SELECT StorNbr, StorName, StreetAddr, City, State, Zip, Phn FROM tblStoreMast_LastVersion WHERE StorNbr =", sStNo.rawValue)

xfa.sourceSet.dcStore.open()

xfa.sourceSet.dcStore.Close()

i get an error stating:

Error: Property: 'commandType' cannot be set because doing so would violate this document's permissions settings.

i am using LiveCycle Designer ES verson 8.2.1.4029.1.523496  Cipher: 128-bit

any help you can provide would be appreciated.

thanks

J

[Former Community Member]

At the bottom of Stefan's blog he discusses this issue ......here is a link to the solution:

http://forms.stefcameron.com/2006/12/07/better-form-design-with-xfa-25/

Paul

[Jeremy_Axe]

Paul,

Silly question:

CODE behind DropDownList:

btnClear.MainPage.sStNo::change - (JavaScript, client)

var oDB = xfa.sourceSet.nodes.item(nIndex).clone(1)

var nIndex = 0

while (oDB.nodes.item(nIndex).className != "command") nIndex++;

oDB.nodes.item(nIndex).query.recordSet.setAttribute("stayBOF", "bofAction");

oDB.nodes.item(nIndex).query.recordSet.setAttribute("stayEOF", "eofAction");

var oDC = xfa.sourceSet.dcStoreData.clone(1)

//tfResultsTest.rawValue = sStNo.rawValue

oDC.#command.query.commandType = "text";

oDC.#command.query.select = concat("SELECT * FROM tblStoreMast_LastVersion WHERE StorNbr =", sStNo.rawValue);

oDC.open();

oDC.Close();

ERROR in JavaScript Debugger:

SyntaxError: illegal character
11:XFA:btnClear[0]:MainPage[0]:sStNo[0]:change

Where am I going wrong?

[Former Community Member]

Two issues ...fist the illegal char ....the # in javascript is not allowed so the #command is the issue. Rewrite the commands to be like this:

xfa.resolveNode("oDC.#command").query.commandType = "text";

xfa.resolveNode("oDC.#command").query.select = concat("SELECT * FROM tblStoreMast_LastVersion WHERE StorNbr =", sStNo.rawValue);

Now the 2nd issue - The rawValue of the DDlist wil not be set until you exit the List so the value you are passing to the sql statement is not going to be correct. I suggest you move your code to the exit event of the DDlist instead of the change event.

Paul

[Jeremy_Axe]

here is my change:

CODE:

btnClear.MainPage.sStNo::exit - (JavaScript, client)

var oDC = xfa.sourceSet.dcStoreData.clone(1);

xfa.resolveNode("oDC.#command").query.commandType = "text";
xfa.resolveNode("oDC.#command").query.select = concat("SELECT * FROM tblStoreMast_LastVersion WHERE StorNbr = ", sStNo.rawValue);

oDC.open();

oDC.Close();

var oDB = xfa.sourceSet.nodes.item(nIndex).clone(1);
var nIndex = 0;
while (oDB.nodes.item(nIndex).className != "command") nIndex++;

oDB.nodes.item(nIndex).query.recordSet.setAttribute("stayBOF", "bofAction");
oDB.nodes.item(nIndex).query.recordSet.setAttribute("stayEOF", "eofAction");

ERROR:

xfa.resolveNode("oDC.#command") is null
3:XFA:btnClear[0]:MainPage[0]:sStNo[0]:exit
TypeError: xfa.resolveNode("oDC.#command") is null
3:XFA:btnClear[0]:MainPage[0]:sStNo[0]:exit

[Former Community Member]

Here is the code I use (javascript) for the cloning operation (oh and this code was on the change event of the DDList). This is assuming that your Dataconnection name is DataConection2.

Paul

var inName = xfa.event.newText;
if (inName == ""){
app.alert("You must enter a valid name - try again!")
}
var nIndex = 0;
while(xfa.sourceSet.nodes.item(nIndex).name != "DataConnection2")
{
nIndex++;
}

var oDB = xfa.sourceSet.nodes.item(nIndex).clone(1); // the node pertaining to the data connection specified
//app.alert(oDB.saveXML("pretty"));

//set up sql call to DB to get specifics about employee

oDB.nodes.item(1).query.setAttribute("text", "commandType");
oDB.nodes.item(1).query.select.nodes.item(0).value = "Select * from table1 where AcctNumber = '" + inName + "'";
//app.alert(oDB.nodes.item(1).saveXML("pretty"));

//now connect to DB and get a record
oDB.open()
oDB.close();

[Jeremy_Axe]

That seemed to fix my error and returned the value to my DDL.

I have modified the code so it looks at the clone of my dataConnection (dcStoreData)

CODE:

btnClear.MainPage.sStNo::change - (JavaScript, client)

var inStrNbr = xfa.event.newText;
if (inStrNbr == ""){
app.alert("You must enter a valid name - try again!") } var nIndex = 0;
while(xfa.sourceSet.nodes.item(nIndex).name != "dcStoreData")
{
nIndex++;
}
tfStoreID.rawValue = inStrNbr;
var oDB = xfa.sourceSet.nodes.item(nIndex).clone(1);
// the node pertaining to the data connection specified
//app.alert(oDB.saveXML("pretty"));
//set up sql call to DB to get specifics about employee
oDB.nodes.item(1).query.setAttribute("text", "commandType");
oDB.nodes.item(1).query.select.nodes.item(0).value = "Select * from tblStoreMast_LastVersion where StorNbr = '" + inStrNbr + "'";
//app.alert(oDB.nodes.item(1).saveXML("pretty"));
//now connect to DB and get a record
oDB.open()
oDB.close();

it runs with no error and I am seeing the value it has given my DDL.rawValue

Now I get the "error:"

GeneralError: Operation failed.
XFAObject.open:16:XFA:btnClear[0]:MainPage[0]:sStNo[0]:change
ado2xfa operation failed.  Item cannot be found in the collection corresponding to the requested name or ordinal.

My Store Number is stored as an int in the database.

Do I have to somehow cast it as a character?

Sorry for the headache!

Jeremy

[Former Community Member]

I do not think that should matter but I am not sure .....can you try modifying your query such that you are asking for a string instead ....just to see if it works?

Paul

[Jeremy_Axe]

that seems to do it.

how then do I link my text fields to the database so the Name, Address, etc... get populated?

is it still in the click event of the DDL?

do the values retrieved from the query get assigned to variables?

[Former Community Member]

In your DataConnection view you can bind the columns to the fields on your form ......now when the values are returned the bindings will put the values in the returned colums into your fields for your. You can set up a binding by simply dragging and dropping the column in the dataview onto the field on your form where you want the data to appear.

Paul

[Jeremy_Axe]

I drug the fields from my DataView panel onto the form but they still do not contain any values.

I see the bind elements in the XML Source tab but they don't seem to be carrying onto the form.

[Former Community Member]

Are you using a single data connection to poulate the dropdown and to get the specific record or are you using two separate connections. If you are

using two connections do you have the delayed open parameter checkbox on in the one that you are using for the specific record?

Paul

[Jeremy_Axe]

Using a single data connection for ddl and txt fields.

Thanks,

Jeremy Axe

Sr. Programmer/Analyst

Outdoor Cap Company, Inc.

479-464-5219

jeremy.axe@outdoorcap.com

http://www.outdoorcap.com

[Former Community Member]

You wil need two connections ...the one you have for the DDL list is fine. The second one uses the same parameters but on the last screen of the connection wizard is a check box for delayed open. Make sure that this is checked on. This indicates that you do not wan the connection to the DB to happen until you say so. Now bind the columns in DataConenction2 to the fields on your form and update your code to point to this connection. Now the connection will not be established until you do an Open() command and the bindings will work correctly.

Paul

[Jeremy_Axe]

Sorry to bother you all day, but it is still not populating the text fields.

btnClear.MainPage.sStNo::change - (JavaScript, client)

var inStrNbr = xfa.event.newText;

if (inStrNbr == "")

{

app.alert("You must enter a valid name - try again!")

}

var nIndex = 0;

while(xfa.sourceSet.nodes.item(nIndex).name != "dcStoreList")

{

nIndex++;

}

tfStoreID.rawValue = inStrNbr;

var sStoreID = tfStoreID.rawValue

var oDB = xfa.sourceSet.nodes.item(nIndex).clone(1);

// the node pertaining to the data connection specified

//app.alert(oDB.saveXML("pretty"));

//set up sql call to DB to get specifics about Store Information

oDB.nodes.item(1).query.setAttribute("text", "commandType");

oDB.nodes.item(1).query.select.nodes.item(0).value = "Select * from tblStoreMast_LastVersion where char(StorNbr) = '" + inStrNbr + "'";

//app.alert(oDB.nodes.item(1).saveXML("pretty"));

//now connect to DB and get a record

oDB.open();

oDB.close();

var oDB2 = xfa.sourceSet.nodes.item(nIndex).clone(1);

// the node pertaining to the data connection specified

//app.alert(oDB2.saveXML("pretty"));

//set up sql call to DB to get specifics about Store Information

oDB2.nodes.item(1).query.setAttribute("text", "commandType");

oDB2.nodes.item(1).query.select.nodes.item(0).value = "Select * from tblStoreMast_LastVersion where char(StorNbr) = '" + inStrNbr + "'";

//app.alert(oDB.nodes.item(1).saveXML("pretty"));

//now connect to DB and get a record

oDB2.open();

oDB2.close();

[Former Community Member]

Can you send me your form so I can see it in context?

You can send it to LiveCycle8@gmail.com ...can you include a link to this thread in th eemail please?

Paul

[Former Community Member]

Can you add a command between the Open and close command .....

app.alert(xfa.record.saveXML("pretty"))

This will dump the returned data top the screen .... I want to verify that something is being returned.

Paul

[Jeremy_Axe]

This dumps an error box on the screen and I see where the value I select from the DDL is added into the SQL statement.

[Jeremy_Axe]

this pops up an error box and I can see where the value i am selecting from the DDL is being added to the SQL statement, but no data is being returned to my text fields.