473,705 Members | 7,096 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 2213

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

Similar topics

2
6392
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
1804
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
1593
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
1614
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
4587
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
6196
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
1610
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
1446
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
2470
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
8768
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
9139
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
9034
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
7895
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6606
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5933
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4440
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...
1
3138
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2083
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.