StrataFrame Forum » StrataFrame Application Framework - V1 » WinForms (How do I?) » Combo values based on another combo value

Combo values based on another combo value

Juan Carlos Pazos — Mon, 16 Jun 2008 09:41:44 GMT

Sorry for the last post, It was completly wrong.

Hope this time I can explain better.

I want to fill a Combo according the value of another Combo.

I have this scenario

Table States

IdState - Int32

Statename - String

Table Cities

IdCity - Int32

IdState - Int32

CityName - String

Table Streets

IdStreets - Int32

IdCity - Int32

IdState - Int32

StreetName - String

The three tables have relations defined.

I create a form to mantein the states, the a form to mantein the cities and all fine.

I create a new form to mantein the streets, and here is my problem. In this user first select the state from a combo and the according to the selected state a second combo must be filled with only the cities of the state selected in the first combo.

There are three BO, one for the form, another for the States and a thierd one for the cities.

In the BO of the cities I have this:

Public Sub FillByState(ByVal State As Integer)...

loCommand.CommandText = "SELECT * FROM Cities WHERE IdState = @IdSate"

...

End Sub

The fill is copy of the one used in the tutorial. The PopulationDataSourceSettings for the CitiesBO are set to execute the FillByState: CitiesBO.FillByState(System.Int32) and PopulateOnFormLoad is FormLoad

In the combo of State I add this code for the SelectedvalueChanged event

Me.CitiesBO.FillBySate(Me.ComboBoxState.SelectedValue)

Me.ComboBoxCities.Requery()

But when I select a state the combo for the cities is not filled.

What do I need to do?

Regards

RE: Combo values based on another combo value

Trent L. Taylor — Mon, 16 Jun 2008 09:41:44 GMT

Better yet, if you could post a sample versus my brain having to do the compiling and execution that would be great :)

RE: Combo values based on another combo value

Trent L. Taylor — Mon, 16 Jun 2008 09:22:40 GMT

Good stuff :) Good answers, Edhy!

RE: Combo values based on another combo value

Edhy Rijo — Sat, 14 Jun 2008 20:33:28 GMT

You're welcome Juan.

RE: Combo values based on another combo value

Juan Carlos Pazos — Sat, 14 Jun 2008 20:29:55 GMT

All solved. Thanks a lot for your help.

RE: Combo values based on another combo value

Juan Carlos Pazos — Sat, 14 Jun 2008 18:26:37 GMT

Hi Edjy

Thanks for all your support. I still trying. If your have some simple sample, will be very good?

I think that in the ListPopulating should go the:

If Me.DetalleSubcategoriasBO.Count > 0 Then

e.Parameters(0).Value = Me.DetalleSubcategoriasBO.IdStio

Else

e.Parameters(0).Value = 0

End If

And in the SelectedIndexChanged the Requery()

Regards.

RE: Combo values based on another combo value

Edhy Rijo — Sat, 14 Jun 2008 17:09:04 GMT

Juan Carlos,

All you need to understand here is how the relation between the 3 tables works, then in the Combobox.SelectedIndexChanged you have to check the ParentBO.Count > 0, so if there is a record, then you can sent the ParentBO.PKFieldValue to the first parameter e.Parameters(0).Value = Me.ParentBO.PKFieldValue.

In the Categories Combobox SelectedIndexChanged use this code:

[codesnippet]

If Me.CategoriasPubBO.Count > 0 Then

e.Parameters(0).Value = Me.CategoriasPubBO.YourPKFieldNameHere

Else

e.Parameters(0).Value = 0

End If

[/codesnippet]

Also I believe it is better to use sample with your real entities name, even if it is in Spanish, this way it will make more sense when posting real code :).

RE: Combo values based on another combo value

Juan Carlos Pazos — Sat, 14 Jun 2008 12:56:26 GMT

This is what I have:

I uses a Contry - States - Cites sample, my tables are Sitios (sites), Categorias (categories) and Subcategorías (subcategories), is the same.

When the user add a new record or edit one existing, firs select the site, the category and the subcategory, then two more fields that are just text.

I already made a for for add sites, then another for add categories, in that I used the code you kindly provide me, and works fine.

In this form is for subcategories, I repeat all code just adjusting the for the last combo.

In the Sites combo the PopulatingDataSourceSettings is: CategoriasPubBO.FillBySitio(System.Int32) FillBySitio is a query to the BO that retrieves all the values in the table. The PopuleteOnFormLoad is on FormLoad

In the event SelectedIndexChanged has this: Me.cboCategoria.Requery()

The Cataegories Combo PopulatingDataSourceSettings is: CategoriasPubBO.FillBySitio(System.Int32) where the query in the BO is:

Public Sub FillBySitio(ByVal Sitio As Integer)

'-- Locals

Dim loCommand As New SqlCommand()

'-- Build the query

loCommand.CommandText = "SELECT * FROM Categorias_Pub WHERE IdSitio = @IdSitio"

'-- Add parameter

loCommand.Parameters.Add("@IdSitio", SqlDbType.Int)

loCommand.Parameters("@IdSitio").Value = Sitio

'-- Execute command

Me.FillDataTable(loCommand)

End Sub

In the SelectedIndexChanged has this:

If Me.DetalleSubcategoriasBO.Count > 0 Then

