Link to home
Start Free TrialLog in
Avatar of frogman79
frogman79

asked on

Fill Data Adapter and Join to existing Data Set

Hello Experts

I'm hoping this is an easy question but I'm having much difficulty with it.

Set thescenario. I have 2 list boxes the left listbox contains a list of unselected items in this case Area, the right list box contains a list of selected areas. There are the usual buttons to select between the 2 listboxes. These list boxes are populated from a dataset which in turn is populated from an xml file. This allows me save out on exit the users last selection to the xml and hence when the user next logs in they get their last selection details. This all works beautifully.

I then have a button which fills a datagrid with data via a dataadapter from an ms access .mdb database. I want this datagrid filled only with data where the area field in the datagrid or dataset is equal to one of those areas selected in the above listbox.

I can't seem to use the existing loaded dataset in the SELECT statement of the data adapter. In access I would simply do a left Join between my data and the table bound to the list box. I could push the data back into access and do the join there as a last resort  but I want to remove my relliance on the .mdb.

I have looked at loading the entire data into the data set then applying a data relationship. This comes close to working when I set the CreateConstraints as true:

Dim DR1 As New DataRelation("AreSlctd_Data", DS.Tables("AreaSlctd").Columns("Area"), DS.Tables("Data").Columns("AREA"), True)
DS.Relations.Add(DR1)

then fill the datagrid. I get all the data in the data grid but a red exclamation mark highlighting all the ones I don't want. So close. This also gives me an error message of "Failed to enable constrains. One or more rows contain values violating non-nll, unique, or foreign-key constraints."

Your help is appreciated in advance.

Many Thanks

Martin
ASKER CERTIFIED SOLUTION
Avatar of Sancler
Sancler

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of frogman79
frogman79

ASKER

Hey Roger, great Idea. Yes I'm aware of the IN operator, but It hadn't crossed my mind to use it here. So simple so obvious, and thank you.

I would prefer to size down the dataset on the orignal SELECT statement as the data set is quite large.

Thanks again.

Martin
Thanks Roger