Comment On Still Paying it Back

God forbid they do a

SELECT * FROM table ORDER BY column DESC

to do an alphabetical search?

 

I need a drink after reading that.

Maybe I'm in the minority here but depending on the design specification, customized Combo Box sorting like this might be called for.  I know that we designed a system that had a list box with a long list of options but 80% of the time the users only used one or 2.  After a few months of using the system we were asked to please put those 2 selections at the top and we implemented a solution very similar to this one.

I do disagree with the field name however -- I'm not into the whole brevity thing.

This site gets mind-bogglinger by the day.

"What's also interesting to note is the last line setting the ListIndex to True; the ListIndex property of a ComboBox determines which item in the list is selected. As it turns out, the VB code was just too easy to follow on its own, so our seasoned veteran had to take advantage of the fact that True converts to "-1" as a number. This effectively caused no item to be selected in the list; which it wouldn't be anyway in the Form_Load method ...

I heard this kind of programming is punishable by death in some countries.

Alex Papadimoulis:

"What's also interesting to note is the last line setting the ListIndex to True; the ListIndex property of a ComboBox determines which item in the list is selected. As it turns out, the VB code was just too easy to follow on its own, so our seasoned veteran had to take advantage of the fact that True converts to "-1" as a number. This effectively caused no item to be selected in the list;

I'll have to remember to use that one in future projects [6]

My first reaction to this was...OH NO.  Not even WTF, just...my heart sank and I let out an audible groan...

Adam Colins, you poor bastard.

Anonymous:

Maybe I'm in the minority here but depending on the design specification, customized Combo Box sorting like this might be called for.  I know that we designed a system that had a list box with a long list of options but 80% of the time the users only used one or 2.  After a few months of using the system we were asked to please put those 2 selections at the top and we implemented a solution very similar to this one.

I do disagree with the field name however -- I'm not into the whole brevity thing.

I guess that's possible, but the post seems to suggest there wasn't any deviation from sorting alpahabetical. 

Or maybe they were using a custom sort field solution like you mentioned at one time, and then just never refactored when they switched back to being just alphabetical.  But its STILL fucked up that they were sorting in the combo box/windows form, rather than using an order by clause to sort on the database by the A_IX field. 

Or maybe they were just on crack.

On a different note, is anyone else having trouble with this Forums' CAPTCHA stuff?  I swear it breaks every now and then even when I put in the right word into the text box.

Fregas:

On a different note, is anyone else having trouble with this Forums' CAPTCHA stuff?  I swear it breaks every now and then even when I put in the right word into the text box.

I set it up so that it's an English word, but I agree, sometimes it's impossible to read. But, that's just the cost of randomness ... note that registered users aren't required to guess WTF the word is ...

Anonymous:

Maybe I'm in the minority here but depending
on the design specification, customized Combo Box sorting like this
might be called for.  I know that we designed a system that had a
list box with a long list of options but 80% of the time the users only
used one or 2.  After a few months of using the system we were
asked to please put those 2 selections at the top and we implemented a
solution very similar to this one.

I do disagree with the field name however -- I'm not into the whole brevity thing.

I might get beat for saying this, but for a lookup from a table like

CREATE TABLE tlkp_stuff ( stuff_id AUTOINCREMENT, stuff_name TEXT, stuff_sort INTEGER);



When they want specific stuff bubbled up to the top, I'd do (and document well):

SELECT stuff_id, stuff_name, -2 FROM tlkp_stuff

UNION ALL

SELECT stuff_id, stuff_name, -1 FROM tlkp_stuff

UNION ALL

SELECT stuff_id, stuff_name, stuff_sort FROM tlkp_stuff

ORDER BY 3;



Just because I'm a firm believer in

a) having the data show up ready for work

b) not crudding the code-behind-form more than necessary

c) delegating the work as low the codebase as possible.

d) leveraging SQL as much as possible.  For a non-Turing-complete language, you can accomplish quite a lot.

Anonymous:

I know that we designed a system that had a list box with a long list of options but 80% of the time the users only used one or 2.  After a few months of using the system we were asked to please put those 2 selections at the top and we implemented a solution very similar to this one.