e.Parameters(0).Value = Me.DetalleSubcategoriasBO.IdStio

Else

e.Parameters(0).Value = 0

End If

The DetalleSubcategoriasBO is the bussines object for the form.

In the SelectedIndexChanged has this: Me.cboSubcategoria.Requery()

The PopulationDataSourceSettings are: SubCategoriasBO.FillByCategoria(System.Int32) and the PopulateOnFormLoad is in manual (I all rady put this in OnLoad and not works).

Public Sub FillByCategoria(ByVal Sitio As Integer)

'-- Locals

Dim loCommand As New SqlCommand()

'-- Build the query

loCommand.CommandText = "SELECT * FROM SubCategorias_Pub WHERE IdCategoria = @IdCategoria"

'-- Add parameter

loCommand.Parameters.Add("@IdCategoria", SqlDbType.Int)

loCommand.Parameters("@IdCategoria").Value = Subcategoria

'-- Execute command

Me.FillDataTable(loCommand)

End Sub

The ListPopulation event is this:

If Me.DetalleSubcategoriasBO.Count > 0 Then

e.Parameters(0).Value = Me.DetalleSubcategoriasBO.IdStio

Else

e.Parameters(0).Value = 0

End If

Finally the ListPopulating event in the las combo is this:

If Me.DetalleSubcategoriasBO.Count > 0 Then

e.Parameters(0).Value = Me.DetalleSubcategoriasBO.IdCategoria

Else

e.Parameters(0).Value = 0

End If

Hope it makes sense and you can help me.

Regards

RE: Combo values based on another combo value

Edhy Rijo — Sat, 14 Jun 2008 11:41:32 GMT

Hi Juan,

Please post the code of the populating event and the one that is causing the error. It is a bit difficult to help you debug without looking at your code. I am sure it is something very simple. Also FYI, what you are trying to do with all your combos, can also be done in the Dialog Browser, in case you needed to know. ;)

RE: Combo values based on another combo value

Juan Carlos Pazos — Sat, 14 Jun 2008 11:37:17 GMT

I have records in the three tables. In fact the problem, is generated in the Requery(), the event for the list with the validation for 0 is passed correctly.

Any ideas?

RE: Combo values based on another combo value

Edhy Rijo — Sat, 14 Jun 2008 11:08:51 GMT

Juan Carlos, it is the same rule for all combos you want to use. The CurrentRowIndex: -1 error is due to the fact that the States_Pub BO does not have any records, so you need to check for this combo.count > 0 before trying to use it. Look at the sample code I posted here before.

RE: Combo values based on another combo value

Juan Carlos Pazos — Sat, 14 Jun 2008 01:08:28 GMT

I already use your model and works fine with two combos, but now I try to put three combos (example: one for Country, another for State and final the City).

Using the same solution for two, in the last combo City it alwas give an error: {"The CurrentRow for table '[dbo].[States_Pub]' could not be evaluated because the CurrentRowIndex is out of range. Business object record count: 0. CurrentRowIndex: -1."}

What I have is the first (country) combo and second combo (states) is exactly as you indicate me before and works fine.

How can put three combos what depend of the previous value selected.

Regards

RE: Combo values based on another combo value

Trent L. Taylor — Tue, 03 Jun 2008 13:11:29 GMT

Good to hear :)

RE: Combo values based on another combo value

Juan Carlos Pazos — Tue, 03 Jun 2008 11:38:35 GMT

Edji, Trent

Thanks for your support, following your insructions the combos work as needed.

Regards

RE: Combo values based on another combo value

Trent L. Taylor — Tue, 03 Jun 2008 09:05:43 GMT

All good advice...the only thing that I see is that you need to supply the second parameter also if you are using the CopyDatafrom method. The ListPopulating event would look something like this:

[codesnippet]e.Parameters(0).Value = MyBO
e.Parameters(1).Value = MicroFour.StrataFrame.Business.BusinessCloneType.ClearAndFillWithDefaultView[/codesnippet]

You have to supply all of the parms to match the method that is being executed on the BO.

RE: Combo values based on another combo value

Edhy Rijo — Tue, 03 Jun 2008 08:07:12 GMT

Hola Juan Carlos,

A couple of things: You don't need to have a BO in the main form for a lookup comboboxes, so in your case you don't need the BOs for the States and Cities in the form.

If all you need is to filter the Cities by the data selected in the State combo, setup the Cities combo as follow:

ComboBoxCities.PopulationDataSourceSettings = CitiesBO.FillByState(System.Int32) (which I believe you have it that way :))

In the ComboBoxCities.ListPopulating Event have a code like this, which is the step you are missing:

[quote][codesnippet]

Private Sub ComboBoxCities_ListPopulating(ByVal e As MicroFour.StrataFrame.UI.ListPopulatingEventArgs) Handles ComboBoxCities.ListPopulating

If Me.StatesBO.Count > 0 Then

e.Parameters(0).Value = Me.StatesBO.YourStatePKFieldValue

Else

e.Parameters(0).Value = 0

End If

End Sub

Private Sub cboStates_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboStates.SelectedIndexChanged

' This is needed to show the correct filtered Cities in the combo.

Me.ComboBoxCities.Requery()

End Sub

[/codesnippet][/quote]