Select the product you need help with

Getting values for setting Multi-select listboxes in SQL data

Article ID: 867124

TechKnowledge Content

SUMMARY

This article describes how to interpret the binary values of a multi-select listbox in the SQL database.

MORE INFORMATION

Dexterity has provided a Multi-Select List Box with a binary(4) data type, therefore all 32 items can be selected using the hex value to mask the binary(4) data type. The values can be set in all stored procedures by using the bitwise operator OR (|). A stored procedure can test to see if a bit position is on by using the bitwise operator AND (&). In addition, there will be two store procedures in the DYNAMICS database one to turn/off the bit position, and one to check it a bit position is on.

Important: Each of the possible 32 multi-select list items has been define as a constant in DYNAMICS..CONSTANTS. It is important to note that the constants define the exponent and not the hex value. This is because the CONSTANTS table store all content values as strings and smGetConstants procedures convert the string to the requested return value. The conversion of a hex string to a binary value is machine dependent and will be converted to different value on big endian and little endian machines. Therefore the smGetConstantMSItem stored procedure must be used to get the correct machine independent value for the bit MASK constant.

DecimalHex MaskConstantExponent
1 0x01000000 MS_ITEM_1 24
2 0x02000000 MS_ITEM_2 25
3 0x04000000 MS_ITEM_3 26
4 0x08000000 MS_ITEM_4 27
5 0x10000000 MS_ITEM_5 28
6 0x20000000 MS_ITEM_6 29
7 0x40000000 MS_ITEM_7 30
8 0x80000000 MS_ITEM_8 31
9 0x00010000 MS_ITEM_9 16
10 0x00020000 MS_ITEM_10 17
11 0x00040000 MS_ITEM_11 18
12 0x00080000 MS_ITEM_12 19
13 0x00100000 MS_ITEM_13 20
14 0x00200000 MS_ITEM_14 21
15 0x00400000 MS_ITEM_15 22
16 0x00800000 MS_ITEM_16 23
17 0x00000100 MS_ITEM_17 8
18 0x00000200 MS_ITEM_18 9
19 0x00000400 MS_ITEM_19 10
20 0x00000800 MS_ITEM_20 11
21 0x00001000 MS_ITEM_21 12
22 0x00002000 MS_ITEM_22 13
23 0x00004000 MS_ITEM_23 14
24 0x00008000 MS_ITEM_24 15
25 0x00000001 MS_ITEM_25 0
26 0x00000002 MS_ITEM_26 1
27 0x00000004 MS_ITEM_27 2
28 0x00000008 MS_ITEM_28 3
29 0x00000010 MS_ITEM_29 4
30 0x00000020 MS_ITEM_30 5
31 0x00000040 MS_ITEM_31 6
32 0x00000080 MS_ITEM_32 7

Examples:

declare @MS_ITEM_1 int, /* this must be defined as an integer */ 

@bListBox binary(4), /* this must be defined as a binary(4) */ 

@iErrorState int, @iStatus int exec 

@iStatus = DYNAMICS..smGetConstantMSItem 'MS_ITEM_1', 

@MS_ITEM_1 output, @iErrorState output 

/* * The multi-select listbox must be initialized. */ 

select @bListBox = 0 

/* * Set multi-select listbox item */ 

select @bListBox = (@bListBox | @MS_ITEM_1) 

/* * check if multi-select listbox item is selected */ 

if (@bListBox & @MS_ITEM_1) = @MS_ITEM_1 

	 print 'Item is selected' 

else 

	 print 'Item was not selected'

In Dynamics C/S+ for SQL, the smGetConstantMSItem stored procedure can be used to get the value to update the ML List box. Here is an example of a call to this stored procedure:

/* * Set @O_iMSValue to MS_ITEM_2 */ 

exec @iStatus = %SysDbName%..smGetConstantMSItem 'MS_ITEM_2', @O_iMSValue output, @O_iErrorState output

In addition, the actual source code for the stored procedure itself is shown below. From this, you can see that we use SQL constants to raise 2 to the appropriate power value. The exception case is a position of 8, so make sure and note that difference.

/*Begin_Procs smGetConstantMSItem */ 

if exists (select * from dbo.sysobjects where id = Object_id('.smGetConstantMSItem') and type = 'P') begin drop proc .smGetConstantMSItem end go 

create procedure dbo.smGetConstantMSItem @I_cConstantName char(255) = NULL, @O_iConstantValue int = NULL output, @O_iErrorState int = NULL output as 

/*Version Info %W% %E% %U%*/ 

/* * Check input for null value (i.e. missing) */ 

if @I_cConstantName is NULL begin 

select @O_iErrorState = 20270 return end 

select @O_iErrorState = 0 if rtrim(ltrim(@I_cConstantName)) = 'MS_ITEM_8' begin 

select @O_iConstantValue = convert(int, 0x80000000) return end 

select @O_iConstantValue = convert (int, convert(binary(4), power(2,convert(int,Value)))) from CONSTANTS where Name = @I_cConstantName 

if @@rowcount <> 1 

select @O_iErrorState = 20170 return go 

grant execute on smGetConstantMSItem to DYNGRP go 

/*End_Procs smGetConstantMSItem */

This article was TechKnowledge Document ID: 9552