Toprow of NameValueList with null value

joel posted on Wednesday, August 15, 2007

I've seen other posts about how to implement NameValueLists with optional toprows (to allow an unselected value), but I don't think anyone has mentioned the possibility of having a nullable type as the key/index. In most examples, the toprow's index for an "Unselected" value is either "" for strings or 0 for integers.  My underlying table uses an int datatype that allows nulls.  Historically, I've left the database field null to represent an unselected value, so I'd like to keep it that way.  How can I specify in my NameValueList class that it should return a list of (nullable (of Integer), String)?

Joel

McManus replied on Wednesday, August 15, 2007

Joel,

David Wendelken created some types (SmartInt16, SmartInt32, etc.) that act like a SmartDate. Perhaps you can use a SmartInt32 as the key in the NVL. The key of the toprow can be an empty SmartInt32.

Cheers,
Herman

david.wendelken replied on Thursday, August 16, 2007

McManus:

David Wendelken created some types (SmartInt16, SmartInt32, etc.) that act like a SmartDate. Perhaps you can use a SmartInt32 as the key in the NVL. The key of the toprow can be an empty SmartInt32.

Herman,

Thanks for the plug on the classes!

Here's the way I like to do sql server stored procs for the dropdownlists. 

If the record is in insert mode, we bring back a <Choose> tag as the first entry.

If the field is optional, we bring back an empty tag as the next entry.

Sort_Order is a dummy column that allows us to force things to sort in a particular order.

CREATE procedure [MySchema].[MyApp_MyTable_ddl]
 @IsOptional bit   -- is this an optional field, or must the user choose something?
,@IsInsert      bit   -- are we in insert mode or update mode?
as

select 
 tab.Id                  as THE_VALUE
,tab.Description as THE_TEXT
,0                           as SORT_ORDER
from [MySchema].[MyTable]
union all
select
 NULL
,''
,-1
where @IsOptional = 1
union all
select
 NULL
,'<Choose>'
,-2
where @IsInsert = 1
order by 3,2

I usually just use strings (because my internal SmartInt32 variables are exposed as string properties).