StrataFrame Forum » StrataFrame Application Framework - V1 » WinForms (How do I?) » Error when I both requery a related combobox and set its selectedvalue in code (on new records only!)

Error when I both requery a related combobox and set its selectedvalue in code (on new records only!)

Larry Tucker — Mon, 09 Apr 2012 13:59:02 GMT

Hi all,

I'm stumped and have spent many hours puzzling over this. I have two related comboxboxes, cboClient and cboCoverage on an EventBO maintenance form.   Each is bound to an FK on the EventBO record (CLI_ID and COV_ID).

When the user selects a new Client, I want the cboCoverage to requery showing only the coverages for this particular client and I want to set its selected value in code to a particular one of the coverage choices. The logic for this doesn't matter here (I am actually setting the recommended coverage for that client based on several other variables...); here is an ultra simplified version setting a fixed value in code just for demo:

[codesnippet]

Private Sub cboClient_SelectionChangeCommitted(sender As Object, e As System.EventArgs)_
     Handles cboClient.SelectionChangeCommitted
        Me.cboCoverage.Requery()
        Me.cboCoverage.SelectedValue = 50022155 ' simplified for demo
End Sub

[/codesnippet]

This works fine when editing an existing record. It fails on a new record with the following error:
[codesnippet]
System.InvalidCastException was unhandled by user code
Message=Conversion from type 'DBNull' to type 'Integer' is not valid.
Source=Microsoft.VisualBasic
StackTrace:
       at Microsoft.VisualBasic.CompilerServices.Conversions.ToInteger(Object Value)
       at ThomBillerBOLibrary.EventBO.FieldDescriptor.SetValue(Object component, Object value) in E:\ThomBiller\ThomBillerBOLibrary\ThomBillerBOLibrary\EventBO.Designer.vb:line 3003
       at System.Windows.Forms.BindToObject.SetValue(Object value)
       at System.Windows.Forms.Binding.PullData(Boolean reformat, Boolean force)
InnerException:
[/codesnippet]

Specifically, this is happening as the EventBO is trying to set the CLI_ID value based on the cboClient selection:

[codesnippet]
        Public Overrides Sub SetValue(ByVal component As Object, ByVal value As Object)
            Select Case Me.Field
                Case EventBOFieldNames.EVE_ID
                    DirectCast(component, EventBO).EVE_ID = CType(value, System.Int32)
                Case EventBOFieldNames.OLDPK
                    DirectCast(component, EventBO).OLDPK = CType(value, System.String)
                Case EventBOFieldNames.CLI_ID
                    DirectCast(component, EventBO).CLI_ID = CType(value, System.Int32)
[/codesnippet]

What is so odd is that on a new EventBO record, I can either requery() the related cbo or set its selectedvalue without error, but not both. If I am simply editing an existing EventBO record, I can do both.

TIA,

Larry

RE: Error when I both requery a related combobox and set its selectedvalue in code (on new records only!)

Ivan George Borges — Mon, 09 Apr 2012 13:59:02 GMT

Glad you got it working, Larry. ;)

RE: Error when I both requery a related combobox and set its selectedvalue in code (on new records only!)

Larry Tucker — Fri, 06 Apr 2012 09:55:15 GMT

So with Edhy's and Ivan's help, I've got it working. Again, the problem was that when I tried to set combo2 only when the user made a selection in combo1, it was failing on a New record. Using SelectedIndexChanged() helps, because it fires before the user has made a selection in combo1 and this somehow prevents the error. So all I had to do was allow this to happen in an innocuous way as the form finished loading, then trap a true user selection. To trap the user selection, I set a form property to hold the combo1's "before" value in its GotFocus(), then see if the "after" SelectedValue is different, indicating a user initiated change (not a simple navigation or form refresh()).

Here is the test code from the Strataframe Sample example. Again, the actual linking of Orders and Products makes no sense, I am just using them as "combo1" and "combo2".

[codesnippet]
    Private Sub cboOrder_GotFocus(sender As Object, e As System.EventArgs) Handles cboOrder.GotFocus
        Me._BeforeOrderValue = CInt(cboOrder.SelectedValue) ' combo1 before value
    End Sub

    Private Sub cboOrder_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cboOrder.SelectedIndexChanged
        '-- Skip this event when the form is loading.
        If Me._FormIsLoading = True Then
            Exit Sub
        End If
        If OrderItemsBO1.Count > 0 Then
            Me.ProductsBO1.FillAll()        'combo2
            Me.cboProduct.Requery()         'combo2
            If Me.OrderItemsBO1.Count > 0 AndAlso Me.ProductsBO1.Count > 0 Then
                If CInt(cboOrder.SelectedValue) = _BeforeOrderValue Then
                    OrderItemsBO1.orit_prod_pk = 0 ' innocuous value on new record with no user interaction
                Else
                    OrderItemsBO1.orit_prod_pk = 2 ' desired value that would normally be based on something in combo1
                End If
            End If
        End If
    End Sub

