Data Connections

Re: Filtering a field dependent on DB and another fiel

Martin Boeck — Mon, 19 Mar 2007 08:21:55 GMT

Dear nukkumatti!

thanks for your help! By the time I've found out that way to add this filter however the problem was that I had to make the form browser-enabled-only so filters weren't allowed.

I've solved my problem according to an article on this link:

http://blogs.msdn.com/infopath/archive/2006/10/12/cascading-dropdowns-in-browser-forms.aspx

where I wrote a webservice which I pass the filter as a parameter and connect this to my form.

Best regards,
Martin

Re: Filtering a field dependent on DB and another fiel

nukkumatti — Fri, 16 Mar 2007 12:10:47 GMT

Forget about my first post there should be an easier way to do this.

Just use a similar statement as you already wrote in your fist post e.g. : SELECT tires,cars from CarsDatabase FROM IdontKnowYourTableName :)

Now bind both ddls to the repeating node of your data connection and set the value and display name of your first to cars and the other to the tires.

Double click your 2nd ddl and click the select xPath button next to entries. click on add filter. from the first dropdownlist select cars from your dataconnection, from the second "is equal to" and for the third use current()/my:nameofyourfirstddl as a function.

Sorry that i can't answer more detailed but i have to catch my plain now as i am going to ireland to have a few pints for st. patricks day.

cheers

may be someone can jump in here ?.

Re: Filtering a field dependent on DB and another fiel

Martin Boeck — Fri, 16 Mar 2007 11:26:26 GMT

Hi nukkumatti!

Thanks for you quick reply! I had to install the VSTA in order to add a OnAfterChange event, now this works. However how do I bind my tires to cars? Where do I get my "valueSelectedInYourFirstDDl" from? Is this just like a variable and I have to name it exactly as the value-name of my first ddl?

Visual Studio doesn't know ADOAdapter2, which assembly do I have to add? I'm actually working in c#.

Best Regards and thanks a lot!
Martin

Re: Filtering a field dependent on DB and another fiel

nukkumatti — Fri, 16 Mar 2007 10:44:22 GMT

Hi,

cannot you do actually the with infopath ?

In your first ddl containing your cars (coming from your fist data connection) set the display name as your cars and the value as your carID.
Set up a second data connection and in the last step of the wizard uncheck the "retrieve data automatically" checkbox.
Bind your second ddl (tires) to this data connection.
Then in an OnAfterChange event of your ddl containing your cars
call something like this and populate your second ddl with the corresponding tires

string sqlCommand = "SELECT tires from CarsDatabase WHERE ([carID]=valueSelectedInYourFirstDDl))";
				//Get SQL dataconnection adapter
				ADOAdapter2 dbConnection = (ADOAdapter2)thisXDocument.DataAdapters["CarsDatabase"];
				//Assign new sql query to the connection adapter
				dbConnection.Command = sqlCommand;
				//Query the dataconnection
				dbConnection.Query();

could this work for you ?

Filtering a field dependent on DB and another fiel

Martin Boeck — Fri, 16 Mar 2007 10:31:58 GMT

Hi all!

I hope somebody can help me, I am a little helpless at the moment.
I have the following problem:

I have two dropdownlists in my InfoPath form:

Cars and Tires package both of them are populated via
database. The tires are dependant on the car (if you have a Porsche you can only have type B-tires and Ferrari A-tires,...) so when you select "ferrari" only the "tires" of Type A should be visible in the other dropdownlist. The tires are stored in a MS SQL 2005 table and so the Cars.

How can I implement this via InfoPath? In ASP .NET you can make a statement like SELECT tires,cars from CarsDatabase WHERE ([carID]=@carID) however this doesn't work for Infopath:

@carID: scalar variable must be declared

I hope somebody can help me.

Thks
a lot
Best Regards
Martin