Looking up a value in SQL to set a value on the InfoPath Form

Question

Looking up a value in SQL to set a value on the InfoPath Form.

I have a data connection setup in my form. If I use this connection alone, in a control by itself, it pulls in data from my SQL server. What I’d like to use this connection for is to set a filed on my form by querying the data connection using a another filed from my form. For example, I have a vender number on my form, I’d like to use the value typed in this field to look up the vendor name and put into another control. This is a one to one relationship.

I have added a run using on change and then also by adding button on click, to update the field with the value from the data connection.

So my rule is:

Set a field’s Value Field VendorName (on the infopath form) Value: @VENDOR_NAME[@VENDOR_ID = VendorNumber]

@Vendor_Name is the database name where the Vendor name is saved. The filter is @vendor_id = VendorNumber. Vendor _ID is in the SQL Database, and VendorNumber is the info path field where my user enters the value.

I think this the way it is supposed to work, but I’ve tried a 100 other methods too, and they don’t work either. Is there something I’m doing wrong? Even if I hard code the VendorNumber so the vaule is @VENDOR_NAME[@VENDOR_ID = string(0004)] I cannot get a result. Please help… What am I doing wrong??

Answer 1

First thing I'd try would be to load up SQL profiler to see if infopath is actually connecting to your sql database.

Once you know for sure that the form can actually 'see' the database and login successfully etc you'll have more options on where to look next.