SQL Join statement not working in InfoPath

zintech
zintech used Ask the Experts™
on
I am using InfoPath to query two different tables.  The SQL syntax itself is fine.  The SQL syntax is:

SELECT PurchOrd.PONbr,PurOrdDet.InvtID from PurchOrd JOIN PurOrdDet ON PurchOrd.PONbr=PurOrdDet.PONbr WHERE PurchOrd.PONbr LIKE '%" + PONbrId + "%' AND PurchOrd.ProjectID LIKE '%" + ProjectId + "%' AND VendName LIKE '%" + VendorNameId + "%' AND PurchOrd.ReqNbr LIKE '%" + PRNbrId + "%' AND PurchOrd.User6 LIKE '%" + ReqId + "%'

When I click the button, all of the fields that are in the PurchOrd table show textboxes, even if I did not select the field data to be shown.  The fields for the PurOrdDet table do not show up, even if I selected them to be shown.  Both tables are in the same datasource.  I have even tried using separate datasources and using a join statement in the SQL but it still does not work.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2011

Commented:
have you tried creating a view of your join?

and using that as the datasource?

Author

Commented:
No I have not.  Which view would I use?
Clay FoxDirector of Business Integration

Commented:
Go into SQL and create a view with your select statement.

Then when adding the datasource in IP, select that view as the table.
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
I created the view like you suggested, clayfox.  I cannot see if it works because every time I create a new data source, and select the view as the datasource, InfoPath crashes and restarts.  It does this anytime I create a new datasource.  I ran Office Diagnostics but that did not find aynthing wrong
Director of Business Integration
Commented:
Have you tried starting a form from scratch?

Also are you creating a data connection using the wizard in InfoPath Designer?
Or are you using Visual Studio or something?

Start simple to verify and troubleshoot.

Author

Commented:
I have encountered this issue in other forms as well, in various forms.  On some forms, it would only lock up all the time when I edited the SQL statement for pulling the data, but work fine if I just selected the fields to pull.  Yeah, I do use the data connection wizard in InfoPath.  

Author

Commented:
I uninstalled and reinstalled Office to see if that was the problem, but it still happened.  I am going to create a whole new form, as the form I am using may be corrupted.

Author

Commented:
I created the new form, and it allows me to add tables and other views as my datasource, just not the one I need to use.  I created the view I was using again from scratch, but InfoPath still gives me the same error.  It locks up and restarts.  I don't know what to do

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