Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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.       

    }

}
share|improve this question
add comment

2 Answers

This doesn't really have anything to do with SharePoint but your problem might be that you are setting values in your main Navigator (nav.) instead of the current row in your empdata (rows.Current.) on the second and third lines in your iteration.

                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));
share|improve this answer
    
Thanks Dave. I know that Infopath can live outside of SharePoint, but indeed, I am hosting it within XMLFormView on a SharePoint site. If there's a more appropriate forum - I will be happy to post there. Also, my bad. I was trying some new things and forgot to change it back. It still doesn't work when I fix that back. –  Sean Feb 10 '12 at 14:37
    
I figured this all out and I am extending my solution. To simplify, I had 2 major problems. –  Sean Feb 16 '12 at 22:17
add comment

1 I had to create a navigator outside my loop I changed it to:

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

            if (rdr.HasRows)
            {
                while (rdr.Read())
                {

I needed to write each value out in turn thus:

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:lname", NamespaceManager).SetValue(rdr.GetString(LastName));

EmpNewData.InsertAfter(EmpNewData);

Oh, and when you get the dupe record:

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

If anyone needs the entire code. I am happy to share.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.