I have successfully populated a drop-dowl list based on this article: http://danstechspot.blogspot.com/2010/05/populate-infopath-2010-drop-down-list.html
I am doing this in C# because of the complexity of my queries and the amount of data in our database.
Here is my code:
public void Department_Changed(object sender, XmlEventArgs e)
{
XPathNavigator DOM = this.MainDataSource.CreateNavigator();
try
{
// Set up SQL connection and query
connection.Open();
string departmentName = e.Site.Value;
command = new SqlCommand();
command.Connection = connection;
command.CommandType = CommandType.Text;
command.CommandText = "SELECT CLIENTS.CLIENT_ID, CLIENTS.FIRSTNAME, CLIENTS.LASTNAME, DEPARTMENTS.DEPT_ID, DEPARTMENTS.NAME, CLIENTS.LASTNAME + ', ' + CLIENTS.FIRSTNAME AS CLIENTFULLNAME FROM DEPARTMENTS DEPARTMENTS INNER JOIN CLTDEPT CLTDEPT ON (CLTDEPT.DEPT_ID = DEPARTMENTS.DEPT_ID) INNER JOIN CLIENTS CLIENTS ON (CLIENTS.CLIENT_ID = CLTDEPT.CLTVISITOR_ID) WHERE ( DEPARTMENTS.NAME = '" + departmentName + "') AND ( CLTDEPT.STATUS = 'A' ) AND ( CLIENTS.FIRSTNAME != '.') GROUP BY CLIENTS.CLIENT_ID, CLIENTS.FIRSTNAME, CLIENTS.LASTNAME, DEPARTMENTS.DEPT_ID, DEPARTMENTS.NAME ORDER BY CLIENTFULLNAME";
dataset = new DataSet();
adapter = new SqlDataAdapter(command);
adapter.Fill(dataset);
// Check dataset
if (dataset.Tables[0].Rows[0][0].ToString() == null || dataset.Tables[0].Rows[0][0].ToString() == "")
{
throw new Exception("No data found.");
}
XPathNavigator node = DOM.SelectSingleNode("/my:myFields/my:group7", this.NamespaceManager);
string ns = node.NamespaceURI;
// Fill options
foreach (DataRow row in dataset.Tables[0].Rows)
{
using (XmlWriter writer = node.AppendChild())
{
writer.WriteStartElement("dropDownGroup", ns);
writer.WriteElementString("dropDownClientName", ns, row["CLIENTFULLNAME"].ToString().Trim());
writer.WriteEndElement();
writer.Flush();
writer.Close();
}
}
}
catch (Exception)
{
}
if (connection != null)
connection.Close();
}
Basically what I am doing is populating Departments in a drop-down list box. Based on what department you select it gives you the clients in that department, pretty simple task.
So in my code I said when the Department field changes show me the clients in that department in the ClientName field.
This is all working great, but one thing. Let's say I select department "A" and it fills in the ClientName field with the clients names then I select a department "B" and it just adds those names to the list. I don't want it to just add the names, I need it to clear the ClientName field first then repopulate the Client name field based on department "B".
Basically everytime I select a department I want it to reset the client name field then input the new data in the client name field.
I just can't seem to figure out how to reset the field properly.
If there is a better way to populate a dropdown using C# I would love to know about it.