InfoPath Dev

This is a desktop form (not web form) using Infopath 2010.

1. I'd like the user to enter an id number, click next, and then query the database1 for that id and populate 4 fields.

2. Then just have the user verify the data and then have them click Submit data button to insert those 4 fields into a different database2.

Right now, they separately look up the information in our custom app and then type it into 4 textbox fields and click submit to enter it into database2. We would like the above method so we can avoid data entry typos and errors.

edit: or another option is have a textbox and then once they click a button, have the form run a query based on the ID entered to insert the data.

The issue is going to be with item 1 - InfoPath secondary data connections to SQL don't allow you to pass in a value from the form natively. This leaves you with some options:

1. Write your own code to do this - you'll need to alter the command of your secondary data connection - here's an MSDN page with a sample.
2. Write your own web service to do this - again, this involves writing your own code.
3. Purchase a web service - Qdabra's DBXL can do this kind of query - more info here. (Free trial available)
4. Purchase qRules - it has a command for modifying a SQL query - more info here. (Free trial available)
5. Return all rows from database1 and use filters for populating your fields based on the ID your user selects. If database 1 is small, this may be an acceptable solution for you - this is an article on add filters that may give you an idea of how to do this - you'd need rules on the field where the user enters the ID to set your other 4 fields based on the data from database 1 having the same ID as that the user entered. If this is an option for you, I can put together a simple sample for you that may help as well.

Hope that helps!