473,287 Members | 1,964 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

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.ageRangeID =
AgeRange.ageRangeID

These two tables exist in a DataSet (MyApplicationDataSet) 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).

AgeRangeTableAdapter.FillByActive: SELECT a.ageRangeID, a.description,
a.active FROM AgeRange a WHERE a.active = 'TRUE'

CustomerTableAdapter.FillByCustomerID: 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 MyApplicationDataSet
'BindingSources
Private WithEvents bsCustomer As New BindingSource
Private WithEvents bsAgeRange As New BindingSource
'Instances of TableAdapters
Private WithEvents taCustomer As New
MyApplicationDataSetTableAdapters.CustomerTableAda pter
Private WithEvents taAgeRange As New
MyApplicationDataSetTableAdapters.AgeRangeTableAda pter
'Variable for CustomerNum property
Private intCustomerNum As Integer
Public Property CustomerNum() As Integer
Get
Return Me.intCustomerNum
End Get
Set(ByVal value As Integer)
Me.intCustomerNum = value
End Set
End Property
Private Sub frmActiveTest_Load(ByVal sender As Object, ByVal e As
System.EventArgs) 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.DataSource = dsMyApp
Me.bsCustomer.DataMember = "Customer"
Me.bsAgeRange.DataSource = dsMyApp
Me.bsAgeRange.DataMember = "AgeRange"
'Data bind Customer textboxes
Me.txtCustomerID.DataBindings.Add(New
System.Windows.Forms.Binding("Text", Me.bsCustomer, "customerID",
True))
Me.txtFirstName.DataBindings.Add(New
System.Windows.Forms.Binding("Text", Me.bsCustomer, "firstName", True))
Me.txtLastName.DataBindings.Add(New
System.Windows.Forms.Binding("Text", Me.bsCustomer, "lastName", True))
'Data bind AgeRange combobox
Me.cboAgeRange.DataSource = Me.bsAgeRange
Me.cboAgeRange.DisplayMember = "description"
Me.cboAgeRange.ValueMember = "ageRangeID"
Me.cboAgeRange.DataBindings.Add(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.FillByActive(Me.dsMyApp.AgeRange)
'Load Customer data. FillByCustomerNum Query = SELECT
c.customerID, c.firstName, c.lastName, c.ageRangeID, c.entryDate FROM
Customer c WHERE c.customerNum = @customerNum
Me.taCustomer.FillByCustomerNum(Me.dsMyApp.Custome r,
Me.CustomerNum)
End Sub
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnSave.Click
'If everything on form validates, send any updates to the
Customer table
If Me.ValidateChildren = True Then
Me.bsCustomer.EndEdit()
Me.taCustomer.Update(Me.dsMyApp.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.SelectedIndex = -1). That makes
sense because the Customer.ageRangeID 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.FillByActiveUnion = 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_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load
Dim boolRefill As Boolean = False
'Data bind Customer textboxes
Me.txtCustomerID.DataBindings.Add(New
System.Windows.Forms.Binding("Text", Me.bsCustomer, "customerID",
True))
Me.txtFirstName.DataBindings.Add(New
System.Windows.Forms.Binding("Text", Me.bsCustomer, "firstName", True))
Me.txtLastName.DataBindings.Add(New
System.Windows.Forms.Binding("Text", Me.bsCustomer, "lastName", True))
'Data bind AgeRange combobox
Me.cboAgeRange.DataSource = Me.bsAgeRange
Me.cboAgeRange.DisplayMember = "description"
Me.cboAgeRange.ValueMember = "ageRangeID"
Me.cboAgeRange.DataBindings.Add(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.FillByActive(Me.dsMyApp.AgeRange)
'Load Customer data. FillByCustomerNum Query = SELECT
c.customerID, c.firstName, c.lastName, c.ageRangeID, c.entryDate FROM
Customer c WHERE c.customerNum = @customerNum
Me.taCustomer.FillByCustomerNum(Me.dsMyApp.Custome r,
Me.CustomerNum)
'Work-around code is from here down
For Each drC As dsMyApp.CustomerRow In dsMyApp.Customer
If drC.IsageRangeIDNull = False And
Me.cboAgeRange.SelectedIndex = -1 Then
Me.taAgeRange.FillByActiveUnion(Me.dsMyApp.AgeRang e,
drC.ageRangeID)
boolRefill = True
Exit For
End If
Next
If boolRefill = True Then
Me.taCustomer.FillByCustomerNum(Me.dsMyApp.Custome r,
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 2184

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

Similar topics

2
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...
6
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...
1
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...
5
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...
30
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...
4
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...
6
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...
0
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...
4
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...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

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.