The best way to accomplish this was to write a SP that got the top x used items and list those two, then show the rest of the list alphabetically.

So what happens if a 3rd option becomes normal, you'll have to re-hard code this shortcut again.  So technically your defense for this method is a wtf solution, imho.

I feel for Adam.

At my last job, the database had about 500 tables, almost all with 2 char names.

AA, AB, AC, SS, DE, FF, XX...



sheesh.

Anonymous:

I know that we designed a system that had a
list box with a long list of options but 80% of the time the users only
used one or 2.  After a few months of using the system we were
asked to please put those 2 selections at the top and we implemented a
solution very similar to this one.

Dude...



SELECT

    *

FROM

    Table

ORDER BY

    CASE

       WHEN Field = 'Most Common Value' THEN 1

       WHEN Field = 'Second Most Common Value' THEN 2

       ELSE 3 -- everybody else goes after those two

    END,

    Field -- alphabetically

Alex Papadimoulis:

Fregas:

On a different note, is anyone else having trouble with this Forums' CAPTCHA stuff?  I swear it breaks every now and then even when I put in the right word into the text box.

I set it up so that it's an English word, but I agree, sometimes it's impossible to read. But, that's just the cost of randomness ... note that registered users aren't required to guess WTF the word is ...

I'm a reg so I don't have to guess WTF the word is [:)] but I know that some CAPTCHA on a few blogs I go to are set to not work if you've been on the page for more than one minute, even if you enter in the correct word. So if you take more than one minute to enter a message before hitting the 'post' button the CAPTCHA will fail no matter what.

travisowens:

So what happens if a 3rd option becomes normal, you'll have to re-hard code this shortcut again.  So technically your defense for this method is a wtf solution, imho.

No, you would change the corresponding A_IX in the database and that is not re-hard coding!

John Smallberries:

I feel for Adam.

At my last job, the database had about 500 tables, almost all with 2 char names.

AA, AB, AC, SS, DE, FF, XX...



sheesh.

Did you do the right thing and create 500 views that mapped the 2 letter names to descriptive names?

travisowens:

Anonymous:

I know that we designed a system that had a list box
with a long list of options but 80% of the time the users only used one
or 2.  After a few months of using the system we were asked to
please put those 2 selections at the top and we implemented a solution
very similar to this one.

The best way to accomplish this was to write a SP
that got the top x used items and list those two, then show the rest of
the list alphabetically.

So what happens if a 3rd option becomes normal,
you'll have to re-hard code this shortcut again.  So technically
your defense for this method is a wtf solution, imho.

Rather than hitting the db twice, or doing UNIONS, I typically include
a DisplayOrder column for UI lookup tables. I can sort on that column
alone, or with the Description column if I want alpha sorting where the
DisplayOrder value is the same. Reordering the display simply requires
updates to the DisplayOrder values.



So I guess I'm saying where's the big WTF here? Column names? Sorting in the Combo instead of SQL?

smitty_one_each:

I might get beat for saying this, but for a lookup from a table like
CREATE TABLE tlkp_stuff ( stuff_id AUTOINCREMENT, stuff_name TEXT, stuff_sort INTEGER);

When they want specific stuff bubbled up to the top, I'd do (and document well):
SELECT stuff_id, stuff_name, -2 FROM tlkp_stuff
UNION ALL
SELECT stuff_id, stuff_name, -1 FROM tlkp_stuff
UNION ALL
SELECT stuff_id, stuff_name, stuff_sort FROM tlkp_stuff
ORDER BY 3;

I'm assuming this is incomplete psuedocode, or something, because if not, it's a bigger WTF than the original post.

 

John Smallberries:

Rather than hitting the db twice, or doing UNIONS, I typically include a DisplayOrder column for UI lookup tables. I can sort on that column alone, or with the Description column if I want alpha sorting where the DisplayOrder value is the same. Reordering the display simply requires updates to the DisplayOrder values.

So I guess I'm saying where's the big WTF here? Column names? Sorting in the Combo instead of SQL?

- Sorting in the combobox (I assume AddItem is a LOT slower when the ComboBox is sorted)

- Cryptic column names

- ListIndex = True