[/codesnippet]

This could probably be cleaned up. Again, in my real situation, I want the suggested value for combo2 to be based on some business logic (in my case, the recommended insurance coverage for the selected client in combo1 based on a number of factors). But here, at least, I am able to both requery() and set the value in combo2 based on user interaction in combo1. That was the general problem and hopefully this is helpful to others. It certainly was for me. Thanks again Edhy and Ivan.

Larry

RE: Error when I both requery a related combobox and set its selectedvalue in code (on new records only!)

Larry Tucker — Tue, 03 Apr 2012 14:09:38 GMT

Ivan and Edhy,

Thanks for the additional work on this. I've been out of the office today and will get back to it tonight. I know I tried both of these suggestions, but at this point, my head is spinning. As I recall, the SelectedIndexChanged on combo1 was firing all the time. When I limited the setting of the combo2 to only the times when a user actually selected something in combo1, I'm pretty sure I was getting the same error. The benefit of SelectionChangeCommitted was that it fired only when a user made a change. I'll take another shot a SelectedIndexChanged with these conditions and see what I can do. I'll post the results.

Thanks very much,

Larry

RE: Error when I both requery a related combobox and set its selectedvalue in code (on new records only!)

Edhy Rijo — Mon, 02 Apr 2012 22:55:48 GMT

Hi Larry, Ivan,
Well, basically the problem was that the Primary Key value of the main BO was being DBNull and this would crash in the BO PropertyDescriptor, all I did was Setup a "Return Alternate on Null" = 0 for the Pirmay Key field in the case of this sample that would be the OrderItemsBO.orit_pk and that would ignore the error and continue assigning the custom value in the cboOrder.SelectedIndexChanged event.

I know on PK fields you hardly need to handle Null, specially when the field is an autoinc integer, but this seems to work just fine for Larry's particular requirement.

RE: Error when I both requery a related combobox and set its selectedvalue in code (on new records only!)

Ivan George Borges — Mon, 02 Apr 2012 17:07:21 GMT

I used the SelectedIndexChanged on your sample and didn't get the error. But maybe your situation has a different scenario.

RE: Error when I both requery a related combobox and set its selectedvalue in code (on new records only!)

Larry Tucker — Mon, 02 Apr 2012 16:37:29 GMT

Hi Ivan,

Thanks for the suggestion, but yes, we did try the SelectedIndexChanged event as well.

It seems that the effort to set the value in cbo2 while the selection process is completing in cbo1 is somehow fouling up the process in cbo1. In the error, it is as if the cbo1 has a null value, even though it is in the process of being selected. And even when nothing is selected for cbo1, the save will still work without an error with a 0 default value instead of a NULL.

Larry

RE: Error when I both requery a related combobox and set its selectedvalue in code (on new records only!)

Ivan George Borges — Mon, 02 Apr 2012 15:09:11 GMT

Hi Larry.

You are using the SelectionChangeCommitted event, have you tried putting your code on the SelectedIndexChanged instead?

RE: Error when I both requery a related combobox and set its selectedvalue in code (on new records only!)

Larry Tucker — Mon, 02 Apr 2012 14:27:51 GMT

Just an update for others out there. Edhy and I spent two hours on this today by remote connection with no luck. I really appreciated the effort. I'm now looking into a kludge of sorts: setting timer on cbo1 so that it can finish whatever it needs to do before cbo2 setting the value in cbo2.

Larry

RE: Error when I both requery a related combobox and set its selectedvalue in code (on new records only!)

Larry Tucker — Mon, 02 Apr 2012 10:09:46 GMT

Edhy,

Thank you for taking the time to put together a demo. I was able to modify it (and misuse the poor Stataframe Sample database) to demonstrate my problem.

Again, the issue is not how to synchronize two combos. I am able to do that as your demo demonstrated.

