Duplicated Entry from Reading a SharePoint List

[sonisick]

--
Stephan Onisick

My whole objective in this exercise is to sort and filter an InfoPath Data
Connection from a SharePoint List.

Using the article from S.Y.M. Wong-A-Ton, "Programmatically add items to a
drop-down list box in InfoPath 2007"
http://www.bizsupportonline.net/inf...populate-drop-down-list-box-infopath-2007.htm, as a template.

I used one of my Datasources that returns data from a SharePoint List.

After Extracting the data. I put it into a Datatable and defined a sort
filter. Then from the data view I filled the datasource "option" with the
sorted data.

Once everything completes, I get a dropdown with all my items--bit with the
last item showing up twice--once at the beginning of the list.

public void FormEvents_Loading(object sender, LoadingEventArgs e)
{
RemoveFirstItem();


// /dfs:myFields/dfs:dataFields/dfs:Two-LetterInfo
XPathNodeIterator twoLetters =
DataSources["Two-LetterInfo"].CreateNavigator().Select("/dfs:myFields/dfs:dataFields/dfs:Two-LetterInfo[@NotInTwoLetter='0']", NamespaceManager);
//XPathNodeIterator twoLetters =
DataSources["Two-LetterInfo"].CreateNavigator().Select("/dfs:myFields/dfs:dataFields/dfs:Two-LetterInfo", NamespaceManager);
string TwoLetter = string.Empty;
string NameandTitle = string.Empty;
DataTable dt = new DataTable("SortedTwoLetters");
DataRow dr;
dt.Columns.Add("Two-Letter", typeof(System.String));
dt.Columns.Add("NameandTitle", typeof(System.String));






foreach (XPathNavigator twoLetter in twoLetters)
{
//string TwoLetter =
twoLetter.SelectSingleNode("/dfs:myFields/dfs:dataFields/dfs:Two-LetterInfo[@Two-Letter]", NamespaceManager).Value;
//string TwoLetter = twoLetter.OuterXml;
//string TwoLetter =
twoLetter.SelectSingleNode("/my:Two-Letter", NamespaceManager).Value;

//string email = twoLetter.SelectSingleNode("[@email]",
NamespaceManager).Value;

//string email = twoLetter.SelectSingleNode("[@email]",
NamespaceManager).Value;
twoLetter.MoveToAttribute("Two-Letter", "");
TwoLetter = twoLetter.Value;
twoLetter.MoveToParent();
twoLetter.MoveToAttribute("NameandTitle", "");
NameandTitle = twoLetter.Value;
twoLetter.MoveToParent();
dr = dt.NewRow();
dr["Two-Letter"]= TwoLetter;
dr["NameandTitle"] = NameandTitle;
dt.Rows.Add(dr);





//AddItem(TwoLetter, email);
}
DataView dv = new DataView(dt);
dv.Sort = "Two-Letter";
foreach (DataRowView drv in dv)
{
TwoLetter = drv["Two-Letter"].ToString();
NameandTitle = drv["NameandTitle"].ToString();
AddItem(TwoLetter, NameandTitle);

}


}

private void RemoveFirstItem()
{
XPathNavigator DOM = DataSources["options"].CreateNavigator();
XPathNavigator group1 = DOM.SelectSingleNode("//options",
NamespaceManager);
XPathNavigator field1 = DOM.SelectSingleNode("//options/option",
NamespaceManager);
field1.DeleteSelf();

}


private void AddItem(string itemName, string itemDisplayName)
{
XPathNavigator DOM = DataSources["options"].CreateNavigator();
XPathNavigator group1 = DOM.SelectSingleNode("//options",
NamespaceManager);
XPathNavigator field1 = DOM.SelectSingleNode("//options/option",
NamespaceManager);
XPathNavigator newNode = field1.Clone();
newNode.SelectSingleNode("value").SetValue(itemName);
newNode.SelectSingleNode("displayname").SetValue(itemDisplayName);
group1.AppendChild(newNode);
}
}

It almost works.

thanks,
Stephan

 

[S.Y.M. Wong-A-Ton]

I reconstructed your code and found out that it's not your code. Your code
works fine. The code in my article contains a bug, which you are the first to
report.

The options XML starts out having two rows (this is done for InfoPath to
recognize the options as a repeating group). The first row is deleted using
the RemoveFirstItem() method before going into the loop to add items. The
AddItem() method then clones the option node that is left every time when an
item is added. So when the last item has been added, it will keep referencing
the last item you added. But since the option node that is cloned every time
is just a "helper" node to add items, it should be deleted when all items
have been added.

