Populating fields from an Access Database

[Former Community Member]

I found an example (purchase.pdf) that connected to an Access Database and through a drop down populated fields. But the problem that i have is that as soon as i try to change the search field from ID to say products, the code does not work. What I am looking to do is have a user input data into a Text Field and on exit, search a database based on that data and populate fields. say... "Select * from customers Where product = " this.rawValue;



This is the working code from the exaple, but, it will not work when I change ID to Products.



if (Len(Ltrim(Rtrim(SelectField.rawValue))) > 0) then



$sourceSet.DataConnection2.#command.query.commandType = "text"



$sourceSet.DataConnection2.#command.query.select.nodes.item(0).value = Concat("Select * from customers Where ID = ", Ltrim(Rtrim(SelectField.rawValue)) ,"")



$sourceSet.DataConnection2.open()

endif

[Former Community Member]

Is the rawValue a string value or numeric value? If it's a string value then you have to put it inside single quotes ('). If that's not the issue, do you get any exceptions in the JavaScript console (hit CTRL-J)? If you run your query directly in the database does it work?



Chris

Adobe Enterprise Developer Support

[Former Community Member]

so where would i drop the single quotes in this statement?



Concat("Select * from products Where prodID = ", Ltrim(Rtrim(prodID.rawValue) ,"")



statement does work if I hard code the string in.

[Former Community Member]

Try this:



Concat("Select * from products Where prodID = '", Ltrim(Rtrim(prodID.rawValue) ,"'")



Note the added single quotes around prodID.



Note that you should be very careful about validating that prodID is a number (or whatever you expect it to be) or you could be exposing yourself to users being able to do things you don't want them doing to your database, by supplying a prodID that includes an apostrophe (to end the quoted string) and then SQL...



--

SteveX

Adobe Systems

[Former Community Member]

I keep getting this...



Error: syntax error near token '$sourceSet' on line 6 column 11.



I tried the signle quotes, which should work (again, hard coded worked i.e.. Where prodID = 'LC2SP18'") would it be easier to do this in JavaScript and if so, how would I do that?

[Former Community Member]

Wow, that took way to long to get working. Finally worked after I took out the trims and just put in prodID.rawValue. Thank for all the help

[Former Community Member]

Cool, glad to hear you got it working.. although it's strange that you had to take out the trims.



Ltrim(Rtrim("hello")) is the same as "hello".



--

SteveX

Adobe Systems