There may be more...

 

Rick:

John Smallberries:

I feel for Adam.

At my last job, the database had about 500 tables, almost all with 2 char names.

AA, AB, AC, SS, DE, FF, XX...



sheesh.

Did you do the right thing and create 500 views that mapped the 2 letter names to descriptive names?

No, I created a table name lookup table, then rewrote all the SQL to
join to this table to get the "real" table name, lookup the actual proc
code, parse in the "real" table names and run it as dynamic SQL. Duh.



</sarcasm>

Also:

- A_ID goes up to at least 129, so users get to pick from (at least) 129 items in a ComboBox -- There's many other more user-friendly ways to accomplish this

- Excessive use of "Me" for no apparent reason

- No .MoveNext, which would make this an infinite loop, though maybe that's just something Alex left out when he anonymized the code.

 

smitty_one_each:

I might get beat for saying this, but for a lookup from a table like
CREATE TABLE tlkp_stuff ( stuff_id AUTOINCREMENT, stuff_name TEXT, stuff_sort INTEGER);

"tlkp"??? Please tell me that isn't a prefix short for "table lookup". Because that would be pretty silly if it was. Especially considering that there is only one thing you can select from in a database: tables (views are virtual tables). That, and the word "lookup" has nothing to do with a data model (discrete domain is what people usually mean when they use that word). Even so, it'd be pretty silly to use a tables name to say it models a DD (it's also against ISO-17779 standards).

But I'm sure you knew this ... and you were just joking around ;-).

John Smallberries:

Rick:

John Smallberries:

I feel for Adam.

At my last job, the database had about 500 tables, almost all with 2 char names.

AA, AB, AC, SS, DE, FF, XX...



sheesh.

Did you do the right thing and create 500 views that mapped the 2 letter names to descriptive names?

No, I created a table name lookup table, then rewrote all the SQL to
join to this table to get the "real" table name, lookup the actual proc
code, parse in the "real" table names and run it as dynamic SQL. Duh.

I know of a place that just lives with the bad names. The names are
usually 3 letters, and it is a 3rd party application, but views are so
easy.

Smitty, other than returning 3x as much data, I don't see the benefit to that (maybe I'm completely missing the trick there).

I don't mean to be so contrary on this one but despite poor naming, poor documentation and misusing the ListIndex method -- this really doesn't hold a candle to some of the other WTF's here.

As for sorting in the Combo?  I'm not so sure that's a WTF.  If you're a firm proponent of keeping your data layers ignorant of your presentation layers you wouldn't write a data accessor that returns a specific sort.  Other components may want to use that same set of data and may not care about it's sort order -- now you have a WTF where you're adding cycles on the database processing to sort a dataset that doesn't require it. 

I like Maurits' solution -- it didn't occur to us at the time but if it had we may have baulked at it.  If we needed to add a 3rd item it would have (in fact) required another code change. By placing the sort order in the database we knew that if sort orders became a big deal we could set up an Admin interface pretty quickly to let the users make the changes their own damn selves!

 

 

 

A Wizard A True Star:

Also:

- A_ID goes up to at least 129, so users get to pick
from (at least) 129 items in a ComboBox -- There's many other more
user-friendly ways to accomplish this

- Excessive use of "Me" for no apparent reason

- No .MoveNext, which would make this an
infinite loop, though maybe that's just something Alex left out when he
anonymized the code.

 

Ok. Dumb coding? No question, but a WTF?

Compared to most of the other classics here, these offenses amount to misdemeanors.

I'm not normally one to defend bad design (and I don't really want to
do so here especially since I don't have the back details) but this
sort of design could be required if you have a custom requirement for
alphabetical sorting that isn't covered by your DB.



I've seen similar circumstances when moving from some archaic solution
that had a few quirks in the sort order that wasn't directly emulatable
without making the code & db more obtuse than just adding a custom
sort order.



Of course with my pet peeve being sh!t naming conventions, the A_IX is inexcusable. How about A_SortOrder? Nah, too easy...

A Wizard A True Star:

Also:

- A_ID goes up to at least 129, so users get to pick from (at least)
129 items in a ComboBox -- There's many other more user-friendly
ways to accomplish this

