SQL Parameter Object Type Mapping

Question

I get a "No Mapping exists" error on my code that should be inserting records into a SQL Server table. I can tell that it has something to do with the parameter setup I have, but not much more than that. From my googling, my basic code structure seems to line up with how to use SQL parameters and C# but I'm clearly missing something. Image below of offending code:

EDIT Per Feedback, adding code block:

            using (SqlConnection connection = new SqlConnection(ConnHelper.CnnVal("DWTest")))
        {
            connection.Open();
            SqlParameter jsonText = new SqlParameter("@Param1", System.Data.SqlDbType.NVarChar, -1)
            {
                Value = PostResult
            }; //-1 = max
            string sqlCMD =
                "DECLARE @json NVARCHAR(MAX) " +
                " SET @json = N'[ @Param1 ]'" +
                "DECLARE @FormattedJson NVARCHAR(MAX) = REPLACE(REPLACE(@json, '{\"success\":1,\"data\":[',''),']}','')" +
                  "Insert into Staging.CDOPSTest" +
                  "  SELECT *  FROM OPENJSON(@FormattedJson) " +
                  "  WITH (" +
                  "CallFrom char(10) 'strict $.call_from'," +
                  "Direction BIT '$.direction'," +
                  "[Hour] INT '$.hour'," +
                  "[Minute] INT '$.minute'," +
                  "ReachedServerLevel CHAR(5) '$.reached_service_level'," +
                  "CallDate NVARCHAR(256) '$.date'," +
                  "TalkTime INT '$.talk_time'," +
                  "AnswerSpeed INT '$.speed_to_answer'," +
                  "DID INT '$.did'," +
                  "StartTime NVARCHAR(256) '$.start_time'," +
                  "Endtime NVARCHAR(256) '$.end_time'," +
                  "ContactID  BIGINT '$.contact_id'," +
                  "AccountType NVARCHAR(256) '$.account_type'" +
                  ")";

            SqlCommand cmd = new SqlCommand(sqlCMD, connection);
            cmd.Parameters.AddWithValue("Param1", jsonText);
            cmd.ExecuteNonQuery();
            connection.Close();
        }

Richardissimo · Accepted Answer · 2019-02-02 00:48:36Z

You are attempting to pass an SqlParameter as the object value of your SqlParameter. The error message is literally saying that it doesn't know what SQL type to convert that into.

You appear to be trying to pass the string as a parameter, so you just need do...

cmd.Parameters.Add(jsonText);

Rather than...

cmd.Parameters.AddWithValue("Param1", jsonText);

Which is passing the SqlParameter object as the value of your parameter.

Then you need to fix how you're using it in your query - you're passing it as @Param1 so there's no need to create a @json parameter and try get it into that by using a literal value in a string, which won't work - you'll just get that literal string, not the value of the parameter.

SqlCommand is IDisposable so should be in a using block.

There's no need to Close the connection because it will be closed by the implicit Dispose as it exits the connection's using block.

I'd advise reading can we stop using AddWithValue.

zneak · Accepted Answer · 2019-01-29 20:24:15Z

0

Use Add(SqlParameter) instead of AddWithValue(String, Object), as the latter is a convenience method that attempts to create an SqlParameter from the Object that was passed. (Or, alternatively, try AddWithValue("@Param1", PostResult).)

answered Jan 29, 2019 at 20:24

zneak

139k48 gold badges270 silver badges342 bronze badges

3 Comments

MPJ567 Over a year ago

cmd.Parameters.AddWithValue("@Param1", jsontext) results in the same error. cmd.Parameters.Add("@Param1", jsontext) results in the same error. cmd.Parameters.Add(jsontext) results in: System.Data.SqlClient.SqlException: 'JSON text is not properly formatted. Unexpected character '@' is found at position 2.' I just noticed you referenced PostResult in your answer, but it shouldn't be jsonText right? Since that is the name of my sqlParameter variable?

zneak Over a year ago

@MPJ567, that’s not the same error. The new one says your json is broken.

MPJ567 Over a year ago

I'm fairly confident thats a false positive error. What that is saying is that '@Param1' was passed into the sql just as the text of that. I can verify this by running the sql in ssms, and I receive the same error. This leads me to believe that using cmd.Parameter.Add is either incorrect, or I am using it incorrectly. Your continued help is appreciated!

Collectives™ on Stack Overflow

SQL Parameter Object Type Mapping

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related