InfoPath Dev

I'd be interested to see the text of the original command. One way to get that is to put a breakpoint on the line below string origCommand = newQuery.Command.ToString(); and then run the code in debug. Another way would be to walk through the data connection wizard again, and select the Edit SQL button to copy the original SQL.....

I set the breakpoint and started to debug.  When it hit the breakpoint, nothing happened.  Do I 'step into'?

Here is the sql statement from the data connection:

shape
 {select "mnBuyerNumber_201","WWMLNM","WWATTL","WWREM1","WWSLNM","WWALPH","WWDC","WWGNNM","WWMDNM","WWSRNM","WWTYC","ALADD1","ALADD2","ALADD3","ALADD4","ALADDZ","ALCTY1","ALCOUN","ALADDS","WPPHTP","WPAR1","WPPH1","WPPHTP2","WPAR2","WPPH2","WPPHTP3","WPAR3","WPPH3","EAEMAL","Processed_Flag" from "dbo"."Buyers" as "Buyers"} as "Buyers"
 append
  ({select "szCommunity_201","szLotNumber_201","szArea_201","szLotAddress_201","szCity_201","szState_201","szZipCode_201","szLotComment_201","cPhase_201","szJobNumber_201","szPlan_201","szElevation_201","cSwing_201","szSalesContractStatus_201","mnBuyerNumber_201","szLegalLotNumber_201","mnBuilderNumber_201","mnAddressNumberCommunit_201","szDateActualFinish_201","szConstructionStartDate_201","szConstructionCompleteDate_201","szAccentColor_201","cColorHold_201","szRoofTile_201","szBodyColor_201","szFasciaColor_201","szColorPackage_201","szCloseDate_201","mnBuyerNumber_501","cMultipleBuyers_501","szSalesContractStatus_501","szSalesDate_501","szCloseDate_501","community_description","div_desc" from "dbo"."Lots" as "Lots"}
   relate "mnBuyerNumber_201" TO "mnBuyerNumber_201") as "Lots",
  ({select "BuyerPhoneID","BuyerNumber","PhoneType","Phone","Preferred" from "dbo"."BuyerPhones" as "BuyerPhones"}
   relate "mnBuyerNumber_201" TO "BuyerNumber") as "BuyerPhones",
  ((shape
   {select "WorkOrderNumber","LongWorkOrderNumber","WorkOrderType","BuyerNumber","DateUpdated","InitialContact","CustomerTemperment","DiagnoseDate","DiagnoseCallVisit","RepairDateStart","RepairDateEnd","CompletionDate","ConfirmationDate","CustomerCareSuper","ServiceRequestStatus","ABAN8","ABALPH","ALADD1","ALADD2","ALADD3","ALADD4","ALADDZ","ALCTY1","ALCOUN","ALADDS","WPPHTP","WPAR1","WPPH1","WPPHTP2","WPAR2","WPPH2","WPPHTP3","WPAR3","WPPH3","EAEMAL" from "dbo"."WorkOrders" as "WorkOrders"}
   append
    ({select "AvailabilityID","WorkOrderNumber","AvailabilityType","Time1","Time1AMPM","Time2","Time2AMPM","DaysofWeek","Date1","Date2" from "dbo"."WorkOrderAvailability" as "WorkOrderAvailability"}
     relate "WorkOrderNumber" TO "WorkOrderNumber") as "WorkOrderAvailability",
    ((shape
     {select "ConcernID","WorkOrderNumber","NatureOfConcern","AdditionalDetails","VendorNumber" from "dbo"."WorkOrderConcern" as "WorkOrderConcern"}
     append
      ({select "InfoID","ConcernID","UpdatedInfo","DateUpdated" from "dbo"."WorkOrderConcernInfo" as "WorkOrderConcernInfo"}
       relate "ConcernID" TO "ConcernID") as "WorkOrderConcernInfo")
     relate "WorkOrderNumber" TO "WorkOrderNumber") as "WorkOrderConcern")
   relate "mnBuyerNumber_201" TO "BuyerNumber") as "WorkOrders"

 

You should be able to hover over a variable with your cursor and see the values.... I think in VSTA you can also view them in the watch window....Well, the code we are using for this adds the 'where' clause to the end of the query. So the 'where' is being added to the end, after the work orders table....I think in order to get this to work, you'll have to do some string manipulation. Since you want the where clause in the select for the Buyers table, I think it would need to go in:

{select "mnBuyerNumber_201","WWMLNM","WWATTL","WWREM1","WWSLNM","WWALPH","WWDC","WWGNNM","WWMDNM","WWSRNM","WWTYC","ALADD1","ALADD2","ALADD3","ALADD4","ALADDZ","ALCTY1","ALCOUN","ALADDS","WPPHTP","WPAR1","WPPH1","WPPHTP2","WPAR2","WPPH2","WPPHTP3","WPAR3","WPPH3","EAEMAL","Processed_Flag" from "dbo"."Buyers" as "Buyers"} as "Buyers"

right about:

{select "mnBuyerNumber_201","WWMLNM","WWATTL","WWREM1","WWSLNM","WWALPH","WWDC","WWGNNM","WWMDNM","WWSRNM","WWTYC","ALADD1","ALADD2","ALADD3","ALADD4","ALADDZ","ALCTY1","ALCOUN","ALADDS","WPPHTP","WPAR1","WPPH1","WPPHTP2","WPAR2","WPPH2","WPPHTP3","WPAR3","WPPH3","EAEMAL","Processed_Flag" from "dbo"."Buyers" as "Buyers" Where (WhereClause)} as "Buyers"

Now, because we are looking at the first table, you could probably get the index of the first '}' and then take the substring of the original command from 1 to that number, concatenate that with the variable that has the where clause, and then concatenate *that* with another substring of the original command from the index of the '}' to the length of the string. Wow. I couldn't have been more obtuse. I mean something like this:

string origCommand = newQuery.Command.ToString();

int firstBrace = origCommand.IndexOf("}");

string origCommandFirstHalf = origCommand.Substring(0, firstBrace);

string origCommandSecondHalf = origCommand.Substring(firstBrace, origCommand.Length - origCommandFirstHalf.Length );

 

WOW - I am still marveling at the jargon...

OK.  I assume that the first string statement that you included is the one that is already in the original code so I just copied the last 3 lines and pasted them in after that line.  Is that correct?  At any rate, I am still getting the same error.

The code that you are giving me isn't sql is it?  We are using C# to create a dynamic sql query, right?  And that is what I am looking at when I am debugging and do the hover thing - the sql that is.  So if I am guessing correctly, the dynamic query that is created appends the 'where' clause at the end and that is what is giving us the syntax error.  Where I am confused is that I have stuck your original code in a couple other forms without incident.  It would seem as if I always get the really strange ones that are very difficult to figure out.  Maybe I should take up needlepoint.....

No, it isn't sql... we are still using C#. I just didn't give you quite enough code, I think. What you need to do is replace this: string origCommand = newQuery.Command.ToString(); //set the query to use the custom command newQuery.Command = origCommand + customQuery; With this: string origCommand = newQuery.Command.ToString(); int firstBrace = origCommand.IndexOf("}"); string origCommandFirstHalf = origCommand.Substring(0, firstBrace); string origCommandSecondHalf = origCommand.Substring(firstBrace, origCommand.Length - origCommandFirstHalf.Length ); newQuery.Command = origCommandFirstHalf + customQuery + origCommandSecondHalf; If you were still using the original newQuery.Command line you would expect to have the same error. Sorry for any horrid formatting in this post -- I'm on my Mac & Safari does NOT play well with Community Server. I don't get the 'HTML' option and it spits out my carriage returns. As to why this has worked for you on other forms in the past -- a lot has to do with the fact that we were adding the 'where' clause at the end of the SQL statement. Were the other forms you were using based off more than one table?

Hilary, you are the Grand Poo Bah Master!  Everything works perfectly!  Thank you so much.  This is going to be a big help.  I hope that there is some way that I can return all of your help.

Tell you what -- keep using the forum, and we'll call it even... :) Glad it all works. Keep in mind, as you use different forms with different data connections, the methodology you use to split and re-form the string that contains your SQL command will change. Currently, we can split on the first brace, because you are looking at the buyers table.... but what if you wanted to add your 'where' clause (SQL) to the lots table? Then you'd have to find a different way to cut up and reform that string, so don't be surprised if this solution is *not* universal, okay?

I think that I will be hanging around the forums for a long time to come, and, who knows, I may be able to answer someone else's question someday ;P

I think that I am starting to get what you are talking about.  I have a SQL book and a C# book now and by studying a bit, I am getting the hang of it. 

Thanks again for all your help, Hilary - you rock!

I'm try to query the secondary data source and have the results displayed in the repeat table in the main data source. I have followed so many online help as I could, but with not much success at the moment. I can query the secondary data source. the major problem I'm having is with the last bit, how to display the results copied from secondary data source repeat table to the table in the main data source. How will I refresh to display these results.

Please I will appreciate your help more if you include code samples.

Thanks

