The use of variables in a SQL statement in a InfoPath form

Steynsk
Steynsk used Ask the Experts™
on
Hi Experts,

In the first part of our InfoPath 2003 form we ask the user to select his department. Once chosen the form has to load and show a second selection box in which the user can select half finished forms from only his department that are stored into our database.  To do so we need a way to modify the SQL select statement based on the earlier selected department.
Is it possible, as it is in ASP and ASP.net, to query a database using variables that are loaded with values within the form?

It if so what is the syntax to call a variable in the SQL statement?
We don't use codesigning and work in infopath 2003

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Clay FoxDirector of Business Integration

Commented:
You are referring to a cascading dropdown. Where the second dropdown is filtered by the first.  

The easiest way is to create a receive data connection to your data which has the department and outstanding form ID's you want for the selection process.

Setup your first dropdown to show the department.
Then the second dropdown to show the ID's but put a filter (button is on the lower left when selecting the fields) and make it where department in the list matches department value of dropdown field 1.

Then put a rule on dropdown 2 that move the selected value to your query parameter field and then query for just that form.

www.infopathdev.com is a good resource too.

Author

Commented:
Hi Clayfox,

I don't think you understand. The SQL and the use of the earlier chosen department is the problem...
Clay FoxDirector of Business Integration

Commented:
Is this a database type form?

Can you query and submit a record successfully as a test?
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
No the form first asks the user what department he is from. Lets say he picks "Sales" Then the second part of the form opens the with list populated by this query:

Select field1, field2, field3 from mytable where department = 'sales'

But this query only knows the department when the user has selected. In classic ASP I would use:

Select field1, field2, field3 from mytable where department = ' & departmentvariable & '

Can I do this in I.F.? And what would be the syntax

Director of Business Integration
Commented:
The issue you will run into is that Data Conenctions in InfoPath are static, you cannot make them dynamic.

2 options, use code possibly, since the data connection can be executed in code.

Or if you do not have too many departments, create one for each that is hardcoded and then just pick that one.

Or you need to move to webservices, or you a database type form with ODBC, etc.

Author

Commented:
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial