Solved

How to populate a combobox w/ unique route numbers?

Posted on 2004-07-31
10
206 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Groupbox Control ? 2 39
ASP.NET/VB: Convert Date and Time to YYYY-MM-DDTHH:MM:SS 3 48
Stop Git from being my repository 1 48
vb.net datagrid point 4 33
Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

739 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