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