InfoPath Dev

Hello, my problem concerns using a wildcard in a query of an Access database.  To make the problem easier to understand, I will reference the Northwind database example found on the Microsoft Help & Support page (http://support.microsoft.com/kb/826992).

 With some copying/pasting/editing, I have been successful in enabling wildcard character for two query fields (Contact Name and Contact Title).  This allows me to refine my search with two fields with wildcards.

 Now when I add a table in the Data Connections (Products for example as there is a relationship with Supplier ID), the query function does not work.  I get the message that is similar to many others I've seen in other posts:

The following error occurred:

The query method on the Document object failed.
InfoPath cannot run the specified query.
[0x80040E14][MSDataShape] Data shape command text contains a syntax error at or near position 350 in the command. The text near the error is: " [Products] WHERE ([Suppliers].[ContactName]".

File:script.js
Line:80

So the question is how can I enable a wildcard query when there are two tables in the Data Connection? 

Hi,  I am having the exact same issue.  Can anyone suggest whether it can be done?

Many thanks,

Larry

I ran into the same issue. The problem is that InfoPath adds to the ADO connection query to relate the tables.  For example, in the one I was using, it added "relate [ID] to [System]" after the select command for the second table.  If you just append the wildcard query to the ADO connection command, you'll get the complaint about the bad syntax.  What I did to work around this was to replace the "from [table_name]" part of the query with "from [table_name] where [field] LIKE "wildcardvalue".  In VB, this looked like this:

"from [Table2] WHERE Field2 LIKE " & Quote & strSearchCriteria & Quote & "}")

 It then worked as long as I used the % as the wildcard character.

You say , "in the one I was using, it added "relate [ID] to [System]" after the select command for the second table", I'm not following...when you say "it" what exactly are you referring to?  Could you just post the amended code in its entirety so I can see what it is you mean?  I'm going through my own code and I don't see where there's any reference to strNewCommand.

 Thanks

By it, RichardA is referring to InfoPath.  And you wouldn't necessarily have strNewCommand in your code.  That is simply a variable name that RichardA is using and is entirely arbitrary.  Perhaps you could post your code and we could try to figure out why it isn't working?

{
  // Get the default SQL command for the form.
  var strOrigSQLCommand = XDocument.QueryAdapter.Command;

  // Get the query node that you want to modify.
  var querySupplier_T = XDocument.DOM.selectSingleNode(
    "/dfs:myFields/dfs:queryFields/q:Supplier_T");
  var queryRegion_T = XDocument.DOM.selectSingleNode(
    "/dfs:myFields/dfs:queryFields/q:Region_T");
  var queryRegionStates_T = XDocument.DOM.selectSingleNode(
    "/dfs:myFields/dfs:queryFields/q:RegionStates_T");
  var queryServices_T = XDocument.DOM.selectSingleNode(
    "/dfs:myFields/dfs:queryFields/q:Services_T");
  var queryServicesAttributes_T = XDocument.DOM.selectSingleNode(
    "/dfs:myFields/dfs:queryFields/q:ServicesAttributes_T");
  var queryComments_T = XDocument.DOM.selectSingleNode(
    "/dfs:myFields/dfs:queryFields/q:Comments_T");
   
   
  // 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 strName = querySupplier_T.selectSingleNode("@strSupplierName").text;
  querySupplier_T.selectSingleNode("@strSupplierName").text = "";
 
  // Ask InfoPath to construct an SQL command that is based on all other field values.
  var strMySQLCommand = XDocument.QueryAdapter.BuildSQLFromXMLNodes(querySupplier_T);

  // Save each of the other query items, and then clear the other query items before the next query.
  var IngSupplierID = querySupplier_T.selectSingleNode("@IngSupplierID").text;
  querySupplier_T.selectSingleNode("@IngSupplierID").text = "";
  var dtSupplierDate = querySupplier_T.selectSingleNode("@dtSupplierDate").text;
  querySupplier_T.selectSingleNode("@dtSupplierDate").text = ""; 
  var strSupplierNumber = querySupplier_T.selectSingleNode("@strSupplierNumber").text;
  querySupplier_T.selectSingleNode("@strSupplierNumber").text = "";
  var strSupplierAddress1 = querySupplier_T.selectSingleNode("@strSupplierAddress1").text;
  querySupplier_T.selectSingleNode("@strSupplierAddress1").text = "";
  var strSupplierAddress2 = querySupplier_T.selectSingleNode("@strSupplierAddress2").text;
  querySupplier_T.selectSingleNode("@strSupplierAddress2").text = "";
  var strSupplierCity = querySupplier_T.selectSingleNode("@strSupplierCity").text;
  querySupplier_T.selectSingleNode("@strSupplierCity").text = "";
  var IngPostalCode = querySupplier_T.selectSingleNode("@IngPostalCode").text;
  querySupplier_T.selectSingleNode("@IngPostalCode").text = "";
  var IngStateID = querySupplier_T.selectSingleNode("@IngStateID").text;
  querySupplier_T.selectSingleNode("@IngStateID").text = "";
  var IngCountryID = querySupplier_T.selectSingleNode("@IngCountryID").text;
  querySupplier_T.selectSingleNode("@IngCountryID").text = "";
  var strRegions = queryRegion_T.selectSingleNode("@strRegions").text;
  queryRegion_T.selectSingleNode("@strRegions").text = "";
  var strRegionStates = queryRegionStates_T.selectSingleNode("@strRegionStates").text;
  queryRegionStates_T.selectSingleNode("@strRegionStates").text = "";
  var strServices = queryServices_T.selectSingleNode("@strServices").text;
  queryServices_T.selectSingleNode("@strServices").text = "";
  var strServicesAttributes = queryServicesAttributes_T.selectSingleNode("@strServicesAttributes").text;
  queryServicesAttributes_T.selectSingleNode("@strServicesAttributes").text = ""; 
  var strCommentEnteredBy = queryComments_T.selectSingleNode("@strCommentEnteredBy").text;
  queryComments_T.selectSingleNode("@strCommentEnteredBy").text = "";
  var dtCommentEnteredDate = queryComments_T.selectSingleNode("@dtCommentEnteredDate").text;
  queryComments_T.selectSingleNode("@dtCommentEnteredDate").text = "";
  var txtComment = queryComments_T.selectSingleNode("@txtComment").text;
  queryComments_T.selectSingleNode("@txtComment").text = "";
 
  //  Add strSupplierName to the query so
  // that strSupplierName can support wildcard characters.
  if (strName != "")
  {
    if (strMySQLCommand != "")
        strMySQLCommand = strMySQLCommand + " AND ";
             
    strMySQLCommand = strMySQLCommand +
       "([Supplier_T].[strSupplierName] LIKE \"" + strName + "\")";
  }
 
  // Construct the full query string.
  var strSQLQuery = strOrigSQLCommand;
  if (strMySQLCommand != "")
      strSQLQuery = strSQLQuery + " WHERE " + strMySQLCommand;
     
  // This is the query.
  //XDocument.UI.Alert(strSQLQuery);

  // Run the query.
  XDocument.QueryAdapter.Command = strSQLQuery;
  XDocument.Query();
 
  // Restore all the user entries to the Query fields so that the user entries will
  // be available if you want to modify and to rerun the query.
  querySupplier_T.selectSingleNode("@IngSupplierID").text = IngSupplierID;
  querySupplier_T.selectSingleNode("@dtSupplierDate").text = dtSupplierDate;
  querySupplier_T.selectSingleNode("@strSupplierName").text = strSupplierName;
  querySupplier_T.selectSingleNode("@strSupplierNumber").text = strSupplierNumber;
  querySupplier_T.selectSingleNode("@strSupplierAddress1").text = strSupplierAddress1;
  querySupplier_T.selectSingleNode("@strSupplierAddress2").text = strSupplierAddress2;
  querySupplier_T.selectSingleNode("@strSupplierCity").text = strSupplierCity;
  querySupplier_T.selectSingleNode("@IngPostalCode").text = IngPostalCode;
  querySupplier_T.selectSingleNode("@IngStateID").text = IngStateID;
  querySupplier_T.selectSingleNode("@IngCountryID").text = IngCountryID;
  queryRegions_T.selectSingleNode("@strRegions").text = strRegions;
  queryRegionStates_T.selectSingleNode("@strRegionStates").text = strRegionStates;
  queryServices_T.selectSingleNode("@strServices").text = strServices;
  queryServicesAttributes_T.selectSingleNode("@strServicesAttributes").text = strServicesAttributes;
  queryComments_T.selectSingleNode("@strCommentEnteredBy").text = strCommentEnteredBy;
  queryComments_T.selectSingleNode("@dtCommentEnteredDate").text = dtCommentEnteredDate;
  queryComments_T.selectSingleNode("@txtComment").text = txtComment;
 
  // Restore the default table command (for the next time).
  XDocument.QueryAdapter.Command = strOrigSQLCommand;
 
  // Switch to data entry view to see results.
  XDocument.View.SwitchView("Advanced Reporting");
 
}

