Passing Array to Stored Procedure and getting the error "Object must implement IConvertible"

Question

Post reply

Passing Array to Stored Procedure and getting the error "Object must implement IConvertible"

Snigdha Sinha

SSC-Addicted

Points: 470
More actions
September 29, 2005 at 4:47 pm

#87947

Hi, I needed to pass an array to my stored procedure. I have a CHeckBox List on my webform. For every checked box in the list I have to insert a value in the database. Hence I read the values of the checked boxes in an array of string. This is the code I have written in VB.Net
If
chkDocumentList.Items(i).Selected = True Then
CheckedDocumentList(i) = chkDocumentList.Items(i).Value
End If
cmdSetData =
New SqlCommand("sproc_addDocumentList", conn)
cmdSetData.CommandType = CommandType.StoredProcedure
prmTestRequest =
New SqlParameter("@TestRequestNo", SqlDbType.VarChar)
prmTestRequest.Direction = ParameterDirection.Input
prmTestRequest.Value = "TR1100"
cmdSetData.Parameters.Add(prmTestRequest)
prmTestRequest =
New SqlParameter("@DocumentList", SqlDbType.VarChar)
prmTestRequest.Direction = ParameterDirection.Input
prmTestRequest.Value = CheckedDocumentList
cmdSetData.Parameters.Add(prmTestRequest)
Try
cmdSetData.ExecuteNonQuery()
Catch err
Response.Write(err.Message)
End Try
My Stored Procedure is as follows:
ALTER PROCEDURE sproc_addDocumentListTestRequest
(@DocumentList varchar(10),
@TestRequestNo varchar(50))
AS
PRINT @DocumentList
GO
When I run this it gives me the error, Object must implement IConvertible. Please do help me with this.
Thanks,
Snigdha

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Carl Federl One Orange Chip Points: 25384 More actions · Answer 1

SQL Server does not support arrays.

One altenative is to pass XML (see SQL Server Books on Line for parsing XML in stored procedures)

See also "Arrays and Lists in SQL Server" by Erland Sommarskog, SQL Server MVP at http://www.sommarskog.se/arrays-in-sql.html

SQL = Scarcely Qualifies as a Language

Rob Reid-246754 SSCarpal Tunnel Points: 4183 More actions · Answer 2

You could pass the data into your stored proc as a comma delimited list of values eg 12,14,16,23

I have used this method in ASP when saving selected items from a multiple select listbox. Just pass the value into a stored proc that does something along these lines.

CREATE PROCEDURE [usp_asp_save_data]

@pk int,

@Values varchar(200) --must allow for comma seperated lists for multiple select boxes

AS

declare @val int,

@separator char(1),

@separator_position int

set @separator = ','

if charindex(@separator,@values)>0

begin --we have a comma delimited string of multiple values to sort out

if right(@values,1)@separator

set @values = @values + @separator

while PATINDEX('%' + @separator + '%' , @Values) 0

begin

select @separator_position = PATINDEX('%' + @separator + '%' , @Values)

select @val = cast(LTRIM(RTRIM(LEFT(@Values, @separator_position - 1)))as int)

INSERT INTO tbl_DATA_CATEGORIES_VALUES

(PK, CategoryFK)

VALUES

(@PK, @val)

-- This replaces what we just processed with and empty string

select @Values = STUFF(@Values, 1, @separator_position, '')

end

Maybe this might help?

Snigdha Sinha SSC-Addicted Points: 470 More actions · Answer 3

Hi,

This not only helped, but was exactly what I wanted. I mimplemented it with very minor changes and my code is working so perfect.

Thanks a lot once again for the so many tips you have provided,

Snigdha