Newbie Questions

Re: Wildcard query when a relationship exists between two tables

Jimmy — Wed, 13 Oct 2010 02:56:26 GMT

You need to construct your SQL query in a manner that is syntactically correct. In this case, it looks like just tacking a WHERE clause on the end of the default SQL statement isn't going to work. Why don't you figure out what the entire query is supposed to be and use that instead?

..

Desmo — Tue, 12 Oct 2010 19:44:07 GMT

Re: Wildcard query when a relationship exists between two tables

RichardA — Mon, 26 Jan 2009 17:41:46 GMT

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

Public Sub CTRL379_7_Clicked(ByVal sender As Object, ByVal e As ClickedEventArgs)

Const Quote As Char = """c"

'Create an XPathNavigator that looks at the Casing Name

Dim NewQueryNav As XPathNavigator = _CreateNavigator().SelectSingleNode("/dfs:myFields/dfs:queryFields/q:CasingModelData/@CasingName", _

NamespaceManager)

Dim CasingNam As String

CasingNam = NewQueryNav.InnerXml

'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.

Dim xpQueryNav As XPathNavigator = _CreateNavigator().SelectSingleNode("/dfs:myFields/dfs:queryFields", _

NamespaceManager)

'Instantiate the query connection

Dim myAdoQueryConnection As AdoQueryConnection = Me.DataConnections("Casing Risk Data")

Dim strOldCommand As String

'Get the existing command being used to retrieve data

strOldCommand = myAdoQueryConnection.Command

Dim strNewCommand As String

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

strNewCommand = strOldCommand.Replace("as [CasingModelData]}", _

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

'MessageBox.Show(strNewCommand)

myAdoQueryConnection.Command = strNewCommand

myAdoQueryConnection.Execute()

myAdoQueryConnection.Command = strOldCommand

NewQueryNav.InnerXml = CasingNam

End Sub

Re: Wildcard query when a relationship exists between two tables

gdgonzal — Mon, 26 Jan 2009 16:59:32 GMT

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.

Re: Wildcard query when a relationship exists between two tables

gdgonzal — Mon, 26 Jan 2009 16:57:17 GMT

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

Re: Wildcard query when a relationship exists between two tables

RichardA — Mon, 26 Jan 2009 16:29:54 GMT

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"

...

strNewCommand = strOldCommand.Replace("as [CasingModelData]}", _

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

Re: Wildcard query when a relationship exists between two tables

Jimmy — Mon, 26 Jan 2009 16:14:16 GMT

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).

Re: Wildcard query when a relationship exists between two tables

gdgonzal — Mon, 26 Jan 2009 16:03:16 GMT

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

Re: Wildcard query when a relationship exists between two tables

RichardA — Mon, 26 Jan 2009 15:58:51 GMT

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

Re: Wildcard query when a relationship exists between two tables

Jimmy — Mon, 26 Jan 2009 15:26:50 GMT

What error do you get when you use that code?

Re: Wildcard query when a relationship exists between two tables

gdgonzal — Mon, 26 Jan 2009 05:02:54 GMT

{
// 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");

}

Re: Wildcard query when a relationship exists between two tables

Jimmy — Mon, 26 Jan 2009 02:09:51 GMT

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?

Re: Wildcard query when a relationship exists between two tables

gdgonzal — Sun, 25 Jan 2009 07:32:13 GMT

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

Re: Wildcard query when a relationship exists between two tables

RichardA — Mon, 12 Jan 2009 16:27:40 GMT

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:

strNewCommand = strOldCommand.Replace("from [Table2]}", _

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

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

Re: Wildcard query when a relationship exists between two tables

Larry — Fri, 30 May 2008 08:39:17 GMT

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

Many thanks,

Larry