the code that I have used so far is

 public void CTRL10_7_Clicked(object sender, ClickedEventArgs e)
        {
            // retrieve field1's value and store it in a string
            string field1 = MainDataSource.CreateNavigator().SelectSingleNode("//dfs:myFields/dfs:queryFields/q:FRAQSTemplate/@CatID", NamespaceManager).Value;
            const string queryString = "select * from [FRAQSTemplate] where [CatID] = 3";

            // make sure that the search string isn't blank
            if (!string.IsNullOrEmpty(field1))
            {
                // instantiate an object to access the data connection. TestTable1 is the name of the data connection
                AdoQueryConnection dc = (AdoQueryConnection)DataConnections["SecondaryConnection"];

                // insert field1's value into the query and assign it to the data connection's query
                dc.Command = string.Format(queryString);

                // run the query
                dc.Execute();

                //AdoQueryConnection newQuery = (AdoQueryConnection)MainDataSource.QueryConnection;
                //newQuery.Execute();
            }
            else
            {
                // show an error when field1 is blank
                MessageBox.Show("The search field cannot be blank!");
            }

       }

 

 

 

        public void CTRL19_7_Clicked(object sender, ClickedEventArgs e)
        {
            // store all of the column names in an array, to be used below
            string[] columnNames = { "ID", "CatID", "QuestionNO", "Question", "Active", "Version", "TS" };
            // store the namespace URLs as strings for easy reference
            string myNamespace = NamespaceManager.LookupNamespace("my");
            // d is the prefix of the repeating nodes in the secondary data source results, as seen in the data source taskpane
            string dNamespace = NamespaceManager.LookupNamespace("d");
            // the attributes on the secondary data source results have no prefix, as seen in the data source taskpane
            string blankNamespace = NamespaceManager.LookupNamespace("");

            // select the node where the results will be placed
            XPathNavigator dataFields = MainDataSource.CreateNavigator().SelectSingleNode("//dfs:myFields/my:QueryResults", NamespaceManager);
            // select the secondary data source node where the results are located
            XPathNavigator queryDataFields = DataSources["SecondaryConnection"].CreateNavigator();
            queryDataFields = queryDataFields.SelectSingleNode("//dfs:myFields/dfs:dataFields", NamespaceManager);

            // remove all ResultRow children of the my:QueryResults node in the main data source
            while (dataFields.MoveToChild("ResultRow", myNamespace))
            {
                dataFields.DeleteSelf();
            }

            // iterate through each results node in the secondary data source
            foreach (XPathNavigator resultRow in queryDataFields.SelectChildren("FRAQSTemplate", dNamespace))
            {
                // append a new child to the my:QueryResults node
                using (XmlWriter writer = dataFields.AppendChild())
                {
                    // write the element name
                    writer.WriteStartElement("ResultRow", myNamespace);
                    // copy over each of the attributes
                    foreach (string colName in columnNames)
                    {
                        writer.WriteStartElement(colName, myNamespace);
                        MessageBox.Show(colName.ToString());
                        writer.WriteValue(resultRow.GetAttribute(colName, blankNamespace));
                        writer.WriteEndElement();
                    }
                    writer.WriteEndElement();
                }
            }
        }

 

 

 

Hello Hilary

I am new in this forum and inforpath user.

This query helps lot for me.

But i got stuck at one place. When I use this quey for seaching within one of the column in my MS Access database it shows records when it start with search string.

I want to search record when search string content anywhere in column field.

Could you please help me in this.

I have used below code

//get the value of the query field we want to use with a wild card

XPathNavigator queryField = MainDataSource.CreateNavigator().SelectSingleNode("/dfs:myFields/dfs:queryFields/q:List/@Field1", NamespaceManager);

string querySeed = queryField.Value;

//clear the query field so that it is ignored in the query

queryField.SetValue("");

//create our custom where clause -- you have some choices here. I've added in the wildcard. You could choose not to, if your users are savvy and can fill it in themselves when searching, all up to you.

string customQuery = " where ([Field1] like '" + querySeed + "%')";

//get the original command (we'll need to set it back)

AdoQueryConnection newQuery = (AdoQueryConnection)MainDataSource.QueryConnection;

string origCommand = newQuery.Command.ToString();

//set the query to use the custom command

newQuery.Command = origCommand + customQuery;

//annnnnnddddd, now we run the query using our shiny new command

newQuery.Execute();

//put everything back the way it was.... you're going to need it!

newQuery.Command = origCommand;

//set the query field back for the user, so they know what they typed in!

queryField.SetValue(querySeed);

Regards 

Gaurav

Hey, there, welcome!

You need to add the wildcard indicator at the begining and end of your search term:

string customQuery = " where ([Field1] like '%" + querySeed + "%')";

Thanks a lot...

Hello Hillary, This is a great forum and it has helped me a lot. Thanks! I was able to follow your sample below and apply it to a form I'm working on, I'm able to build the new query to execute a stored proc, and I see the new string with the proper syntax, and it seems like the form is executing it, but I do not see any records generated in the database when the stored proc was executed. Is this not possible? or I am missing a step. thansk!

Was able to fill in the gaps with this article:

http://support.microsoft.com/kb/826992