sham
going with the flow
Posts:40
 |
| 10-10-2009 06:44 PM |
|
How do I prepare a stored procedure to insert data into a table from a listbox with multiple selections? Please provide an example. |
|
|
|
|
David To
river guide
Posts:2719
|
| 10-12-2009 07:10 AM |
|
Hi, a multi-select listbox will save your data like this:
Let's say you have options "one,two,three" in your listbox. If you select "one" and "three", the shortname field will yield:
one,three
so the values you pick are separated by a comma, just like a checkbox group option. You can save this entire value to a column in your table your extract the results by comma separator and store each answer in it's own table column. A quick store procedure would be:
create procedure usp_insertSQL (@listbox nvarchar(2000))
as
begin
insert into sqltable(listbox) values(@listbox)
end
You would call the store procedure in your Dynamic Forms SQL form completion event:
usp_insertSQL '$(listbox)'
-- David |
|
|
|
|
sham
going with the flow
Posts:40
|
| 10-12-2009 07:38 AM |
|
will selection one & three appear in its own record respectively? or will it appear in the same record / column as the following "one,three"? If it's going to appear in the same record / column how can I get the selections to appear in it's own records? |
|
|
|
|
David To
river guide
Posts:2719
|
| 10-12-2009 08:00 AM |
|
HI, it will store as "one,three" in same column. You will need to use SQL to parse out the data column by ",". It would be something like:
DECLARE @Delimeter char(1),@option nvarchar(100)
SET @Delimeter = ','
--Parse the string
DECLARE @StartPos int, @Length int
WHILE LEN(@listbox) > 0
BEGIN
SET @StartPos = CHARINDEX(@Delimeter, @listbox)
IF @StartPos < 0 SET @StartPos = 0
SET @Length = LEN(@listbox) - @StartPos - 1
IF @Length < 0 SET @Length = 0
IF @StartPos > 0
BEGIN
SET @option = SUBSTRING(@listbox, 1, @StartPos - 1)
SET @listbox= SUBSTRING(@listbox, @StartPos + 1, LEN(@listbox) - @StartPos)
END
ELSE
BEGIN
SET @option= @listbox
SET @listbox= ''
END
END
-- David |
|
|
|
|
sham
going with the flow
Posts:40
|
| 10-12-2009 08:27 AM |
|
I am not an expert with SQL, so I am a little lost. If you could modify your sql script in relation to updating an actual table / fields, then maybe I can follow.
For example, let's say I have a table title "tbl_test" with two field names "test_id" (primary key) and field name "test_description". Now I have a listbox with two options selected, I want to insert two records in this "tbl_test" table. What sql statement would I need to execute to carry out this action? |
|
|
|
|
David To
river guide
Posts:2719
|
| 10-12-2009 08:54 AM |
|
create procedure usp_insert_tbl_test (@listbox nvarchar(2000))
as
begin
DECLARE @Delimeter char(1),@option nvarchar(100)
SET @Delimeter = ','
--Parse the string
DECLARE @StartPos int, @Length int
WHILE LEN(@listbox) > 0
BEGIN
SET @StartPos = CHARINDEX(@Delimeter, @listbox)
IF @StartPos < 0 SET @StartPos = 0
SET @Length = LEN(@listbox) - @StartPos - 1
IF @Length < 0 SET @Length = 0
IF @StartPos > 0
BEGIN
SET @option = SUBSTRING(@listbox, 1, @StartPos - 1)
insert into tbl_test(test_description) values(@option)
SET @listbox= SUBSTRING(@listbox, @StartPos + 1, LEN(@listbox) - @StartPos)
END
ELSE
BEGIN
SET @option= @listbox
insert into tbl_test(test_description) values(@option)
SET @listbox= ''
END
end
-- David |
|
|
|
|
sham
going with the flow
Posts:40
|
| 10-12-2009 09:00 AM |
|
ok, thank you very much, I think can follow it now. I will give it shut this evening. |
|
|
|
|