Wildcard code fails only once connection has been converted to sharepoint...please help - InfoPath Dev
in

InfoPath Dev

Use our Google Custom Search for best site search results.

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

Last post 05-16-2011 12:42 AM by chammond. 7 replies.
Page 1 of 1 (8 items)
Sort Posts: Previous Next
  • 05-11-2011 12:10 PM

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

    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

  • 05-13-2011 12:50 AM In reply to

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

    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

  • 05-13-2011 01:17 AM In reply to

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

    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!

    Patrick Halstead
    Project Manager at Qdabra
  • 05-13-2011 01:25 AM In reply to

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

    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

     

  • 05-13-2011 02:48 AM In reply to

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

    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.

  • 05-13-2011 08:42 AM In reply to

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

    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?

     

    Hilary Stoupa

  • 05-13-2011 08:42 AM In reply to

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

    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 

  • 05-16-2011 12:42 AM In reply to

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

    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 :-)

Page 1 of 1 (8 items)
Copyright © 2003-2019 Qdabra Software. All rights reserved.
View our Terms of Use.