SharePoint Integration

Re: Wildcard code fails only once connection has been converted to sharepoint...please help

chammond — Mon, 16 May 2011 07:42:36 GMT

Hi Steph, thanks for the SSO explanation, I tried this but still got errors.

I seem to have found a solution to the problem....I had to publish the form as an administrator approved form, adding the form into the farm in Central Administration and then activating the form at the site collection level. All is working now

Thanks for all the help and suggestions. I hope I can help some others out on this forum soon.

Speak soon :-)

Re: Wildcard code fails only once connection has been converted to sharepoint...please help

Steph11 — Fri, 13 May 2011 15:42:33 GMT

I've done this before using the Secure store service in sharepoinrt 2010 Central Administration -> adding a new target Application ID -> Setting the credentials (the user or group that uses the form) -> Windows User Name (domain user with permissions in SQL).

After that I've added the following line in the udc files <udc:Authentication><udc:SSO AppId='YourAppTargetId' CredentialType='NTLM' /></udc:Authentication>.

Let me know if works for you

Re: Wildcard code fails only once connection has been converted to sharepoint...please help

Hilary Stoupa — Fri, 13 May 2011 15:42:29 GMT

What exactly is the error you are getting? I see in your first post, you say the error is that the data connection can't be accessed -- but I'm wondering about exact syntax, specifically when you run in debug ....

The code line in this post seems to be different from what you were using before, so I'd anticipate that other lines have changed -- what is the code prior to this now?

Re: Wildcard code fails only once connection has been converted to sharepoint...please help

chammond — Fri, 13 May 2011 09:48:01 GMT

Just an update, I edited the udc file to use explicit credentials, using a domain login I know has access to the database, can login, and can view the table i am trying to view with the form.

I still get the error....

When in debug, the connection fails at the point below:

//Have InfoPath construct an SQL command that is based on all other field values.

string strMySQLCommand = cn.BuildSqlFromXmlNodes(xnAttendQuery);

I have also tried a SQL account, and hardcoded this into the file, but I get the same error.

Can someone please please please help if possible, I am running out of time to get this up and running.

Re: Wildcard code fails only once connection has been converted to sharepoint...please help

chammond — Fri, 13 May 2011 08:25:52 GMT

Hi Patrick,

Thanks very much for the reply.

I did try getting the udcx file to use an SSO mapping to give it elevated priviledges.

I dont mind hard coding some credentials into the connection file if it will get it to work (there is no real sensitive data in this database). Could you give me an idea how to do this? and what permissions the user account would need in the SQL database?

Thanks once again for your help

Cheers

Chris

Re: Wildcard code fails only once connection has been converted to sharepoint...please help

Patrick Halstead — Fri, 13 May 2011 08:17:47 GMT

It's a permissions issue. Since the UDC will connect to SQL directly you need to add credentials to the UDC file. Unfortunately, these are viewable by all. Is that what you want?

If not, you'll need to write a Web service, or just install our DBXL one - no code and you'll be up and running in 30 minutes with complex queries.

Cheers!

Re: Wildcard code fails only once connection has been converted to sharepoint...please help

chammond — Fri, 13 May 2011 07:50:31 GMT

Please, any help on this would be great...starting to pull my hair out over this now (what I have left anyway) - :-)

Thanks in advance of any assistance

Cheers

Chris

Wildcard code fails only once connection has been converted to sharepoint...please help

chammond — Wed, 11 May 2011 19:10:42 GMT

Hi there everyone,

I wanted to use wildcards in a serach form which queries a SQL database for student attendance. I followed the guide found here to write the appropriate code, which works fine in the client application....in fact it works great!! My actual code is shown below:

