ASP.NET - Listbox - Asked By swaroop on 14-Jul-10 06:07 AM

How to insert multiple selected listbox value or text in to sql database
Anand Malli replied to swaroop on 14-Jul-10 06:37 AM

Hi Swaroop


For this i have written one SQL Function which takes Comma sepreated string which you will get it from your listbox,loopiing through your items and making comma delimited string right and you will pass it to the function,below is the code of function as well as how to call it to insert all of them in table,you just have to copy and paste this Function to your sql query editor and run it,hence it will create function,now after that you will have to call it from one of the SP so that it can insert those data into your table


CREATE FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))    
returns @temptable TABLE (id integer,items varchar(8000))    
as    
begin    
  declare @idx int    
  declare @slice varchar(8000)    
  declare @idcount int
     
   set @idcount = 0  
  select @idx = 1    
    if len(@String)<1 or @String is null  return    
     
  while @idx!= 0    
  begin    
    set  @idcount= @idcount+ 1
    set @idx = charindex(@Delimiter,@String)    
    if @idx!=0    
      set @slice = left(@String,@idx - 1)    
    else    
      set @slice = @String    
       
    if(len(@slice)>0)   
      insert into @temptable(Items,id) values(@slice,@idcount)    
    
    set @String = right(@String,len(@String) - @idx)    
    if len(@String) = 0 break    
  end  
return    
end


here first argument is comma delimited string and second argument is which is the char to split the string


insert into <your table name>(<your col name>)
SELECT items FROM  split(@Ids,',')


here split is the name of above function and @Ids is argument which you will pass by making comma delimited string to this SP


and this is working code


enjoy

thxs

Super Man replied to swaroop on 14-Jul-10 09:28 AM

foreach (ListBox l in l1.SelectedItems)
{
string val = l.Text;
//code to insert val variable to insert in database

}