The problem is adding a step to the synchronization which involves setting a suggested value in combo 2. The reason this is important to me is that in my medical billing application, a given client can have multiple insurance coverage records with different priorities, different dates, different services covered. When I am entering charges, and I select a client, I want the coverage (cbo2) to refresh with only that client's coverages and "stop" on the one that fits the current date, service, etc. In other words, on new records, when I select a client on cbo1 I want a recommended selection to appear on cbo2. (On existing records, I will actually compare the existing cbo2 value to the recommended on and give them a warning if they have the wrong one.)

I got this working when editing an existing charge record. It fails when working on a new charge.

It turns out that the problem is being cause by this second action: setting cbo2.SelectedValue = <MyRecommendCoverage>. Since this is being done from within the cbo1 selection process (within cbo1.SelectedChangeCommitted(() event handler) I think it is somehow short-circuiting the normal selection process on that combo. The error is being thrown by the form's "ISA" as it tries to save the cbo1 selection.

Anyway, this revised demo will show the same error. I had to misuse the Strataframe Sample database because I needed a table with two FKs on it. So I picked OrderItems because it has two FKs to Orders and Products.

You have to suspend some critical thinking when you use the demo, because in reality it makes no sense to have a OrderItemsMaintenanceForm like I built. But on it, I use cboOrders and cboProducts as stand-ins for the truly related Client and Coverage tables in my real database. Just think of them as cbo1 and cbo2, and pretend that cbo2 is a child of cbo1, and that when you pick something in cbo1, it not only refreshes cbo2 but also sets a suggested value. This will throw the same error I have been getting when you try it on a New record. Whew...

Thanks very much.

Larry

RE: Error when I both requery a related combobox and set its selectedvalue in code (on new records only!)

Edhy Rijo — Sun, 01 Apr 2012 17:06:54 GMT

Hi Larry,
Sometimes, this kind of situation are better explained with a small and quick sample application, so here it is. I created the small VB application which will show 2 comboboxes using data from the StrataFrameSample database.
It is pretty basic and straight forward: When you select a customer from combobox 1, the CustomerNotes combo will be populated with all the notes for the selected customer.
Pay attention to the Population used in the comboxes as well as their events. If the comboboxes are binded to the BO, then there is no need to update the 2nd combo SelectedValue since the binding will take care of that for you.
If I misunderstood you situation, then use this sample to duplicate your issue and post it again so we can all learn from it.

RE: Error when I both requery a related combobox and set its selectedvalue in code (on new records only!)

Larry Tucker — Sun, 01 Apr 2012 10:21:28 GMT

Edhy,

Thanks for the suggestions but neither did the trick.

For 1, I set the CLI_ID Null Replacement Value as shown below, rebuilt both the EventBO partial and solution, and got the same error.

Still, FWIW, I get the error as the BO is trying to set CLI_ID to NULL. I can't tell if the "Null Replacement" process in SF is supposed to handle this or the inverse, returning a NULL.

As for 2, changing the lookup BO (CoverageBO) to something manipulated on the form and then copied into the dropdown did not change the result either.

I am still struck by how odd this is:

a) In the cboClient.SelectedChangeCommitted, I can do the CoverageBO.requery() alone and no problem. So nothing in the CoverageBO is causing the problem. It doesn't matter if it comes back filled or empty. If all I was trying to do was a standard "synchronization" between related comboboxes, I'd be fine.

b) Similarly, in the cboClient.SelectedChangeCommitted, I can set the cboCoverage.SelectedValue alone and no problem. So it doesn't seem to be a form or control refresh issue.

c) But if I try to do them both, synchronize and set the related combo, it fails... but only if I am on a new record.

d) And finally, the error is saying the field is NULL when it is in fact bound to the combobox I just made a selection on. It is not like I've left the combo blank; I'm getting a NULL error right as I make a selection.

Go figure.

Unfortunately, I don't yet have the VS debugging skills to walk back from the error and see what is going on in the SF code. Any suggestions on this, or other ideas on the problem, would be appreciated.

Larry

RE: Error when I both requery a related combobox and set its selectedvalue in code (on new records only!)

Edhy Rijo — Sat, 31 Mar 2012 16:55:47 GMT

Hi Larry,

What I believe is happening is that when you requery your combobox, it is not bringing any records then you are getting a DBNull value.

There are several ways to deal with DBNulls:

In the BOM, set the appropriate Null value replacement so it will be used when a DBNull is found, in your case if your FK values are Integers use zero (0).
For my listview and comboboxes I always use a BO instance dropped in the form, then I fill the BO and use the list/combo population method to show the data, in that case you can always check if the BO has any records BO.Count>0 before you can try to use any of the BO.FieldProperty and avoid getting this error.