How to modify "Run Query" button functionality - InfoPath Dev

InfoPath Dev

Use our Google Custom Search for best site search results.

How to modify "Run Query" button functionality

Last post 01-28-2010 11:11 AM by mganci350. 9 replies.
Page 1 of 1 (10 items)
Sort Posts: Previous Next
  • 01-15-2010 09:29 AM

    How to modify "Run Query" button functionality

    Hi Everybody,

    I have a form based on an Access database with related tables. InfoPath automatically added the Run Query button. Everything works fine except that in order for me to retrieve a record, the query field must contain an exact match. What I would want is for example I enter "infopath" in the ProjectName query field, the result will include all records with ProjectName containing the string "infopath". Any help would be greatly appreciated. Thanks!


  • 01-17-2010 03:59 PM In reply to

    Re: How to modify "Run Query" button functionality

    Here's a sample at Microsoft that uses script. It wouldn't be too tricky to update to managed code. Here's a thread on this forum with some additional information that might help you.

    If you need more help than these resources provide, please post back!
    Hilary Stoupa

  • 01-18-2010 07:19 AM In reply to

    Re: How to modify "Run Query" button functionality

    Thank you so much Hilary. I really don't know how I would ever survive without you. You've been a great help to me and I'm very grateful for this.

    Thanks again.

    - Ric

  • 01-18-2010 09:29 AM In reply to

    Re: How to modify "Run Query" button functionality

    Hi Hilary, Sorry to be back so soon! Guess what? :)

    Yeah, I'm running into a problem. Since I'm using C#, I referred to this thread instead of the one with Microsoft. I studied the discussion and followed all your instructions including the use of first and second half substring manipulation. However, I'm getting this error when I click Run Query button:

    Failed connection to data source: InfoPath cannot connect to a data source.

    The query cannot be run for the following DataObject: Main connection
    InfoPath cannot run the specified query.
    [0x80040E14][Microsoft Office Access Database Engine] Syntax error (missing operator) in query expression '[Version] where ([ProjectName] like 'Data%')'.
    [0x80040E14][MSDataShape] Data provider failed while executing a provider command.

    A required data source is not available.

    Here's my code for the button: 

    public void btnRunQuery_Clicked(object sender, ClickedEventArgs e)
     //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:tblProjects/@ProjectName", NamespaceManager);
     string querySeed = queryField.Value;

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

     //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 ([ProjectName] 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
     int firstBrace = origCommand.IndexOf("}");
     string origCommand1stHalf = origCommand.Substring(0, firstBrace);
     string origCommand2ndHalf = origCommand.Substring(firstBrace,origCommand.Length-origCommand1stHalf.Length);
     newQuery.Command = origCommand1stHalf + customQuery + origCommand2ndHalf;

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

     //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!

    Here's my OrigCommand string:

    {select [ProjectID],[ProjectNo],[ProjectName],[ProjectDesc],[Version],[Active] from [tblProjects] as [tblProjects] order by [ProjectName],[Version]} as [tblProjects]
     {select [AssetID],[AssetNo],[AssetName],[ProjectID],[AssetClassID],[AssetValue],[IntRate],[YrStarted],
     [YrCapitalized],[YrAmortized],[RegionID],[DeptID],[CostCentreID] from [tblAssetsMaster]}
      ({select [AssetDetailID],[AssetID],[FYear],[NBV],[Amort],[Interest] from [tblAssetsDetail]}
      relate [AssetID] TO [AssetID]) as [tblAssetsDetail])
     relate [ProjectID] TO [ProjectID]) as [tblAssetsMaster]

    Here's my CustomQuery string:

    where ([ProjectName] like 'Data%')

    Everything seems correct but it's not. When I don't add customQuery string to the newQuery.Command, I don't get the error... but ofcourse I don't get the result I want. What am I missing here? Thanks.

  • 01-18-2010 10:15 AM In reply to

    Re: How to modify "Run Query" button functionality

      Looks like that's happening because you're trying to insert the WHERE clause after the ORDER BY clause.  That's not correct syntax, as far as I know.

    Jimmy Rishe / Software Developer / Microsoft MVP
    Qdabra Software
  • 01-18-2010 11:14 AM In reply to

    Re: How to modify "Run Query" button functionality

    Hi Jimmy,

    You're absolutely right! Oh well, I guess I have to brush up on my SQL then. :)

    Thank you so much.

    - Ric

  • 01-18-2010 05:14 PM In reply to

    Re: How to modify "Run Query" button functionality

    You'll probably need to use some string functions to split up your original query and put it back together so that you can put your WHERE clause before your ORDER BY clause. Holler if you get stuck.

    Hilary Stoupa

  • 01-18-2010 06:42 PM In reply to

    Re: How to modify "Run Query" button functionality

    Thanks Hilary. I Got it working already. I hope you guys don't get tired helping us newbies. :)

  • 01-25-2010 07:10 AM In reply to

    Re: How to modify "Run Query" button functionality

    Hi Hilary,

    I was wondering if there's a fix to the issue I'm having right now with my form where I modified my "Run Query" button. I thought it was already perfect but then I read this thread and found out indeed this error "The Structure of the Datafield Group has been changed and no longer matches the structure of the data source" pops up everytime you run the modified Run Query, make changes to data, submitting the form then use the New Record button and submit a new one.

    Have you tried this with your modified "Run Query" button? Hope there's a solution for this issue. Any help will be much appreciated. Thanks.

  • 01-28-2010 11:11 AM In reply to

    Re: How to modify "Run Query" button functionality

    So far what we do know is that it is happening with C# or  Jscript and Infopath 2003 and Infopath 2007. I wonder if it could be a bug?

    A work around is to is to use the "Add Record" Hyperlink in the corner of the table. This seems to work ok.

    Rico, do you have a master/detail query?

    I am using master/detail.



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