Newbie Questions

Re: Wildcard Query

jkies00 — Thu, 14 Dec 2006 18:40:15 GMT

I'm not sure what the SQL manager query window is, but I ran it through Access, and it worked. Is SQL manager query window a utility in InfoPath? If so, where do I find it? The SQL syntax was part of the problem, as it turns out...

The SQL syntax, while it works in Access, didn't work in InfoPath. I had to change the * wildcard indicators to %.

From:

select [ID],[Name] from [tDisc] as [tDisc] WHERE tDisc.Name LIKE "*acro*"

To:

select [ID],[Name] from [tDisc] as [tDisc] WHERE tDisc.Name LIKE "%acro%"

I figured this out by reading the KnowledgeBase article above. It said to enter "Sales%" into their example form to enable wildcard searching. I just built the % character into the code, so whatever the user enters will be prepended and appended with %.

The second part of the problem was that I needed to use the ADOAdapter object to enable InfoPath to use more complex SQL actions. Excerpt from the KB article: "...By using the ADOAdapter object, you can modify an SQL statement to perform more complex actions such as using wildcard characters in a query..."

Here's the function I use to perform wildcard queries on the DB, based on the code in the KB article:

function CTRL2_8::OnClick(eventObj)
{	
	// Get the default SQL command for the form & instantiate variable for new SQL code.
	var strOrigSQLCommand = XDocument.QueryAdapter.Command;
	var strNewSQLCommand = strOrigSQLCommand;
	    
	// Get the query node that you want to modify.    
	var querySuppliers = XDocument.DOM.selectSingleNode("/dfs:myFields/dfs:queryFields/q:tDisc");
    
	// Obtain the text that was entered for the wildcard character search, and then clear
	// the current query parameter so that InfoPath will leave the current query parameter blank.
	var strTitle = XDocument.DOM.selectSingleNode("/dfs:myFields/dfs:queryFields/q:tDisc/@Name").text;
	querySuppliers.selectSingleNode("@Name").text = "";
		
	if (strTitle != "")
	{
	    strNewSQLCommand +=	" WHERE [tDisc].[Name] LIKE \"%" + strTitle + "%\"";
	}
	  
	// This is the query.
	XDocument.UI.Alert(strNewSQLCommand);

	// Run the query.
	XDocument.QueryAdapter.Command = strNewSQLCommand;
	XDocument.Query();
		  
	// Restore the default table command (for the next time).
	XDocument.QueryAdapter.Command = strOrigSQLCommand;	
}

I'd like to refactor the code above, specifically the part dealing with XPath, but every attempt I've made breaks the form. I hope someone find this code helpful, and if anyone can succesfully refactor the XPath bit, I'm all ears!

Thanks!

Re: Wildcard Query

Greg Collins — Thu, 14 Dec 2006 17:18:50 GMT

Did you verify that this command works from the SQL manager query window? If it doesn't, then its likely an issue with the command syntax. If it does, then perhaps the Command property isn't getting set somehow.

Wildcard Query

jkies00 — Tue, 12 Dec 2006 19:59:29 GMT

Hello,

I'm trying to perform a simple wildcard query through InfoPath. The query works when I run it in Access, but it doesn't do anything when I run it through InfoPath. The query is below:

select [ID],[Name] from [tDisc] as [tDisc] WHERE tDisc.Name LIKE "*acro*"

I got the syntax for the wildcard from a Microsoft Knowledge Base article:
http://support.microsoft.com/default.aspx?kbid=826992&product=infopath2003
But it still won't work. When I click the button to run my custom code, it returns no information - no records, no notification that no data was returned, or no error. Here's the code I use to generate the SQL:

function CTRL2_8::OnClick(eventObj)
{
	var origSQLCommand = XDocument.DataAdapters["Main connection"].Command;
	var queryInputString = XDocument.DOM.selectSingleNode("//dfs:queryFields/q:tDisc/@Name").text;
	var newSQLCommand = origSQLCommand;
	newSQLCommand += " WHERE tDisc.Name LIKE \"*" + queryInputString + "*\"";
	XDocument.UI.Alert(newSQLCommand);
	XDocument.DataAdapters["Main connection"].Command = newSQLCommand;
	XDocument.DataAdapters["Main connection"].Query();
	XDocument.DataAdapters["Main connection"].Command = origSQLCommand;	
}

How can I debug if I'm not getting any information back? Is there a certain way wildcards need to be used in InfoPath that I'm not aware of?