BindingSource.Filter

[Arg81]

I'm trying to get a grid to show only the 1 row based on the value of a field in the main table.

I think I need to pick the value out of the dataTable itself, instead of from what's being displayed.

Anyway, the code I'm using is;

 me.recipeBindingSource.Filter = string.format("RecipeID = '{0}'", me.lblRecipeID.text)

lblRecipeID is from my "main" table, "Development".

I've added that line of code to Form_Load, and each of the navigation buttons.

However, it doesn't work as expected, it's always 1 "step" out.
I.E.

I have a bindingNavigator for "development", the first 4 rows use recipeID 1 and the 5th row is recipeID 2.

If I browse through, the 5th row stays as 1, the 6th row then becomes 2. Even if I navigate back, I get the next row's value, not the one going back.


Unfortunately I can't use relationship, as the relation is 1 recipe ("child table") to many developments ("main table"), not the other way round - so I can't just drop the relation on the grid!

Any advice appriciated.

 

[Arg81]

I've now got this working.

I've had to create another dataTable as a "lookup", which I've just got SELECT query only for RecipeID and RecipeName.

I've then databound a combobox to this RecipeLookup data table, and set it's selectedvalue to the RecipeID in the main table.

When the app closes, it errored because I was trying to format an integer into a string with the line -
string.format("RecipeID = '{0}'", me.lblRecipeID.text)

So instead of using that, I'm now using;
string.format("RecipeName = '{0}'", me.cboRecipe.text)

and it's in the DCBindingSource_PostionChanged event. All working as expected....

If there's any better / easier way then please let me know.

 

[cjard]

Unfortunately I can't use relationship, as the relation is 1 recipe ("child table") to many developments ("main table"), not the other way round - so I can't just drop the relation on the grid!

Of course you can. Add a relationship that targets recipe as the parent and developments as the child.. Whomever told you it had to be the other way round?

 

[Arg81]

Going back to this thread http://www.vbdotnetforums.com/showthread.php?t=19015

The Parent (Development) has 1 recipe against it. The child (Recipe) can be used on many DevelopmentID's.

So in theory the parent is Recipe and the child is Development, but it's not...it's the other way round!

EDIT - so in DataSources, there is no relationship in the DC showing me the Recipe , instead there is a relationship in the Recipe showing me the DC....

 

[Arg81]

ah...I've now edited the relationship so that it's;

Parent Table - Development ======= Child Table - Recipe

RecipeID (fk) ================== RecipeID (pk)

so the relationship now shows 1 DC can have many Recipes (which is wrong, but it makes the grid work).

Am I going to have any downfall with doing it this way, such as inserting data into the database, seeing as my SQL relationships (data diagram) is set to the original way?

 

[cjard]

No, but think about this:

There is nothing to say your dataset has to have anything to do with the database, ever.. There is even nothing to say that if you have 2 tables, P and C that are parent/child most the time that you cant have another dataset with the relation the other way round.

I still dont understand your data structure here. If one recipe can be present in many developments (and you know the recipe ID and want to find all related developments), then it is the recipe that is the parent

 

[Arg81]

If one recipe can be present in many developments, then it is the recipe that is the parent

I know it's hard to try and describe this fully (well I'm struggling to !!!)

The Recipe table has the RecipeID as PK, and RecipeID is in the DC table as FK.

However, when I said about DC being parent, I should really of said "main".

DC is the main table that stores all the information, then stores the RecipeID. For instance there are 20 recipes and 30 DCs. A DC can only have the one RecipeID, but that RecipeID (e.g. 1) can be used on many DCs.

The Recipe table is basically a lookup, although it stores a few more fields than just an ID and Name.

I actually have 2 ID fields like this in the DC table - RecipeID and SieveID. Again, the Sieve table stores all the information on the various sieves and only 1 sieve is used per DC, but it can be used in more than one DC.

Therefore my Parent-Child relationship has "flipped" in a sense if you understand what I mean.

I've swapped the relationship round in my dataSet, so that I can drop the relationship on the grid so that the correct Recipe details are shown based on the RecipeID in the DC table.

My concern on flipping this relationship was when I add new rows to the system, however I haven't come across any issues yet.

 

[cjard]

Therefore my Parent-Child relationship has "flipped" in a sense if you understand what I mean.

It sounds like you want to know which DCs have a RecipeID of X, rather than what recipe details are related to this recipe ID of X

Which would usually be achieved with a FillByRecipeID = X on the DC table

 

[Arg81]

yeah!

Well changing the relationship in the dataSet has worked, so I'm going to do some testing of inserting data how the user would be doing it, if it's all fine I'll leave as is

cheers for the pointers...