How 2 store values through Stored Procedure from ARRAYLISTwhich is from Database only

[KalariaNitya]

hi All,

i am working on ASP.NET and Sql Server 2005.

i want to send arraylist from frontend to sql server and fatch it's value in database and then insert one by one row..

means in my arraylist more than one rows are present. and i want to send that arraylist in database and then fatch value from that arraylist and then store.

i want to use database connection only one time to send arraylist.

can any body help me, how to do that?

any help will be really appriciated..

[r035198x]

I'm still not clear on one thing. Do you want to store the ArrayList it self in the database or just the ArrayList's contents?

[KalariaNitya]

first of all Thanks r035198x ,

i have to store arraylist contant in database

i have one arraylist which has values with comma(,) seperated...

it contains more then one row.

i send it already in database

now i want to fatch values which is comma(,) seperated & stored in table...

now u get what i want?

[r035198x]

Maybe post some relevant code and explain the part that you are stuck with now.

[KalariaNitya]

hi,
i made stored procedure for that..

 Alter procedure b
(    
    @code varchar(Max),
    @policyid varchar(Max),
    @sign varchar(Max),
    @discount varchar(Max),
    @commission varchar(Max)
)
As
Begin
    --for code
    declare @varcode varchar(MAX)
    declare @splitcode varchar(MAX)
 
    --for policyid
    declare @varpolicyid varchar(MAX)
    declare @splitpolicyid varchar(MAX)
 
    --for discountedsign 
    declare @varsign varchar(MAX)
    declare @splitsign varchar(MAX)
 
    --for discountgiventoclients
    declare @vardiscount varchar(MAX)
    declare @splitdiscount varchar(MAX)
 
    --for commissiongivenbycompany
    declare @varcommission varchar(MAX)
    declare @splitcommission varchar(MAX)
 
    set @varcode= @code
    set @varpolicyid=@policyid
    set @varsign=@sign
    set @vardiscount=@discount
    set @varcommission=@commission
 
    print len(@varcode)
    print len(@varpolicyid)
    print len(@varsign)
    print len(@vardiscount)
    print len(@varcommission)
 
    while (len(@varcode) > 0 ) and (len(@varpolicyid) > 0 ) and (len(@varsign) > 0 ) and (len(@vardiscount) > 0)    and (len(@varcommission) > 0 ) 
        begin
            set @splitcode         =  substring(@varcode,0,charindex(',',@varcode))
            set @splitpolicyid   =  substring(@varpolicyid,0,charindex(',',@varpolicyid))
            set @splitsign       =  substring(@varsign,0,charindex(',',@varsign))
            set @splitdiscount   =  substring(@vardiscount,0,charindex(',',@vardiscount))
            set @splitcommission =  substring(@varcommission,0,charindex(',',@varcommission))
 
                print @splitcode
                print @splitpolicyid
                print @splitsign
                print @splitdiscount
                print @splitcommission
 
            set @varcode = substring(@varcode,charindex(',',@varcode)+1,len(@varcode))
            set @varpolicyid = substring(@varpolicyid,charindex(',',@varpolicyid)+1,len(@varpolicyid))
            set @varsign = substring(@varsign,charindex(',',@varsign)+1,len(@varsign))
            set @vardiscount = substring(@vardiscount,charindex(',',@vardiscount)+1,len(@vardiscount))
            set @varcommission = substring(@varcommission,charindex(',',@varcommission)+1,len(@varcommission))
 
                print len(@varcode)
    print len(@varpolicyid)
    print len(@varsign)
    print len(@vardiscount)
    print len(@varcommission)
 
            insert into commission_master
            (
                code,
                policyid,
                discountedsign,
                discountgiventoclients,
                commissiongivenbycompany
            )
            values
            (
                @splitcode,
                @splitpolicyid,
                @splitsign,
                @splitdiscount,
                @splitcommission
            )            
        end
End

exec b 'A0000001,BR000036,SA000038','p0000001,p0000008,p0 000009','<=,<=,>','10,20,30','40,50,60'


up to two rows it instered successfully

but when the time of instering third row
it gives error

Msg 8114, Level 16, State 5, Procedure b, Line 69
Error converting data type varchar to numeric.

out put up to two rows like below:

1 A0000001 p0000001 <= 10 40
2 BR000036 p0000008 <= 20 50

please help me...

thanking u in advanced...

[r035198x]

..

Msg 8114, Level 16, State 5, Procedure b, Line 69
Error converting data type varchar to numeric.

...

The error message does seem to be explaining what the problem is ...