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??

Thanks! Amanda