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


Error when I both requery a related combobox and set its selectedvalue...
Author
Message
Larry Tucker
Larry Tucker
StrataFrame Novice (99 reputation)StrataFrame Novice (99 reputation)StrataFrame Novice (99 reputation)StrataFrame Novice (99 reputation)StrataFrame Novice (99 reputation)StrataFrame Novice (99 reputation)StrataFrame Novice (99 reputation)StrataFrame Novice (99 reputation)StrataFrame Novice (99 reputation)
Group: StrataFrame Users
Posts: 69, Visits: 308
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:



 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



This works fine when editing an existing record.  It fails on a new record with the following error:

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:


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


        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)


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
Tags
Edhy Rijo
E
StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)
Group: StrataFrame Users
Posts: 2.4K, Visits: 23K
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:
  1. 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).
  2. 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.


Edhy Rijo

Larry Tucker
Larry Tucker
StrataFrame Novice (99 reputation)StrataFrame Novice (99 reputation)StrataFrame Novice (99 reputation)StrataFrame Novice (99 reputation)StrataFrame Novice (99 reputation)StrataFrame Novice (99 reputation)StrataFrame Novice (99 reputation)StrataFrame Novice (99 reputation)StrataFrame Novice (99 reputation)
Group: StrataFrame Users
Posts: 69, Visits: 308
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.

  http://forum.strataframe.net/Uploads/Images/774cd579-0f5c-40bd-9e58-5a4d.jpg

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
Edhy Rijo
E
StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)
Group: StrataFrame Users
Posts: 2.4K, Visits: 23K
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.

Edhy Rijo

Attachments
ComboboxRequerySample.rar (124 views, 214.00 KB)
Tags
Larry Tucker
Larry Tucker
StrataFrame Novice (99 reputation)StrataFrame Novice (99 reputation)StrataFrame Novice (99 reputation)StrataFrame Novice (99 reputation)StrataFrame Novice (99 reputation)StrataFrame Novice (99 reputation)StrataFrame Novice (99 reputation)StrataFrame Novice (99 reputation)StrataFrame Novice (99 reputation)
Group: StrataFrame Users
Posts: 69, Visits: 308
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. 

http://forum.strataframe.net/Uploads/Images/5ba4eac9-9211-485a-bcf2-454b.jpg

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
Attachments
SFtest.zip (125 views, 295.00 KB)
Larry Tucker
Larry Tucker
StrataFrame Novice (99 reputation)StrataFrame Novice (99 reputation)StrataFrame Novice (99 reputation)StrataFrame Novice (99 reputation)StrataFrame Novice (99 reputation)StrataFrame Novice (99 reputation)StrataFrame Novice (99 reputation)StrataFrame Novice (99 reputation)StrataFrame Novice (99 reputation)
Group: StrataFrame Users
Posts: 69, Visits: 308
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
Ivan George Borges
Ivan George Borges
Strategic Support Team Member (2.2K reputation)Strategic Support Team Member (2.2K reputation)Strategic Support Team Member (2.2K reputation)Strategic Support Team Member (2.2K reputation)Strategic Support Team Member (2.2K reputation)Strategic Support Team Member (2.2K reputation)Strategic Support Team Member (2.2K reputation)Strategic Support Team Member (2.2K reputation)Strategic Support Team Member (2.2K reputation)
Group: StrataFrame MVPs
Posts: 1.9K, Visits: 21K
Hi Larry.

You are using the SelectionChangeCommitted event, have you tried putting your code on the SelectedIndexChanged instead?
Larry Tucker
Larry Tucker
StrataFrame Novice (99 reputation)StrataFrame Novice (99 reputation)StrataFrame Novice (99 reputation)StrataFrame Novice (99 reputation)StrataFrame Novice (99 reputation)StrataFrame Novice (99 reputation)StrataFrame Novice (99 reputation)StrataFrame Novice (99 reputation)StrataFrame Novice (99 reputation)
Group: StrataFrame Users
Posts: 69, Visits: 308
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
Ivan George Borges
Ivan George Borges
Strategic Support Team Member (2.2K reputation)Strategic Support Team Member (2.2K reputation)Strategic Support Team Member (2.2K reputation)Strategic Support Team Member (2.2K reputation)Strategic Support Team Member (2.2K reputation)Strategic Support Team Member (2.2K reputation)Strategic Support Team Member (2.2K reputation)Strategic Support Team Member (2.2K reputation)Strategic Support Team Member (2.2K reputation)
Group: StrataFrame MVPs
Posts: 1.9K, Visits: 21K
I used the SelectedIndexChanged on your sample and didn't get the error. But maybe your situation has a different scenario.
Edhy Rijo
E
StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)
Group: StrataFrame Users
Posts: 2.4K, Visits: 23K
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.

Edhy Rijo

Attachments
SFtest2.rar (114 views, 250.00 KB)
GO

Merge Selected

Merge into selected topic...



Merge into merge target...



Merge into a specific topic ID...




Similar Topics

Reading This Topic

Login

Explore
Messages
Mentions
Search