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

Question

I keep getting the exception

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

while executing the following code:

string StrQuery;
using (SqlConnection conn = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=SanFransiscoData;Integrated Security=True;Pooling=False"))
{
    using (SqlCommand comm = new SqlCommand())
    {
        comm.Connection = conn;
        conn.Open();
       // SqlParameter author = new SqlParameter("@author", dataGridView1.Rows[0].Cells[0].Value.ToString());
        comm.Parameters.Add("@author", SqlDbType.VarChar);
        comm.Parameters.Add("@title", SqlDbType.NVarChar);
        comm.Parameters.Add("@genre", SqlDbType.VarChar);
        comm.Parameters.Add("@price", SqlDbType.Float);
        comm.Parameters.Add("@publish_date", SqlDbType.Date);
        comm.Parameters.Add("@description", SqlDbType.NVarChar);
        comm.Parameters.Add("@bookid", SqlDbType.VarChar);
        for (int i = 0; i < dataGridView1.Rows.Count; i++)
        {
            StrQuery = "INSERT INTO BooksData VALUES(@author,@title,@genre,@price,@publish_date,@description,@bookid)";
            comm.Parameters.Add(dataGridView1.Rows[i].Cells[0].Value.ToString());
            comm.Parameters.Add(dataGridView1.Rows[i].Cells[1].Value.ToString());
            comm.Parameters.Add(dataGridView1.Rows[i].Cells[2].Value.ToString());
            comm.Parameters.Add(Convert.ToDecimal(dataGridView1.Rows[i].Cells[3].Value));
            comm.Parameters.Add(Convert.ToDateTime(dataGridView1.Rows[i].Cells[4].Value));
            comm.Parameters.Add(dataGridView1.Rows[i].Cells[5].Value.ToString());
            comm.Parameters.Add(dataGridView1.Rows[i].Cells[6].Value.ToString());
            comm.CommandText = StrQuery;
            comm.ExecuteNonQuery();
        }
    }
}

Please tell me where I'm going wrong.

Earful

The error message is straightforward. You should not be adding strings to the Parameters collection. Create a new SqlParameter object and add that to the collection. – Hackney

possible duplicate of The SqlParameterCollection only accepts non-null SqlParameter type objects, not DBNull objects – Hamrnand

In my case, I have used cmd.Parameters.AddRange and pass List<object>, This list should contain SqlParameter, but by mistake, I have passed one string object. – Injection

Answer 1

When you use Add method, you are trying to add a new parameter. What you want to do is to assign value. So change this:

comm.Parameters.Add(dataGridView1.Rows[i].Cells[0].Value.ToString());

to this:

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

Similarly for the other parameters.

Concelebrate

Answer 2

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

Gonyea

Thank you for the answer. It helped. The type 'SqlParameter' exists in both namespaces that Dah Sra mentioned. And System.Data.SqlClient goes first in intellisence helper in Visual Studio. – Discountenance

I think System.Data.SqlClient is being retired. – Kowalczyk

Swapped the dependencies and got same error, but in different DLL: The SqlParameterCollection only accepts non-null Microsoft.Data.SqlClient.SqlParameter type objects, not System.String objects. – Solmization

Answer 3

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;
        }
    }

Hixon

I honestly thought there's something wrong with visual studio but then I tried casting one to the other. – Rosalie

Answer 4

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

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

Underneath

But AddWithValue will generate different length of parameters for SQL Server and different execution plans will be created. It's better to add the parameter and specify the length (example: searching for @name='Alex' or searching for @name='Martin' will generate a parameter of VARCHAR(4) for Alex and VARCHAR(6) for Martin and different execution plans in SQL Server) – Flosser

Microsoft.Data.SqlClient.SqlParameterCollection no longer has AddWithValue – Hixon

Don't use AddWithValue link – Cavalryman

Answer 5

Try below

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

Hargreaves

Answer 6

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.

Kehr

Answer 7

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"]); 

Prestigious

Answer 8

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)

Cushman

Answer 9

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.

Helot

Answer 10

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 });

Hixon

Answer 11

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();

Vernievernier