SQL Database Integration

Re: From Sharepoint List to Access DB

Hilary Stoupa — Wed, 24 Apr 2013 00:48:56 GMT

Can you test your intended query in SQL Manager? That is to say - a like clause should work w/ 2 or more words - like '%East Lansing%'

Re: From Sharepoint List to Access DB

ndisa — Tue, 23 Apr 2013 17:58:07 GMT

Thanks Ill let you know if I get it worked out. One other question is there a way to query with multiple words? I am getting no returns if I use two words. Exp: "Lansing" I get multi returns (Lansing & East Lansing" If I search on "East Lansing" I get 0 returns.

Nick

Re: From Sharepoint List to Access DB

Hilary Stoupa — Tue, 23 Apr 2013 15:15:06 GMT

So, your issue is here:

XPathNavigator queryField = MainDataSource.CreateNavigator().SelectSingleNode("/dfs:myFields/dfs:queryFields/q:Provider_Info/@Provider_Name", NamespaceManager);

This is because your XPath is not a field in the main data source, so queryField is null. You need something like the below example, replacing NAME OF YOUR DATA SOURCE GOES HERE with your data source name.

XPathNavigator queryField = this.DataSources["NAME OF YOUR DATA SOURCE GOES HERE"].CreateNavigator().SelectSingleNode("/dfs:myFields/dfs:queryFields/q:Provider_Info/@Provider_Name", NamespaceManager);

Re: From Sharepoint List to Access DB

ndisa — Tue, 23 Apr 2013 14:49:13 GMT

Hi Hilary,

I successfully built a form that utilizes your code to do wildcard searches. In that form the data connection is the "Main" Data connection. However when I decided to create a new form wherein the data I want to wildcard query is not the "main" but rather the "Secondary" data connection I get the following errors:

System.NullReferenceException
Object reference not set to an instance of an object.
   at DataMining_v2.FormCode.BtnQuery_Clicked(Object sender, ClickedEventArgs e)
   at Microsoft.Office.InfoPath.Internal.ButtonEventHost.OnButtonClick(DocActionEvent pEvent)
   at Microsoft.Office.Interop.InfoPath.SemiTrust._ButtonEventSink_SinkHelper.OnClick(DocActionEvent pEvent)

Below is my code but is really yours with a little change:

XPathNavigator queryField = MainDataSource.CreateNavigator().SelectSingleNode("/dfs:myFields/dfs:queryFields/q:Provider_Info/@Provider_Name", NamespaceManager);

string querySeed = queryField.Value;

queryField.SetValue("");

string customQuery = " where ([Provider Name] like '%" + querySeed + "%')";

AdoQueryConnection newQuery = (AdoQueryConnection)MainDataSource.QueryConnection;

string origCommand = newQuery.Command.ToString();

newQuery.Command = origCommand + customQuery;

newQuery.Execute();

newQuery.Command = origCommand;

queryField.SetValue(querySeed);

Any suggestions or direction pointing would be greatly appreciated.

ndisa

Re: From Sharepoint List to Access DB

Hilary Stoupa — Thu, 28 Feb 2013 20:06:26 GMT

Replied in your other thread: http://www.infopathdev.com/forums/p/25249/87570.aspx#87570

Re: From Sharepoint List to Access DB

RobSil1976 — Thu, 28 Feb 2013 15:26:44 GMT

Hilary,

How do you read the results of the query and use it in your InfoPath Form? After the .Execute() how would you see what came back from the DB?

Thx,

Rob

Re: From Sharepoint List to Access DB

SeanMG — Wed, 16 Nov 2011 19:44:23 GMT

Was able to fill in the gaps with this article:

http://support.microsoft.com/kb/826992

Re: From Sharepoint List to Access DB

slashwt — Wed, 10 Aug 2011 16:58:45 GMT

Hello Hillary, This is a great forum and it has helped me a lot. Thanks! I was able to follow your sample below and apply it to a form I'm working on, I'm able to build the new query to execute a stored proc, and I see the new string with the proper syntax, and it seems like the form is executing it, but I do not see any records generated in the database when the stored proc was executed. Is this not possible? or I am missing a step. thansk!

Re: From Sharepoint List to Access DB

gshetye — Mon, 21 Mar 2011 09:10:37 GMT

Thanks a lot...

Re: From Sharepoint List to Access DB

Hilary Stoupa — Fri, 18 Mar 2011 15:54:54 GMT

Hey, there, welcome!

You need to add the wildcard indicator at the begining and end of your search term:

string customQuery = " where ([Field1] like '%" + querySeed + "%')";

Re: From Sharepoint List to Access DB

gshetye — Fri, 18 Mar 2011 06:30:34 GMT

Hello Hilary

I am new in this forum and inforpath user.

This query helps lot for me.

But i got stuck at one place. When I use this quey for seaching within one of the column in my MS Access database it shows records when it start with search string.

I want to search record when search string content anywhere in column field.

Could you please help me in this.

I have used below code

//get the value of the query field we want to use with a wild card

XPathNavigator queryField = MainDataSource.CreateNavigator().SelectSingleNode("/dfs:myFields/dfs:queryFields/q:List/@Field1", NamespaceManager);

string querySeed = queryField.Value;

//clear the query field so that it is ignored in the query

queryField.SetValue("");

//create our custom where clause -- you have some choices here. I've added in the wildcard. You could choose not to, if your users are savvy and can fill it in themselves when searching, all up to you.

string customQuery = " where ([Field1] like '" + querySeed + "%')";

//get the original command (we'll need to set it back)

AdoQueryConnection newQuery = (AdoQueryConnection)MainDataSource.QueryConnection;

string origCommand = newQuery.Command.ToString();

//set the query to use the custom command

newQuery.Command = origCommand + customQuery;

//annnnnnddddd, now we run the query using our shiny new command

newQuery.Execute();

//put everything back the way it was.... you're going to need it!

newQuery.Command = origCommand;

//set the query field back for the user, so they know what they typed in!

queryField.SetValue(querySeed);

Regards

Gaurav

Re: From Sharepoint List to Access DB

cmufandaidza — Mon, 01 Dec 2008 16:45:25 GMT

I'm try to query the secondary data source and have the results displayed in the repeat table in the main data source. I have followed so many online help as I could, but with not much success at the moment. I can query the secondary data source. the major problem I'm having is with the last bit, how to display the results copied from secondary data source repeat table to the table in the main data source. How will I refresh to display these results.

Please I will appreciate your help more if you include code samples.

Thanks

the code that I have used so far is

public void CTRL10_7_Clicked(object sender, ClickedEventArgs e)
        {
            // retrieve field1's value and store it in a string
            string field1 = MainDataSource.CreateNavigator().SelectSingleNode("//dfs:myFields/dfs:queryFields/q:FRAQSTemplate/@CatID", NamespaceManager).Value;
            const string queryString = "select * from [FRAQSTemplate] where [CatID] = 3";

            // make sure that the search string isn't blank
            if (!string.IsNullOrEmpty(field1))
            {
                // instantiate an object to access the data connection. TestTable1 is the name of the data connection
                AdoQueryConnection dc = (AdoQueryConnection)DataConnections["SecondaryConnection"];

// insert field1's value into the query and assign it to the data connection's query
dc.Command = string.Format(queryString);

// run the query
dc.Execute();

                //AdoQueryConnection newQuery = (AdoQueryConnection)MainDataSource.QueryConnection;
                //newQuery.Execute();
            }
            else
            {
                // show an error when field1 is blank
                MessageBox.Show("The search field cannot be blank!");
            }

}

        public void CTRL19_7_Clicked(object sender, ClickedEventArgs e)
        {
            // store all of the column names in an array, to be used below
            string[] columnNames = { "ID", "CatID", "QuestionNO", "Question", "Active", "Version", "TS" };
            // store the namespace URLs as strings for easy reference
            string myNamespace = NamespaceManager.LookupNamespace("my");
            // d is the prefix of the repeating nodes in the secondary data source results, as seen in the data source taskpane
            string dNamespace = NamespaceManager.LookupNamespace("d");
            // the attributes on the secondary data source results have no prefix, as seen in the data source taskpane
            string blankNamespace = NamespaceManager.LookupNamespace("");

            // select the node where the results will be placed
            XPathNavigator dataFields = MainDataSource.CreateNavigator().SelectSingleNode("//dfs:myFields/my:QueryResults", NamespaceManager);
            // select the secondary data source node where the results are located
            XPathNavigator queryDataFields = DataSources["SecondaryConnection"].CreateNavigator();
            queryDataFields = queryDataFields.SelectSingleNode("//dfs:myFields/dfs:dataFields", NamespaceManager);

            // remove all ResultRow children of the my:QueryResults node in the main data source
            while (dataFields.MoveToChild("ResultRow", myNamespace))
            {
                dataFields.DeleteSelf();
            }

            // iterate through each results node in the secondary data source
            foreach (XPathNavigator resultRow in queryDataFields.SelectChildren("FRAQSTemplate", dNamespace))
            {
                // append a new child to the my:QueryResults node
                using (XmlWriter writer = dataFields.AppendChild())
                {
                    // write the element name
                    writer.WriteStartElement("ResultRow", myNamespace);
                    // copy over each of the attributes
                    foreach (string colName in columnNames)
                    {
                        writer.WriteStartElement(colName, myNamespace);
                        MessageBox.Show(colName.ToString());
                        writer.WriteValue(resultRow.GetAttribute(colName, blankNamespace));
                        writer.WriteEndElement();
                    }
                    writer.WriteEndElement();
                }
            }
        }

Re: From Sharepoint List to Access DB

BuildPro Guru — Fri, 07 Nov 2008 15:35:45 GMT

I think that I will be hanging around the forums for a long time to come, and, who knows, I may be able to answer someone else's question someday ;P

I think that I am starting to get what you are talking about. I have a SQL book and a C# book now and by studying a bit, I am getting the hang of it.

Thanks again for all your help, Hilary - you rock!

Re: From Sharepoint List to Access DB

Hilary Stoupa — Fri, 07 Nov 2008 15:27:03 GMT

Tell you what -- keep using the forum, and we'll call it even... :) Glad it all works. Keep in mind, as you use different forms with different data connections, the methodology you use to split and re-form the string that contains your SQL command will change. Currently, we can split on the first brace, because you are looking at the buyers table.... but what if you wanted to add your 'where' clause (SQL) to the lots table? Then you'd have to find a different way to cut up and reform that string, so don't be surprised if this solution is *not* universal, okay?

Re: From Sharepoint List to Access DB

BuildPro Guru — Fri, 07 Nov 2008 12:35:04 GMT

Hilary, you are the Grand Poo Bah Master! Everything works perfectly! Thank you so much. This is going to be a big help. I hope that there is some way that I can return all of your help.