Search Defend on Look Up

[xswzaq]

I have two combobox, first one is StateComboBox, and the second one is CityCombobox. There is two thing I want to do for these combobox, but I am really lost. PLEASE HELP!

First I have this StateColumn with California, Texas, Florida, California, California, Texas, Arizona.....
When I binding it to the Statecombobox it show all. I need help with delete any duplicate array when showing in the combo box. It only should be showing California, Texas, Florida, Arizona.....

Second. when I click StateCombobox, the city will automatically show up. Like if I select California, only city under California will show up in the CityComboBox like Sacramento...San Diego... and more. When i select Florida, same thing only city under Florida should show up, (Sarasota...Miami...Orlando...ect...). But I don't know how to do it.

can I run a search first (using fillby) and make the citycombobox read and add items into the box from the citycolumn?

Is there a way to do it?

 

[Arg81]

First I have this StateColumn with California, Texas, Florida, California, California, Texas, Arizona.....
When I binding it to the Statecombobox it show all. I need help with delete any duplicate array when showing in the combo box. It only should be showing California, Texas, Florida, Arizona.....

I assume you don't have a lookup table in your database for the States then? I.E. you would have a table that showed;

StateID | StateName
1............California
2.............Florida
3.............Texas
4.............Arizona
etc etc

In your main table, you would then simply use StateID instead of State, and this will display the numeric value instead.

This is how databases are meant to be designed, the way you have it set up means you have a lot of duplicate data and "potential" errors - i.e. if a state is spelt wrong in one row, it wouldn't be included in a query you run.

If you don't want to use this route, and keep what you have, you need your select query to be along the lines of SELECT DISTINCT StateName FROM State

Second. when I click StateCombobox, the city will automatically show up. Like if I select California, only city under California will show up in the CityComboBox like Sacramento...San Diego... and more. When i select Florida, same thing only city under Florida should show up, (Sarasota...Miami...Orlando...ect...). But I don't know how to do it.

You will need to have a relationship in your database between State and City. Therefore this is probably best to sit down and redesign your database so it's all nice and neat.

You'd have a State table, and then a City Table.

State will be like the example above, and City will use a FK (Foreign Key) to relate to the State table.

Example

CityID | StateID | CityName

1...........1............Sacramento
2...........1.............San Diego
3...........2.............Miami
4...........2.............Orlando
etc etc

As you can see, the relationship shows that for StateID 1 (California) there are 2 cities, Sacramento and San Diego, and for StateID 2 (Florida) there are 2 cities, Miami and Orlando.


Once your tables are in place, you can create the relationships. You would have a one-to-many relationship between State and City, StateID as a Primary Key in State table, related to StateID (as an FK) to the City table. You would have CityID as the Primary Key in your City table.

 

[cjard]

I agree with the database redesign.. Populate a table of states, and then keep improving your list of cities by providing an editable combobox that does autocomplete. If the user types an entry not in the list, add it to your cities table..

 

[xswzaq]

I guess you right, I need to redesign my programs. Thank you all.

 

[xswzaq]

Date Search

is there any way to let the combobox read the Citycolumn using fillbysearch query?

 

[cjard]

I have this sentence (select lot, time, part from update_table where part = part (+) and TIME BETWEEN :From_Date AND :To_Date) to handle my search, but whenever I type in from_date textbox and to_date textbox, it did not work like i want it to be. The from_date textbox is fine, but not the to_date textbox, it alway minus one day. Like if I type in 6/6/2007 and 7/8/2007. It go out and search then get my back data only from 6/6/2007 to 7/7/2007. and if i type in 6/6/2007 and 7/9/2007. i got date from 6/6/2007 to 7/8/2007. Why? I been working on it, but could not figure out what did i do wrong. I even chang my query to select lot, time, part from update_table where part = part (+) and TIME >= :From_Date AND time <= :To_Date, but i still have same problems.

I dont know where that post went but this SQL appears to be duff for a start:


select lot, time, part from update_table where part = part (+) and TIME BETWEEN :From_Date AND :To_Date

What exactly is the purpose of the part=part(+) bit (in your mind; it makes no logical sense in mine)?

From_Date and To_Date are parameters, and judging by the colon, youre working wih ORACLE
I usually avoid the BETWEEN operator because it isnt clear whether it is inclusive or exclusive of one or both operands



select lot, time, part from update_table where TIME >= :From_Date AND time < :To_Date




the last question, i dont understand at all

 

[xswzaq]

The post above I move it to new thread since it is two different question.

the (+) is for outer join operator. It is to make it quicker for me to do the search (since there are more than 20,000 records in three different table (the above are just a little bit of line in my query, not all)). Even if I used select lot, time, part from update_table where TIME >= :From_Date AND time <= :To_Date[/B]. I still got same result, somehow it stop at the date before To_Date.


My last question is that I create a fill by search for the State, when I enter the State in the combobox it will go out, and bring me back a table data that is in state I select only. And in that table there are city-column, so I want to populated that city-column in my CityCombobox. Is this possible to do?

 

[Arg81]

when I enter the State in the combobox it will go out, and bring me back a table data that is in state I select only. And in that table there are city-column, so I want to populated that city-column in my CityCombobox. Is this possible to do?

Yes, as I have shown on my previous post, you will need to create a CityID table that is related to the State table. When a state is selected, the city dataTable only lists the city(s) for that State.

 

[xswzaq]

About the Date Problem, I figure out a way to make it work the way I want. Thank you very much for all of your help

 

[xswzaq]

Hey Arg81, your idea really help me. it finally works. Thank you very much.

 

[Arg81]

no problem