InfoPath Dev

 Good afternoon,

 I've seen this issue surface before in previous thread but I don't believe it was given any consideration. So for infopath 2003, it looks like anyone who wants to enable a wildcard search on their forms must use the appropriate query code. There is a helpful tutuorial about this here: http://support.microsoft.com/kb/826992.  The only problem is that this method seems to fail when a related table is added to the main connection.  Without the existence of the related table, it works perfect, but it probably would hurt performance if I just created one giant table.

So it seems the query somehow ends up running on the related table ("FA_Contact_Attrib" in this case) even though I'm only referencing the main table in the code ("FA_Contacts")

Please see my example.

 
function MyQuery::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:FA_Contacts");
   
  // 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 = querySuppliers.selectSingleNode("@Contact").text;
  querySuppliers.selectSingleNode("@Contact").text = "";
 
 
  //  Add ContactTitle to the query so
  // that ContactTitle can support wildcard characters.
  if (strTitle != "")
{
        strNewSQLCommand +=    " WHERE [FA_Contacts].[Contact] LIKE \"" + strTitle + "%\"";
}
// This is the query.


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

 Any help would be appreciated!!

Hi:

When you walk through the data connection wizard and you connect to a SQL table, there is a button you can click to see the SQL that InfoPath is generating. Copy that out to a text editor so you can refer to it. My hunch is that since you are appending your "WHERE" clause to the end of the existing SQL command, your new command has syntax errors. You may want to either completely replace the IP SQL command in your code (then set it back, of course) or modify your logic so your end result is like that created by IP. There is a way to run the script editor in debug.... check here for more info.