- Excessive use of "Me" for no apparent reason

- No .MoveNext, which would make this an infinite loop, though
maybe that's just something Alex left out when he anonymized the code.

 

What's wrong with using 'Me'? For one thing it makes coding quicker,
and there'll me no difference in compiler output. Clearly not a WTF at
all.

A Wizard A True Star:

- No .MoveNext, which would make this an infinite loop, though maybe that's just something Alex left out when he anonymized the code.

Nice Catch.  There's no way out!

I don't agree that the use of Me is excessive. It adds clarity and doesn't affect performance.

 

Rick:

I know of a place that just lives with the bad names. The names are
usually 3 letters, and it is a 3rd party application, but views are so
easy.

The other developers there *liked* the 2 char names (it was their design). They wouldn't let me change 'em.

A Wizard A True Star:

- No .MoveNext, which would make this an infinite loop, though maybe that's just something Alex left out when he anonymized the code.

Whoops! Actually this code is pretty much exactly what was sent in ... not sure how valuable it would be to anonymize it when we have "Payback" as the title so frequently :-). But it was my error ... I ended up transcribing it from a screenshot ...

Rick:

I know of a place that just lives with the bad names. The names are
usually 3 letters, and it is a 3rd party application, but views are so
easy.

I've come to the opinion that all those third-party and custom apps
with incomprehensible db/table names are doing it as a form of
encryption. If you can't figure out how the hell it does it, you can't
edit it or extend it, so you NEED them to make any changes for you,
assuming they'll even bother with a small customer with custom
requirements.

Some more details:

The stored procedure that returns this data does indeed order by A_IX, so the data is getting sorted by the SP and when it is inserted into the combobox.  I have no idea whether or not that affects performance, but it seems silly at least.

Here's the stored procedure code*:

CREATE PROCEDURE Accts
AS
SELECT A_ID, A_TX = A_Z + '->' + SBG + Space(135) + CONVERT(varchar,A_ID), cmb_Index = A_IX
FROM Accts
ORDER BY A_IX
GO

The 'Space(135)' is in there so that...well, hmmm, danged if I know.  The only thing I see it doing is making it so that only the last four characters of SBG are visible to the far left of the combobox while A_ID is visible in the far right of the combobox.

As for special sorting concerns:  I looked at the list when ordered alphabetically by A_TX and it is in pretty much the same order as when ordered by A_IX, save for about 11 items.  Those items are dispersed seemingly randomly though the list, not clumped at the top or the bottom like would make sense.  I have a hard time accepting that the users told the fromer developers, "NO!  A_TX "ABCdef" must 117th in the list!  ONE HUNDRED AND SEVENTEENTH!"  But I guess stranger things have happened.

 

*While it remains true to the spirit of the real code, the posted code has been somewhat anonymized.

You're right, but this still has to make you want to get drunk on white russians.

UncleMidriff:

A_TX = A_Z + '->' + SBG + Space(135) + CONVERT(varchar,A_ID)

OMFG

Anonymous:

Maybe I'm in the minority here but depending
on the design specification, customized Combo Box sorting like this
might be called for.  I know that we designed a system that had a
list box with a long list of options but 80% of the time the users only
used one or 2.  After a few months of using the system we were
asked to please put those 2 selections at the top and we implemented a
solution very similar to this one.

I do disagree with the field name however -- I'm not into the whole brevity thing.

You can actually do that all through SQL by a union of 2 select
statements, the first one returning the 2 options specifically and then
the second returning the rest of the combo options. But that's just how
I would've done it.

Jon Limjap:

You can actually do that all through SQL by a union of 2 select
statements, the first one returning the 2 options specifically and then
the second returning the rest of the combo options. But that's just how
I would've done it.

You get the two options listed twice, but that could be considered a feature.

UncleMidriff:

SELECT A_ID, A_TX = A_Z + '->' + SBG + Space(135) + CONVERT(varchar,A_ID),

The scary thing is that I know exactly why the Space(135) is in there.

Combo boxes in VB are not like dropdowns in HTML, where you can say something like...

Ten

