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. |
|
|
|
|