InfoPath Dev

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 coded 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 
            //  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) rows.Current.SelectSingleNode("/my:myFields/my:Emp/my:EmpData/my:midint", NamespaceManager).SetValue(rdr.GetString(MidINt));
                        if (rdr[LastName] != null) rows.Current.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.       

    }

}

It looks as though you are reading a list of employee records from an SQL connection and then want to load those into a repeating section/table within your form.

I don't see any part of your code which would iterate through the records contained within rdr object. You only do one rdr.Read (so that puts you on the first record). Then you are asking your form to return whatever records it *already has* in the EmpData group. If it's a new form I would suspect your EmpData group only contains one 'record'. You then ask that one record to load itself with the values from the current record in your rdr object, which it does. The problem is, you are looping through the EmpData records, not the rsr.Records, and there is only 1 EmpData record, so it finishes.

Your code logic may need to be something like:

1. Read SQL records into rdr

2. Loop through records returned in rdr

2.1 Create new EmpData group

2.2 Load current rdr record details into new EmpData group

2.3 Next record in rdr, loop to 2.

Sorry if I'm misread your intention, this is my stab at trying to help :-) Good luck!

First, thanks very much for responding.  OK, I am not sure I understand - but I had a go. I am looping through the rdr like you said. It is looping fine now as I set my breakpoint on the while loop.  However, it is only printing the very last record to the form fields instead of the very first.  I am unsure what it means to create a new EmpData group.  Once again - thanks!

SqlDataReader rdr = command.ExecuteReader();
                if (rdr.HasRows)
                {

                    rdr.Read();
                    while (rdr.Read())
                    {
                        int FirstName = rdr.GetOrdinal("FirstName");
                        int LastName = rdr.GetOrdinal("LastName");

                       
                        XPathNavigator nav = MainDataSource.CreateNavigator();
                        XPathNodeIterator rows = nav.Select("/my:myFields/my:Emp/my:EmpData", NamespaceManager);
                       
                       
                       
                            if (rdr[FirstName] != null) rows.Current.SelectSingleNode("/my:myFields/my:Emp/my:EmpData/my:fname", NamespaceManager).SetValue(rdr.GetString(FirstName));
                            if (rdr[LastName] != null) rows.Current.SelectSingleNode("/my:myFields/my:Emp/my:EmpData/my:lname", NamespaceManager).SetValue(rdr.GetString(LastName));
                            //how do I write it out?                    
                           
                       
                       
                    }

                }

Thanks for the help Skarn: I have this working (for the most part).  I have each record on my form now.  However, when I run a new query - the old results remain instead of being replaced by the new query results.  So, if I search on lname = Ryan, all the results appear.  I then change the query to lname = Jones and these results appear above the current 3 records.  Is there a way for the prior query results to be purged or cleared?

if (rdr.HasRows)
                {

                    rdr.Read();
                    XPathNavigator nav = MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:Emp/my:EmpData", NamespaceManager);
                    XPathNavigator EmpNewData = nav.Clone();
               
                    while (rdr.Read())
                    {
                     
                        int FirstName = rdr.GetOrdinal("FirstName");
                        int Incentive = rdr.GetOrdinal("Incentive");
                        int LastName = rdr.GetOrdinal("LastName");
 
                        if (rdr[FirstName] != null) EmpNewData.SelectSingleNode("/my:myFields/my:Emp/my:EmpData/my:fname", NamespaceManager).SetValue(rdr.GetString(FirstName));
                        if (rdr[LastName] != null) EmpNewData.SelectSingleNode("/my:myFields/my:Emp/my:EmpData/my:incentive", NamespaceManager).SetValue(rdr.GetString(Incentive));
                        if (rdr[LastName] != null) EmpNewData.SelectSingleNode("/my:myFields/my:Emp/my:EmpData/my:lname", NamespaceManager).SetValue(rdr.GetString(LastName));

                        EmpNewData.InsertAfter(nav);
                      

                    }
                    // it will run the last record again so get rid of it.
                    XPathNavigator firstNode = MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:Emp/my:EmpData[1]", NamespaceManager);
                    firstNode.DeleteSelf();
            

                }

Can't you use your DeleteSelf command on each of the nodes you've added during the last search when the 'search' button is pressed?

I think you would use SelectNodes with an xpath which selects all the  EmpData nodes currently in the form and then delete them.

Though you probably need to leave at least one (the first one?) as that node is what you are assigning to the nav object and you then clone it. Imagine if you delete all the EmpData nodes, then the nav object creation will fail because there is no EmpData nodes at all!

Yes, I did that.  I reworked some code I found (http://stevemannspath.blogspot.com) and made it into  a new method. I am having issues with it though.

private void RemoveAllItems()
        {
            XPathNavigator rTable = MainDataSource.CreateNavigator();
            XPathNodeIterator tableRows = rTable.Select("/my:myFields/my:Emp/my:EmpData", NamespaceManager);
            int rCnt = tableRows.Count;
           
            if (rCnt > 0)
            {
              // if I use i > 0 it will error immediately.
                for (int i = rCnt; i > 1; i--) 
                {
                    string thisXPath = "/my:myFields/my:Emp/my:EmpData[" + i + "]";
                    XPathNavigator reTable = MainDataSource.CreateNavigator();
                    XPathNavigator reTableRows = reTable.SelectSingleNode(thisXPath, NamespaceManager);
                                    
                    reTableRows.DeleteSelf();

                }
            }
        }

As you state however; I am getting an error if I perform a search and come up empty.  If I search lname = 'zxffer' it will leave me with zero rows available to fill.  I am deleting the first down further in the code.

  XPathNavigator firstNode = MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:Emp/my:EmpData[1]", NamespaceManager);
  firstNode.DeleteSelf();

 I am going to keep working to see if I can fix it.

Thanks.

 I have it. Thanks very much for your help.  I hope I may return the favor some day.  I will clean up the code and post it tomorrow.

Cheers!

 :-) great, though you would have yourself to credit too for getting there in the end with pretty limited help ;-) good luck!

 Thanks! Here's the code.

I have tried to document it well for others that need this. My task to this point was to run a query against a list of Employees in a SQL DB and return one or multiple records.  Each time I change the query input, it should refill the table in front of the user.  If the query is empty, it should find the logged in user. 

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);
            //Next line is if I want to place something in the Change Event later...
            //EventManager.FormEvents.ContextChanged += new ContextChangedEventHandler(FormEvents_ContextChanged);
        }

        private void RemoveAllItems()
        {
            //This is to clear the repeating table results when there is a query beforehand
            //I think this may be improved upon.
            XPathNavigator rTable = MainDataSource.CreateNavigator();
            XPathNodeIterator tableRows = rTable.Select("/my:myFields/my:Emp/my:EmpData", NamespaceManager);
            
            int rCnt = tableRows.Count;            
            if (rCnt > 0)
            {
                for (int i = rCnt; i > 1; i--)
                {
                    string thisXPath = "/my:myFields/my:Emp/my:EmpData[" + i + "]";
                    XPathNavigator reTable = MainDataSource.CreateNavigator();
                    XPathNavigator reTableRows = reTable.SelectSingleNode(thisXPath, NamespaceManager);
                    reTableRows.DeleteSelf();
                }
            }
        }

        public void btnGo_Clicked(object sender, ClickedEventArgs e)
        {
            //clear data first by calling the new method.
            RemoveAllItems();
            
            // References used are:
            //  References:
            //  Microsoft.Office.Infopath
            //  Microsoft.Office.Interop.Infopath
            //  Microsoft.SharePoint
            //  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 user name of the current user.
                //string userName = this.Application.User.UserName;
                string userName = null;

                XPathNavigator navi = MainDataSource.CreateNavigator();
                string fieldValue = navi.SelectSingleNode("//my:sfield", NamespaceManager).Value;
                string qName = null;
                if (fieldValue != "")
                {
                    userName = fieldValue;
                    qName = "LastName";  
                }
                else
                {
                    userName = this.Application.User.UserName;
                    qName = "Alias";
                }


                SqlConnection conn = new SqlConnection("data source=DataBaseSource1;database=Database;User id=xxxxx;password=xxxxx;");
                SqlCommand command = new SqlCommand();

                command.Connection = conn;

                //straight text query that will need to be changed to reflect a Stored Procedure for Input validation.
                //Note: I added Alias here.  This field would match the ActiveDir Logged on ID.
              command.CommandText = "SELECT DISTINCT FirstName, MidInt, LastName, Alias"
                + " FROM EmployeeTable "
                + " where " + qName + " LIKE '" + userName + "%'";

             

                //The next line would change when using a stored proc
                //to - command.CommandType = CommandType.StoredProcedure;
                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();

                XPathNavigator nav = MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:Emp/my:EmpData", NamespaceManager);
                XPathNavigator EmpNewData = nav.Clone();

                if (rdr.HasRows)
                {
                    while (rdr.Read())
                    {
                        int FirstName = rdr.GetOrdinal("FirstName");
                        int MidInt = rdr.GetOrdinal("MidInt");
                        int LastName = rdr.GetOrdinal("LastName");
                        

                        if (rdr[FirstName] != null) EmpNewData.SelectSingleNode("/my:myFields/my:Emp/my:EmpData/my:fname", NamespaceManager).SetValue(rdr.GetString(FirstName));
                        if (rdr[MidInt] != null) EmpNewData.SelectSingleNode("/my:myFields/my:Emp/my:EmpData/my:midint", NamespaceManager).SetValue(rdr.GetString(MidInt));
                        if (rdr[LastName] != null) EmpNewData.SelectSingleNode("/my:myFields/my:Emp/my:EmpData/my:lname", NamespaceManager).SetValue(rdr.GetString(LastName));
                        //This line writes out the row to the repeating table
                        EmpNewData.InsertAfter(EmpNewData);
                    }
                    
                    //It will run the last record again so get rid of it or you will have one duplicate.
                    XPathNavigator firstNode = MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:Emp/my:EmpData[1]", NamespaceManager);
                    firstNode.DeleteSelf();
                }
                else
                {
                    //put some default values in here to preserve the row.                    
                    EmpNewData.SelectSingleNode("/my:myFields/my:Emp/my:EmpData/my:fname", NamespaceManager).SetValue("First");
                    EmpNewData.SelectSingleNode("/my:myFields/my:Emp/my:EmpData/my:midint", NamespaceManager).SetValue("MidInt");
                    EmpNewData.SelectSingleNode("/my:myFields/my:Emp/my:EmpData/my:lname", NamespaceManager).SetValue("Last");
                    //This line writes out the row to the repeating table
                    EmpNewData.InsertAfter(EmpNewData);
                    
                    XPathNavigator firstNode = MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:Emp/my:EmpData[1]", NamespaceManager);
                    firstNode.DeleteSelf();
                }
            }              


            catch (Exception ex)
            {
                throw new InvalidOperationException("data could not be read", ex);
            }

            finally
            {
                //close and dispose of stuff later...
                
            }
        }
    //The visual hierarchy for the Main DataSource in Infopath looks like
    //myFields
        //Emp
            //EmpData
                //fname
                //midint
                //lname
        //sfield

        
        //public void FormEvents_ContextChanged(object sender, ContextChangedEventArgs e)
        //{ //As referred above - if I want to place some code for a change event.
        //    if (e.ChangeType == "ContextNode")
        //    {               
        //        //string myNamespace = NamespaceManager.LookupNamespace("my");
        //        //return;
        //    }
        // }

        }

    }