473,890 Members | 1,342 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Problem with ComboBoxes data bound to lookup tables

I've been working with VB .NET for less than a year and this is the
first time I've posted on one of these groups, so let me apologize
beforehand if I'm being unclear, not posting my issue correctly,
posting to the wrong forum, or committing some other sort of faux pas.

My team is developing a Windows Forms application using VS 2005 with
SQL Server 2005 on the back end and we're having a problem using
ComboBoxes data bound to lookup tables.

I've created a simplified example to illustrate the problem:

Consider the following SQL Server tables:

AgeRange:

ageRangeID description active
1 under 18 FALSE
2 18 - 25 TRUE
3 26 - 30 TRUE
4 30 or over TRUE

Customer:

customerID firstName lastName ageRangeID
9368 John Smith 1
9369 Jill Johnson 3

A Foreign Key relationship between the tables: Customer.ageRan geID =
AgeRange.ageRan geID

These two tables exist in a DataSet (MyApplicationD ataSet) within the
project. Each table has a corresponding TableAdapter.

Each TableAdapter has one custom query (in addition to the standard set
of queries generated by the creation of any TableAdapter).

AgeRangeTableAd apter.FillByAct ive: SELECT a.ageRangeID, a.description,
a.active FROM AgeRange a WHERE a.active = 'TRUE'

CustomerTableAd apter.FillByCus tomerID: SELECT c.customerID,
c.firstName, c.lastName, c.ageRangeID, c.entryDate FROM Customer c
WHERE c.customerID = @customerID

Consider the following form:

Name: frmActiveTest
Controls: 3 TextBoxes (txtCustomerID, txtFirstName, txtLastName)
1 ComboBox (cboAgeRange)
1 Button (btnSave)

Form code:

Public Class frmActiveTest
'I know most of what you're about to see can be done in the
Designer...I'm just coding by hand to illustrate my problem
'Instance of DataSet
Private WithEvents dsMyApp As New MyApplicationDa taSet
'BindingSources
Private WithEvents bsCustomer As New BindingSource
Private WithEvents bsAgeRange As New BindingSource
'Instances of TableAdapters
Private WithEvents taCustomer As New
MyApplicationDa taSetTableAdapt ers.CustomerTab leAdapter
Private WithEvents taAgeRange As New
MyApplicationDa taSetTableAdapt ers.AgeRangeTab leAdapter
'Variable for CustomerNum property
Private intCustomerNum As Integer
Public Property CustomerNum() As Integer
Get
Return Me.intCustomerN um
End Get
Set(ByVal value As Integer)
Me.intCustomerN um = value
End Set
End Property
Private Sub frmActiveTest_L oad(ByVal sender As Object, ByVal e As
System.EventArg s) Handles Me.Load
'Change this value to bring up the Customer record you wish to
view
Me.CustomerNum = 9368
'Set up binding sources
Me.bsCustomer.D ataSource = dsMyApp
Me.bsCustomer.D ataMember = "Customer"
Me.bsAgeRange.D ataSource = dsMyApp
Me.bsAgeRange.D ataMember = "AgeRange"
'Data bind Customer textboxes
Me.txtCustomerI D.DataBindings. Add(New
System.Windows. Forms.Binding(" Text", Me.bsCustomer, "customerID ",
True))
Me.txtFirstName .DataBindings.A dd(New
System.Windows. Forms.Binding(" Text", Me.bsCustomer, "firstName" , True))
Me.txtLastName. DataBindings.Ad d(New
System.Windows. Forms.Binding(" Text", Me.bsCustomer, "lastName", True))
'Data bind AgeRange combobox
Me.cboAgeRange. DataSource = Me.bsAgeRange
Me.cboAgeRange. DisplayMember = "descriptio n"
Me.cboAgeRange. ValueMember = "ageRangeID "
Me.cboAgeRange. DataBindings.Ad d(New
System.Windows. Forms.Binding(" SelectedValue", Me.bsCustomer,
"ageRangeID ", True))
'Load AgeRange data. FillByActive Query = SELECT a.ageRangeID,
a.description, a.active FROM AgeRange a WHERE a.active = 'TRUE'
Me.taAgeRange.F illByActive(Me. dsMyApp.AgeRang e)
'Load Customer data. FillByCustomerN um Query = SELECT
c.customerID, c.firstName, c.lastName, c.ageRangeID, c.entryDate FROM
Customer c WHERE c.customerNum = @customerNum
Me.taCustomer.F illByCustomerNu m(Me.dsMyApp.Cu stomer,
Me.CustomerNum)
End Sub
Private Sub btnSave_Click(B yVal sender As System.Object, ByVal e As
System.EventArg s) Handles btnSave.Click
'If everything on form validates, send any updates to the
Customer table
If Me.ValidateChil dren = True Then
Me.bsCustomer.E ndEdit()
Me.taCustomer.U pdate(Me.dsMyAp p.Customer)
End If
End Sub
End Class

