Solved

How to populate a combobox w/ unique route numbers?

Posted on 2004-07-31
10
201 Views
Last Modified: 2010-05-18
I need to organize a list of sites into "routes" to be visited throughout the week.  Site 101 would be the first stop on Monday, Site 201 the first on Tues., etc.

What is the easiest way to populate a combobox with a list of available route numbers?

I already have a dataset of the sites with RouteNum as one of the fields (but not sorted on this field).    Is this of use, or do I need to requery the database to find which numbers have already been taken?

Capt
0
Comment
Question by:Glen Gibb
  • 4
  • 4
  • 2
10 Comments
 
LVL 18

Expert Comment

by:DotNetLover_Baan
ID: 11686735
Hi there...
it would be easier if you change your SQL statement while reading the data. Use, "DISTINCT" select. eg:
"SELECT DISTINCT RouteNum FROM RouteTable".  Get a reader, and do  .Item.Add() to populate the combo.
-Baan
0
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 11688636
you could also use an aggregate column in your dataset to get this ...

http://msdn.microsoft.com/library/en-us/cpguide/html/cpconcreatingexpressioncolumns.asp
http://msdn.microsoft.com/library/en-us/dndive/html/data05312002.asp

you would add them to another datatable then just bind the second datatable to the list, you could also keep in this second datatable the aggregate of the count etc of the sites in the routing number
0
 

Author Comment

by:Glen Gibb
ID: 11700506
Good suggestions, guys, but I need to populate the combo w/ values that have NOT YET been taken!

If 101, 102, 103, 104 are already in the database, how do I easily let my user know that he can assign 110, 111, 112, and 114 to the next customer's sites?

Capt
0
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 11700652
well you would do a "not in" query ...

for instance ... select value from table where id not in select id from parenttable where yourcriteria
0
 

Author Comment

by:Glen Gibb
ID: 11712065
If I get your drift, then, I need a table w/ values from say 100 - 699, so that I can select the ones that have not been used?

Capt
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 37

Expert Comment

by:gregoryyoung
ID: 11712097
i am assuming that one gets used you write it back to the database somehow ? what is your table structure ?
0
 
LVL 18

Expert Comment

by:DotNetLover_Baan
ID: 11712176
well, how do define values that are  "Used / Not Used" ?
0
 

Author Comment

by:Glen Gibb
ID: 11730667
Additional items for the last two questions:

The table looks like this:

tblCropYear
SiteID  AgriYear  GrowerFK  AgriFieldFK ... RouteNum
123...       2004              1           2510              201

If the user wishes to assign another grower's site to a route that should be visited on Tuesday, he needs a unique value from 200-299.  But 201 is not available, because it has been assigned to another route this crop year.  Thus the route number has been "used" in the current crop year.  (In 2005, route 201 could be reassigned to a different crop site.)

My idea is to give the user a popup window that asks what day of the week he wishes to assign a crop site to be visited.  If he clicks "Monday" then the idea is to return to the main AgriField screen, where he is presented with a combobox of unassigned route numbers from 100-199.  He selects the number and tblCropYear is then updated.

The question is, then, I already have tblCropYear loaded.  How do I find what route numbers have been assigned (for Monday, in the case above)?  Do I have to re-query and sort on values >99 and <200, or is there a magic way to filter the values already in memory?

Some suggestions above seem to suggest putting all the open routenumbers (from 100-699) into a table, but isn't this inefficient as far as database usage goes?  A loop can produce those numbers very quickly.  It's just that those already assigned need to be discarded from the array.

An additional question--into what sort of data structure does one place the available routenumbers to populate the combobox of the original screen?  Already tried an arraylist, which doesn't seem to work.  Trying to pass a combobox as a property doesn't seem to be successful either.

Help!

0
 
LVL 37

Accepted Solution

by:
gregoryyoung earned 500 total points
ID: 11730736
there is a magic way ! you currently have the data in a datatable ?

you can use a dataview to set a filter on the table !

DataView dv = new DataView(ds.Tables["yourtable"]);
dv.Filter = criteria;

you can also sort using dv.Sort ...

I would recommend using a loop and the querying the datatable using its "select" method to see if the route number is open (if it returns a row its not) http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatadatatableclassselecttopic2.asp

0
 

Author Comment

by:Glen Gibb
ID: 11738538
So there IS a magic bullet!  Thanks for everything.  The popup is coded and under way.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now