Problem in dynamically load data to Drop-Down list box in InfoPath 2007?

Question

I have a Drop-Down list in my InfoPath form and I am loading some other fields based on the selection of the drop-down list. so that I have written code as follows for the "changed" event of the drop down list.

public void ProjectName_Changed(object sender, XmlEventArgs e)
{
            string projectId = e.NewValue;
            dataQueryConnection = (AdoQueryConnection)this.DataConnections["ProjectInformation"];
            dataQueryConnection.Command = dataQueryConnection.Command + string.Format(" WHERE ProjectId = '{0}'",             projectId);
            dataQueryConnection.Execute();

}

For the first time when I change an item in the drop down list its working fine but for the subsequent changes of items(2nd time, etc..) its give the following error,

The query cannot be run for the following DataObject: ProjectInformation InfoPath cannot run the specified query. [0x80040E14][Microsoft OLE DB Provider for SQL Server] Incorrect syntax near the keyword 'WHERE'.

And this is the reason, for the second time,

dataQueryConnection.Command = select "EmployeeID","Accountname","ProjectName","ProjectId","ProjectRole","BillableUtilization","ClientName","BillingCode","BUHead" from "TRF"."hrt_vw_ProjectInformation" as "hrt_vw_ProjectInformation" WHERE ProjectId = '3072507' WHERE ProjectId = '3076478'

subsequent event firing biding the WHERE clause every time with the previous executed query.

How I can over come from this issue?

Answer 1

Store the initial command string in a global variable in your code (in the loading event). Then in your Changed function append to the global variable instead of to the previous value of the command.

string OrigString

public void FormEvents_Loading(object sender, LoadingEventArgs e)
{
   OrigString = dataQueryConnection.Command;
}

public void ProjectName_Changed(object sender, XmlEventArgs e) 
{ 
            string projectId = e.NewValue; 
            dataQueryConnection = (AdoQueryConnection)this.DataConnections["ProjectInformation"]; 
            dataQueryConnection.Command = OrigString + string.Format(" WHERE ProjectId = '{0}'",             projectId); 
            dataQueryConnection.Execute(); 

}