OK, I am trying something I think should be simple and I am messing up somewhere. I want to query a table in SQL. I am doing it in the code here to clarify my question. I am receiving data back and have no errors.
I want to select from an Employee table based on lastname. I am querying "Ryan" and I should get 3 rows of data. I always get one. What am I doing wrong or over-thinking? I am still pretty new to Infopath. In fact, this is my first form. I have comments in the code. Thanks.
using Microsoft.Office.InfoPath;
using System;
using System.DirectoryServices;
using System.Xml;
using System.Xml.XPath;
using Microsoft.SharePoint;
using System.Data;
using System.Data.SqlClient;
namespace loopthru
{
public partial class FormCode
{
public void InternalStartup()
{
((ButtonEvent)EventManager.ControlEvents["btnGo"]).Clicked += new ClickedEventHandler(btnGo_Clicked);
}
public void btnGo_Clicked(object sender, ClickedEventArgs e)
{ // References used are:
// References:
// Microsoft.Office.Infopath
// Microsoft.Office.Interop.Infopath
// Microsoft.SharePoint (copy to project from <DRIVE>:\Program Files\Common Files\Microsoft Shared\web server extensions\60\ISAPI)
// Microsoft.VisualStudio.Tools.Application.Adapter
// Microsoft.VisualStudio.Tools.Application.Contract
// System
// System.AddIn.Contract
// System.Data
// System.DirectoryServices
// System.Web
// System.XML
try
{
// Get the employee name or the current user.
string userName = null;
XPathNavigator navi = MainDataSource.CreateNavigator();
string fieldValue = navi.SelectSingleNode("//my:sfield", NamespaceManager).Value;
//sfield stands for SearchField.
if (fieldValue != "")
{
userName = fieldValue;
}
else
{
userName = this.Application.User.UserName;
}
//connect to my Database
SqlConnection conn = new SqlConnection("data source=DataBaseSource1;database=Database;User id=xxxxx;password=xxxxx;");
SqlCommand command = new SqlCommand();
command.Connection = conn;
command.CommandText = "SELECT DISTINCT FirstName, MidInt, LastName"
+ " FROM EmployeeTable "
+ " where LastName ='" + userName + "'";
command.CommandType = CommandType.Text;
conn.Open();
SqlDataAdapter da = new SqlDataAdapter(command);
DataSet ds = new DataSet();
da.Fill(ds, "Emps");
//I may not need this but it is clean.
SqlDataReader rdr = command.ExecuteReader();
if (rdr.HasRows)
{
rdr.Read();
int FirstName = rdr.GetOrdinal("FirstName");
int MidInt = rdr.GetOrdinal("MidInt");
int LastName = rdr.GetOrdinal("LastName");
XPathNavigator nav = MainDataSource.CreateNavigator();
////This next line is where I think I am having problems. I know I am doing this part wrong.
//// I think I need to iterate through the recordset returned in the DataSet ds "Emps" but have no clue how.
XPathNodeIterator rows = nav.Select("/my:myFields/my:Emp/my:EmpData", NamespaceManager);
foreach (XPathNavigator row in rows)
//while (rows.MoveNext())
//// I tried while and foreach. everything here works except that I get only one record and it jumps out of the loop.
//// I should get three rows based on the lastname entry I place in the my:sfield textbox and click btnGo.
{
if (rdr[FirstName] != null) rows.Current.SelectSingleNode("/my:myFields/my:Emp/my:EmpData/my:fname", NamespaceManager).SetValue(rdr.GetString(FirstName));
if (rdr[MidInt] != null) nav.SelectSingleNode("/my:myFields/my:Emp/my:EmpData/my:midint", NamespaceManager).SetValue(rdr.GetString(MidINt));
if (rdr[LastName] != null) nav.SelectSingleNode("/my:myFields/my:Emp/my:EmpData/my:lname", NamespaceManager).SetValue(rdr.GetString(LastName));
}
}
}
catch (Exception ex)
{
throw new InvalidOperationException("data could not be read", ex);
}
}
//The visual hierarchy for the Main DataSource in Infopath looks like
//myFields
//Emp
//EmpData
//fname
//midint
//lname
//sfield
//Also: I didn't add a datasource within the Infopath interface - Add Data Connection. I only did so in the code behind.
}
}
