No mapping exists from object type system.int32[] to a known managed provider native type.

Question

1.00/5 (1 vote)

See more:

C#

cmd = new SqlCommand("INSERT INTO Table_2 VALUES (@Diagnostic_Data,@Error_Codes,@Input_Output,@Shift_Inhibits) ", conn);
                   
  cmd.Parameters.Clear();
  cmd.Parameters.AddWithValue("@Diagnostic_Data", display_diagnostic_data);
  cmd.Parameters.AddWithValue("@Error_Codes", Error_Codes);
  cmd.Parameters.AddWithValue("@Input_Output", Input_Output);
   cmd.Parameters.AddWithValue("@Shift_Inhibits", Shift_Inhibits);
      conn.Open();
      cmd.ExecuteNonQuery();//error
       conn.Close();

What I have tried:

C#

cmd = new SqlCommand("INSERT INTO Table_2 VALUES (@Diagnostic_Data,@Error_Codes,@Input_Output,@Shift_Inhibits) ", conn);
                   
  cmd.Parameters.Clear();
  cmd.Parameters.AddWithValue("@Diagnostic_Data", display_diagnostic_data);
  cmd.Parameters.AddWithValue("@Error_Codes", Error_Codes);
  cmd.Parameters.AddWithValue("@Input_Output", Input_Output);
   cmd.Parameters.AddWithValue("@Shift_Inhibits", Shift_Inhibits);
      conn.Open();
      cmd.ExecuteNonQuery();//error
       conn.Close();

Posted 23-Jun-20 0:44am

Member 14120682

Updated 25-Jun-20 5:10am

Maciej Los

v2

Add a Solution

Comments

F-ES Sitecore 23-Jun-20 6:57am

One of your param values will be an int array which you can't set as a SQL parameter as SQL doesn't have a suitable type to use. The solution depends on how you want to handle the array parameter which we don't know. It might be you need to turn it into a string like "1,2,3,4" or that you need to call your SP once for each item in the array.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2020-06-23T01:08:00

You can't pass an array of integers as an object to SQL - SQL has no concept of arrays, so it doesn't know what to do with it. The only array type you can send directly is a byte[] to a VARBINARY or similar field.

We have no idea which of those parameters is an array of integers - though I'd suspect Error_Codes - but the chances are you will need to either send each value separately to a new row, or change your DB and send them as bytes of a string (though the latter is generally a bad idea).

In addition, you should always list the columns into which you wish to INSERT:

SQL

INSERT INTO Table_2 (DiagData, ErrCodes, InOut, ShiftInhibits) VALUES (@Diagnostic_Data,@Error_Codes,@Input_Output,@Shift_Inhibits)

If you don;t then SQL starts with the current "left most" column, and tries to insert the data in sequence. If you tabel starts with an ID row - and it should - then SQL will try to write to that column first, and you can get some nasty errors that way.

MadMyche · Answer 2 · 2020-06-25T05:10:00

Without knowing what your table structure is and what the data is that you are passing... it is really hard to help you.
Not only that; while the code is within C# the actual problem you are experiencing is within SQL. And again you not specified what kind of SQL you are actually using

So I am going to answer this based on:
1. OriginalGriff is correct with the Error_Codes being an array.
2. You are using a version of (MS) SQL Server released within the last 5 years.

If this was my task; I would
_ Create a Stored Procedure on the SQL Server to do this
_ Pass the array value to the Stored Procedure as a comma-delineated string

Stored Procedure would utilize the String_Split function, and returns a table with one row per delineated value.

SQL

CREATE PROCEDURE dbo.Table2_Insert (
   @Diagnostic_Data NVARCHAR(100),
   @Error_Codes     NVARCHAR(100),
   @Input_Output    NVARCHAR(100),
   @Shift_Inhibits  NVARCHAR(100)
) AS
BEGIN
   INSERT into Table_2
   SELECT @Diagnostic_Data
        , value
        , @Input_Output
        , @Shift_Inhibits
   FROM String_Split('@Error_Codes', ',');
END
GO

Within your C# code, there would be minimal alterations
_ Change the SQL Command Text
_ Set the SQL Command CommandType
_ Convert the Error_Codes array to a delineated-string

C#

cmd = new SqlCommand("Table2_Insert", conn);

cmd.CommandType = CommandType.StoredProcedure;
                   
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@Diagnostic_Data", display_diagnostic_data);
cmd.Parameters.AddWithValue("@Error_Codes", String.Join(",", Error_Codes));
cmd.Parameters.AddWithValue("@Input_Output", Input_Output);
cmd.Parameters.AddWithValue("@Shift_Inhibits", Shift_Inhibits);

conn.Open();
   int RowsAffected = cmd.ExecuteNonQuery();
/* NOTE
   RowsAffected will tell you how many rows were added.
   You could compare this to your arrays.length value
*/
conn.Close();

References:
MS Docs: SQL Split_String()[^]
MS DOCS: C# String.Join()[^]

No mapping exists from object type system.int32[] to a known managed provider native type.

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0