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


[CODE=sql] 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 discountgivento clients
declare @vardiscount varchar(MAX)
declare @splitdiscount varchar(MAX)

--for commissiongiven bycompany
declare @varcommission varchar(MAX)
declare @splitcommissio n varchar(MAX)

set @varcode= @code
set @varpolicyid=@p olicyid
set @varsign=@sign
set @vardiscount=@d iscount
set @varcommission= @commission

print len(@varcode)
print len(@varpolicyi d)
print len(@varsign)
print len(@vardiscoun t)
print len(@varcommiss ion)

while (len(@varcode) > 0 ) and (len(@varpolicy id) > 0 ) and (len(@varsign) > 0 ) and (len(@vardiscou nt) > 0) and (len(@varcommis sion) > 0 )
begin
set @splitcode = substring(@varc ode,0,charindex (',',@varcode))
set @splitpolicyid = substring(@varp olicyid,0,chari ndex(',',@varpo licyid))
set @splitsign = substring(@vars ign,0,charindex (',',@varsign))
set @splitdiscount = substring(@vard iscount,0,chari ndex(',',@vardi scount))
set @splitcommissio n = substring(@varc ommission,0,cha rindex(',',@var commission))

print @splitcode
print @splitpolicyid
print @splitsign
print @splitdiscount
print @splitcommissio n

set @varcode = substring(@varc ode,charindex(' ,',@varcode)+1, len(@varcode))
set @varpolicyid = substring(@varp olicyid,charind ex(',',@varpoli cyid)+1,len(@va rpolicyid))
set @varsign = substring(@vars ign,charindex(' ,',@varsign)+1, len(@varsign))
set @vardiscount = substring(@vard iscount,charind ex(',',@vardisc ount)+1,len(@va rdiscount))
set @varcommission = substring(@varc ommission,chari ndex(',',@varco mmission)+1,len (@varcommission ))

print len(@varcode)
print len(@varpolicyi d)
print len(@varsign)
print len(@vardiscoun t)
print len(@varcommiss ion)

insert into commission_mast er
(
code,
policyid,
discountedsign,
discountgivento clients,
commissiongiven bycompany
)
values
(
@splitcode,
@splitpolicyid,
@splitsign,
@splitdiscount,
@splitcommissio n
)
end
End[/CODE]




exec b 'A0000001,BR000 036,SA000038',' p0000001,p00000 08,p0000009','< =,<=,>','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 ...