[RESOLVED] Combo Box

[dr223]

Hi,

On my form I have a combo box called 'cmbtraininglocation'. This field can be left blank on first form upload as training location is not yet selected.

At my backend SQL I have a table called -

dbo.tbltraininglocType

LocationID (PK) LocationName
1 New York
2 London
3 Toronto


Now, I called a dataset to populate the combo box on form load..

The problem is if the field was not populated at the first instance it populates New York as it is the first record on the table.

Note: the combo box has a dropdownstyle = DropDownList..

Ok performed a work around


LocationID (PK) LocationName
1 New York
2 London
3 Toronto
4

New locationId = 4 with empty string, then I ordered by LocationName.. This resolved the problem and on form load if the combo box is emoty at the backend I do get an empty combo box.

However, the combo box appears like

Empty Space
London
New York
Toronto

The annoying bit is the Empty space, which serves the purpose, but also as it is top on the list of selection will ignite wrong selection in some occassions. Also, having an ampty space in the backend table is NOT neat..

Any suggestions please..

Many thanks

[dday9]

Why not just have:

London
New York
Toronto
Training

That away when you sort it, it should appear at the bottom of the list and in your database you'll instantly recognize what it is(as opposed to just an empty space).

[make me rain]

I did not know which is your back end , how ever i would like to say that
(1) don't add empty string in the records ( LocationName ) for this purpose

(2) if you want the specific LocationName on top of the ORDER , then REORDER the default ORDER BY in your SQL statement which loads the combobox
i,e In the below example ordering is shown

SELECT
traininglocation.LocationId,
traininglocation.LocationName
FROM
traininglocation 
ORDER BY
CASE WHEN traininglocation.LocationName = "INDIA" THEN -1 ELSE traininglocation.LocationId END

[dr223]

Ok Tried;

SELECT        LocationID, TrainingLoc
FROM            tblTrainingLocType
ORDER BY CASE WHEN dbo.tblTrainingLocType.TrainingLoc = "London" THEN - 1 ELSE dbo.tblTrainingLocType.LocationID END

At runtime, I receive the error - Inavlid Column Name 'London'

Please help - Thank you ...

[make me rain]

read post #3

I did not know which is your back end , how ever i would like to say that
(1) don't add empty string in the records ( LocationName ) for this purpose

[make me rain]

i doubt the miss spell of the filed names
i reproduce the same query as per your table just try it as it is

SELECT
tblTrainingLocType.LocationID ,
tblTrainingLocType.TrainingLoc
FROM
tblTrainingLocType
ORDER BY
CASE WHEN tblTrainingLocType.TrainingLoc = "London" THEN - 1 ELSE tblTrainingLocType.LocationID END

[IanRyder]

Hi,

You have both made the same simple mistake in that SQL code. You need to use the Single Quote character in that Case statement and not the Double Quote character.

Err?, I have just quickly read the rest of this thread and if I understand what you are wanting to do correctly, just set the SelectedIndex property of the ComboBox to -1 in the FormShown event to make sure that nothing is selected in the ComboBox after the Form has loaded.

Hope that helps.

Cheers,

Ian

[dr223]

Ok -

This works but still I dont get my empty space on the top of th ecombo box instead I receive London on Form Load

SELECT 
tblTrainingLocType.LocationID ,
tblTrainingLocType.TrainingLoc 
FROM 
tblTrainingLocType
ORDER BY 
CASE WHEN tblTrainingLocType.TrainingLoc = "London" THEN - 1 ELSE tblTrainingLocType.LocationID END

I know I can use selectedindex = -1 onmn form load to have an ampty space, but when I click th eedit button. The user should be able to insert an empty string as location is not allocated a s yet.. My dropdownlist combobox MUST have an empty option..

Adding an empty string on the table at the backend doesnt look good..

Any ideas

Thank you

[techgnome]

Oi....

1) Remove the empty row from your table... it should have never been there in the first place.
2) Re-write the query as follows:

SELECT -1 as LocationID , '' As LocationName
UNION ALL
SELECT
traininglocation.LocationId,
traininglocation.LocationName
FROM
training
Order by 1

Note: that is TWO single quote marks... not a single double... muy importante!

This will give you a result where the blank floats to the top, and the rest remain in their ID order.

Not sure what it was everyone else was trying to do with a CASE in the ORDER clause - it only affected one row anyways, and not even the row that was needing manipulation. But since the blank record should never have existed in the first place, it's moot.

-tg

[dr223]

Resolved.. Thank you guys