I had to write some SQL udf's to handle this.
The combo box is sent to SQL as a comma-delimited string (e.g. "1,2,3,4,5,6") Make sure to note there are no spaces in that string either.
I wrote a UDF to handle inserts of these values, as well as to retrieve them...
For inserts, @combobox_commastring is the comma delimited string that is holding the "1,2,3,5,6,7" or whatever are the values passed from your combobox
declare @string varchar(500)
set @string = @combobox_commastring
declare @pos int
declare @piece varchar(500)
-- Need to tack a delimiter onto the end of the input string if one doesn't exist
if right(rtrim(@string),1) <> ','
set @string = @string + ','
set @pos = patindex('%,%' , @string)
while @pos <> 0
begin
set @piece = left(@string, @pos - 1)
-- Insert the individual values in to a unique row
INSERT INTO combo_boxvalues ([relational_id], [single_value], [IsActive],[CreateDateTime])
Values (@relational_id, cast(@piece as int),'1',getdate())
set @string = stuff(@string, 1, @pos, '')
set @pos = patindex('%,%' , @string)
end
END
To retrieve the values, assume you need them for SQL Bind or whatever... the UDF below will create a comma-delimited string
CREATE FUNCTION [dbo].[udf_Get_delimited_string](@relational_id varchar(50))
RETURNS VARCHAR(1000)AS
BEGIN
DECLARE @ComboBoxValues varchar(1000)
SELECT @ComboBoxValues = COALESCE(@ComboBoxValues + ',', '') + s.single_value
FROM [combobox_values] s
WHERE (s.relational_id = @relational_id) and (s.IsActive = '1')
RETURN @ComboBoxValues
END
Then, to use this UDF in the SQL Bind (better to put it in a stored procedure of course)
select relational_id, field1, field2, field3, [dbo].[udf_Get_delimited_string] (relational_id) as combo_box_values_field, field5, field6 from my table
where my_condition = 'met' |