Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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

share|improve this question

1 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.

share|improve this answer
    
I turnered on profiler and I'm very confused. I see the connection being made, the sql being passed is: select "LAST_MODIFIED","VENDOR_ID","VENDOR_NAME","NAME_UNO","VENDTYP_CODE" from "dbo"."APM_VENDOR" as "APM_VENDOR" WHERE 1 = 0 which I don't know what the 1=0 is all about. Then a little further down is the correct select: select "LAST_MODIFIED","VENDOR_ID","VENDOR_NAME","NAME_UNO","VENDTYP_CODE" from "dbo"."APM_VENDOR" as "APM_VENDOR" –  Amanda Porter Dec 6 '11 at 15:08
    
When I get to my field and make a change, I never see it try to connect to the database. Does info path require the data to be stored in the form? Or does this imply, something is wrong with my rule? Can the rule cause the query? –  Amanda Porter Dec 6 '11 at 15:13
    
Sorry, I should have included the connection I saw and the query I saw run was when the form was open, not when I made a change to the field. I did not see a connection made at all when I changed the field to set the value of the other field. –  Amanda Porter Dec 6 '11 at 15:14
    
Hmm, to be honest I'm not sure –  Paul Lucas Dec 8 '11 at 11:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.