Click here to Skip to main content
15,891,529 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
No mapping exists from object type System.Int32[] to a known managed provider native type.


What I have tried:

SqlConnection conn = new SqlConnection("Data Source=EME-01-LAP17\\SQLEXPRESS;Initial Catalog=my_sql;Integrated Security=True");
           SqlDataAdapter ada = new SqlDataAdapter();
           SqlCommand cmd = new SqlCommand("INSERT INTO Table_1 VALUES(@Diagnostic_Data)", conn);
           cmd.Parameters.AddWithValue("@Diagnostic_Data", diagnostic_data);
           conn.Open();
           cmd.ExecuteNonQuery();
           conn.Close();
Posted
Updated 15-Mar-20 22:23pm

It's exactly what the error says:
No mapping exists from object type System.Int32[] to a known managed provider native type.
You are passing an array of integers - diagnostic_data - to your SQL query for INSERT into your table but SQL does not support arrays.

I have no idea what your DB looks like, what exactly you expect to be storing, or even whet kind of column your data is supposed to be inserted into: but you can't pass SQL an array of integers.

Start with "is an array of integers what you meant to pass?", then check the column type and decide what you should be storing in it.

And do yourself a favour: never do
SQL
INSERT INTO MyTable VALUES (...
Always list the columns you expect to insert to in order - it makes your app more resilient when your DB changes:
SQL
INSERT INTO MyTable (MyColumn1, MyCOlumn2, ... ) VALUES (...
 
Share this answer
 
Comments
Jon McKee 16-Mar-20 4:29am    
+5 Excellent tip with the explicit columns!
You are trying to insert an integer array into an int database field.
You should loop through your array and insert each single integer value.
 
Share this answer
 
Let's break down the error:
No mapping

So we're dealing with some conversion between values.
System.Int32[]

So the source data is of type int32[] which would be diagnostic_data given your code.
known managed provider native type.

A type that the SQL adapter (the provider) you're using knows about for the database. As in, it doesn't know of a database type that it can use in this situation.

So putting it all together: int32[] has no native database type it can convert into. This makes complete sense. When you use AddWithValue, you're creating an SqlParameter[^]. An SqlParameter is treated as a single literal value for safety reasons (sql injection). Since an array of integers has no obvious single literal value it can convert into, you get the error.

So how to fix it? I rather like this solution[^] found right here on CodeProject. You could also manually build an array of SqlParameters, something like:
C#
SqlCommand cmd = new SqlCommand("INSERT INTO Table_1 VALUES(@value0, @value1)"); /*add however many values you need*/
List<SqlParameter> commands = new List<SqlParameter> {
    new SqlParameter("@value0", diagnostic_data[0]),
    new SqlParameter("@value1", diagnostic_data[1])
    //etc..
};
cmd.Parameters.AddRange(commands);

To my knowledge though, there's no way to do this with a single parameter due to the way parameters are handled for safety.
 
Share this answer
 
v5

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900