And then use your language of choice to get the value and the text as separate items. No, VB 6 combo boxes only have the List property, and the Index property. List is the text, and Index is the item's index, relative to its position in the list. (0 for the first item, 1 for the second item, etc.)

So if you want some extra value associated with your dropdown item (like "10"), you can either do the smart thing, which is use the ItemData catch-all property and stuff it in there, or you can do the WTF thing, which is to pad your unique value out to the right with a whole bunch of spaces. Make your dropdown narrow enough, and the user won't see the unique value. Then, when the user saves his selection, parse out the right-padded number with Mid or something similar.

So, obviously, someone wanted to get the A_ID value associated with the selected item, and either they didn't know about ItemData, or this is code ported from an earlier version of VB that didn't have that property. Either way, they should have RTFM.

 

Grrr.. the forum software deleted the HTML in my previous post.

The "Ten" should have read (imagine the parentheses are angle brackets):

(option value="10")Ten(/option)

 

Anonymous:

Smitty, other than returning 3x as much data, I don't see the benefit to that (maybe I'm completely missing the trick there).

I don't mean to be so contrary on this one but despite poor naming, poor documentation and misusing the ListIndex method -- this really doesn't hold a candle to some of the other WTF's here.

As for sorting in the Combo?  I'm not so sure that's a WTF.  If you're a firm proponent of keeping your data layers ignorant of your presentation layers you wouldn't write a data accessor that returns a specific sort.  Other components may want to use that same set of data and may not care about it's sort order -- now you have a WTF where you're adding cycles on the database processing to sort a dataset that doesn't require it. 

I like Maurits' solution -- it didn't occur to us at the time but if it had we may have baulked at it.  If we needed to add a 3rd item it would have (in fact) required another code change. By placing the sort order in the database we knew that if sort orders became a big deal we could set up an Admin interface pretty quickly to let the users make the changes their own damn selves!

 

 

 

I pretty much agree with you on this one. If this were a multi-tier system it would really suck having to go back to the database to deal with the sorting.

When dealing with LOV's (lists-of-values) we had a pretty generic/reusable solution for dealing with them and one of the deals with them is arbitrary ordering. When the user was setting up their seed data they could choose that an LOV was to be sorted by display name ascending or descending, by sort order ascending or descending, relative ranking (all 1's together, all 2's together), and custom (i.e. a developer provided a custom comparator for them). If they wanted to change between any of the times (except for custom - unless we ate our wheaties) the customers could change this at any time.

We also didn't enforce the ordering in the SQL because this was in a multi-tier system and when data was being displayed in a grid the users could re-sort by clicking on one or more column headers or they could choose to reset the sort order back to the default (as defined in the metadata). This did two things for us, a.) the sorting logic was consistent within the presentation tier for all value objects but b.) we pushed some of the processing load off of the database server and onto to the client - i.e. it didn't have to execute the query and then sort it, it returned the results in natural order and let somebody/something else format it however they wanted.

The naming conventions in the Payback system are absolutely horrible. Reminds me of programming back in the days of Basic on the Commodore 64... X, XY, XX, A, EI, FU, etc. I suddenly feel old, that was over 20 years ago...

"It's only $70,000 not $140,000, doesn't anybody listen?" - Porter, Payback (1999).

foxyshadis:

Rick:

I know of a place that just lives with the bad names. The names are usually 3 letters, and it is a 3rd party application, but views are so easy.

I've come to the opinion that all those third-party and custom apps with incomprehensible db/table names are doing it as a form of encryption. If you can't figure out how the hell it does it, you can't edit it or extend it, so you NEED them to make any changes for you, assuming they'll even bother with a small customer with custom requirements.

I played a space-warfare PBEM once that used Access databases as the backend store.  All the table names were sort-of encrypted, for, I'm sure, exactly the reason you mention.

 

I'm just wondering, what's with calling all your columns A_ ?

We prepend ours with fld, standing for field. Not exactly sure why, but they were doing this since before I started, and well, at least you know when people are talking about a field (fld) or table(tbl). They say stuff like 'From tuhblUser you must get fuhldName' and you know what they are talking about. But A_ ??

What'sit stand for?

Drak

