Error Passing ARRAY for Stored Procedure Query

Question

Iam trying to pass an array to a method then using it to a Stored procedure

protected void Button1_Click(object sender, EventArgs e)
{
    string[] names = new string[3] { "Matt", "Joanne", "Robert" };
    string User = "Employee";
    mav.AccessType(names,User);
}

METHOD:

   public void AccessType(String[] ARR, String Userty)
{
    SysCon.Open();
    SqlCommand cmdC = new SqlCommand("sp_AccessTypes", SysCon);
    cmdC.CommandType = CommandType.StoredProcedure;
    cmdC.Parameters.AddWithValue("@ARRAY", ARR);
    cmdC.Parameters.AddWithValue("@USER", Userty);
    cmdC.ExecuteNonQuery();
    SysCon.Close();
}

STORED PROC

ALTER PROCEDURE [dbo].[sp_AccessType] 
-- Add the parameters for the stored procedure here
@ARRAY AccessType1 Readonly,
@USER nvarchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;


INSERT INTO tbl_samplednd1(UserType,parameter1) SELECT * FROM @ARRAY
END

it gives an error:

No mapping exists from object type System.String[] to a known managed provider native type.

How to fix it? Thank you

Can you tell us the parent type of AccessType1 on the SQL server? — Abdullah Nehir, Mar 08 '17 at 07:00
You can't pass an array as a SQL Server's parameter, because it does not support arrays. — Salah Akbari, Mar 08 '17 at 07:00
Possible duplicate of [How to pass table value parameters to stored procedure from .net code](http://stackoverflow.com/questions/5595353/how-to-pass-table-value-parameters-to-stored-procedure-from-net-code) — , Mar 08 '17 at 07:57

score 0 · Accepted Answer · edited May 23 '17 at 10:29

MS SQL Server does not support arrays to be given as SP parameters. As you said in your comment, the @ARRAY parameter is not an array either, it is a user defined table type.

Thus you should prepare a System.Data.DataTable in your .net code and pass it as the parameter.

You can find a good example in this question:

How to pass table value parameters to stored procedure from .net code

Your DataTable should contain at least two rows that has the information to fill UserType and parameter1 columns of the table tbl_samplednd1

Error Passing ARRAY for Stored Procedure Query

1 Answers1