InfoPath Dev

Hi there - I really hope someone can help me out here!

I have an infopath browser based form that has a secondary SQL datasource connection. I have a field in my form that will match the data in SQL. I checked out Hilarys post from way back (http://www.infopathdev.com/forums/p/8940/31780.aspx#31780) which is what I want to do, basically compare a value in the form, query the secondary source and then populate a list to a drop down.

The code I am using is below but I get an saying it cant connect to the datasource. Also, Im unsure what drop down im supposed to be populating?! Hope someone can shed some light on this!
public void InternalStartup()

{

((ButtonEvent)EventManager.ControlEvents["CTRL324_6"]).Clicked += new ClickedEventHandler(CTRL324_6_Clicked);

}

public void CTRL324_6_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("/my:myFields/my:SelectDisplay/my:SalesPersonLkup", 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 ([SalesPersonLkup] like '" + querySeed + "%')";

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

AdoQueryConnection newQuery = (AdoQueryConnection)this.DataSources["FullInfo"].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);

Prodcues the errors:

The query cannot be run for the following DataObject: FullInfo
InfoPath cannot run the specified query.
[0x80040E14][Microsoft OLE DB Provider for SQL Server] Invalid column name 'SalesPersonLkup'

and

System.Net.WebException
InfoPath has encountered an error. The operation failed.

The form's code contains an error.

The following call in the form's code failed: ADOAdapter.Query
Operation aborted

   at Microsoft.Office.InfoPath.Internal.MomExceptionHelper.ExecuteDataConnectionAction(OMCall d)
   at Microsoft.Office.InfoPath.Internal.AdoQueryConnectionHost.Execute()
   at Sales_Visit_Request_SQL_Connection.FormCode.CTRL324_6_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)

Hi - the error on your query is that you don't have a column named "SalesPersonLkup" in your table that you are querying - is that the case?

As to your question:

Bricey:

Also, Im unsure what drop down im supposed to be populating?!

Well - the code should give you a populate data source - yours is named "FullInfo". Then you would have a drop down on your form that uses this for its list items? But you could also use the data in other ways. Drag the data fields from the FullInfo data source onto your form while you are working so you can see what is being returned once you get your code error addressed.

Thats the ticket! - Thank you so much Hilary!

I use infopath alot, but hardly ever with code. I shall make an effort to visit here often and share anything I learn!