InfoPath Dev

Hello Everyone, I have an Infopath 2013 form, which pulls some records info from a sql servers. I want a manual query, I have a field - QuestionID on each line of the repeating table, where user will enter a QuestionID. The search button will look up records in the SQL DB . The first line works perfectly. Problem happens when I add a new line - the query is stuck on the first search. I would like to requery the data for the new record. below is my code. I will really appreciate the help. Thanks public void CTRL5_5_Clicked(object sender, ClickedEventArgs e) { // create an xmlNamespaceManager XmlNamespaceManager nSpace = this.NamespaceManager; // create an XPathNavigator for the main data source XPathNavigator xMain = this.MainDataSource.CreateNavigator(); //cast the SQL Secondary Data Connection to an AdoQueryConnection. AdoQueryConnection SQLConn = (AdoQueryConnection)this.DataConnections["QuestionswithResponses"]; string strSQL = SQLConn.Command; //Get the filter vaile from column in the form string strFilter = xMain.SelectSingleNode("/my:myFields/my:group1/my:group2/my:QuestionID", nSpace).Value; //Add filter value to SQL query SQLConn.Command = SQLConn.Command + "WHERE QuestionID = '" + strFilter + "'"; //Now excecute the connection SQLConn.Execute(); //Reset the SQL query to Orignal SQLConn.Command = strSQL;

Hi! so your button is in the repeating table? Your XPath in this line will always return the first node: xMain.SelectSingleNode("/my:myFields/my:group1/my:group2/my:QuestionID", nSpace).Value. That's going to get you the first QuestionID, always, as you have found. Good news - your button is context aware and knows what instance of the repeating table it is in. e.Source is the current node of the repeating table, in this case. So I think you could do: e.Source.SelectSingleNode("my:QuestionID", nSpace).Value. Forgive me if that's not quite correct, it has been a while since I've written C# for an InfoPath form - hopefully this will get you pointed in the right direction. :)

Hi Hilary, Thank you so much for your reply! The button is not in repeating table its outside of the repeating table. when I enter the question ID in repeating table column and click search the first search works when I add new row and type new questions ID it's not querying the second record

If you always want to query for the last question ID you could use: xMain.SelectSingleNode("/my:myFields/my:group1/my:group2[last()]/my:QuestionID", nSpace).Value. But otherwise, if your button is not in the repeating table, I'm not sure how we'd guess what question id to run the query for :)

Thanks again, I tried the xMain.SelectSingleNode("/my:myFields/my:group1/my:group2[last()]/my:QuestionID", nSpace).Value it works and brings the new value But the problem is now the previews results from the first row disappeared, I want every search result to stay in repeating table I set the fields value using this formala @QuestionText[@QuestionID = current()/../QuestionID]

Any time you requery your secondary data connection, it will only contain the data you have queried for. You could copy the queried data to the main data source for each row - then you don't have to query for all the questions each time, and the data returned to your form is preserved (stored in the main data source). I'd probably do that. Just add fields to the repeating group, and use e.Source to get the right instance and set it. Or, you could change your code to use an "in" clause in your query, and always query for all the question IDs, I suppose.

do you have any example how to copy the queried data from secondary source to main main data source table... or change the code to use the "in" caluse..I am new to to infopath andC# and badly want to learn it ...:)... Thank u so much agian for your time and help I really appreciate it

Can you tell me how you want the form to behave? Should the user enter all their data, then click the button to get data for all rows? Or do you want them to click the button to populate each row? Here's a code sample that may help you iterate through the main nodes and set values: https://web.archive.org/web/20180110000650/http://www.bizsupportonline.net/infopath2007/copy-rows-from-sharepoint-list-to-main-data-source.htm

I want the user to enter one Question ID at time and click the search button and than add other row and enter the Question ID and Click the search button .....

Ok. I don't know the full XPath to either your secondary data source node or to your main data source node where you currently have the default value from the 2ds - but the code after you run the SQL query might be something like:

XPathNavigator secDSNav = DataSources["QuestionswithResponses"].CreateNavigator();
xMain.SelectSingleNode("/my:myFields/my:group1/my:group2[last()]/my:QuestionText", nSpace).Value = secDSNav.SelectSingleNode("XPath to QuestionText here", nSpace).Value;

You'll need to add correct XPaths and I am sure I have errors here - again, it has been a long time since I've written code for InfoPath. But hopefully this will give you an idea of what to try.