public void btnQuery_Clicked(object sender, ClickedEventArgs e)
        {
            //Create an XPathNavigator object for the main data source
            XPathNavigator xnMain = this.MainDataSource.CreateNavigator();

            //Create an AdoQueryConnection from the main data source by "casting" the default
            //data connection to an "AdoQueryConnection"
            AdoQueryConnection cn = (AdoQueryConnection)this.DataConnections["Main connection"];

//Obtain the default SQL command for the form.
string strOrigSQLCommand = cn.Command.ToString();

// Obtain the query node that you want to change.
XPathNavigator xnAttendQuery = xnMain.SelectSingleNode("/dfs:myFields/dfs:queryFields/q:RegisterMarks", this.NamespaceManager);

            //Obtain the text that was entered for the wildcard character search, and then clear the current query parameter so that InfoPath will leave the current query parameter blank.
            string strStudRef = xnAttendQuery.SelectSingleNode("@StudRef", this.NamespaceManager).Value;
            xnAttendQuery.SelectSingleNode("@StudRef", this.NamespaceManager).SetValue(string.Empty);

            //MessageBox.Show(strStudRef);

            string strSurname = xnAttendQuery.SelectSingleNode("@Surname", this.NamespaceManager).Value;
            xnAttendQuery.SelectSingleNode("@Surname", this.NamespaceManager).SetValue(string.Empty);

//MessageBox.Show(strSurname);

string strForenames = xnAttendQuery.SelectSingleNode("@Forenames", this.NamespaceManager).Value;
xnAttendQuery.SelectSingleNode("@Forenames", this.NamespaceManager).SetValue(string.Empty);

string strReference = xnAttendQuery.SelectSingleNode("@Reference", this.NamespaceManager).Value;
xnAttendQuery.SelectSingleNode("@Reference", this.NamespaceManager).SetValue(string.Empty);

string strName = xnAttendQuery.SelectSingleNode("@Name", this.NamespaceManager).Value;
xnAttendQuery.SelectSingleNode("@Name", this.NamespaceManager).SetValue(string.Empty);

string strDay = xnAttendQuery.SelectSingleNode("@Day", this.NamespaceManager).Value;
xnAttendQuery.SelectSingleNode("@Day", this.NamespaceManager).SetValue(string.Empty);

string strDate = xnAttendQuery.SelectSingleNode("@Date", this.NamespaceManager).Value;
xnAttendQuery.SelectSingleNode("@Date", this.NamespaceManager).SetValue(string.Empty);

string strCurrentStatus = xnAttendQuery.SelectSingleNode("@Current_status", this.NamespaceManager).Value;
xnAttendQuery.SelectSingleNode("@Current_status", this.NamespaceManager).SetValue(string.Empty);

string strMark = xnAttendQuery.SelectSingleNode("@Mark", this.NamespaceManager).Value;
xnAttendQuery.SelectSingleNode("@Mark", this.NamespaceManager).SetValue(string.Empty);

string strMarkDescription = xnAttendQuery.SelectSingleNode("@Mark_Description", this.NamespaceManager).Value;
xnAttendQuery.SelectSingleNode("@Mark_Description", this.NamespaceManager).SetValue(string.Empty);

//Have InfoPath construct an SQL command that is based on all other field values.
string strMySQLCommand = cn.BuildSqlFromXmlNodes(xnAttendQuery);

            //Add Fields to the query so that Job_Title can support wildcard characters.
            if (strStudRef != string.Empty)
            {
                if (strMySQLCommand != string.Empty)
                    strMySQLCommand += " AND ";

                //Check whether the user entered the wildcard character (%) as part of the title.
                if (strStudRef.Contains("%"))
                    strMySQLCommand += "[StudRef] LIKE '" + strStudRef + "'";
                else
                    strMySQLCommand += "[StudRef] LIKE '" + strStudRef + "%'";
            }

//Check Surname
//MessageBox.Show(strSurname);

            if (strSurname != string.Empty)
            {
                if (strMySQLCommand != string.Empty)
                    strMySQLCommand += " AND ";

                //Check whether the user entered the wildcard character (%) as part of the title.
                if (strSurname.Contains("%"))
                    strMySQLCommand += "[Surname] LIKE '" + strSurname + "'";
                else
                    strMySQLCommand += "[Surname] LIKE '" + strSurname + "%'";
            }

            if (strForenames != string.Empty)
            {
                if (strMySQLCommand != string.Empty)
                    strMySQLCommand += " AND ";

                //Check whether the user entered the wildcard character (%) as part of the title.
                if (strForenames.Contains("%"))
                    strMySQLCommand += "[Forenames] LIKE '" + strForenames + "'";
                else
                    strMySQLCommand += "[Forenames] LIKE '" + strForenames + "%'";
            }

            if (strReference != string.Empty)
            {
                if (strMySQLCommand != string.Empty)
                    strMySQLCommand += " AND ";

                //Check whether the user entered the wildcard character (%) as part of the title.
                if (strReference.Contains("%"))
                    strMySQLCommand += "[Reference] LIKE '" + strReference + "'";
                else
                    strMySQLCommand += "[Reference] LIKE '" + strReference + "%'";
            }

            if (strName != string.Empty)
            {
                if (strMySQLCommand != string.Empty)
                    strMySQLCommand += " AND ";

                //Check whether the user entered the wildcard character (%) as part of the title.
                if (strName.Contains("%"))
                    strMySQLCommand += "[Name] LIKE '" + strName + "'";
                else
                    strMySQLCommand += "[Name] LIKE '" + strName + "%'";
            }

            if (strDay != string.Empty)
            {
                if (strMySQLCommand != string.Empty)
                    strMySQLCommand += " AND ";

                //Check whether the user entered the wildcard character (%) as part of the title.
                if (strDay.Contains("%"))
                    strMySQLCommand += "[Day] LIKE '" + strDay + "'";
                else
                    strMySQLCommand += "[Day] LIKE '" + strDay + "%'";
            }

            if (strDate != string.Empty)
            {
                if (strMySQLCommand != string.Empty)
                    strMySQLCommand += " AND ";

                //Check whether the user entered the wildcard character (%) as part of the title.
                if (strDate.Contains("%"))
                    strMySQLCommand += "[Date] LIKE '" + strDate + "'";
                else
                    strMySQLCommand += "[Date] LIKE '" + strDate + "%'";
            }

            if (strCurrentStatus != string.Empty)
            {
                if (strMySQLCommand != string.Empty)
                    strMySQLCommand += " AND ";

                //Check whether the user entered the wildcard character (%) as part of the title.
                if (strCurrentStatus.Contains("%"))
                    strMySQLCommand += "[Current status] LIKE '" + strCurrentStatus + "'";
                else
                    strMySQLCommand += "[Current status] LIKE '" + strCurrentStatus + "%'";
            }

            if (strMark != string.Empty)
            {
                if (strMySQLCommand != string.Empty)
                    strMySQLCommand += " AND ";

                //Check whether the user entered the wildcard character (%) as part of the title.
                if (strMark.Contains("%"))
                    strMySQLCommand += "[Mark] LIKE '" + strMark + "'";
                else
                    strMySQLCommand += "[Mark] LIKE '" + strMark + "%'";
            }

            if (strMarkDescription != string.Empty)
            {
                if (strMySQLCommand != string.Empty)
                    strMySQLCommand += " AND ";

                //Check whether the user entered the wildcard character (%) as part of the title.
                if (strMarkDescription.Contains("%"))
                    strMySQLCommand += "[Mark Description] LIKE '" + strMarkDescription + "'";
                else
                    strMySQLCommand += "[Mark Description] LIKE '" + strMarkDescription + "%'";
            }

            //Construct the full query string.
            string strSQLQuery = strOrigSQLCommand;
            if (strMySQLCommand != string.Empty)
                strSQLQuery += " WHERE " + strMySQLCommand;

//MessageBox.Show(strSQLQuery);

            //Set the command and run the query.
            cn.Command = strSQLQuery;
            cn.Execute();

            //Restore all the user entries to the Query fields so that the user entries will
            //be available if you want to change and to rerun the query.
            xnAttendQuery.SelectSingleNode("@StudRef", this.NamespaceManager).SetValue(strStudRef);
            xnAttendQuery.SelectSingleNode("@Surname", this.NamespaceManager).SetValue(strSurname);
            xnAttendQuery.SelectSingleNode("@Forenames", this.NamespaceManager).SetValue(strForenames);
            xnAttendQuery.SelectSingleNode("@Reference", this.NamespaceManager).SetValue(strReference);
            xnAttendQuery.SelectSingleNode("@Name", this.NamespaceManager).SetValue(strName);
            xnAttendQuery.SelectSingleNode("@Day", this.NamespaceManager).SetValue(strDay);
            xnAttendQuery.SelectSingleNode("@Date", this.NamespaceManager).SetValue(strDate);
            xnAttendQuery.SelectSingleNode("@Current_status", this.NamespaceManager).SetValue(strCurrentStatus);
            xnAttendQuery.SelectSingleNode("@Mark", this.NamespaceManager).SetValue(strMark);
            xnAttendQuery.SelectSingleNode("@Mark_Description", this.NamespaceManager).SetValue(strMarkDescription);

//Restore the default table command (for the next time).
cn.Command = strOrigSQLCommand;

            //Clean up
            xnMain = null;
            cn = null;
           xnAttendQuery = null;
        }

Like I say..this worked fine.......until I converted the connection file and published to a SharePoint library. I have approved the connection file, I have tried using a SSO mapping to run the connection as Administrator...still no luck.

I just get the error saying the data source could not be connected to.

I assume I am referencing the connection in my code in a way that is not supported in published connections.

I would really appreciate some help with this.

Thank you very much in advance of your assistance.

Cheers

Chris