I'm stunned...
I'm no expert database user, but using a columln for storing the alphabetical order?? OMG!

John Smallberries:

I feel for Adam.

At my last job, the database had about 500 tables, almost all with 2 char names.

AA, AB, AC, SS, DE, FF, XX...



sheesh.

That may be a problem for the developer, but I once saw a realty
software system that had multiple tabs, each with a different colour
and a two letter code.  What kind of a UI is that?  Maybe the
tab names were kept short so they could get more tabs in.

I like the way this approach will work really well if anyone is ever foolish enough to dabble with one of those new fangled WHERE clauses and only select some of the data...

Drak:

I'm just wondering, what's with calling all your columns A_ ?

A is the table name, it therefore makes every field in the database have a unique name so when you write SQL you do not have to prefix the table name

Drak:

We prepend ours with fld, standing for field. Not exactly sure why, but they were doing this since before I started, and well, at least you know when people are talking about a field (fld) or table(tbl). They say stuff like 'From tuhblUser you must get fuhldName' and you know what they are talking about.

Someone implemented Hungarian Notation without understanding Hungarian Notation, interesting essay on exactly this here:-

http://www.joelonsoftware.com/articles/Wrong.html

The query must be bringing the items back in order anyway, because you can't add an item to a non-existant index in the combo's item list.

Jon Limjap:

You can actually do that all through SQL by a union of 2 select
statements, the first one returning the 2 options specifically and then
the second returning the rest of the combo options. But that's just how
I would've done it.

As someone mentioned above, doing that hardcodes the sort into the SQL.
If the desired order changes, or you want more than 2 "special" items,
you need to recode the proc.



Using a sort/display order metadata column lets you change it at any time without any code mods.



For reasonable dropdown/selection lists (less than about 100 items) I
can't believe that the ORDER BY clause imposes significant overhead for
the DB server, especially if the table is indexed properly. Offloading
this processing to the middle/presentation tier (which, even if using a
cuspy algorithm, is not going to be optimized like a DBMS) seems like
an unecessary complexity.

A Wizard A True Star:

smitty_one_each:

I might get beat for saying this, but for a lookup from a table like
CREATE TABLE tlkp_stuff ( stuff_id AUTOINCREMENT, stuff_name TEXT, stuff_sort INTEGER);

When they want specific stuff bubbled up to the top, I'd do (and document well):
SELECT stuff_id, stuff_name, -2 FROM tlkp_stuff
UNION ALL
SELECT stuff_id, stuff_name, -1 FROM tlkp_stuff
UNION ALL
SELECT stuff_id, stuff_name, stuff_sort FROM tlkp_stuff
ORDER BY 3;

I'm assuming this is incomplete psuedocode, or something, because if not, it's a bigger WTF than the original post.

 

Yes, there is a piece missing as an exercise for the reader.  (Translation: it was an off-the-cuff, untested bit).

However, the idea still works.

"Someone implemented Hungarian Notation without understanding Hungarian Notation, interesting essay on exactly this here"

Oh, yes.

That essay.

It's well written, but someone needs to slap Joel around the head some. When I've completed my full matching set of Tuits, I'll do an article on what is wrong with what he's written.

Drak:

We prepend ours with fld, standing for field. Not exactly sure why, but they were doing this since before I started, and well, at least you know when people are talking about a field (fld) or table(tbl). They say stuff like 'From tuhblUser you must get fuhldName' and you know what they are talking about.

Holy WTF naming convention. The irony of that being that FIELDS DO NOT EXIST IN A DATABASE (there are, however, columns).

I suppose that's soo much clearer than saing, "From the Users table you get the Name colum."

John Smallberries:

As someone mentioned above, doing that hardcodes the sort into the SQL. If the desired order changes, or you want more than 2 "special" items, you need to recode the proc.

I don't see a problem with that. You don't need to compile a proc for it to work, so that kind of edit would never drop everybody's session or anything.

Also, this probably wouldn't happen too much; and even if it would, the counterpart solution seems slower to me. I think that opening Enterprise Manager and editing a procedure would be quicker than opening and editing the table itself.

I knew what A_IX stood for before I read your piece :)

BTW A_TX stands for transaction