Re: Custom Filter all of a sudden not working

erinselena — Tue, 03 Jul 2007 13:49:39 GMT

For some reason my post duplicated itself so here is the code again.

DataConnection objADOAdapter;
AdoQueryConnection connection;

objADOAdapter = this.DataSources["NavigationGroups"].QueryConnection;
connection = ( (AdoQueryConnection)( objADOAdapter ) );

// Retrieve the field in the InfoPath form whose value will be used in the inner join
XPathNavigator node = this.CreateNavigator( ).SelectSingleNode( "/my:MF/my:IAFT/my:AFT", this.NamespaceManager );

//only want to change the query string if we want to filter down. otherwise keep the original query.

                        if ( connection.Command.Contains( "where" ) )
                        {
                            int index = connection.Command.IndexOf( "where" );
                            connection.Command = connection.Command.Remove( index );
                        }

                        if ( connection.Command.Contains( "inner join" ) )
                        {
                            int index = connection.Command.IndexOf( "inner" );
                            connection.Command = connection.Command.Remove( index );
                        }
                        // Generate the new SQL statement with WHERE clause
                        string strSQL;
                        if ( node.InnerXml.ToLower( ) != "all" )
                        {
                            strSQL = connection.Command + " inner join linker_parent_to_child_nav l on l.childid = NavigationGroups.id where isSelectable = 1 and parentid = '" + node.InnerXml.ToUpper() + "'";
                        }
                        else
                        {
                            strSQL = "select id as Id, name as Name from NavigationGroups as NavigationGroups where type = 'Asset' and isSelectable = 1 union select assetid as Id, assetName as Name from assets where isUseradded = 1 order by name";
                        }
                        connection.Command = strSQL;

this.DataConnections["NavigationGroups"].Execute( );

Custom Filter all of a sudden not working

erinselena — Tue, 03 Jul 2007 13:48:32 GMT

Hello Everyone,

I have created a custom filter in my infopath 2007 form. The user enters a value in a text box and then I grab the AdoQueryConnection and re-write the sql statement that populates this data connection. This was working and now all of a sudden it doesn't work and I don't know why. I used to have a master/detail on my form and then i changed this to a separate repeating section adn repeating table (not master detail) that aren't connected to eachother but they are created from the same section of XML, they aren't showing at the same time it's just a different way to view the information adn the user has to click a check box to toggle between the views, i don't think this is the reason it's not working though. HEre is the code i use to update my datasource query. Anyone know why this might be happening?

DataConnection objADOAdapter;

AdoQueryConnection connection;

objADOAdapter = this.DataSources["NavigationGroups"].QueryConnection;

connection = ( (AdoQueryConnection)( objADOAdapter ) );

// Retrieve the field in the InfoPath form whose value will be used in the inner join

objADOAdapter;

AdoQueryConnection connection;

objADOAdapter = this.DataSources["NavigationGroups"].QueryConnection;

connection = ( (AdoQueryConnection)( objADOAdapter ) );

// Retrieve the field in the InfoPath form whose value will be used in the inner join

AdoQueryConnection connection;

objADOAdapter = this.DataSources["NavigationGroups"].QueryConnection;

connection = ( (AdoQueryConnection)( objADOAdapter ) );

// Retrieve the field in the InfoPath form whose value will be used in the inner join

this.DataSources["NavigationGroups"].QueryConnection;

connection = ( (AdoQueryConnection)( objADOAdapter ) );

// Retrieve the field in the InfoPath form whose value will be used in the inner join

AdoQueryConnection)( objADOAdapter ) );

// Retrieve the field in the InfoPath form whose value will be used in the inner join

XPathNavigator node = this.CreateNavigator( ).SelectSingleNode( "/my:MF/my:IAFT/my:AFT", this.NamespaceManager );

//only want to change the query string if we want to filter down. otherwise keep the original query.

XPathNavigator node = this.CreateNavigator( ).SelectSingleNode( "/my:MF/my:IAFT/my:AFT", this.NamespaceManager );

//only want to change the query string if we want to filter down. otherwise keep the original query.

if ( connection.Command.Contains( "where" ) )

{

int index = connection.Command.IndexOf( "where" );