Hi, I am new to Infopath but have find quite some good solution on this site. The problem I am experiencing is the following:
'- I am using Infopath 2010 and trying to read via SQL data from a SQL server and this should retrieve multiple records
'- These records should be populated in the form in a repeating table
'The code I wrote (got mostly on this site)
namespace Re_planning_template_own
{
public partial class FormCode
{
// Member variables are not supported in browser-enabled forms.
// Instead, write and read these values from the FormState
// dictionary using code such as the following:
//
// private object _memberVariable
// {
// get
// {
// return FormState["_memberVariable"];
// }
// set
// {
// FormState["_memberVariable"] = value;
// }
// }
// NOTE: The following procedure is required by Microsoft InfoPath.
// It can be modified using Microsoft InfoPath.public void InternalStartup()
{
((ButtonEvent)EventManager.ControlEvents["Get_material_data"]).Clicked += new ClickedEventHandler(Get_material_data_Clicked);
}
public void Get_material_data_Clicked(object sender, ClickedEventArgs e)
{
// retrieve field1's value and store it in a stringstring field1 = MainDataSource.CreateNavigator().SelectSingleNode("/my:TLM_Data/my:TLM_ID", NamespaceManager).Value;string TLM = field1;
// a format string for the query. The {0}s will be replaced by field1's valuestring queryString = "select count(*) from [tblSAPAPO_MATLOC] where [ZZ_TLM_SUB] = " + "'" + TLM + "'";
// make sure that the search string isn't blankif (!string.IsNullOrEmpty(TLM))
{
// instantiate an object to access the data connection. TestTable1 is the name of the data connectionAdoQueryConnection dc = (AdoQueryConnection)DataConnections["tblSAPAPO_MATLOC"];
// insert field1's value into the query and assign it to the data connection's query
//dc.Command = string.Format(queryString, field1);
// run the query
dc.Execute();
}
// store the namespace URLs as strings for easy referencestring myNamespace = NamespaceManager.LookupNamespace("my");
// the attributes on the secondary data source results have no prefix, as seen in the data source taskpanestring blankNamespace = NamespaceManager.LookupNamespace("");
// select the node where the results will be placedXPathNavigator dataFields = MainDataSource.CreateNavigator().SelectSingleNode("/my:TLM_Data/my:DataFields/my:ResultRow", NamespaceManager);
// select the secondary data source node where the results are locatedXPathNavigator queryDataFields = DataSources["tblSAPAPO_MATLOC"].CreateNavigator();
queryDataFields = queryDataFields.SelectSingleNode(
"/dfs:myFields/dfs:dataFields", NamespaceManager);
// remove all ResultRow children of the my:QueryResults node in the main data sourcewhile (dataFields.MoveToChild("ResultRow", myNamespace))
{
dataFields.DeleteSelf();
}
// iterate through each results node in the secondary data sourceforeach (XPathNavigator resultRow in queryDataFields.Select("*"))
{
// add a row to the results' repeating table/section. The name that corresponds to
// "ResultRow_19" here can be found on the Advanced tab of the repeating row/section's
// propertiesCurrentView.ExecuteAction(ActionType.XCollectionInsert, "MATID_94");
// Select all of the nodes in the newly created row and copy values into them.
// Their names must correspond precisely to the names of the nodes you want to copy overforeach (XPathNavigator resultNode in dataFields.Select("my:ResultRow[last()]/MATID", NamespaceManager))
{
resultNode.SetValue(resultRow.GetAttribute(resultNode.LocalName, blankNamespace));
}
}
}
}
}
The issue I am having is that all looks to be working but there is no data retrieved and in debug the following error is popping up:
-Message: The CLR has been unable to transition from COM context 0x6472c8 to COM context 0x646fe8 for 60 seconds. The thread that owns the destination context/apartment is most likely either doing a non pumping wait or processing a very long running operation without pumping Windows messages. This situation generally has a negative performance impact and may even lead to the application becoming non responsive or memory usage accumulating continually over time. To avoid this problem, all single threaded apartment (STA) threads should use pumping wait primitives (such as CoWaitForMultipleHandles) and routinely pump messages during long running operations. Can anybody tell me what I am doing wrong? Regards, Bert