Using Wildcards in Infopath to make a query on a SQL Server

[Patrick Lipinski]

Hello Newsgroup!

I've got a problem with an Infopath form.

I'm using Infopath 2003 (SP1), MS SQL Server 2003 Enterprise on Windows 2003
Server Standard.

My intention is to create a form to search our CD-Database (it is only one
table -> num for CD number, cdname for program name) that's based on the SQL
Server. I am able to set up a dropdown menu, which contains the
alphabetically sorted names of the programs. You can scroll that list for
the program name, make a query after selection and you get the CD number.

The only problem is, that this list has 180 entries. So it's not very
comfortable to use.

That's why I want to have a textfield to search the database using only
parts of the name (plus wildcards).

For example you are looking for Microsoft Exchange 2003 Server and search
for %Exchange% you get the result

CD | Program
--------------------
15 | Microsoft Exchange 2000
89 | Microsoft Exchange 2003 Server
173 | Exchange...

I've tried to edit the SQL query on the database connection but that won't
work:

select "num","cdname" from "dbo"."cds" as "cds" WHERE "cdname" LIKE '%_%' OR
"num" LIKE '%_%'

Is there a way to read out the input in the textfield, save that to a
variable and query the SQL Server using that variable?

I have no clue, how to do that in Infopath... Maybe somebody here can help
me!

Thanks in advance!
Patrick Lipinski