Here is my problem:

I created the record for customerID 9368 on 01/01/06 with ageRangeID =
1 (under 18). On 01/05/06 I set the value of AgeRange.active to FALSE
for ageRangeID = 1 (under 18). So from 01/05/06 onward, no new
Customer records can be created with an ageRangeID = 1 (under 18).
That's fine, that's what I want.

However, now on 01/06/06 when I pull up customerID 9368, the AgeRange
ComboBox shows as empty (cboAgeRange.Se lectedIndex = -1). That makes
sense because the Customer.ageRan geID value (1) is not in the results
returned by the query I am using to fill the AgeRange table since the
FillByActive query only looks for those AgeRange records with an
AgeRange.active value of TRUE.

That's my problem. How can I fill a ComboBox with only those records
whose AgeRange.active value is TRUE, yet make sure I ALWAYS include the
AgeRange record that relates to the current Customer record, even if
its active value is FALSE?

I found one work-around, but it is not very elegant and I feel like
it's a lot of code for what I think would be a simple and common
problem.

My work-around was to add the following custom query to the AgeRange
TableAdapter:

AgeRange.FillBy ActiveUnion = SELECT a.ageRangeID, a.description,
a.active FROM AgeRange a WHERE a.active = 'TRUE' UNION SELECT
b.ageRangeID, b.description, b.active FROM AgeRange b WHERE
b.ageRangeID = @ageRangeID

Then make the following changes to my form Load event:

Private Sub frmActiveTest_L oad(ByVal sender As Object, ByVal e As
System.EventArg s) Handles Me.Load
Dim boolRefill As Boolean = False
'Data bind Customer textboxes
Me.txtCustomerI D.DataBindings. Add(New
System.Windows. Forms.Binding(" Text", Me.bsCustomer, "customerID ",
True))
Me.txtFirstName .DataBindings.A dd(New
System.Windows. Forms.Binding(" Text", Me.bsCustomer, "firstName" , True))
Me.txtLastName. DataBindings.Ad d(New
System.Windows. Forms.Binding(" Text", Me.bsCustomer, "lastName", True))
'Data bind AgeRange combobox
Me.cboAgeRange. DataSource = Me.bsAgeRange
Me.cboAgeRange. DisplayMember = "descriptio n"
Me.cboAgeRange. ValueMember = "ageRangeID "
Me.cboAgeRange. DataBindings.Ad d(New
System.Windows. Forms.Binding(" SelectedValue", Me.bsCustomer,
"ageRangeID ", True))
'Load AgeRange data. FillByActive Query = SELECT a.ageRangeID,
a.description, a.active FROM AgeRange a WHERE a.active = 'TRUE'
Me.taAgeRange.F illByActive(Me. dsMyApp.AgeRang e)
'Load Customer data. FillByCustomerN um Query = SELECT
c.customerID, c.firstName, c.lastName, c.ageRangeID, c.entryDate FROM
Customer c WHERE c.customerNum = @customerNum
Me.taCustomer.F illByCustomerNu m(Me.dsMyApp.Cu stomer,
Me.CustomerNum)
'Work-around code is from here down
For Each drC As dsMyApp.Custome rRow In dsMyApp.Custome r
If drC.IsageRangeI DNull = False And
Me.cboAgeRange. SelectedIndex = -1 Then
Me.taAgeRange.F illByActiveUnio n(Me.dsMyApp.Ag eRange,
drC.ageRangeID)
boolRefill = True
Exit For
End If
Next
If boolRefill = True Then
Me.taCustomer.F illByCustomerNu m(Me.dsMyApp.Cu stomer,
Me.CustomerNum)
End If
End Sub

