InfoPath Dev

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!

Ric

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.

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

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
 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 ([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           
 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);
}

Here's my OrigCommand string:

shape
{select [ProjectID],[ProjectNo],[ProjectName],[ProjectDesc],[Version],[Active] from [tblProjects] as [tblProjects] order by [ProjectName],[Version]} as [tblProjects]
 append
 ((shape
 {select [AssetID],[AssetNo],[AssetName],[ProjectID],[AssetClassID],[AssetValue],[IntRate],[YrStarted],
 [YrCapitalized],[YrAmortized],[RegionID],[DeptID],[CostCentreID] from [tblAssetsMaster]}
  append
  ({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.

http://technet.microsoft.com/en-us/library/ms189499.aspx

  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.

Hi Jimmy,

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

Thank you so much.

- Ric

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.

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

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.

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.

Thanks

Mark