"SqlParameterCollection only accepts non-null SqlParameter type objects, not String objects"

I tried all the above-mentioned steps but nothing helps.

Finally found and fixed it by changing the namespace

using System.Data.SqlClient to using Microsoft.Data.SqlClient

Reference: https://learn.microsoft.com/en-us/sql/connect/ado-net/introduction-microsoft-data-sqlclient-namespace?view=sql-server-ver16

I replaced my reference to System.Data.SqlClient with Microsoft.Data.SqlClient corrected the using statements and my problems went away

In my .csproj I now have

  <ItemGroup>
    <PackageReference Include="Microsoft.Data.SqlClient" Version="1.1.2" />
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="3.1.3" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="3.1.3" />
  </ItemGroup>

But I also found I had use of the following scenario where I created a System.Data.SqlClient.SqlParameter

    public static List<T> RunQuery<T>(ApiDbContext context, string query, Func<DbDataReader, T> map, params SqlParameter[] parameters)
    {
        var cn = context.Database.GetDbConnection();
        var oldState = cn.State;
        if (cn.State.Equals(ConnectionState.Closed)) { cn.Open(); }

        using (var command = cn.CreateCommand())
        {
            command.CommandText = query;
            command.CommandType = CommandType.Text;
            foreach (var param in parameters)
            {
                var p = new System.Data.SqlClient.SqlParameter
                {
                    ParameterName = param.ParameterName, Value = param.Value, SqlDbType = param.SqlDbType
                };
                command.Parameters.Add(p);
            }
            if (cn.State.Equals(ConnectionState.Closed)) { cn.Open(); }
            var entities = new List<T>();
            using (var result = command.ExecuteReader())
            {
                while (result.Read())
                {
                    entities.Add(map(result));
                }
            }

            if (oldState.Equals(ConnectionState.Closed) && cn.State == ConnectionState.Open) { cn.Close(); }
            return entities;
        }
    }

I had been getting the same error and had to use AddWithValue like this...

cmd.Parameters.AddWithValue(@columnToUpdate, newValue);
cmd.Parameters.AddWithValue(@conditionalColumn, conditionalValue);

Try below

comm.Parameters.Add("@author", SqlDbType.VarChar);
comm.Parameters["@author"].Value = dataGridView1.Rows[i].Cells[0].Value.ToString();

I ran into the same issue, but mine was doing an object[2] = object[1] as SqlParameters, similar to what was being tried.

Just to add to the thread, I have this simple object array of SqlParameters added from a method like this,

    private SqlParameter GetGenericParamsObject(string name, object data)
    {
        return new SqlParameter(name, SetSqlDataType(data.GetType().ToString())) { Direction = Input, Value = data };
    }

Where there is a simple switch for the SetSqlDataType(), i.e. SqlDbType.Int is one of the return types to set it.

Then I run

    private static void ExecuteSqlCommand(DbContext dbContext, string sql, params object[] sqlParameters)
    {
        try
        {
            if (dbContext.Database.Connection.State == ConnectionState.Closed)
                dbContext.Database.Connection.Open();
            var cmd = dbContext.Database.Connection.CreateCommand();
            cmd.CommandText = sql;
            foreach (var param in sqlParameters)
                cmd.Parameters.Add(param);
            cmd.ExecuteNonQuery();
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
            throw;
        }
    }

This helps with casting to the proper data type and moves the param name, datatype and object out of the command method for easier debugging and then just adding the entire SqlParameter to the cmd.Parameters.

For any future readers of this question - the examples deal with .Add(), but .Remove() will also throw this error if not used correctly. The proper way to use .Add(), as has been documented by others is :

cmd.Parameters.AddWithValue("Key", "Value");

The proper way to use .Remove() is:

cmd.Parameters.Remove(command.Parameters["Key"]); 

I ran into the same error when I was using

cmd.Parameters.Add(pars)

where pars was a an array of SqlParameter. The issue was that I was using the Add() function but I should've used AddRange() instead.

cmd.Parameters.AddRange(pars)

The following simple solution works for me:

SqlCommand command = ...
command.Parameters.Add(parameterPlaceholder, SqlDbType.Char);
command.Parameters[parameterPlaceholder].Value = (object)parameterValue ?? DBNull.Value;

Replacing a null value with "DBNull.Value" is the key. Don't forget to adapt the SqlDbType to your needs, in the example above.

I also got this error using System.Data.Entity.Database

In that case with EF 6.4 I needed to use something like

  var parameters = new List<SqlParameter> parameters  // using System.Data.SqlClient
  sql = $"{sql} and ( headkey like @headkey)";
  parameters.Add(new SqlParameter("@headkey", $"%{headKey}%") { DbType = DbType.String });

try this!! It would help

I've been trying this way in my project, it solve my problem

cmd.Parameters.Add(new SqlParameter("@author", SqlDbType.VarChar));

StrQuery = "INSERT INTO BooksData 
VALUES(@author,@title,@genre,@price,@publish_date,@description,@bookid)";

cmd.Parameters.AddWithValue("@author", dataGridView1.Rows[i].Cells[0].Value.ToString());

cmd.ExecuteNonQuery();