Confused by Multi-Select List Box control.

Question

Hello – This control is driving me crazy, I think I have two problems that are compounding my frustrations, one is properly extracting the data from SQL Server and the other is presenting it on the form. So any help is extremely appreciated.

Here’s the background - I have a form with a Multi-Select List Box on it. The form can be used to either enter new data or update existing data. Data is stored in a SQL Server database.

On the database side, I have two secondary data sources that are used to populate the fields in the form if the user chooses to update the record. The main table has a one-to-many relationship with another table.

When I query the database, I return the main table in one dataset and I return the second table in another dataset – If I do a JOIN in my query, I will end up with several records for each entry that has multiple entries in table2.

I have a third secondary data source which I use to create the Multi-Select List Box. (All the options)

Where’s what I want to do.

When a user decides to update an existing record, I need to populate the Multi-Select List Box with all available values (Third data source) but select (Add check marks) to the items that currently exist in the database. Then if the user adds or removes a check mark, I need to update the database.

1.       What’s the best way to get the data out of SQL Server to do what I need to do? Single query with a JOIN or two queries and let InfoPath figure out the one-to-many relationship? I need to be able to retrieve only one data row for the main data table.

2.       How can I set the values of the selected items in the list box?

3.       How can I know what’s been changed in the list box?

4.       Is there a way to update the form without coding it? Coding is no issue, just asking. I tend to escape the ambiguities of InfoPath by coding like I would in the gold old days of creating Windows Forms.

 

Again, thanks a million for helping out.

 

Cheers.

Ron…

---

Thanks Ron...

Answer 1

Hi RonInOttawa,

1.       I would like suggest you to use Join since it would improve the performance. And please give a new thread to SQL Server forum for only need to get one row data question.

2.       There is no OOB solution for your requirement, so please try to do some programming. Then you could set value to multiple text control and capture the changes in the multiple text control with coding.
Here is a blog about submitting to a data base in InfoPath Forms Services via Web Services.
http://blogs.msdn.com/b/infopath/archive/2007/01/29/submitting-to-a-database-via-web-services-in-infopath-forms-services.aspx

Thanks & Regards,
Emir Liu
TechNet Subscriber Support in forum
If you have any feedback on our support, please contact  [e-mail].