What error do you get when you use that code?

Given my level of programming skills, my recommendation would be to add in a number of MessageBox.show statements so you can see the value of the string strMySQLCommand.  By doing this, you will be able to see that by appending the where clause at the end of the SQL statement is creating bad syntax.   Add in the message boxes, and you can see how the sql statement is being modified with your changes.  You may need to use some creative string processing to put the where clause in the right spot

 Rich

The query method on the Document object failed.

InfoPath cannot run the specified query.

[0x80040E14][MSDataShape] Data shape command text contains a syntax error at or near 1066 command. The text near the error is: "[Comments_T]Where([Supplier_T].[strSupplierName]".

File: script.js

Line: 105 

I know the Microsoft example uses this syntax, but I don't think it's correct to use double quotes with a LIKE clause:

   strMySQLCommand = strMySQLCommand +
       "([Supplier_T].[strSupplierName] LIKE \"" + strName + "\")";
 

What happens if you replace that with this:

   strMySQLCommand = strMySQLCommand +
       "([Supplier_T].[strSupplierName] LIKE '" + strName + "')";
 

I've replaced the double quotes before and after strName with single quotes (shown in red).

Not that you asked, but with my wildcard search, I used a constant to provide the double quotes (this is in VB.NET):

Const Quote As Char = """c"

...

I get the exact same error as before...thanks.

Thanks for your help Richard, but I'm afraid I'm not well versed in coding.  What typically helps me is if I can see the code in context.  Is there anyway you could post your entire code syntax rathen than snippets?  Perhaps then I can get a better understanding on how to apply it, thanks.

Here you go.  I hope it makes sense.  I am using VB.NET in VSTA to process the Clicked event for a button.  I'm not an all-star coder myself so there are probably better ways but this works.  Regards, Rich

Const Quote As Char = """c"

'Create an XPathNavigator that looks at the Casing Name

Dim CasingNam As String

'This line sets the CasingName element to null so it is not included in the filter.

NewQueryNav.InnerXml = ""

'Create an XPathNavigator that looks at the query connection.

'Instantiate the query connection

Dim strOldCommand As String

'Get the existing command being used to retrieve data

Dim strNewCommand As String

'This line adds a where clause to the command that will filter the results.

"as [CasingModelData] WHERE CasingName LIKE " & Quote & CasingNam & Quote & "}")

'MessageBox.Show(strNewCommand)

End Sub