Great example. Looked almost identical to mine, and I really like the
simple XML connector idea. On mine however the dropdown still didn't change
it's data set so I installed the SP1 preview and "Viola!" it works. You did
say "On SP1" below and I should have paid attention. The problem I have is
that my user base won't have SP1 for a while, so I will have to think of a
different strategy.
Thanks for your help Greg!
--
Okay, so I took the sample you have been playing with and ran a test (I have
not posted my changes, but I will explain what I did below).
I added two buttons to the view. One I labeled as "Full List", the other I
labeled as "Search". I also added a new data connectoin to a simple XML file
of the following structure:
Search.xml
<Query>
<SearchString/>
</Query>
I dropped the SearchString node into the view in front of the Search button.
Then I added script for each button as follows:
function btnFullList::OnClick(eventObj)
{
var oAdoAdapter = XDocument.DataObjects("Customers").QueryAdapter;
oAdoAdapter.Command = "SELECT * FROM dbo.Customers";
oAdoAdapter.Query();
}
function btnSearch::OnClick(eventObj)
{
var sSearchString =
XDocument.GetDOM("Query").selectSingleNode("//SearchString").text;
var oAdoAdapter = XDocument.DataObjects("Customers").QueryAdapter;
oAdoAdapter.Command = "SELECT * FROM dbo.Customers WHERE ContactName
LIKE '" + sSearchString +"%'";
oAdoAdapter.Query();
}
With these changes in place, I can click either of the buttons and have the
drop-down list box dynamically update. If I type "B" into the SearchString
text box, and then click the Search button, I can then click on the
drop-down list box and see only names that start with the letter B.
I hope this helps to solve the issue.
--
Greg Collins [InfoPath MVP]
Please visit:
http://www.InfoPathDev.com
I may be totally cluless, but I don't see how to do this. Editing the SQL
string in the connection does me no good because I don't know what the query
will be until the user supplies me with parameters.
Specifically:
I have a support call database with 60M log entries for about 13 million
support cases. I have a section on my form with a text box and a "search"
button. I want to let the user enter a case number and get back all the log
entries for that case. I am successful building the query and executing it.
Through SQL profiler I can see that the query makes it to SQL server and a
result set gets returned. I just don't understand how to populate the node.
Do I actually have to write a loop to insertNode for every row returned in
the record set, or can the result just be a property of that node somehow?
And where is that returned recordset, anyway. I know this is probably
obvious, but for the life of me I am unable to figure it out.
At the risk of sounding tedious, let me try it another way. If I create a
secondary data source for SQL and give it a stock query that returns an
entire table, then bind a DropDown to it, I get the entire table in the
dropdown when the form opens. Elementary. Then, through script, I modify
the query and re-execute it. The profiler log shows my new query has been
processed by SQL. So where does the data from this new query go? The list
of items in the dropdown does not change, and there doesn't seem to be a
recordset property. Crudely walking the node behind the dropdown reveals,
to nobody's great surprise, the original record set. Where does InfoPath
put the data?
Thanks again for your help!
-Tom
--
Yes, I chose to include the entire table, but could have just as easily
restricted it. When working with data connections, you can specify your own
SQL query. If you were to look at my example form, and opened the Customers
data connection, there is a button on the wizard that let's you edit the
SQL... you can then customize this however you want.
The drop-down list box itself uses no script. It simply points to the
Customers DOM and selects the node to bind to.
From your original question, it sould like you should be able to modify this
example fairly easily and turn it into what you are trying to do.
--
Greg Collins [InfoPath MVP]
Please visit:
http://www.InfoPathDev.com
Thanks Greg - I actually looked (briefly) at that example before I posted.
It looked to me like you are querying for everything and using jscript to
filter on the result set for display. I need to have a more precise result
set as I'm pulling from a data source with about 60 million rows. I need to
modify the query itself and send that, not just grab every row. Before you
answer this, though, let me take a closer look at your example. I'm all SQL
and very little XML, so I need to lip-read through the whole thing...
-T
--
If you add your SQL database as a data connection in InfoPath SP-1, you can
set your query there. Then it's just a matter of binding your drop-down list
box to the secondary DOM created by this data connection. I have a couple
examples of this for download at
http://www.InfoPathDev.com/examples. The
easiest one to look at will be titled, "Populating Fields After Selection".
--
Greg Collins [InfoPath MVP]
Please visit:
http://www.InfoPathDev.com
I am new to InfoPath and feel like a dork for asking this, but is there a
way to just build a query string and submit it to populate a dropdown from
SQL?
ie:
simple query:
SELECT SomeField FROM SomeDatabase WHERE SomeOtherField = "text"
I would grab "text" from a user entered value.
I can set the DataAdapter.Command to the query and execute the
DataObjects.Query() method, but how to I bind the result, at runtime, to the
desired Drop Down?
Thanks!
-Tom
