Solved

How to populate a combobox w/ unique route numbers?

Posted on 2004-07-31
10
204 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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …

829 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