So if you add RemoveFirstItem() again *after* you've added all of the items,
your sorting should work fine. I've tested it and it works for me.

So try this:

foreach (DataRowView drv in dv)
{
TwoLetter = drv["Two-Letter"].ToString();
NameandTitle = drv["NameandTitle"].ToString();
AddItem(TwoLetter, NameandTitle);
}
RemoveFirstItem();


Thank you for reporting the bug. I'll update the code in the article when I
get a chance.
---
S.Y.M. Wong-A-Ton
http://www.bizsupportonline.net

--
Stephan Onisick

My whole objective in this exercise is to sort and filter an InfoPath Data
Connection from a SharePoint List.

Using the article from S.Y.M. Wong-A-Ton, "Programmatically add items to a
drop-down list box in InfoPath 2007",
http://www.bizsupportonline.net/inf...populate-drop-down-list-box-infopath-2007.htm, as a template.

I used one of my Datasources that returns data from a SharePoint List.

After Extracting the data. I put it into a Datatable and defined a sort
filter. Then from the data view I filled the datasource "option" with the
sorted data.

Once everything completes, I get a dropdown with all my items--bit with the
last item showing up twice--once at the beginning of the list.

public void FormEvents_Loading(object sender, LoadingEventArgs e)
{
RemoveFirstItem();


// /dfs:myFields/dfs:dataFields/dfs:Two-LetterInfo
XPathNodeIterator twoLetters =
DataSources["Two-LetterInfo"].CreateNavigator().Select("/dfs:myFields/dfs:dataFields/dfs:Two-LetterInfo[@NotInTwoLetter='0']", NamespaceManager);
//XPathNodeIterator twoLetters =
DataSources["Two-LetterInfo"].CreateNavigator().Select("/dfs:myFields/dfs:dataFields/dfs:Two-LetterInfo", NamespaceManager);
string TwoLetter = string.Empty;
string NameandTitle = string.Empty;
DataTable dt = new DataTable("SortedTwoLetters");
DataRow dr;
dt.Columns.Add("Two-Letter", typeof(System.String));
dt.Columns.Add("NameandTitle", typeof(System.String));






foreach (XPathNavigator twoLetter in twoLetters)
{
//string TwoLetter =
twoLetter.SelectSingleNode("/dfs:myFields/dfs:dataFields/dfs:Two-LetterInfo[@Two-Letter]", NamespaceManager).Value;
//string TwoLetter = twoLetter.OuterXml;
//string TwoLetter =
twoLetter.SelectSingleNode("/my:Two-Letter", NamespaceManager).Value;

//string email = twoLetter.SelectSingleNode("[@email]",
NamespaceManager).Value;

//string email = twoLetter.SelectSingleNode("[@email]",
NamespaceManager).Value;
twoLetter.MoveToAttribute("Two-Letter", "");
TwoLetter = twoLetter.Value;
twoLetter.MoveToParent();
twoLetter.MoveToAttribute("NameandTitle", "");
NameandTitle = twoLetter.Value;
twoLetter.MoveToParent();
dr = dt.NewRow();
dr["Two-Letter"]= TwoLetter;
dr["NameandTitle"] = NameandTitle;
dt.Rows.Add(dr);





//AddItem(TwoLetter, email);
}
DataView dv = new DataView(dt);
dv.Sort = "Two-Letter";
foreach (DataRowView drv in dv)
{
TwoLetter = drv["Two-Letter"].ToString();
NameandTitle = drv["NameandTitle"].ToString();
AddItem(TwoLetter, NameandTitle);

}


}

private void RemoveFirstItem()
{
XPathNavigator DOM = DataSources["options"].CreateNavigator();
XPathNavigator group1 = DOM.SelectSingleNode("//options",
NamespaceManager);
XPathNavigator field1 = DOM.SelectSingleNode("//options/option",
NamespaceManager);
field1.DeleteSelf();

}


private void AddItem(string itemName, string itemDisplayName)
{
XPathNavigator DOM = DataSources["options"].CreateNavigator();
XPathNavigator group1 = DOM.SelectSingleNode("//options",
NamespaceManager);
XPathNavigator field1 = DOM.SelectSingleNode("//options/option",
NamespaceManager);
XPathNavigator newNode = field1.Clone();
newNode.SelectSingleNode("value").SetValue(itemName);
newNode.SelectSingleNode("displayname").SetValue(itemDisplayName);
group1.AppendChild(newNode);
}
}

It almost works.

thanks,
Stephan