Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 215
  • Last Modified:

How to populate a combobox w/ unique route numbers?

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
Glen Gibb
Asked:
Glen Gibb
  • 4
  • 4
  • 2
1 Solution
 
DotNetLover_BaanCommented:
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
 
gregoryyoungCommented:
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
 
Glen GibbAuthor Commented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
gregoryyoungCommented:
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
 
Glen GibbAuthor Commented:
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
 
gregoryyoungCommented:
i am assuming that one gets used you write it back to the database somehow ? what is your table structure ?
0
 
DotNetLover_BaanCommented:
well, how do define values that are  "Used / Not Used" ?
0
 
Glen GibbAuthor Commented:
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
 
gregoryyoungCommented:
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
 
Glen GibbAuthor Commented:
So there IS a magic bullet!  Thanks for everything.  The popup is coded and under way.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now