I have to implement this on dozens of ComboBoxes using dozens of
different lookup tables (like AgeRange) throughout my application, so
I'm looking for the fastest, easiest solution to this problem.

Any help I can get from anyone out there is greatly appreciated.

Thanks in advance!

Regards,

MJS

Mar 14 '06 #1
0 2224

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
6401
by: DBQueen | last post by:
Hi - In my A2K database, I have a form with 2 Unbound Comboboxes, both of which open up forms to a particular record. One of these comboboxes works perfectly. For some reason the 2nd one WILL NOT autofill. If I enter a number to lookup by typing it in, it ALWAYS says it's not in the list (Error 2237) - but if I select the item, it works fine. I only have 1 field in this combobox - the SampleID. It is set up with the following...
6
1811
by: Andi Plotsky | last post by:
Hi - I sent this request last week, but didn't get an answer to my problem - let's try again: In my A2K database, I have a form with 2 Unbound Comboboxes, both of which open up forms to a particular record. One of these comboboxes works perfectly. For some reason the 2nd one WILL NOT autofill. If I enter a number to lookup by typing it in, it ALWAYS says it's not in the list (Error 2237) - but if I select the item, it works fine. I...
1
1597
by: Sergio Florez M. | last post by:
I have a windows form with a bunch of textboxes, comboboxes and three datagrids. My dataset has 4 tables and the necesary relations. Basically it's a master page with three detail grids. My problem is that when I move the position of the BindingContext the datagrids reflect the change but the textboxes and comboboxes don't. -- El que persevera insiste Sergio Florez M. Medellín, Colombia
5
1623
by: Mika M | last post by:
Hello! I have Windows Forms application form containing TextBoxes, six ComboBoxes, and DataGrid for details. I have created DataSet with needed tables, and created relations between tables, and bound controls to datatables. Everything is working almost fine including saving data, but when I press 'New' - button ... thisCurrencyManager.EndCurrentEdit()
30
4622
by: dbuchanan | last post by:
ComboBox databindng Problem == How the ComboBox is setup and used: My comboBox is populated by a lookup table. The ValueMember is the lookup table's Id and the DisplayMember is the text from a corresponding field in the lookup table. In my data table we store the ID in what I will call the 'key' field. == Description of the desired operation:
4
6207
by: ECathell | last post by:
I am trying to databind 2 combo boxes to the same datasource but different fields using the same lookup table. The information in combobox2 is duplicating combobox 1(ie it appears to be gathering the wrong information) I have verified the proper information through query analyzer.... Me.FormatName1ComboBox.DataSource = LabelFormats Me.FormatName1ComboBox.DisplayMember = "Formatname" Me.FormatName1ComboBox.ValueMember = "FormatName" ...
6
1615
by: Senna_Rettop | last post by:
Hello, I'm new at Access and ran into a problem. I have a table with a field for customer's names. I want to make a lookup field out of the names by linking it to a table that holds all the customer's names, adresses, phone numbers, ect. I made a form with a name field and would like to be able to lookup the name, click it and the have that person's info come up intantly in a subform. I have the layout all set up, but I've run into a...
0
1451
by: ReneMarxis | last post by:
Hello all first let me say i start getting an idea on how powerful data binding is. You can hold your code as short as possible and also have a pretty big flexibility. Also thanks to this great community here, without you i bet many of us would not succeed that fast. So now to my problem :)
4
2478
by: Karl | last post by:
Using A2000 with 2 tables Table 1 is a reference table with 2 fields Table 2 has 2 fields and 2 combo boxes to lookup date in table 1 Combo1 binds to col 1 in table 1, this works OK Combo 2 is set to bind to col 2 in table 1 but it always binds col 1 Column Count is set to 2 and I can see both columns in the dropdown Bound Column is set to 2
0
9978
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9819
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11222
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10811
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10919
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
5846
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6041
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4270
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3275
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.